Hannu Krosing wrote:
There was ample space for keeping the indexes in linux cache (it has 1GB cached currently) though the system may have decided to start writing it to disk, so I suspect that most of the time was spent copying random index pages back and forth between shared buffers and disk cache.
Low shared_buffers settings will result in the same pages more often being written multiple times per checkpoint, particularly index pages, which is less efficient than keeping in the database cache and updating them there. This is a slightly different issue than just the overhead of copying them back and forth; by keeping them in cache, you actually reduce writes to the OS cache. What I do to quantify that is...well, the attached shows it better than I can describe; only works on 9.0 or later as it depends on a feature I added for this purpose there. It measures exactly how much buffer cache churn happened during a test, in this case creating a pgbench database.
-- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@xxxxxxxxxxxxxxx www.2ndQuadrant.us -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
