A Change of Role

I'm pleased to report that I have changed roles within EnterpriseDB and am now officially a member of EnterpriseDB's support and professional services team.  I have a number of other responsibilities as well, so I will not be spending all of my time on support, but I have been and will continue to spend a significant chunk of time on it each day.  When I've talked about this with people, they sometimes look at me as if I have two heads.  After all, I am a backend server developer, and developers as a breed are not known for enjoying support.

PostgreSQL East, and The MySQL Conference and Expo

Last month, I attended (and spoke at) PostgreSQL East in New York City, which this year featured a MongoDB track.  This past week, I was in Santa Clara at the O'Reilly MySQL Conference & Expo, which had a substantial PostgreSQL track this year, where I also spoke.

Both conferences had some very good talks.  The first talk I attended at the MySQL conference turned out to be one of the best - it was entitled Linux and H/W optimizations for MySQL.  I had a little difficulty understanding Yoshinori Matsunobu's accent at times, but the slides were excellent, and very detailed.  Some of his more interesting findings: (1) SSDs speed things up both on the master and on replication slaves, but the speedup is larger on the slaves; so it's useful to put hard disks on the master and SSDs on the slave to make it possible for single-threaded recovery there to keep up with the master; (2) while SSDs are much faster for random access, they are actually slower for sequential access and fsync, so a RAID array with a battery-backed or flash-backed write cache may still be a better option in those cases, (3) Fusion I/O drives were FAR faster than Intel drives, (4) the Intel Nehalem architecture was much faster than the AMD Opteron architecture when used in combination with SSDs, and (5) HyperThreading helps more in SSD environments than it does otherwise, because the system, overall, becomes more heavily CPU-bound; for the same reasons, mutex contention hurts more.

Another very good talk was Peter Zaitsev's discussion of Innodb and XtraDB Architecture and Performance Optimization, which gave me the feeling of looking into a sort of carnival mirror, where you recognize yourself, but it's all distorted.  Two of the problems that give PostgreSQL DBAs heartburn - bloat, and checkpoint I/O spikes (and less frequently, purge not keeping up a la vacuum not keeping up) - are apparently problems for MySQL as well, though with significantly different details.  I'm not even going to attempt to summarize the differences, or say which problem is worse or occurs more often, because I honestly have no idea.  I was a bit surprised to hear dump-and-reload recommended to recover from certain worst-case scenarios, though.

There were other good talks, too, which helped me understand what's going on in the world of MySQL forks.  Apparently, the Drizzle team is busy removing features that they consider half-baked and modularizing the code so that it is easier to understand and improve, while the MariaDB team is busy adding optimizer features, including support for hash joins and persistent statistics.  From what I understand, the MySQL optimizer has typically worked by gathering statistics through on-the-fly index probes, which can be a problem in some situations.  It's not so easy to categorize the work that Oracle is doing, but it seems to involve a fair amount of filing down of rough edges, and various improvements to replication, including, perhaps most significantly, parallel replication apply.

At PostgreSQL East, I think my favorite talk was Ken Rosensteel's talk, somewhat misleadingly titled Large Customers Want PostgreSQL, Too.  This talk turned to be about migrating a large Oracle mainframe application to use PostgreSQL, and the challenges faced during that migration.  He, or his team, built an Oracle-to-PostgreSQL converter for stored procedures; it was interesting to see that they got bitten by our bizarre casting rules around the smallint data type.  They also ended up doing some very interesting work optimizing the performance of ECPG for small FETCH statements; these are areas of the code that I think don't normally get a lot of attention, and it was great to hear about the optimization work that got done.

I was disappointed that Jon Hoffman's talk on Experiences with Postgres and MongoDB at foursquare.com got cancelled; I think that would have been an interesting talk.  I did have an opportunity to attend Jake Luciani's talk Comparing the Apache Cassandra Architecutre to PostgreSQL, which turned out to be more about Cassandra than PostgreSQL, but was nevertheless interesting.  I would have been interested to hear a more technical talk, though, about how problems like distributed serialization anomalies and distributed checkpointing are handled.

Next month, I'll be speaking at PGCon 2011 on Using The PostgreSQL System Catalogs and How To Get Your PostgreSQL Patch Accepted.  And after that, unlike Bruce, I'm going to stay home for a few months!

One-Way Tuning

I've been noticing that many of the parameters in postgresql.conf only ever need to be adjusted in one direction from the default.

shared_buffers, temp_buffers, work_mem, maintenance_work_mem,  checkpoint_segments, checkpoint_timeout, and checkpoint_completion_target, join_collapse_limit, and from_collapse_limit only ever need to be adjusted upward from the default.

bgwriter_delay, seq_page_cost, and random_page_cost only ever need to be adjusted downward from the default.

Does this mean we should change some of the defaults?

CommitFests and Meritocracy

Yesterday, I wrote a blog post on whether and to what extend the PostgreSQL community is a welcoming community, in which I quoted some remarks that Selena Deckelmann made, and she responded with her own post on where meritocracy fails.  I want to just recap a few things that may not be totally obvious to casual observers of our community; and then I have a few remarks about meritocracy.

A Welcoming Community?

I attended PostgreSQL East last week and, as usual, the most interesting discussions were the ones that nobody planned.  Ed Boyajian, the CEO here at EnterpriseDB where I work, threw out a remark to the effect that the PostgreSQL community is a meritocracy.  Selena Deckelmann wasn't convinced, "not to say that we don't do try hard, and do pretty well."  During the closing session, Josh Drake made some remarks to the effect that, in effect, the process for submitting a patch to PostgreSQL is long, difficult, and fraught with arbitrary and capricious rejection, which caused me to rise to my feet and object that that while it's not perfect, I think we do pretty well, and better than we used to.  Josh agreed with some of what I have to say, and I see his point, too.  But it got me thinking about the whole way that we operate as a development community, and what is good and bad about it.

Working Toward PostgreSQL 9.1beta

I'm pleased to report that we seem to be making good progress toward PostgreSQL 9.1beta.  As we did for PostgreSQL 9.0, we are maintaining a list of open items that we need either to address or to decide that they aren't so important after all (there are also some issues that are being discussed on the mailing list that aren't reflected in that open items list).  What we're really doing here is trying to stabilize the code, particularly with respect to the features committed at the very end of the development cycle.  So far, the main problem areas appear to be (1) collation support, (2) changes to streaming replication, especially the new synchronous replication feature, and (3) the new serializable isolation level.  But we are making rapid progress in sorting out the loose ends, and I feel a lot better about the release now than I did even a week ago.

Barring objections or unforeseen problems, I'm hoping to bundle an alpha5 release a week from today, on Monday, March 28.  We haven't discussed the time line for beta yet, other than to hope that we'll be able to get there in April.  I still think that's a realistic timeline, although there is quite a bit of work that must be done between now and then for us to hit it.  In the meantime, if you're in a position to build from source, please keep testing and sending in bug reports.  Thanks!

Query Planner Enhancements in PostgreSQL 9.1

PostgreSQL has an awesome query planner.  And it keeps getting better.  postgresql.org has a survey asking people Which PostgreSQL 9.1 Feature Are You Most Excited About? (to vote on it, if it's still open when you read this, go here).  For some reason, query planner features tend not to be listed when people talk about what they're most excited about in a new release, but there's ample room for excitement.  If a particular query planner feature happens to apply to your use case, it's possible to see multiple-order-of-magnitude speedups, avoiding the need for a major application redesign or even an expensive hardware purchase.  This is a big deal.