PostgreSQL

In perhaps a new trend, I’m blogging from 39011 feet (or so says the seatback in front of me). I’m traveling back home to the east coast from San Jose, CA where I attended (and spoke) at this year’s O’Reilly Velocity Conference.

I participated (and blogged) about the Velocity Summit in which I’ve participated for the past two years. The summit is the unconference preceding the real conference that help the organizers digest current hot topics and better define the conference track for the actual conference. The summit itself is filled with enough brain power to warp space-time, so I drop everything to go to that.

Ironically, despite being a well respected authority in web site (and general internet) scalability and performance, my talk proposals for Velocity 2008 were not accepted — I clearly need to write better proposals. This year, I managed to work my way into the workshop track on Monday. Despite having a bad headache and feeling "off" the day before, I managed to get my act together and put on an A-game for my workshop. For those of you interested, here is my scalable09 slide stack.

I thought I’d take a moment to talk about what I liked about the conference and what I think could use some improvement. I realize this is a down economy and that might be a legitimate justification for some the actions that resulted in some of my disappointment.

First, the negative. I usually start with positive and end with negative because I’m a pessimist. However, all in all the conference was awesome, so I thought I’d get my short list of gripes out of the way early.

O’Reilly is infamous for throwing good conferences for geeks. In my opinion, the field of web operations has been so severely neglected and applies so broadly to the world today that this conference needs to be for everyone.

1. In the next conference, I’d love to see a technical business track. Several of the talks I went to spoke to the dollars and cents lost or earned by paying the right amount of attention to web site performance and better operational paradigms. I thought a lot of the topics would be very useful to business managers.
2. The first day was not video taped and the second and third day were only half video taped. Come on guys, ante up. The attendance fee was substantial, you can afford to give your attendees the value of watching what they had to choose not to attend. I like the option when I go to a conference to choose a session that seems interesting so that I have the opportunity to participate, but often times I find that another session was top notch and I expect to be able to later review a recording of that.
3. Lastly, and this is the most significant. While I thought the conference was extremely well executed (excellent job Jesse, Steve, all your support, and most definitely O’Reilly), it lacked sufficient PR and marketing outreach. I talked with several journalists (as a part of my normal day job) while I was at that conference and not one of them was aware of Velocity — simply embarrassing. Given that the Structure conference was in town that same week, O’Reilly should have invested more in their PR and marketing outreach. It would have resulted in a substantially increased audience and a better venue for teaching the world to run a faster web.

Now that I’ve griped and aired my disappointment. I can focus on the gobs of awesomeness that was Velocity.

1. The conference was put on quite well from an operational perspective. Things started on time, A/V problems were non-existent. Like an idiot, I managed to lose my MacBook Air power adapter and the A/V crew managed to recover it for me. Conferences just plain suck when they have technical difficulties; this one had none.
2. The two tracks at the conference were extremely well articulated and while I wanted to be in both all the time (as OmniTI is a full-stack company, we care about both equally) it was an excellent split.
3. One track was performance which focused intently on user-perceived performance. This was largely front-end (HTML,CSS,JS,etc.) but also had a healthy amount of deep stack performance discussion as well including the often ignored, but much deserving networking aspect of delivering the web to users.
4. The second track was operations and I feared it would be bunch of blind “The Cloud Solves All Our Problems” sessions. Much to the contrary, it focused heavily on operational strategy and and what it takes to execute tactically. There was a bit of cloud here and there (okay everywhere), but there was very little blind and ignorant mentality that launching in the cloud was a solution to a hard scaling problem.
5. I had the opportunity to see a lot of old faces, but spent most of my time meeting new ones. The opportunity to learn more about other people’s problems is what completes me as an engineer. The people I met at this conference were both honest and open and provided a fabulous and refreshing perspective on what today’s performance and scalability problems really are.

In my workshop, I spent about 20% of the time discussing the philosophy of being a good engineer and 80% discussing practice (non-cookbook) with examples and advice. The basic message is that systems are complex and you must think of all the parts holistically or its a recipe for disaster — or failure.

Two of my favorite talks were Nicole Sullivan's “The Fast and the Fabulous: 9 ways engineering and design come together to make your site slow” and “ 10+ Deploys Per Day: Dev and Ops Cooperation at Flickr” by John Allspaw and Paul Hammond. While Nicole's presentation, like mine, was not recorded, the other was and if you want to break down the divide between operations and development, it is a must see.

All in all, I would encourage everyone reading this to attend next year's Velocity conference. I am certain you will walk away with knowledge that is both valuable and applicable.

PostgreSQL has pretty awesome date/time functionality. I've used a lot of database and the functionality and thoroughness of the treatment of dates and times (and particularly timezones) is unparalleled. As much as I'm impressed with it, I knew there would come a time where the outcome of all that cleverness would backfire.

Recently, I was doing some data partitioning. I split a couple of largish (approximately billion row) tables up into month segments. I wrote a tiny little pl/pgsql function that takes a parent table, and creates an inherited child with the right indexes, triggers, check constraints (for constraint exclusion) and permissions. I renamed the big table something transient, created a new parent table with the old table's name, made the old unwieldy table a child of that table and then created a whole bunch of new partitions. This allowed me to pretty much ignore my shenanigans from the application side. Once I created the partitions, I need to back populate them. To do this, I did the following:

ALTER TABLE newchildtable_200903 DISABLE TRIGGER ALL;
INSERT INTO newchildtable_200903
  SELECT * FROM oldcrappytable
  WHERE whence >= '2009-03-01 00:00:00-00'::timestamptz
      AND whence < '2009-03-01 00:00:00-00'::timstamptz + '1 month'::interval;
DELETE FROM oldcrappytable
  WHERE whence >= '2009-03-01 00:00:00-00'::timestamptz
      AND whence < '2009-03-01 00:00:00-00'::timstamptz + '1 month'::interval;
ALTER TABLE newchildtable_200903 ENABLE TRIGGER ALL;

Suffice it to say, this did not do what I wanted at all.

PostgreSQL's interval type is one of its more clever features. The idea that a month isn't always equal to a month is a Gregorian truism. So, PostgreSQL is design to "do the right thing" and consider a month in the context of another argument. A month in the above example is a month with respect to March. Right? No.

The lacking part here is the timezone. I do the partitioning in UTC (because I'm not insane). So, I need the month of March in UTC. Although I explicitly stated '2009-03-01 00:00:00-00' in UTC, PostgreSQL interprets that in the client's timezone... and then adds a month. I'm in US/Eastern which is trailing UTC by four or five hours and thus the reference starting point is actually in February of 2009, which only had 28 days. So, the latter inequality up there does not do through the end of March!

I would argue that this behavior is invalid, because of the extremely unexpected results of the following simple test case:

postgres=# select ('2009-03-01 00:00:00-00'::timestamptz + '1 month'::interval);
2009-03-28 19:00:00-04

postgres=# set timezone = 'utc';
SET

postgres=# select ('2009-03-01 00:00:00-00'::timestamptz + '1 month'::interval);
2009-04-01 00:00:00+00

Here I get a completely different date/time if I ask what appears to be a very unambiguous question depending on whether I'm left or right of the Prime Meridian.

I post here, I post there... I certainly don't post everywhere. Anyway, I wrote an article that discusses the basic technologies that power large cloud-like storage systems. Thought you might be interested.

A little over a year ago, I started in on a project that was of significant scope. Not a few scripts hacked together, nor a conglomeration of pre-existing tools, but rather a carefully engineered product. What product is this? Reconnoiter.

About 10 years ago, we were neck deep in large scale e-mail architecture. We felt pain, we were up at 3am every night attempting to make systems work. Finally, we decided enough was enough and started a skunkworks project to build a better e-mail server. Well, that turned out pretty well. It's got considerable momentum at this stage and is leading the industry as the most advanced digital messaging platform on the planet.

Over the past several (12) years, we have run operations for small and large sites alike. We're responsible for waking up and fixing things at 3am when they are not working. We're responsible for not only designing highly scalable architectures, we're responsible sticking around and seeing them through to the finish. Many people are writing tools for management; two in the recent spotlight are Puppet and Chef. We had very little pain in the arena of provisioning and maintaining systems. I have a theory as to why that is, but that is a topic for another monologue. One of the distinct pains we have suffered since we began revolves around monitoring.

The first issue is that monitoring is two things:

1. trend analysis for long-term capacity planning and postmortem analysis,
2. fault detection.

There are many tools today that are hard to use and fail to address our needs for managing thousands of very different machines. Worse, the tools do only one or the other. This means that we must invest time checking disk space in the fault detection tool to alert us when it is "too full" and configure a similar check in a trending tool to show us historical information. Some patchwork was introduced into fault detection tools like Nagios to add trending features... and when I use it, it is clear it was not central to the design.

I have a lot of gripes, but I won't go into all of them. Suffice to say I have them and I think they are the true fuel for developing a next-gen tool to make operations folk suffer less. Combine that with the combustive talent of the engineering group at OmniTI (and now a few outside it) and the oxygen that the open source community provides, and we'll be having a barbecue in no time.

So what's new?

A lot has happened in recent months on the Reconnoiter front. Here's a set of highlights:

• OmniTI labs got its IANA enterprise number. On that we build SNMP trap support into noitd.
• Reconnoiter got its IANA assigned port number, no more picking random ports. 43919 or bust!
• We support durable streaming (for collection and storage of metrics)
• We support temporal streaming and check cloning for real-time, high-granularity metric collection. This means cross-check (even cross-datacenter) real-time graphs for pretty awesome event correlation.
• We integrated Esper as a streaming database (complex event processor) as the foundation on which to build fault detection. There's a long way to go here, but the plumbing is in place.
• We build a custodial daemon capable of running nagios-type checks to ease adoption -- even though it made me cry.
• We started sitting on #noit on freenode, though it is still eerily quiet in there.
• We got some new users with the brilliant patience for alpha software and excellent feedback!
• I'll be talking about Reconnoiter at the 2009 O'Reilly Open Source Convention.
• It's starting to get some serious attention, so it's time we add some polish!

We've been slowly introducing our managed clients to Reconnoiter and we have, at this point, about a terabyte of metric data. In Reconnoiter, there is no default action to discard data. Yes, that's right. Go buy more disk. It's cheap. You'll thank me next time you have an anomaly today that you think reminds you of one seven months ago... and when you go look at the graph you actually find all the data at its original granularity.