Re: Crappy performance even though not swapping - Mailing list pgsql-novice
From | Jim Stalewski |
---|---|
Subject | Re: Crappy performance even though not swapping |
Date | |
Msg-id | 2EA705769274A24C9637C6686F0B53E4DA31F2@OE.OG.local Whole thread Raw |
In response to | Re: Crappy performance even though not swapping ("Neil Aggarwal" <neil@JAMMConsulting.com>) |
List | pgsql-novice |
Neil, RAID1 sucks for write performance, in case you didn't know. Best for both read and write performance is RAID 10, next is IBM's RAID 5E, provided you have at least 4 drives in the RAID set - and that would require an IBM hardware RAID controller and not software RAID. RAID 10 gives you the same capacity as RAID 1 with better performance and very good fault-tolerance. I don't know if software RAID 10 is available to you or not. If you choose to switch to RAID 10, make sure your stripe size matches the PGSQL default blocksize for maximum performance. I believe that's 8K, IIRC. What filesystem are you running for your database files? Have you tweaked it for PGSQL performance? (Journaling comes to mind.) You don't want filesystem overhead adding to the time it takes for PGSQL table inserts. Depending on your configuration, using a separate physical device for transaction log files to keep the transaction logging separate from the table writes also may also improve table insert performance, IIRC. Jim. -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Neil Aggarwal Sent: Tuesday, February 27, 2007 9:19 PM To: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Crappy performance even though not swapping Philip: Thanks for the advice. If you look at my next post, I figured out the problem was an index on the table I was inserting into. In answer to your questions: 1. I have 4 SATA drives and am using software RAID1 on them. 2. I have not increased the default RAM for the pg process, but that does not seem to be a problem after I removed the index. Would adding more RAM to the process speed up the inserts even with the index on the table? Thanks, Neil -- Neil Aggarwal, (214)986-3533, www.JAMMConsulting.com FREE! Eliminate junk email and reclaim your inbox. Visit http://www.spammilter.com for details. -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Phillip Smith Sent: Tuesday, February 27, 2007 9:10 PM To: 'Neil Aggarwal'; pgsql-novice@postgresql.org Subject: Re: [NOVICE] Crappy performance even though not swapping What hard drives are you running? IDE / SCSI / SATA? Are they in a hardware / software RAID? PG still has to commit changes to disk regardless of swapping - there might be a bottleneck at the disk I/O for the commits. Also, your postmaster doesn't seem to be using much RAM. Have you tweaked the conf files to allow the pg processes to use more RAM than default? Cheers, ~p -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Neil Aggarwal Sent: Wednesday, 28 February 2007 13:50 To: pgsql-novice@postgresql.org Subject: [NOVICE] Crappy performance even though not swapping Hello: I have a Java program that is trying to import over 8 million rows from a text file. When the program starts up, it runs fine, but after a while it begins to crawl. This is usually symptomatic of a machine that started swapping, but take a look at the attached output from top. I am using a LOT of memory, but the swap is still very low. I don't think the machine should be paging to disk. The CPU states show that the cpus are spending most of their time in an idle state. The process list shows the postmaster and java processes are less than 10 percent the CPU cycles. Having said that, the load average is still about 2. Any ideas what could be going on? Thanks, Neil -- Neil Aggarwal, (214)986-3533, www.JAMMConsulting.com FREE! Eliminate junk email and reclaim your inbox. Visit http://www.spammilter.com for details. *******************Confidentiality and Privilege Notice******************* The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster This email and any files transmitted with it are confidential and intended solely for the use of the individual or entityto whom they are addressed. If you have received this email in error please notify the sender and delete it. Pleasenote that any views or opinions presented in this email are solely those of the author and do not necessarily representthose of the company. No employee or agent is authorized to conclude any binding agreement on behalf of Visa Lighting with another party by emailwithout express written confirmation by an authorized representative of the Company. Finally, the recipient should check this email and any attachments for the presence of viruses. The company accepts no liabilityfor any damage caused by any virus transmitted by this email.
pgsql-novice by date: