From: Mark Wong <markwkm@gmail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: Selena Deckelmann <selenamarie@gmail.com>
Cc: Gabrielle Roth <gorthx@gmail.com>
Subject: dbt-2 tuning results with postgresql-8.3.5
Date: Sat, 20 Dec 2008 16:54:39 -0800
Message-ID: <70c01d1d0812201654i5f55150ep619a6c7ff094470@mail.gmail.com> (raw)
Hi all,
So after a long hiatus after running this OLTP workload at the OSDL,
many of you know the community has had some equipment donated by HP: a
DL380 G5 and an MSA70 disk array. We are currently using the hardware
to do some tuning exercises to show the effects of various GUC
parameters. I wanted to share what I've started with for input for
what is realistic to tune an OLTP database on a single large LUN. The
initial goal is to show how much can (or can't) be tuned on an OLTP
type workload with just database and kernel parameters before
physically partitioning the database. I hope this is actually a
useful exercise (it was certainly helped get the kit updated a little
bit.)
To recap, dbt2 is a fair-use derivative of the TPC-C benchmark. We
are using a 1000 warehouse database, which amounts to about 100GB of
raw text data. The DL380 G5 is an 8 core Xeon E5405 with 32GB of
memory. The MSA70 is a 25-disk 15,000 RPM SAS array, currently
configured as a 25-disk RAID-0 array. More specific hardware details
can be found here:
http://wiki.postgresql.org/wiki/HP_ProLiant_DL380_G5_Tuning_Guide#Hardware_Details
So first task is to show the confidence of the results, here are a
link to a few repeated runs using all default GUC values except the
number of connections (250):
http://pugs.postgresql.org/node/502
Here are links to how the throughput changes when increasing shared_buffers:
http://pugs.postgresql.org/node/505
And another series of tests to show how throughput changes when
checkpoint_segments are increased:
http://pugs.postgresql.org/node/503
The links go to a graphical summary and raw data. Note that the
maximum theoretical throughput at this scale factor is approximately
12000 notpm.
My first glance takes tells me that the system performance is quite
erratic when increasing the shared_buffers. I'm also not what to
gather from increasing the checkpoint_segments. Is it simply that the
more checkpoint segments you have, the more time the database spends
fsyncing when at a checkpoint?
Moving forward, what other parameters (or combinations of) do people
feel would be valuable to illustrate with this workload?
Regards,
Mark
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: markwkm@gmail.com, selenamarie@gmail.com, gorthx@gmail.com
Subject: Re: dbt-2 tuning results with postgresql-8.3.5
In-Reply-To: <70c01d1d0812201654i5f55150ep619a6c7ff094470@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox