Обсуждение: Where is my bottleneck?
Hi all, I have a performance problem and I don't know where is my bottleneck. I have postgresql 7.4.2 running on a debian server with kernel 2.4.26-1-686-smp with two Xeon(TM) at 2.80GHz and 4GB of RAM and a RAID 5 made with SCSI disks. Maybe its not the latest hardware but I think it's not that bad. My problem is that the general performance is not good enough and I don't know where is the bottleneck. It could be because the queries are not optimized as they should be, but I also think it can be a postgresql configuration problem or hardware problem (HDs not beeing fast enough, not enough RAM, ... ) The configuration of postgresql is the default, I tried to tune the postgresql.conf and the results where disappointing, so I left again the default values. When I do top I get: top - 19:10:24 up 452 days, 15:48, 4 users, load average: 6.31, 6.27, 6.52 Tasks: 91 total, 8 running, 83 sleeping, 0 stopped, 0 zombie Cpu(s): 24.8% user, 15.4% system, 0.0% nice, 59.9% idle Mem: 3748956k total, 3629252k used, 119704k free, 57604k buffers Swap: 2097136k total, 14188k used, 2082948k free, 3303620k cached Most of the time the idle value is even higher than 60%. I know it's a problem with a very big scope, but could you give me a hint about where I should look to? Thank you very much -- Arnau
On Tue, Jan 24, 2006 at 07:40:22PM +0100, Arnau Rebassa Villalonga wrote: > I have a performance problem and I don't know where is my bottleneck. [snip] > Most of the time the idle value is even higher than 60%. It's generally a fairly safe bet that if you are running slow and your cpu is idle, your i/o isn't fast enough. Mike Stone
Arnau, On 1/24/06 10:40 AM, "Arnau Rebassa Villalonga" <arebassa@androme.es> wrote: > I know it's a problem with a very big scope, but could you give me a > hint about where I should look to? Try this: time bash -c "dd if=/dev/zero of=bigfile bs=8k count=2000000 && sync" time dd if=bigfile of=/dev/null bs=8k And report the results back here. If it takes too long to complete (more than a couple of minutes), bring up another window and run "vmstat 1" then report the values in the columns "bi" and "bo". - Luke
On Tue, Jan 24, 2006 at 07:40:22PM +0100, Arnau Rebassa Villalonga wrote: > Hi all, > > I have a performance problem and I don't know where is my bottleneck. > I have postgresql 7.4.2 running on a debian server with kernel You should really upgrade to the latest 7.4 version. You're probably vulnerable to some data-loss issues. > 2.4.26-1-686-smp with two Xeon(TM) at 2.80GHz and 4GB of RAM and a RAID > 5 made with SCSI disks. Maybe its not the latest hardware but I think Generally speaking, databases (or anything else that does a lot of random writes) don't like RAID5. > My problem is that the general performance is not good enough and I > don't know where is the bottleneck. It could be because the queries are > not optimized as they should be, but I also think it can be a postgresql > configuration problem or hardware problem (HDs not beeing fast enough, > not enough RAM, ... ) What kind of performance are you expecting? What are you actually seeing? > The configuration of postgresql is the default, I tried to tune the > postgresql.conf and the results where disappointing, so I left again the > default values. Probably not so good... you'll most likely want to tune shared_buffers, sort_mem and effective_cache_size at a minimum. Granted, that might not be your current bottleneck, but it would probably be your next. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Arnau Rebassa Villalonga wrote: > > The configuration of postgresql is the default, I tried to tune the > postgresql.conf and the results where disappointing, so I left again the > default values. That's the first thing to fix. Go to the page below and read through the "Performance Tuning" article. http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php -- Richard Huxton Archonet Ltd
Hi, Michael, Michael Stone wrote: >> I have a performance problem and I don't know where is my bottleneck. > > [snip] > >> Most of the time the idle value is even higher than 60%. > > It's generally a fairly safe bet that if you are running slow and your > cpu is idle, your i/o isn't fast enough. Or the query is misoptimized (low work_mem, missing indices) and cause much more I/O than necessary. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org