Re: Slow Postgresql server - Mailing list pgsql-performance

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@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954