Re: Where is my bottleneck?

On Tue, 2006-01-24 at 12:39, Arnau 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
> 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?

Well, this might get more traction on the perform list, just fyi. Admin
is more generally for questions about adding users, setting permissions
and such.

That said, it looks like you're likely I/O bound.

Do you have Hyperthreading turned on? generally this results in slower,
not faster performance, as the caches / registers in the pseudo CPUs are
often thrashed harder by having it turned on. I've found that having it
turned off generally gives better performance under heavy parallel load.

The most common changes to look at making in postgresql.conf are to
raise shared buffers. A setting of a 1000 to 10000 is pretty common.

You might do better with a lower random_page_cost, normally between 1.4
and 2.0 is good. Any lower than that and you're likely to see index
scans chosen when seq scans are really the better choice.

You should really upgrade your version to the latest 7.4 branch.

There are a LOT of performance enhancements in 8.0/8.1. If you can
upgrade to the latest 8.1 version that might help as well.

Turn on the logging of long queries, and run explain analyze on one or
more of those long running queries, and post the output here.

What do iostat and vmstat have to say?

Does your RAID 5 have the option for batter backed cache?

Is your load mostly read, or a mix or read and write?

Read through this document:

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html