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

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.

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

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


<<attachment: pg_select.gif>>

<<attachment: pg_tpcb.gif>>

Attachment: PostgreSQL Benchmark.sxc
Description: OpenOffice Calc spreadsheet

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?


Reply via email to