Entries (RSS)
In world-class motorcycle racing, a small Italian company named Ducati has dominated the much larger and resource-rich Japanese manufacturers. While motorcycle racing is ignored by Americans, it is a very big sport in much of the rest of the world. Since motorcycle sales follow the “win on Sunday, sell on Monday” model, more than pride is at stake and motorcycle companies pour large sums of money into performance engineering and professional racing. Japanese bikes weigh about the same as the Ducatis, make about the same horsepower and are ridden by equally talented riders, yet the Ducatis win a lot more races.
So what does all of this have to do with my day job? Well, I’m as addicted to motorcycle racing and design as I am to columnar DBMS architecture and design, and I naturally find parallels between the two, so I hope you’ll pardon me weaving the two together…
For those who aren’t already familiar with the term ‘columnar database’, it refers primarily to how the data is laid out on the disk. There are other subtleties that we’ll look at in the future, but that’s the dramatic technology difference.
Since disk-layout is what differentiates columnar databases, it is important to note that from your application’s perspective it looks just like a conventional “row-wise” database such as Oracle, SQL Server, DB2, Teradata or Netezza. ParAccel, therefore, talks to your application in standard SQL through ODBC and JDBC. It is fully SQL92 compliant and also includes many SQL99 and SQL2003 features.
How does columnar disk organization impact query performance? Let’s use an extremely simplified example assuming a single computer with a single disk drive and a single data table. The example I typically use is a demographic scenario using US Census-type data with 100 fields including a person’s name, state, age, gender and so on. Assume the drive is laid out in 100 “sections” and that will frame how we place our data.
- In a row-wise database:
- The 300 million records are grouped into 100 “data blocks”, with each section having 3 million rows.
- In a columnar database:
- The 100 fields are grouped into 100 “column blocks”, with each column containing the 300 million entries for that field.
- Each column is written to one section of the disk, in exactly the same record order (e.g., record 1’s column 1 value is followed by record 2’s column 1 value which is followed by record 3’s, etc.)
(Note: This discussion is ignoring all of the interesting ways the data can be compressed or indexed, but that is the topic of another discussion. Let’s keep this one purely on the topic of columnar versus row-wise storage.)
If we pose a simple analytic query – “What is the average age of people, by state?” — then each type of DBMS will return fifty records (one per state) with two fields (age and state), though they will perform the I/O very differently:
- The row-wise database will
- Scan all 100 sections of the disk, sequentially (1 through 100)
- After the data arrives at the CPU, extract the age-state pair and aggregate it into the fifty-record result set.
- The columnar database will
- Scan only the age and state sections of the disk, at the same time, interleaving the sequential reads from the two columns and essentially splicing together a stream of “2-column rows”.
- After the data arrives at the CPU, aggregate into the fifty-record result set.
The key difference is that the row-wise database did 50 times more disk I/O than the columnar database. Since disks are really, really slow ( compared to memory and CPU) they are typically the gating factor in performance — this explains why columnar databases can so easily be 50X faster than row-wise with no tuning!
If we made the query a little more interesting by asking “What is the average age by state where income > $100,000 and residency > 3 years”, then the columnar database would now be going to the disk for 4 columns worth of information and would be 25X faster than row-wise. You get the point.
Now let’s return to the important topic of this blog post. Why do Ducatis dominate the world racing circuit? The answer is surprisingly simple. Unlike NASCAR racing, motorcycle racing involves a lot of curves and very few straights. The critical measure is how fast you are on the plentiful curves rather than how fast you are on the less common straights. The problem is that if you apply too much power to the tires, you will exceed the tire’s ability to hold onto the pavement and it will slide out (aka crash). If you apply too little power, you will lose to the guys who apply just a little more.
Here’s the subtlety. The Japanese racing bikes all use 4-cylinder engines that spin at high RPMs and thus deliver to the tires about 150 power pulses per second. The Ducatis use 2-cylinder engines than spin at lower RPMs and thus deliver about 50 power pulses per section.
On the Japanese bike delivering 150 pulses, if the tire begins to slide due to an excessive power pulse, the next pulse increases the sliding and the next pulse makes it worse. In under a second, the bike is sliding off the track in a shower of sparks and the rider is scuffing up an otherwise colorful leather racing suit. On the Ducati, when the tire begins to slide from an excessive power pulse, the tire can recover traction before the next power pulse. In other words, the very nature of how a Ducati delivers its power to the tire is what makes a Ducati a winner.
In high performance databases, as in high performance motorcycles, simple aspects of technology can make the difference between the winner and loser.
[Note: When I was an amateur racer, I raced a motorcycle with 1 cylinder and low RPMS and I never crashed it. I did, however, crash a Ducati on the racetrack which proves that DBMS engineers should probably not race motorcycles.]
Tags: column-wise, columnar, ducatis, row-wise
Dear Barry,
I think that you hit the nail on the head when you called the difference in the power delivery of a v-twin and inline-4 engines a ’subtlety’. There are many other important factors that affect the performance of a motorcycle on the race track. Simply put, Ducatis are high-end machines that cost at least 50% more than a Japanese machine of comparable displacement, with some models being 2 times more expensive. When it comes to technicalities, not only are Ducatis lighter, they also have better suspensions and brakes. Japanese bikes are produced in much larger numbers, hence they have to be reasonably affordable. The choices made by the Japanese engineers represent a different tradeoff because the goal of the exercise was different.
Building ‘the ultimate column-store RDBMS’ may win some benchmarks. Does it mean that it will be more commercially successful than the competition? Not necessarily. In the real world database installations are built to achieve certain business goals, not to set a new world record. Budget is always important, if money were no object, everyone would simply build a Teradata system that is big enough and there would be no need for column-stores or other performance optimizations. Even if you have the best weapon, it does not mean that you will win the war. You need the weapon that is good enough and you need enough of it. During the second world war, Germany has built some of the most effective tanks of all countries involved in the conflict. Compared to German PzKpfw V “Panther”, the Russian T-34s were rather crudely built, not very well armored and their guns were not as effective. Their huge upside was that they could be built in much larger numbers. In the end Germans found themselves outgunned and outnumbered. Ultimately, having successfully designed and built the ultimate tank did not quite pan out for the Third Reich. The whole point of buying an analytic RDBMS that functions on MPP platform is ability to scale out easily and inexpensively. Unfortunately SAN storage is anything but inexpensive. A commodity server with direct-attached storage is a lot like a T-34 tank. It is by no means fancy or a top performer, but they can be had in large numbers for not a lot of money. So long as the RDBMS that runs on them scales close to linearly – it’s all good.