Re: Performance Tuning Document?

On Thu, 28 Mar 2002, Matthew Kirkwood wrote:

[ oops, forgot to send this ages ago ]

> > > Under the "crossSectionTests(Mixed IR)" part of an OSDB run, a
> > > large number of shared_buffers causes severe slowdown on one of
> > > the tests -- it goes from a little over 200 seconds to nearly
> > > 2000.

> > --postgresql=no_hash_index

> Ah, that would make a lot of sense. I'll do a run again with
> that option and see what turns up.

That was right on the nose. The numbers are much better now.

My initial interest was in benchmarking different filesystems
on Linux. In case anyone is interested, here are today's
numbers:

tuning? single ir cs-ir oltp cs-oltp
(sec) (tps) (sec) (tps) (sec)
ext3 kn 841.28 61.52 203.33 407.58 159.72
ext3-wb kn 841.19 63.73 217.19 406.30 160.88
ext3-jd kn 839.96 58.96 203.02 307.85 159.89
jfs kn 840.53 62.74 205.90 348.33 177.70
minix kn 841.51 62.12 201.44 343.87 176.68
ext2 kn 840.72 65.02 205.40 338.20 182.22

ext3-wb is ext3 with the "data=writeback" mount option. ext3-jd
is ext3 with "data=journal" and a 200Mb journal instead of the
usual 32Mb one. All filesystems were mounted noatime.

postgresql.conf for all these runs looks like:

tcpip_socket = true
shared_buffers = 10240
max_fsm_relations = 100
max_fsm_pages = 10000
max_locks_per_transaction = 256
wal_buffers = 10240
sort_mem = 5120000
vacuum_mem = 81920

Without hash indexes, it looks like only OLTP loads can
differentiate the filesystems. Sometime (once I have got
a more recent kernel going) I'll try a dataset larger than
memory.

Matthew.