From: | "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> |
---|---|
To: | "Hackers" <pgsql-hackers(at)postgresql(dot)org>, "Advocacy" <pgsql-advocacy(at)postgresql(dot)org> |
Subject: | PostgreSQL Tuning Results |
Date: | 2003-02-12 04:53:49 |
Message-ID: | GNELIHDDFBOCMGBFGEFOCEIBCFAA.chriskl@familyhealth.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-advocacy pgsql-hackers |
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 | Content-Type | Size |
---|---|---|
![]() |
image/gif | 4.5 KB |
![]() |
image/gif | 8.1 KB |
PostgreSQL Benchmark.sxc | application/vnd.sun.xml.calc | 11.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Dann Corbit | 2003-02-12 04:58:50 | Re: [HACKERS] PostgreSQL Tuning Results |
Previous Message | Tom Lane | 2003-02-12 04:24:26 | Re: Changing the default configuration (was Re: |
From | Date | Subject | |
---|---|---|---|
Next Message | Marc G. Fournier | 2003-02-12 04:55:28 | Re: PGP signing release |
Previous Message | Curt Sampson | 2003-02-12 04:53:17 | Re: location of the configuration files |