PGCon2010 - Final Release III
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 |
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 pgdump 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 pgdump). 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.