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?
Later on, I'll run pg_autotune to see how its recommendation matches my
findings.
Chris
