Re: Need help with 8.4 Performance Testing - Mailing list pgsql-performance

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Greg Smith wrote:
| On Mon, 8 Dec 2008, Merlin Moncure wrote:
|
|> I wonder if shared_buffers has any effect on how far you can go before
|> you hit the 'tipping point'.
|
| If your operating system has any reasonable caching itself, not so much at
| first.  As long as the index on the account table fits in shared_buffers,
| even the basic sort of caching logic an OS uses is perfectly functional
| for swapping the individual pages of the account table in and out, the
| main limiting factor on pgbench performance.
|
| There is a further out tipping point I've theorized about but not really
| explored:  the point where even the database indexes stop fitting in
| memory usefully.  As you get closer to that, I'd expect that the clock
| sweep algorithm used by shared_buffers should make it a bit more likely
| that those important blocks would hang around usefully if you put them
| there, rather than giving most of the memory to the OS to manage.

I am by no means an expert at this.

But one thing that can matter is whether you want to improve just the
performance of the dbms, or the performance of the entire system, on which
the dbms runs. Because if you want to improve the whole system, you would
want as much of the caching to take place in the system's buffers so the use
of the memory could be optimized over the entire workload, not just the load
of the dbms itself. I suppose on a dedicated system with only one dbms
running with only one database open (at a time, anyway), this might be moot,
but not otherwise.

Now I agree that it would be good to get the entire index (or at least the
working set of the index) into the memory of the computer. But does it
really matter if it is in the system's cache, or the postgres cache? Is it
any more likely to be in postgres's cache than in the system cache if the
system is hurting for memory? I would think the system would be equally
likely to page out "idle" pages no matter where they are unless they are
locked to memory, and I do not know if all operating systems can do this,
and even if they can, I do not know if postgres uses that ability. I doubt
it, since I believe (at least in Linux) a process can do that only if run as
root, which I imagine few (if any) users do.
|
| Since the data is about 7.5X as large as the indexes, that point is way
| further out than the basic bottlenecks.  And if you graph pgbench results
| on a scale that usefully shows the results for in-memory TPS scores, you
| can barely see that part of the chart a well.  One day I may get to
| mapping that out better, and if I do it will be interesting to see if the
| balance of shared_buffers to OS cache works the way I expect.  I was
| waiting until I finished the pgtune program for that, that's building some
| of the guts I wanted to make it easier to tweak postgresql.conf settings
| programmatically in between pgbench runs.
|
| --
| * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
|


- --
~  .~.  Jean-David Beyer          Registered Linux User 85642.
~  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
~ /( )\ Shrewsbury, New Jersey    http://counter.li.org
~ ^^-^^ 07:55:02 up 5 days, 18:13, 4 users, load average: 4.18, 4.17, 4.11
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org

iD8DBQFJPm2+Ptu2XpovyZoRAlcJAKCIN098quZKZ7MfAs3MOkuL3WWxrQCdHCVl
sUQoIVleRWVLvcMZoihztpE=
=n6uO
-----END PGP SIGNATURE-----