From: Scott Marlowe <smarlowe@g2switchworks.com>
To: arnaulist@andromeiberica.com
Cc: pgsql-admin@postgresql.org
Subject: Re: Where is my bottleneck?
Date: Tue, 24 Jan 2006 13:08:13 -0600
Message-ID: <1138129693.25819.35.camel@state.g2switchworks.com> (raw)
In-Reply-To: <43D67477.8020204@andromeiberica.com>
References: <43D67477.8020204@andromeiberica.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: smarlowe@g2switchworks.com, arnaulist@andromeiberica.com
Subject: Re: Where is my bottleneck?
In-Reply-To: <1138129693.25819.35.camel@state.g2switchworks.com>
References: <43D67477.8020204@andromeiberica.com> <1138129693.25819.35.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox