PGCon2010 - Final Release III
PGCon 2010
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Greg Smith |
| Schedule | |
|---|---|
| Day | Talks - 2 - 2010-05-21 |
| Room | DMS 1160 |
| Start time | 11:30 |
| Duration | 01:00 |
| Info | |
| ID | 218 |
| Event type | Lecture |
| Track | Performance |
| Language used for presentation | English |
Monitoring PostgreSQL Buffer Cache Internals
Watching disk caching inside the database
When you give your database server memory, you expect it's going to use it. But for what? A look inside PostgreSQL's buffer cache can tell you exactly what that memory is doing for you. Every systematic database tuning effort should include a look at this critical resource. When it comes to optimization work, profiling beats guessing every time.
PostgreSQL keeps most of its working data inside a block of shared memory allocated when the server starts, used for caching disk reads and writes. Looking at the contents of that cache can give you valuable clues to how your database application really works. The best ways to handle many types of optimization tasks involve carefully measuring the variables you're changing, but most people change the size of this cache without any plan for measuring the impact. Information about data moving in and out of the cache is useful for performance tuning, query optimization, system monitoring, and even predicting the future!
This presentation aims to describe the basics of how the cache is organized, how to query its contents, and how to interpret the results of those queries. By monitoring what goes in and out of the cache, you get a unique window into what's really happening inside your database when it's running your application.