Greg,
Alexei Vladishev wrote:
Is there a way of configuring PostgreSQL so that one specific table
would
use, say, 4GB of buffer cache while other tables would use the rest?
It sounds like you're looking for what other databases call
"pinning". It's not supported in PostgreSQL right now, and as far as
I know it's not on anybody's hotlist of features they're working on.
It would be straightforward to add actually; I know exactly where the
code that evicts pages from the buffer cache would need to be tweaked
to support this. See the "Inside the PostgreSQL Buffer Cache"
presentation at http://www.westnet.com/~gsmith/content/postgresql/ for
more details about how the current implementation works. Be happy to
talk about what what it would take to sponsor the bit of development
required if this is something you really need for your app--it's not a
giant feature to build, just not one that anyone has needed badly
enough so far to bother writing.
Thank you for the link. Lots of useful information there.
I would like to keep the table and its indexes always in "hot" state,
so that
other queries won't pollute this part of the buffer cache. It would
ensure
reliable performance and much less disk IOPS working with the table.
The PostgreSQL design presumes that the OS cache is significantly
larger than the RAM dedicated to the database, so a database cache
miss doesn't necessarily turn into physical I/O. Part of the reason
this feature hasn't been been perceived as more valuable is because
just letting the two cache levels involved here sort out what's really
valuable or not can often outperform what an application developer
thinks the optimal configuration will be.
Interesting! I assumed that it is a common practice to dedicate most of
the RAM to a database engine leaving only small percentage to OS.
Kind regards,
Alexei
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
