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, …

Running with Big Data

It’s spring here in Boston, though one could hardly tell (still barely hitting 40°F). So, for those stuck indoors working out on the treadmill, or those lucky enough to do a workout outdoors, we’ve got a great podcast. Our Chief Scientist and co-founder Martín Farach-Colton had the privilege of sitting down with Sheeri Cabral and Sarah Novotny for their weekly MySQL Database Community Podcast (OurSQL Episode 39). In it, he speaks about Tokutek and TokuDB v5.0, which was just released last week (see …

In Part 1, and Part 2 of this series, I presented some thoughts on partitioning. I heard some great feedback on why people use partitioning. Here, I present a flow chart that summarizes what I’ve learned. In summary: with TokuDB in the picture there’s almost no reason to use partitioning. Or I should say, there are almost always better (higher performing, more robust, lower maintenance) alternatives to partitioning.

Here goes:

1. Spindle contention? In other words, are you partitioning in order to spread your query work load across many disks? I’ve yet to see a compelling technical case that RAIDing your disks doesn’t do this as well, with much less setup and maintenance.
2. …

Review

In part one, I presented a very brief and particular view of partitioning. I covered what partitioning is, with hardly a mention of why one would use partitioning. In this post, I’ll talk about a few use cases often cited as justification for using partitions.

Lots of disks → Lots of partitioning of tables

One use case for justifying partitions is that each partition can be placed on a separate disk to avoid spindle contention. I have to say that on this one, I agree with Kevin Burton, who makes the point that if you want to distribute I/O load across several disks, you can use a RAID configuration on the disks. In this case, he says that partitioning is not worth the trouble. [NB. He makes the point that this …

Why partition?

Partitioning is a commonly touted method for achieving performance in MySQL and other databases. (See here, here, here and many other examples.) I started wondering where the performance from partitions comes from, and I’ve summarized some of my thoughts here.

But first, what is partitioning? (I’ve taken the examples from Giuseppe Maxia’s Partitions in Practice intro.)

CREATE TABLE by_year (
   d DATE
)
PARTITION BY RANGE (YEAR(d))
(
   PARTITION P1 VALUES LESS THAN (2001),
   PARTITION P2 VALUES LESS THAN …

I was happy to see the announcement yesterday from Tokutek regarding their addition of MariaDB to their list of supported platforms for the TokuDB storage engine.

One particular item in the press release caught my eye: “Our customers are choosing MariaDB more and more frequently for their most demanding database applications.” We’ve added many new features over the past year in our various 5.1 and 5.2 releases, but beyond the new goodies we’ve focused a lot of energy on removing bugs and improving performance. We’ve known for a while that MariaDB is the …

Tokutek is pleased to announce support for MariaDB for the first time with TokuDB v4.1.1 for MariaDB v5.1.47.

Our customers are choosing MariaDB more and more frequently for their most demanding database applications. We are delighted to help raise MariaDB performance to the next level by making TokuDB available on this new platform. One of our customers, who wishes to remain unnamed for the present time, chose MariaDB + TokuDB for a 3 TB database after having evaluated other MySQL alternatives and finding them unacceptably slow.

TokuDB continues to be the ideal choice for complex / high-volume applications that must have fast response times and that must simultaneously store and query large volumes of rapidly arriving data:

• Social Networking
• Real-time clickstream analysis
• Logfile Analysis
• eCommerce …

TokuDB has a big advantage over B-trees when trickle loading data into existing tables. However, it is possible to preprocess the data when bulk loading into empty tables or when new indexes are created. TokuDB release 4 now uses a parallel algorithm to speed up these types of bulk insertions. How does the parallel loader performance compare with the serial loader? We use the Air Traffic Control (ATC) data and queries described in a Percona blog and also used in an experiment with TokuDB 2.1.0 to gain some insight.

Our ATC data is about 122M rows in size, is stored in a 40GiB CSV file, and can be found in our Amazon S3 public bucket. See the end of this blog for details. We …

Tokutek is pleased to announce immediate availability of TokuDB for MySQL, version 4.1.1. It is ideally suited for delivering fast response times for complex / high-volume Web applications that must simultaneously store and query large volumes of rapidly arriving data:

• Social Networking
• Real-time clickstream analysis
• Logfile Analysis
• eCommerce Personalization
• High-speed Webcrawling

TokuDB v4.1.1 replaces TokuDB v4.1.0 and is recommended for all users. (We found a bug in v4.1.0 and have withdrawn it from our website). The new version has all of v4.1.0′s new features, including support for SAVEPOINT and an even better Fast Loader. As always, this release uses our high-performance Fractal Tree™ indexing to provide a unique combination of capabilities:

• 10x-50x faster indexing …

In posts on June 30 and July 6, I explained how implementing the commands “replace into” and “insert ignore” with TokuDB’s fractal trees data structures can be two orders of magnitude faster than implementing them with B-trees. Towards the end of each post, I hinted at that there are some caveats that complicate the story a little. On July 21st I explained one caveat, secondary keys, and on August 3rd, Rich explained another caveat. In this post, I explain the other …