Righting Your Writes

Talk Type: 
45 Minute Talk
Technical Level: 
Creative Commons - Attribution Only

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 research at 2ndQuadrant has provided new insight into exactly how PostgreSQL systems that write constantly break down under serious loads.

The results of this work are a set of patches that instrument the most troublesome write paths, and a second that tunes write timing. But it turns out that the improved timing technique is only applicable to some operating systems and filesystems. Examining filesystem performance from this unique perspective, that of highly instrumented database internals, will give you a much better idea what each popular filesystem is really good and bad at when it's getting hammered with more writes than it can handle. The trade-offs between optimizing for latency and throughput are highlighted when watching operations at this level.

Both the new "checkpoint goggles" and "sync spread checkpoint" features will be contributed toward PostgreSQL 9.1 development. But understanding what problems they fix can help you make the right long-term choices about how to tune your 9.0 system, or provide ammunition for why you need to plan an upgrade when you run into this class of problem in earlier versions.

RightingWrites.pdf165.03 KB