PGCon2011 - Add 4 Video (2015.09.18)
PGCon 2011
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Greg Smith |
| Schedule | |
|---|---|
| Day | Talks - 2 - 2011-05-20 |
| Room | DMS 1140 |
| Start time | 15:00 |
| Duration | 01:00 |
| Info | |
| ID | 337 |
| Event type | Lecture |
| Track | Performance |
| Language used for presentation | English |
The Write Stuff
Reaching peak write performance under heavy loads
One of the most difficult things to do with a database system is handle a high volume of writes, whether it's from a single user or thousands. The steps a database like PostgreSQL takes to protect your data are expensive, and it's hard to split them into independent pieces. Recent changes available in the upcoming PostgreSQL 9.1 have given new insight into exactly how PostgreSQL systems that write constantly break down under serious loads.
The replication features in PostgreSQL 9.0 make it easier than ever to scale your application performance up by adding more read-only nodes with additional copies of the data. But that also makes it easier than ever before to end up running into a harder to resolve write bottleneck as a result.
This talk will cover the major approaches and trade-offs in tuning for high write performance on a PostgreSQL server. The write-ahead log, checkpoint timing, and buffer management policies of the database all interact to limit just fast data can be shuffled in and out of the server. Some recently added features to PostgreSQL 9.1 will also be introduced. These give better insight than was ever available before into exactly what caused the kind of checkpoint write "spikes" that have traditionally plagued write-heavy PostgreSQL databases. The trade-offs between optimizing for latency and throughput are highlighted when watching operations at this level.
Understanding these problems can help you make the right long-term choices about how to tune system, or provide ammunition for why you need to plan an upgrade to 9.1 when it becomes available in the future.