PGCon2010 - Final Release

PGCon 2010
The PostgreSQL Conference

Speakers
Cedric Villemain
Schedule
Day Talks - 1 - 2010-05-20
Room DMS 1160
Start time 15:00
Duration 01:00
Info
ID 261
Event type Lecture
Track Performance
Language used for presentation English
Feedback

PgFincore and the OS Page Cache

Is my table in memory ?

While PostgreSQL can see the contents of shared buffers, it does not know about the OS page cache, which in turn tells which pages are actually in memory.
PgFincore provides this information, which allows us to:

  • Preload the exact pages that PostgreSQL will probably want in order to respond more quickly to the first queries on server restart.
  • Try to improve planner choice and cost estimation.

It suggest ideas to :

  • Keep pg_dump from trashing the OS Page Cache
  • Explicitly ask for a non-cached sequential scan.

PgFincore also provides information about how the data in the OS page cache is distributed.

You are a DBA, you know your database and your hardware. You are already able to get a lot of information about shared buffers but you still don't know if your tables or indexes are in RAM. Perhaps you have an application which needs to get the best performance from PostgreSQL as soon as it starts. Finally pg_dump trash your OS Page Cache

Finally you totally trash your OS Page Cache with pg_dump or like to execute some sequential scan (that's it : a lot of the interesting pages in memory are replaced by those read for the pg_dump). As PostgreSQL actually can not know eactly what happens in the OS Page Cache, the planner may sometime choose a bad plan and you would like to use planner hint.

Maybe we can optimise and improve those points.