theory in practice

Dealing with plan regressions caused by changes to the query optimizer has been a royal pain for any development organization. Even if you manage to shield your customers with clever tricks from experiencing these in their end-user workloads, plan regressions are a major drain of resources during software development. Therefore, knowing whether a change in software (or configuration) is beneficial or detrimental is a huge benefit!

Together with my friends at Microsoft I’ve developed such an ‘early warning system’ for plan regressions based on relative orders within a plan space.

The details are in this paper, accepted at this year’s DBTest workshop at SIGMOD where we’ll talk about optimizer testing in detail — yet another good reason to book your ticket for Athens quick!

Performance evaluation with benchmarks has a long-standing tradition in the database industry and it has become customary to use benchmarks to underline the usefulness of many an idea in research papers over the past decades. Besides testing a system with end-to-end scenario, most benchmarks are also suitable to demonstrate performance enhancements of storage and query execution etc. in isolation. However, none of the benchmarks, I would argue, are any good at assessing the query optimizer.

There is probably no other single component in a database system on which vendors have spent more research and development dollars than the query optimizer. And yet, there is no dedicated optimizer benchmark that helps judge their return on investment. Granted, in many cases a very good or a very bad optimizer will not go unnoticed even in standard benchmarks (take a moment to compare the official TPC-H reports for individual query times across different systems and you’ll see what I mean). But apart from the obvious cases, it remains difficult even for the expert to tell whether the optimizer did a great job for a given query.

I’ve been toying with the idea for an optimizer benchmark over the past years and tried to encourage researchers in industrial and academic labs to give it a thought—unfortunately, after great initial excitement everybody seemed to have arrived at the conclusion that it was just ‘too difficult’. I’m not one to give up quickly, so, let’s take a shot at it!

First, we need to come to an understanding what we want to get out of such an exercise. Well, here’s a list of attributes that I would like to see associated with this benchmark:

simple
The score of the benchmark has to be a simple value. Multi-dimensional measures—i.e., vectors of individual (maybe even unrelated) measures—are difficult to compare. One of the strength of the TPC benchmarks is just that: a single score.

transparent
The score should express a strong and semantically clear assessment. Ideally, the score relates to a simple and tangible concept of goodness, e.g., number of mistakes the optimizer has made, etc. A great counter example would be, say, the error margin in cardinality estimation: that’s clearly an important measure but it doesn’t tell me (a) whether I can do better at all, and (b) if so, how?

technology agnostic
The benchmark should be agnostic of the particular optimizer technology used. We would like to be able to compare different optimization algorithms and strategies. I don’t care whether your optimizer uses heuristics or is cost-based or a smart hybrid—as long as it does generate great plans you should get a great score!

platform specific
A query optimizer tries to generate the optimal plan for a given target platform, i.e., a combination of execution and storage components, among other things. In general, no two target platforms are the same. For example, execution engines differ in the operators they support, storage engines differ in performance for scans and look-ups, etc. The score must take into account that every optimizer will optimize for a different target platform.

portable
If you thought the previous two were already hard to reconcile, you’ll enjoy this one: ideally the benchmark can be applied to any query optimizer. If special hooks are required, they should be general enough that any optimizer can be expected to provide these.

surgical
The benchmark should work for individual queries. This will enable implementers to troubleshoot specific optimizer problems that are otherwise buried in larger workloads or hidden by interactions of other components.

application specific
The benchmark, or an extension of it, should enable application specific comparisons. That is, it should apply to any workload in addition to individual queries. This will enable operators and users to assess whether an optimizer is suitable for a specific application scenario.

practical
The score should be straight-forward to compute and lend itself easily to automation in test suites and daily reporting within a development organization.

Finally a word of caution: a lot of people seem to get suckered into what I would call the ‘intuitive stuff’: trying to argue about  plans being ‘acceptable’ if their anticipated cost is within X times the optimal and so on. I know, it’s very tempting but I’m afraid it will not get us any closer to a scientific method. So, we’ll try to stay away from these.

I know the list above is quite a tall order! For now, I leave you with this set of desiderata, and will present a few ideas over the next couple of months.

A few days ago a frustrated recruiter from an agency we have been using on and off for a while wrote to me

I [come] to the conclusion that your standards are impossibly high.  I don’t see myself being able to fill any of your roles.

For a fact, we do hire engineers on an on-going basis via all conceivable channels including agencies, which goes to show that it isn’t impossible if difficult.  We do pride ourselves for hiring only the top candidates, but, hey, that’s what they all say, right? Anyways, this little episode made me think some more about our hiring process and in the next couple of posts I’d like to shed some light on how to find great talent. Let’s first take a look at the target candidates.

The candidates we are looking for

A number of folks (most notably Joel Spolsky) have written extensively on this subject in the past and boiled it down to the simple and catchy formula: “smart people who get stuff done!”. In a software industry context I actually prefer to call them “people who can do both research and development”. Unlike a number of other places out there, here at Greenplum we’re looking for people who can drive a research agenda, solve hard problems, and communicate the solutions effectively, and, at the same time, they have to be able to turn theoretical results into enterprise software. Simple enough you might think, and yet, I see plenty of candidates who tell me they do R&D but “don’t code”. I’ve come to the conclusion, most candidates are good at one or the other — but only very few excel at both. In order to find the right talent (and enable a larger team to do so in order to scale out the process) you need to understand your candidate pool. The little drawing on the left is what I found captures the landscape best: the theoreticians, the pragmatists, and, well, the people who are both and these are the ones you want to hire.

The theoreticians. The upper left quadrant (‘research’) are the theoreticians who may very well be able to solve hard problems but are having a hard time to turn their results into robust and elegant software solutions. In fact, writing good software is, as you know, extremely challenging. Turning the solution for a hard problem into software is much harder than most people think. Turning it into maintainable software that leaves enough headroom to use it as a workbench for future research is even harder. (If you don’t believe me, try to find an implementation of Paxos that doesn’t require major rewrites before it fits your project.)

The pragmatics. The lower right quadrant (‘development’) are the pragmatics who produce huge amounts of code rapidly that, to no surprise, needs constant fixing afterward. The pragmatics use the generally agreed upon fact that there is no such thing as perfect software as an excuse to create prototypes that tend to escape into production.

Ironically, both of these group have surprisingly little regard for each other: the pragmatics call the theorists “too academic”, the theorist think of the pragmatics as “hackers”; and that’s putting it mildly. Interestingly, it also does not occur to them that combining their skills/talents would be highly desirable — they typically view the world as either research or development.

R&D: pragmatic theoreticians

Here at Greenplum, we’re looking for people who can do it all — the candidates in the upper right quadrant (‘R&D’). And that’s not just because we think it’s “somehow” better. What we’re looking for are folks who are strong abstract thinkers, who can solve hard problems on paper, file for patents, and publish their solutions; but also design and build elegant software solutions, drive designs top-down and implementations bottom-up.

Luckily there are folks who are willing to take on hard challenges! If you’re intrigued and think you have what it takes, visit http://www.greenplum.com/about-us/careers and apply for the Query Processing or the Distributed Systems Engineering position.

P.S. we’re opening offices in Beijing and Seattle!

Is workload management via admission control good enough? Definitely not.

Admission control based workload management is only one part of the solution and most database systems support this by now, one way or the other. However, the ability to run queries in the background without affecting more important query workloads is key to a better resource utilization as well as customer satisfaction.

Recently, we’ve designed some pretty nifty mechanisms, based on control theory, for prioritization of queries and workloads that are both intuitive/easy to administrate as well as highly resource efficient—including advanced features like priority inversion, i.e., speeding up a low priority query that got in the way of a high priority one, etc.

The details are in this paper, accepted at next year’s ICDE.

Welcome to “theory in practice” — I intend to turn this into a place to discuss database technology, ideas related to “big data”, and what it takes to turn these ideas into products. And although you will eventually find all sorts of subjects here, there are three recurring themes that are near and dear to my heart:

Research & Development – In my job research and development are the two sides of the same coin. We are constantly looking for exciting ways to enhance database technology by solving some darn hard problems and we are constantly looking how best to turn these solutions into solid products.

Expectations & Applications – Users have data problems not database problems. We database people tend to think in terms of implementations and features but forget sometimes what it actually was our users wanted to solve in the first place and — equally important — in which context.

The Human Element – To generate great ideas and build great products, you need a great team. Hiring and mentoring is a fundamental part of my professional life — hence, we will talk about observations around the hiring process and tips & tricks for candidates and hiring managers alike.

Okay, this will be fun. Stay tuned.