Re: Slow Postgresql server

On Wed, 11 Apr 2007, Jason Lustig wrote:

> Hello all,
>
> My website has been having issues with our new Linux/PostgreSQL server being
> somewhat slow. I have done tests using Apache Benchmark and for pages that do
> not connect to Postgres, the speeds are much faster (334 requests/second v.
> 1-2 requests/second), so it seems that Postgres is what's causing the problem
> and not Apache. I did some reserach, and it seems that the bottleneck is in
> fact the hard drives! Here's an excerpt from vmstat:
>
> procs -----------memory---------- ---swap-- -----io---- --system--
> -----cpu------
> r b swpd free buff cache si so bi bo in cs us sy id wa
> st
> 1 1 140 24780 166636 575144 0 0 0 3900 1462 3299 1 4 49 48
> 0
> 0 1 140 24780 166636 575144 0 0 0 3828 1455 3391 0 4 48 48
> 0
> 1 1 140 24780 166636 575144 0 0 0 2440 960 2033 0 3 48 48
> 0
> 0 1 140 24780 166636 575144 0 0 0 2552 1001 2131 0 2 50 49
> 0
> 0 1 140 24780 166636 575144 0 0 0 3188 1233 2755 0 3 49 48
> 0
> 0 1 140 24780 166636 575144 0 0 0 2048 868 1812 0 2 49 49
> 0
> 0 1 140 24780 166636 575144 0 0 0 2720 1094 2386 0 3 49 49
> 0
>
> As you can see, almost 50% of the CPU is waiting on I/O. This doesn't seem
> like it should be happening, however, since we are using a RAID 1 setup
> (160+160). We have 1GB ram, and have upped shared_buffers to 13000 and
> work_mem to 8096. What would cause the computer to only use such a small
> percentage of the CPU, with more than half of it waiting on I/O requests?

Well, the simple answer is a slow disk subsystem. Is it hardware or software
RAID1? If hardware, what's the RAID controller? Based on your vmstat output,
I'd guess that this query activity is all writes since I see only blocks out.
Can you identify what the slow queries are? What version of postgres? How
large is the database? Can you post the non-default values in your
postgresql.conf?

I'd suggest you test your disk subsystem to see if it's as performant as you
think with bonnie++. Here's some output from my RAID1 test server:

Version 1.03 ------Sequential Output------ --Sequential Input- --Random-
-Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP
pgtest 4G 47090 92 52348 11 30954 6 41838 65 73396 8 255.9 1
------Sequential Create------ --------Random Create--------
-Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP
16 894 2 +++++ +++ 854 1 817 2 +++++ +++ 969 2

So, that's 52MB/sec block writes and 73MB/sec block reads. That's typical of
a RAID1 on 2 semi-fast SATA drives.

If you're doing writes to the DB on every web page, you might consider playing
with the commit_delay and commit_siblings parameters in the postgresql.conf.
Also, if you're doing multiple inserts as separate transactions, you should
consider batching them up in one transaction.

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954