> 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.

/andy

---
Andy Riebs, andy.riebs@compaq.com           High Performance Technical
(w) 603-884-1521, (fax) 603-884-0630             Computing/Linux Group
    <http://cub.sourceforge.net/>          Compaq Computer Corporation
(h) ariebs@earthlink.net                 <http://www.compaq.com/linux>
    <http://osdb.sourceforge.net/>      <http://opensource.compaq.com>

Thu, 28 Mar 2002 13:09:44 -0500
"Riebs, Andy" <Andy.Riebs@compaq.com>
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.


Thu, 28 Mar 2002 23:15:24 +0000 (GMT)
Matthew Kirkwood <matthew@hairy.beasts.org>
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.


Sun, 14 Apr 2002 15:25:19 +0100 (BST)
Matthew Kirkwood <matthew@hairy.beasts.org>
Re: Performance Tuning Document?