-----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@xxxxxxxxxxxxx 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----- -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
