The problem many MySQL/MariaDB 5.5+ users are painfully aware of:

InnoDB: Using Linux native AIO
InnoDB: Warning: io_setup() failed with EAGAIN. Will make 5 attempts before giving up.
InnoDB: Warning: io_setup() attempt 1 failed.
InnoDB: Warning: io_setup() attempt 2 failed.
InnoDB: Warning: io_setup() attempt 3 failed.
InnoDB: Warning: io_setup() attempt 4 failed.
InnoDB: Warning: io_setup() attempt 5 failed.
InnoDB: Error: io_setup() failed with EAGAIN after 5 attempts.
InnoDB: You can disable Linux Native AIO by setting innodb_native_aio = off in my.cnf
InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: Completed initialization of buffer pool
mysqld got signal 11 ;

There is no news that disabling InnoDB native AIO is not exactly the best possible option. It’s also not a secret that the alternative is increasing aio-max-nr if …

Benchmarking is a tricky thing, especially when it comes to compression. Some data compresses quite well while other data does not compress at all. Storing jpeg images in a BLOB column produces 0% compression, but storing the string “AAAAAAAAAAAAAAAAAAAA” in a VARCHAR(20) column produces extremely high (and unrealistic) compression numbers.

This week I was assisting a TokuDB customer understand the insertion performance of TokuDB versus InnoDB and MyISAM for their actual data. The table contained a single VARCHAR(50), multiple INTEGER, one SET, one DECIMAL, and a surrogate primary key.  To support a varied query workload they needed 6 indexes.

Here is an obfuscated schema of the table:

col1 varchar(50) NOT NULL,
col2 int(40) NOT NULL DEFAULT '0',
col3 int(10) NOT NULL DEFAULT '0',
col4 int(10) NOT NULL DEFAULT '0',
col5 int(10) NOT NULL DEFAULT '0',
col6 set('val1', 'val2', ..., ‘val19’, 'val20',) NOT NULL DEFAULT …

When you use SHOW VARIABLES LIKE “have_%” to see whether a particular feature is enabled, you will note the value of NO for some, and DISABLED for others. These values are not intrinsically clear for the casual onlooker, and often cause confusion. Typically, this happens with SSL and InnoDB. So, here is a quick clarification!

• NO means that the feature was not enabled (or was actively disabled) in the build. This means the code and any required libraries are not present in the binary.
• DISABLED means that the feature is built in and capable of working in the binary, but is disabled due to relevant my.cnf settings.
• YES means the feature is available, and configured in my.cnf.

SSL tends to show up as DISABLED, until you configure the appropriate settings to use it …

Can OLTP database workloads use Amazon S3 as primary storage? Now they can, thanks to the Cloud Storage Engine (ClouSE), but the question is: how fast?

• ClouSE on “across the street” vs. “across the continent” cloud storage
• ClouSE vs InnoDB
• ClouSE benefits

To answer the question about performance, we decided to run db_STRESS benchmark on a MySQL database in Amazon EC2.  We compared 3 configurations:

• “Across the street storage”: ClouSE with data stored in S3 in the same …

Solving the Challenges of Big Databases with MySQL

When you’re using MySQL for big data (more than ten times as large as main memory), these challenges often arise: loading data fast; maintaining indexes under insertions deletions, and updates; adding and removing columns online; adding indexes online; preventing slave lag; and compressing data effectively.

This session shows why some of these challenges are difficult to solve with storage engines based on B-trees, how Fractal Tree® data structures work, and why they can help solve these problems. Tokutek sells a transaction-safe Fractal Tree storage engine for MySQL, but the presentation is primarily about the underlying technology. It includes a discussion of both the theoretical and practical aspects of Fractal Tree indexes.

I have the privilege of being able to give this talk at both conferences, so please stop by my presentation at …

In my last post, I wrote that I observed many similarities between TokuDB and MySQL Cluster. Many features that benefit TokuDB also benefit MySQL Cluster, and vice versa, with Hot Column Addition and Deletion (HCAD) being an example. Over my next few posts, I expand on some more of these possibly unexpected similarities.

Today I want to focus on optimizer support for clustering keys. Both MySQL Cluster and TokuDB can benefit from the MySQL optimizer supporting clustering keys. For TokuDB, the benefit is obvious, as TokuDB supports clustering keys. A non-negligible part of our effort is changing the …

Table optimization is a necessary evil; tables sometimes need to be optimized to reclaim space or to improve query performance.  Unfortunately, MySQL blocks writes to a table while it is being optimized.  Because optimization time is proportional to the table size, writes can be blocked for a long time.  Fractal Tree indexes support online optimization; however, the MySQL metadata lock gets in the way of writing while optimizing.  We will describe a simple patch to MySQL that enables online optimization of TokuDB tables.

Why do tables need to be optimized?  Here are some reasons.

• Insertions with random keys can result in a tree with underutilized leaf blocks.  Many tree algorithms split nodes in half when they become full.  If these nodes are stored in fixed sized blocks, like many B-trees do, then there can be a lot of wasted space.  Table optimization of B-trees write blocks with less …

We just ran into a nasty InnoDB bug that only seems to exist in MySQL 5.5.25:

An InnoDB update that modifies a rows primary key can trigger some recursive behavior that creates new rows until all disk space is exceeded. This does not affect all primary key updates in general but only gets triggered when a few other conditions are also met, so you're not too likely to run into it, but if you do your mysqld server will waste a lot of IO bandwidth and storage space on this and will usually eventually run out of disk space.

read more

As a TokuDB storage engine developer, numerous times I’ve been struck by the similarities between MySQL Cluster and TokuDB. Namely, many times where I find myself thinking, “TokuDB would benefit from this feature”, I also end up thinking “MySQL Cluster would benefit from this feature” as well.

At first glance, one may wonder why. TokuDB is a storage engine designed to work well on big data, providing compression, agility, and performance, while MySQL Cluster is a distributed database solution (http://www.mysql.com/products/cluster/) that provides (among many other things) auto sharding and 99.999% availability. TokuDB’s innovation, Fractal Trees® indexes, are designed to drastically reduce the number of disk seeks performed, but TokuDB still operates on a hard disk. MySQL Cluster operates over a network. How can we be two peas in a pod?

But when I …

Last week one of my collagues came to me with the following problem. He was not able to create an innodb table. MySQL returned ERROR 1005: Can’t create table (errno: -1). Most of the times this problem is a foreign key related issue but now this wasn’t the case (Foreign key error will return errno: 150). What actually happened is that ibdata already contains the registration of the new table but the frm file wasn’t there. This way when he wanted to create the table the server responded with error. Although he wasn’t able to remove the table either. It was caused the file-by-file cloning of the database.

For better understanding you have to be familiar with the inner operations of MySQL. With the most simplistic explaination I could say that MySQL is just working on a high SQL abstraction level which can use different storage engine for storing datas. Viewing from this aspect MySQL doesn’t care how the files …