Yesterday, Tokutek was invited (thanks to David Hughson, Vice-Consul of the British Consulate) to the Massachusetts State House for a forum on international business, hosted by Governor Deval Patrick. I felt pretty good about our Commonwealth – apparently we are the only state whose bond rating has improved since 2007. And, even though big firms like Google, Microsoft and IBM have headquarters elsewhere, it’s their MA offices that are their fastest growing ones. We also get along well with European tech centers. Despite some unpleasantness for a brief period in the past, the UK is the number one country for Massachusetts exports and 250 Massachusetts companies have operations there. I was happy to hear this, given all the expertise we see in MySQL in the UK.

…

Yesterday, Percona held Percona Live NYC, which they describe as an “intensive one-day MySQL summit.” They meant it. It was like drinking from a firehose. There was too much for me to give a complete report, so I’d like to highlight two sessions that stuck out for me.

Why SQL Wins

Sergei Tsarev (Clustrix) gave a great overview of the last 50 years of database development. He talked about the early days, in which what we now think of as database functionality had to be implemented in each application. Programmer productivity was therefore low.

As modern SQL databases emerged, productivity shot up since databases bundled up common functionality with an easy-to-code interface. This now seems like a golden age of databases, in which transactional semantics were hashed out.

Fast forward to today. Database performance has failed to keep up with …

OldSQL DBs based on B-trees have some well-known problems and workarounds. TokuDB is a NewSQL storage engines based on Fractal Tree indexing, so the natural question is how InnoDB practice translates into TokuDB. This post gives a quick overview. Enjoy!

FAQ

Q: How do I tune TokuDB?
A: You don’t!

TokuDB has almost no parameters you can set, and many of them are related to diagnostics — things like how often should the information about insertion or query progress be updated. We recommend that parameters be left unchanged.

Q: What do I do when indexes are bigger than memory?
A: Nothing!

TokuDB is based on Fractal Trees, which are designed to index fast, even when indexes are bigger than memory.

Q: What’s …

I’ve recently been blogging about how partitioning is a poor man’s answer to covering indexes. I got the following comment from Jaimie Sirovich:

“There are many environments where you could end up creating N! indices to cover queries for queries against lots of dimensions.”

[Just a note: this is only one of several points he made. I just wanted to dig into this one in some detail. Here goes...]

Although it is, in theory, possible to generate a workload that would take N! indexes, this is not a realistic (or useful) bound (leaving aside that this workload would kill partitioning!). For one thing, it would take N! queries to exercise all those indexes. And the queries would have to include every field in the where clause — as we’ll get into below.

So what is a reasonable bound on the number of covering indexes that …

On April 1st, the Department of Computer Science at Rutgers University, where I am a professor, held an open house. I gave a talk called “Elephants on a Trapeze: Keeping Big Data Agile”.

The talk is an introduction to performance issues related to big data without getting too technical. You’ll have to decide if I succeeded with the “not too technical” part. My take is on how to keep big data indexed — not surprising since the work in this talk is the basis for TokuDB®, Tokutek’s MySQL storage engine for keeping large data indexed. A video of my talk can be found here.

Elephants on a Trapeze: Keeping Big Data Agile from Tokutek on …

Kudos to Ronald Bradford for creating a new MySQL meetup group in New York city and giving MySQL related talks. The next one is tonight, titled “MySQL Idiosyncrasies That Bite”. Information on it can be found at http://ny.effectivemysql.com/events/16884850/.

We’ll have a contingent from our New York office there this evening. We went to the last one on indexing (a favorite topic of ours) in March and it was excellent.

We look forward to seeing folks there as well as at upcoming NY events, including Percona Live (May 26th) and future Effective MySQL meetups.

What a busy week – between O’Reilly and Collaborate we gave a number of talks, sponsored a booth, and took some long flights in between the shows. But what an interesting week. The MySQL conference is a great time to catch up with customers and, as always, we heard some excellent feedback on a variety of topics. From a storage engine provider’s perspective, we were happy to hear about the improvements to the Optimizer, and appreciated having the opportunity to attend the …

Jawa

Issue addressed:

• Process over 10 million log entries per day without partitions or other workarounds.

MySQL + TokuDB Tames Huge Logfile Processing Workload

The Company: Founded in 2005, Jawa develops software and media solutions enabling people to stay connected to information, entertainment and communities while on the go. Jawa is a privately held company headquartered in Scottsdale, Arizona.

The Challenge: Jawa is experiencing tremendous growth as it provides critical billing infrastructure to mobile gaming and payment applications for third party solutions. Part of this requires the maintenance and analysis of hundreds of gigabytes of logfiles …

Why do B-trees need “Tricks” to work?

Marko Mäkelä recently posted a couple of “tips and tricks” you can use to improve InnoDB performance. Tips and tricks. A general purpose relational database like MySQL shouldn’t need “tips and tricks” to perform well, and I lay the blame on design choices that were made in the early ’70s: the B-tree data structure underlying all OldSQL databases. B-trees were designed for machines that had very different performance characteristics than the machines of today. Hardware has changed, but B-trees are the same. Tips and Tricks are an attempt to make up the difference.

So B-tree implementers — InnoDB, Oracle, MS SQL Server — are fighting an uphill battle; they’re fighting the future. B-trees just aren’t meant to cope with high-bandwidth, slow-seek-time storage systems, because …

Hot Column Addition and Deletion (HCAD)

In the previous HCAD post, I described HCAD and showed that it can reduce the downtime of column addition (or deletion) from 18 hours to 3 seconds. In fact, the downtime of InnoDB is proportional to the size of the database, whereas the downtime for TokuDB 5.0 depends on the time it takes for MySQL to close and reopen a table — a time that’s independent of database size. Go ahead and build bigger tables. The HCAD downtime for TokuDB won’t increase.

You may be wondering how we do HCAD. Here goes:

Under the hood

TokuDB is based on Fractal Tree indexing, one of the cools features of which is that they replace random I/O with sequential I/O. The way this happens has an impact on how HCAD happens, …