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
