Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] PostgreSQL Tuning Results

From: "Dann Corbit" <DCorbit(at)connx(dot)com>
To: "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au>,"Hackers" <pgsql-hackers(at)postgresql(dot)org>,"Advocacy" <pgsql-advocacy(at)postgresql(dot)org>
Subject: Re: [HACKERS] PostgreSQL Tuning Results
Date: 2003-02-12 04:58:50
Message-ID: D90A5A6C612A39408103E6ECDD77B8294CD92E@voyager.corporate.connx.com (view raw or flat)
Thread:
Lists: pgsql-advocacypgsql-hackers
> -----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.

Responses

pgsql-hackers by date

Next:From: Christopher Kings-LynneDate: 2003-02-12 05:08:38
Subject: Re: [HACKERS] PostgreSQL Tuning Results
Previous:From: Marc G. FournierDate: 2003-02-12 04:55:28
Subject: Re: PGP signing release

pgsql-advocacy by date

Next:From: Christopher Kings-LynneDate: 2003-02-12 05:08:38
Subject: Re: [HACKERS] PostgreSQL Tuning Results
Previous:From: Christopher Kings-LynneDate: 2003-02-12 04:53:49
Subject: PostgreSQL Tuning Results

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group