Dragged into the (Postgres) Open
A few months ago, Greg Smith of PostgreSQL fame suggested I submit a proposal to the new Postgres Open conference here in Chicago. Some of us residents of the Midwest have long waited for a PostgreSQL-related conference of our very own, and now the glorious day has finally arrived. I was asked to submit proposals to other conferences, but the travel involved quickly put me off; now I can be lazy and still help spread The Word.
But what is The Word, and why am I spreading it? Well, I’m not. Not really. PostgreSQL as a database engine really advocates itself, and though this happened a number of years ago, it has reached a level of maturity and complexity that necessitates expert intervention. Because it sucks? Quite the opposite, actually. What started as a proof of concept-much like the Linux kernel-has grown into a veritable development platform. It’s not just that SQL and the set-theory which drives most databases is an anathema to garden-variety coders, because language-slingers will eventually see the light. But for every logical process, there’s a dark incantation; for every standard usage pattern, there’s a sheer drop into oblivion.
A good example of this is the topic of my presentation, actually. I won’t go into much detail here so I can preserve the suspense, but sometimes even the best intentions and experience can be counter-intuitive or otherwise not suggest a feasible solution. A good DBA is also part System Administrator, knowing the basics of RAID, the operating and file systems lurking beneath their favorite database engine, and every nook and cranny of the software itself. A math background for set theory helps with insight to how databases think. A CS degree will at least touch on Big-O notation to help explain and decipher SQL performance difficulties. Having a solid knowledge of programming language theory will encourage scalable stored procedures, for all those times an application developer needs help. All of these things and more, are just the barest of beginnings.
But thousands of people do it every day, so I’m clearly not suggesting DBAs are some kind of super-human oracles. What I am implying though, is that so much background introduces a lot of room for interpretation, wrong guesses, red herrings, and blind alleys. What happens if an application is slamming the database and the developers have already implemented several intermediate layers of cache? What happens if indexes and simplification have already optimized all of the main queries crossing the database? What happens when the database is far too large to run from memory, but active tables constantly churn the OS inode cache in such a way disk throughput is pegged over 90% utilized for hours at a time? Fix it! Fix it now! Why haven’t you fixed it yet?!
Aaaah! What now?! Bigger server? Sure. More memory? A SAN, MSA, or DAS with more dedicated spindles sporting a BBU and immense write cache? May work. Vertical scaling through multiple read-only mirrors? That’ll require architectural changes so writes are directed toward the data master, or the introduction of PGPool as an intermediate layer, not to mention the requirement of maintaining a full database copy on every server. How about Horizontal scaling, then? Sharding, data segmentation along logical or hashed server assignment is perfect, but requires even more architectural and application changes!
All of these things are feasible and recommended long-term solutions in various scenarios, and my presentation will cover another, more immediate solution along the same lines as SSDs. But I won’t just talk about what we actually did, but why. What led to that decision, and more importantly, why a lot of other things-including SSDs-wouldn’t have worked. For OLTP databases that take thousands or tens of thousands of transactions per second, being a good DBA isn’t enough. Sometimes hardware and existing architecture can actively thwart the most valiant effort. The trick is in knowing how to fix it anyway, and that’s one incantation I can impart.
If you’re in the Chicago area during September 14th to the 16th, go ahead and drop by. Bruce Momjian and a few other notable PostgreSQL names will be there, too! It’ll be an honor to be there, and I hope many of you are too!
Originally published at BonesMoses.org. You can comment here or there.