> -----Original Message-----
> From: Christopher Kings-Lynne [mailto:chriskl@familyhealth.com.au] 
> Sent: Tuesday, February 11, 2003 8:54 PM
> To: Hackers; Advocacy
> Subject: [HACKERS] PostgreSQL Tuning Results
> 
> 
> Hi Everyone,
> 
> I have just completed a basic set of benchmarking on our new 
> database server.  I wanted to figure out a good value for 
> shared_buffers before we go live.
> 
> We are a busy ecommerce-style website and so we probably get 
> 10 or 20 to 1 read transactions vs. write transactions.  We 
> also don't have particularly large tables.
> 
> Attached are the charts for select only and tpc-b runs.  Also 
> attached is an OpenOffice.org spreadsheet with all the 
> results, averages and charts.  I place all these attachments 
> in the public domain, so you guys can use them how you wish.
> 
> I installed pgbench, and set up a pgbench database with scale 
> factor 1.
> 
> I then set shared_buffers to all the values between 2000 and 
> 11000 and tested select and tcp-b with each.  I ran each test 
> 3 times and averaged the values.  TPC-B was run after select 
> so had advantages due to the buffers already being filled, 
> but I was consistent with this.
> 
> Machine:
> 256MB RAM, FreeBSD 4.7, EIDE HDD, > 1 Ghz
> 
> TPC-B config:
> pgbench -c 64 -t 100 pgbench (Note: only 64 users here)
> 
> SELECT config:
> pgbench -c 128 -t 100 -S pgbench (Note: full 128 users here)
> 
> I'm not sure why 8000 and 9000 are low on tpc-b, it's odd.
> 
> Anyway, from the attached results you can see that 4000 
> buffers gave the best SELECT only performance, whereas the 
> TPC-B stuff seemed to max out way up at 10000 or so.  Since 
> there is a 20% gain in performance on TPC-B going from 4000 
> buffers to 5000 buffers and only a 2% loss in performance for 
> SELECTs, I have configured my server to use 5000 shared 
> buffers, eg. 45MB RAM.
> 
> I am now going to leave it on 5000 and play with wal_buffers. 
>  Is there anything else people are interested in me trying?

Keenly interested.  Who wouldn't want to know how to optimize it?
That's the hardest guideline to find.
 
> Later on, I'll run pg_autotune to see how its recommendation 
> matches my findings.

I would like to hear about that also.  Please report on it.

Tue, 11 Feb 2003 20:58:50 -0800
"Dann Corbit" <DCorbit@connx.com>
Re: [HACKERS] PostgreSQL Tuning Results
> > I am now going to leave it on 5000 and play with wal_buffers.
> >  Is there anything else people are interested in me trying?
>
> Keenly interested.  Who wouldn't want to know how to optimize it?
> That's the hardest guideline to find.

Oops - what that sentence was supposed to say is "Is there anyone else
interested in me trying any other variables?"

What I don't really know is what is actually affected by wal_buffers?  I
assume my select only tests won't even touch the WAL, so I guess I have to
just play with tpc-b.

Chris


Wed, 12 Feb 2003 13:08:38 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [HACKERS] PostgreSQL Tuning Results