Thread: High load average with PostgreSQL 7.4.2 on debian/ibm eserver.

Hello,

I'm using PostgreSQL 7.4.2 (package from backports.org)
on a Debian (woody) box. The machine is IBM eServer 345
with two 2.8 Xeon CPUs, it has 1024MB of RAM and
two 15k RPM SCSI disks running in hardware RAID1, which
is provided by the onboard LSI Logic controller (LSI53C1030).

The database consists of two rather large tables
(currently about 15 million rows in one table
and about 5 million in the other one). Both tables
have 5 indexes (4 btree/1 hash).
Application running on the server INSERTs a lot of
stuff to the tables (which is not the target use of
the DB, it'll add data periodically, about
300 rows per 10 minutes). Queries (SELECTs) run perfectly
fine on the database, thanks to the indexes we have
here probably.

Performance issue, I'm experiencing here, is somewhat
weird - server gets high average load (from 5 up to 15,
8 on average). Standard performance monitoring
utilities (like top) show that CPUs are not loaded
(below 20%, often near zero).
With kernel 2.6.x which I was using earlier,
top showed very high "wa" values (which indicate I/O waiting, AFAIK).
I've googled some issues with 2.6 kernels and LSI Logic controllers
running RAID, so I've downgraded the kernel to 2.4.26.
The machine started to behave a bit better, but still high
load states look weird. Unfortunately, top with 2.4 kernels
does not show "wa" column, so I can't be sure if the load is
caused by waiting for disks, but high idle values and high average
load would suggest it. With kernel 2.6 swap was almost always 100% free,
with 2.4.26 Linux eats below 5 megabytes of swapspace.
PostgreSQL is running with shared_mem set to 48000,
sort_mem = 4096, fsync off.

Whole config is available here:
http://ludojad.itpp.pl/~eleven/pg-high-load.conf

I've also made some iostat report (using iostat 3 1000 as
suggested in one of the posts):
http://ludojad.itpp.pl/~eleven/iostat.log

Any solutions I should consider?
I'd be grateful getting some hints on this.

--
11.

On Tue, 29 Jun 2004 17:55:37 +0200, eleven@ludojad.itpp.pl
<eleven@ludojad.itpp.pl> wrote:

> Performance issue, I'm experiencing here, is somewhat
> weird - server gets high average load (from 5 up to 15,
> 8 on average). Standard performance monitoring
> utilities (like top) show that CPUs are not loaded
> (below 20%, often near zero).

So ... you never actually say what the performance issue you
experience is.  Having a high load average is not necessarily a
performance issue.

What is it that you want to fix?

eleven@ludojad.itpp.pl wrote:

> <>I'm using PostgreSQL 7.4.2 (package from backports.org)
> on a Debian (woody) box. The machine is IBM eServer 345
> with two 2.8 Xeon CPUs, it has 1024MB of RAM and
> two 15k RPM SCSI disks running in hardware RAID1, which
> is provided by the onboard LSI Logic controller (LSI53C1030).

<snip>

> <>With kernel 2.6.x which I was using earlier,
> top showed very high "wa" values (which indicate I/O waiting, AFAIK)


It sounds like you are very much bound by disk I/O. Your iostat output
indicates a good amount of I/O going on--I bet an iostat -x /dev/sdX
would show very high await times (time in ms before an IO request to the
device is serviced).

If your RAID controller has a battery-backed cache, check that you have
write-back (as opposed to write-through) enabled. This will cause the
controller to report data written only to RAID cache and not yet flushed
to disk as sync'd. You can experience large gains in write performance
this way.

If write-back is already enabled, or enabling it does not give a large
enough performance boost, you may need to buy more disks. In general, if
you have the budget for lots of disks, RAID 10 is the best you can do
performance-wise; if your budget for disks is limited, RAID 5 is the
next best thing. Also, you will get more bang for your buck with a
larger number of 10k disks than a smaller number of 15k disks.

Good luck,

Bill Montgomery

On Tue, Jun 29, 2004 at 09:17:36AM -0700, Marc wrote:

> > Performance issue, I'm experiencing here, is somewhat
> > weird - server gets high average load (from 5 up to 15,
> > 8 on average). Standard performance monitoring
> > utilities (like top) show that CPUs are not loaded
> > (below 20%, often near zero).
> So ... you never actually say what the performance issue you
> experience is.  Having a high load average is not necessarily a
> performance issue.

Well, if the server's CPUs are idle and the machine
is starting to hog itself, one can
suspect something bad going on.

> What is it that you want to fix?

Basically, I'm wondering if I'm already on the edge of
performance capabilities of this machine/configuration, or maybe
there's some abnormal behaviour happening (which
could be noticed by somebody from this mailing list, hopefully).

In particular - could someone tell me if those iostat
values can tell if I'm close to upper performance boundary
of fast SCSI (Ultra 320, 15k RPM) disks?

--
11.

On Tue, 2004-06-29 at 09:55, eleven@ludojad.itpp.pl wrote:
> Hello,
>
> I'm using PostgreSQL 7.4.2 (package from backports.org)
> on a Debian (woody) box. The machine is IBM eServer 345
> with two 2.8 Xeon CPUs, it has 1024MB of RAM and
> two 15k RPM SCSI disks running in hardware RAID1, which
> is provided by the onboard LSI Logic controller (LSI53C1030).
>
> The database consists of two rather large tables
> (currently about 15 million rows in one table
> and about 5 million in the other one). Both tables
> have 5 indexes (4 btree/1 hash).
> Application running on the server INSERTs a lot of
> stuff to the tables (which is not the target use of
> the DB, it'll add data periodically, about
> 300 rows per 10 minutes). Queries (SELECTs) run perfectly
> fine on the database, thanks to the indexes we have
> here probably.
>
> Performance issue, I'm experiencing here, is somewhat
> weird - server gets high average load (from 5 up to 15,
> 8 on average). Standard performance monitoring
> utilities (like top) show that CPUs are not loaded
> (below 20%, often near zero).

Anytime you have high load but low CPU utilization, you usually have an
I/O bound system.

Ad disks to your RAID (big RAID 5 or RAID 1+0) and make sure you have
battery backed cache set to write back.  also, put as memory as you can
in the machine.  Jumping up to 2 Gigs may help quite a bit as well.

Eleven,

> In particular - could someone tell me if those iostat
> values can tell if I'm close to upper performance boundary
> of fast SCSI (Ultra 320, 15k RPM) disks?

It's quite possible that you need to improve your disk array; certainly I
would have spec'd a lot more disk than you're using (like raid 0+1 with 6
disks or RAID 5 with seven disks).

However, there's the other end as well; it's quite possible that your queries
are doing seq scans and other disk-intensive operations that could be
avoided.   Have you analyed this at all?

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

eleven@ludojad.itpp.pl wrote:


> Whole config is available here:
> http://ludojad.itpp.pl/~eleven/pg-high-load.conf

effective_cache_size = 4000    # typically 8KB each
#random_page_cost = 4        # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01        # (same)
#cpu_index_tuple_cost = 0.001    # (same)
#cpu_operator_cost = 0.0025    # (same)


These values are too higher for your hardware, try to execute the
explain analyze for the queries that are running on your box and
repeat it lowering these values, I bet postgres is running seq scan
instead of an index scan.

These are the value that I use for a configuration closer to your:


effective_cache_size = 20000
random_page_cost = 2.0
cpu_tuple_cost = 0.005
cpu_index_tuple_cost = 0.0005
cpu_operator_cost = 0.0025


last question, do you use the autovacuum daemon ?
If no => you have to use it
If yes => did you apply the patch that will not fail with
           big tables like yours ?


if you can post the autovacuum daemon log ( last lines ).



Regards
Gaetano Mendola