Re: [HACKERS] PostgreSQL Tuning Results

> -----Original Message-----
> From: Christopher Kings-Lynne [mailto:chriskl(at)familyhealth(dot)com(dot)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.