Many database management tasks become difficult as you move from millions of rows and gigabytes of data to billions of rows and terabytes of data. Such tasks include ingesting data while maintaining indexes; changing schemas without downtime; and supporting connections, replication, and backup. For some scaling problems (connections and replication), MySQL® is better than most of the competition. For others, such as indexing, schema changes, and backup, MySQL has typically been harder to use. Fortunately, the tasks MySQL does well are in its core, whereas the tasks that are more difficult can be solved with storage engine plug-ins.

I recently gave a talk at Oracle Open World 11, a copy of which can be found here. This presentation discusses how MySQL’s storage engines have recently made dramatic progress in large database manageability.

A list of …

I’ve been benchmarking and testing TokuDB for a few months now. One goal of benchmarking is to understand what is limiting the performance of a particular configuration. I frequently use “show engine [innodb/tokudb] status;” from within the MySQL command line client as part of my research.

As I run most of my benchmarks on InnoDB as well as TokuDB, I noticed that there are significant differences in the way each present status information. InnoDB returns a single row, with various sections and carriage returns to maintain readability. In contrast, TokuDB presents one piece of status information per row (currently 139 rows as of TokuDB v5.0.5). This is an important distinction if you want to parse, compare, or store discrete status values. Here is sample output from each engine. I’ve cut out portions of each to maintain readability.

InnoDB plugin v1.0.13

mysql> show engine innodb status; …

We were happy to see a lot of folks from Japan on Twitter this weekend having a discussion about MySQL and Tokutek. While we always endeavor to explain ourselves as simply as possible, hearing what users and peers have to say and ask in their native language is very helpful. Here is a sampling of several of the 30+ tweets and re-tweets (translations courtesy of a colleague I know from frequent past visits to Tokyo and Yokohama):

.

First, @frsyuki provided a general overview:

“TokuDB” 新種のMySQLストレージエンジン。INSERTが20〜80倍ほど速い、パーティションなしで数TBのデータを突っ込める、MVCCサポートなど。Fractal Treeというアルゴリズムを実装しているらしい。http://www.tokutek.com/

…

Unless you are Ashton Kutcher (@aplusk), or one of his Hollywood buddies, you don’t need to read any further. Allow me to explain…

Over the weekend, we launched our new website. This type of announcement used to be interesting in the high-tech world. I heard Kara Swisher of the WSJ’s All things D speak at a MassTLC event in May.  She admitted back in the 1990s, when the web was just getting into high gear, that a new website from an interesting company might actually get some coverage. Not anymore.

I’ve also been told at all the SEO classes I’ve …

Thanks again to Erin O’Neill and Mike Tougeron for having me at the SF MySQL Meetup last month for the talk on “Understanding Indexing.” The crowd was very interactive, and I appreciated that over 100 people signed up for the event and left some very positive comments and reviews.

Thanks to Mike, a video of the talk is now available:

As a brief overview – Application performance often depends on how fast a query can respond and query performance almost always depends on good indexing. So one of the quickest and least expensive ways to increase application performance is to optimize the indexes. This talk presents three simple and effective rules on how to construct indexes around queries that …

Last week I gave a talk entitled “Don’t Thrash: How to Cache your Hash.” The talk took place at the Workshop on Algorithms and Data Structures (ADS) in a medieval castle turned conference center in Bertinoro, Italy. An earlier version of this work (with the same title) appeared at the HotStorage conference in Portland, OR. Tokutek co-founders Bradley, Martin, and I are coauthors on the work, along with students and other faculty at Stony Brook University.

The talk title is colorful and doggerel-y. Here’s what the title means. “Cache your hash”—the so-called Bloom Filter type data structure. A Bloom filter acts like a negative cache, …

Shard-Query is an open source tool kit which helps improve the performance of queries against a MySQL database by distributing the work over multiple machines and/or multiple cores. This is similar to the divide and conquer approach that Hive takes in combination with Hadoop. Shard-Query applies a clever approach to parallelism which allows it to significantly improve the performance of queries by spreading the work over all available compute resources. In this test, Shard-Query averages a nearly 6x (max over 10x) improvement over the baseline, as shown in the following graph:

One significant advantage of Shard-Query over Hive is that it works with existing MySQL data sets and queries. Another advantage is that it works with all MySQL …

I had a good and lively audience at my presentation today. I was happy to give them a sneek preview of the Mk.III implementation of OQGRAPH.

Oh yeah! I guess I'm announcing the availability of the source code of the MkIII implementation.

Links are in my slides... http://goo.gl/UrybZ

Btw, building the storage engine requires Boost libraries and libJudy installed. It's currently in a MariaDB

 Just got done with day 1 from the MySQL conf 2011... tutorial day. I decided to attend a session a bit outside of my comfort zone, so I chose MySQL Plugin Development. I haven't written any serious C/C++ for over 10 years, but lately it's been more appealing to me.

read more

I recently worked on a customer case where at seemingly random times, inserts would fail with Innodb error 139. This is a rather simple problem, but due to it’s nature, it may only affect you after you already have a system running in production for a while.

Suppose you have the following table structure:

CREATE TABLE example (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
fname TEXT NOT NULL,
fcomment TEXT,
ftitle TEXT NOT NULL,
fsubtitle TEXT NOT NULL,
fcontent TEXT NOT NULL,
fheader TEXT,
ffooter TEXT,
fdisclaimer TEXT,
fcopyright TEXT,
fstylesheet TEXT,
fterms TEXT,
PRIMARY KEY (id)
) Engine=InnoDB;

Now you insert some test data into it:
mysql> INSERT INTO example
-> VALUES (
->   NULL,
->   'First example',
->   'First comment',
->   'First title',
->   'First subtitle',
->   'First …