Re: Performance Tuning Document?

On Thu, 28 Mar 2002, Riebs, Andy wrote:

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. I suspect internal lock contention, or maybe it's just
that the read() path in Linux is quicker than PG's own cache?

Matthew, are you using the --postgresql=no_hash_index option for OSDB?
It's conceivable that you are hitting an artifact of the hash index
problem here.

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

Thanks,
Matthew.

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.