PostgreSQL Tuning Results - Mailing list pgsql-advocacy

From Christopher Kings-Lynne
Subject PostgreSQL Tuning Results
Date
Msg-id GNELIHDDFBOCMGBFGEFOCEIBCFAA.chriskl@familyhealth.com.au
Whole thread Raw
Responses Re: [HACKERS] PostgreSQL Tuning Results
List pgsql-advocacy
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

Attachment

pgsql-advocacy by date:

Previous
From: "Christopher Kings-Lynne"
Date:
Subject: Re: [HACKERS] Changing the default configuration (was Re:
Next
From: "Dann Corbit"
Date:
Subject: Re: [HACKERS] PostgreSQL Tuning Results