InnoDB holepunch experiments

After excellent blogs by Mark Callaghan (see links below), I decided to use some of my time to experiment how different filesystems behave if the holepunch feature is used in MariaDB 10.1. First of all, MariaDB 10.1 does not use holepunch by default even if a table is page compressed (a term used in MariaDB). The holepunch feature in MariaDB is enabled with the innodb-use-trim=1 configuration variable and naturally requires support of the fallocate system call with the FALLOC_FL_PUNCH_HOLE and FALLOC_FL_KEEP_SIZE parameters. Support for these is checked during the cmake build phase.

I used CentOS Linux release 7.1.1503 (Core) using the 3.10.0-229.el7.x86_64 Linux kernel and few SSD drives in a RAID-0 setup (Intel X25-E Extreme SSDSA2SH032 G1GN 2.5-inch 32GB SATA II SLC Internal Solid State Drive (SSD)). In the system I used ext4, btrfs (v3.16.2), and xfs as file systems. Note that NVMFS, where this feature was designed, can’t be currently used on normal SSDs as a file system.

I used LinkBench with 2.5x database size resulting is 26G of uncompressed tables and 16G of holepunch tables using zip compression.

Filesystem Time to drop uncompressed db Time to drop holepunch compressed db
ext4 6.43sec 6.53sec
btrfs 1.74sec 13.82sec
xfs 6.66sec 1 min 1.73sec

Clearly, dropping holepunch compressed tables in xfs takes magnitudes longer than uncompressed meaning that the holepunch feature is not useful on xfs if the workload requires dropping big tables. In ext4 there is basically no difference and in btrfs the difference is 10x. However, the problem could be that the database is too small, thus I also tested LinkBench with a 5x database size.

Filesystem Time to drop uncompressed db Time to drop holepunch compressed db
ext4 7.75sec 26.55sec
btrfs 11.69sec 33.18sec

Similar test was done using a normal HD and 20x LinkBench using only ext4. Firstly, loading holepunch compressed tables took significantly longer than an uncompressed database. Similarly, dropping the database was significantly different:

Filesystem Time to drop uncompressed db Time to drop holepunch compressed db
ext4 40.44sec 5 minutes 55.65sec

Finally, similar test was done using ioMemory SX300-1600 with VSL driver 4.2.1 build 1137 and NVMFS 1.1.1 using 20X LinkBench database. In this setting no significant difference was found.

Filesystem Time to drop uncompressed db Time to drop holepunch compressed db
nvmfs 3.30sec 3.65sec


Based on my and other experiments its clear that many of these filesystem have a large overhead dealing with large sparse files. This conclusion leads to need for alternative design for holepunch i.e. where one could use this the feature without punch hole operation resulting in a slightly denser file than otherwise would be possible based on the true Page Compression architecture.


MariaDB Connector/J has evolved a lot during the year. In this post I will talk about the failover capabilities in the connector and give some guidance on how to use them in some certain cases. One other important new feature that I’ll cover in a later article is the fact that MariaDB Connector/J can do load balancing over several servers now as well.

To start off with we’ll need the connector itself. Do either of the following to get version 1.2.3 of MariaDB Connector/J which is the newest stable version as of writing:

  • Download the binary from (requires login) . Look for Connectors / Java on the download page once logged in.
  • The connector is also available through Maven. To instruct Maven to include the MariaDB Connector/J add the following dependency to your application’s pom.xml file:

The failover support added to MariaDB Connector/J can be used in several high availability scenarios:

  1. The most typical one, where normal MariaDB or MySQL replication is used and the setup consists of one master host for write operations and several slave hosts for the read operations.
  2. Multi-master setups, such as MariaDB Galera Cluster, where every host is a master
  3. Support for Amazon Aurora and its automatic failover functionality
  4. The connector also supports having several masters in a normal replication topology as described in 1, but the connector will be unaware of the differences between the masters and will just pick one according to the order in which the masters have been given in the JDBC URL.

To demonstrate the failover capability I will focus on scenario 3) and set up an Amazon Aurora cluster and use Connector/J to connect to it and simulate a failover in the cluster to see how the connector reacts to that. In case you want to know more about Aurora please refer to its documentation.

To start off with I’ll create one Aurora instance that will act as the master node for the cluster:

  • To find Aurora, go to your AWS console, click on RDS under Databases and you should see a button saying “Launch an Aurora DB instance”. Click on it.
  • Type in the requested information on the Specify DB Details -page. Notice that when you move between the fields there is a good explanation of the field appearing on the right hand side. Once done click “Next Step”
  • On the “Configure Advanced Settings” the first part is important. Choose VPC and VPC subnet group or create a new VPC. In the VPC Security Group(s) select the VPC security groups that should be able to connect to the Aurora cluster.

Once done you should have one node of Aurora up and running looking like this:

AWS Console -RDS Aurora

You’ll notice that it says “Writer” in the Replication Role column of the above table.

Let’s add a slave to this master. A slave is called a replica within the Aurora cluster:

  1. To do that click in the checkbox next to the instance in the table above. It will expand the row and you should see a “Instance Actions” button. Click on it and choose “Create Aurora Replica”.
  2. On the next “Create Aurora Replica” page you’ll have to fill in the “DB Instance Identifier*” field. Other than that you can go with the default ones if you don’t want to change any of the default values. Finally hit the “Create Aurora Replica” button.

Repeat the above steps for the amount of replicas you want for your cluster. The maximum amount is 15.

Once done you should have an instance table looking like this:

AWS Console RDS Aurora multiple


The failover documentation for MariaDB Connector/J failover can be found here and the start page of the full documentation for the connector here.

Failover example for Aurora

Next, let’s write a program that uses the failover capabilities of the MariaDB connector. Below you’ll see the Java class TestConnection. Take a moment and read through the code. It’s pretty straightforward and should be easy to follow.

The important step in the above example Java program is where the URL is specified:

By giving the keyword aurora in jdbc:mysql:aurora:// we instruct the driver that it should take Aurora failover support into use. Next in the url the three servers are given, aurora-demo-1…, aurora-demo-2… and aurora-demo-3 and finally the username and the password.

RDS AWS Console details

Make sure to use instance endpoint (in green) and not cluster endpoint (in red).

If we run the program it will start outputting rows with about 1 second interval looking like this:
16:15:22.467 connected-to:aurora-demo-2 master:aurora-demo-2 slave:aurora-demo-1,aurora-demo-3
16:15:23.579 connected-to:aurora-demo-2 master:aurora-demo-2 slave:aurora-demo-1,aurora-demo-3

First a time indicator, then to which server the connection points currently and then which server is master and which is slave from INFORMATION_SCHEMA. This data in the INFORMATION_SCHEMA is Aurora specific and doesn’t exist for MariaDB or MySQL.

The more interesting part is when we keep the program running and a failover occurs in the Aurora cluster, i.e. the slave becomes the master. Then the output would look like this:
20:56:49.241 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
20:56:50.370 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
20:56:51.501 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
20:56:52.637 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
20:58:19.374 connected-to:aurora-demo-2 master:aurora-demo-2 slave:aurora-demo-1,aurora-demo-3
20:58:20.481 connected-to:aurora-demo-2 master:aurora-demo-2 slave:aurora-demo-1,aurora-demo-3
20:58:21.587 connected-to:aurora-demo-2 master:aurora-demo-2 slave:aurora-demo-1,aurora-demo-3

The interesting rows are highlighted. While running the small java program I forced a failover in the Aurora cluster. As you see the program then waits for a while until it gets a new master connection and then continues. As you can see the original slave aurora-demo-2 has become the master to which also the connections are established after the failover. The only thing visible from application point of view is that there will be a delay until the server responds again.That’s automatic failover!

If the new master’s election would have taken more than 120 seconds (the default value of failoverLoopRetries -parameter), the connector would have thrown an SQLException. There are more parameters with which the failover capabilities can be tuned to meet your needs. You can find them in the documentation.

Failover on slaves for Aurora

MariaDB Connector/J has 2 underlying connections when using failover with either “replication” or in this case the ”aurora” parameter; one connection to master and one to a slave. Using the previous program with a parameter “true”, the driver will use the slave connection.

The output looks like this when suddenly rebooting a slave instance that is in use:
16:27:24.524 connected-to:aurora-demo-3 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:25.638 connected-to:aurora-demo-3 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:26.759 connected-to:aurora-demo-3 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:27.982 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:29.089 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:30.196 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:31.310 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:32.435 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:33.542 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:34.649 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:35.757 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:36.872 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:37.982 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:39.089 connected-to:aurora-demo-1 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:40.200 connected-to:aurora-demo-2 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:41.301 connected-to:aurora-demo-2 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:42.406 connected-to:aurora-demo-2 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3
16:27:43.522 connected-to:aurora-demo-2 master:aurora-demo-1 slave:aurora-demo-2,aurora-demo-3

The driver that was using a connection to the slave aurora-demo-3 switch immediately to the connection to the master aurora-demo-1. When a new slave connection (to aurora-demo-2) is established, the driver will switch another time the connection.

Load-balancing and other capabilities

MariaDB Connector/J also includes load balancing, execution of prepared statements on server side and other interesting new features that I will cover in upcoming blog posts. For properly testing load balancing I’ll set up a connection pool.


A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. The purpose of the foreign key is to identify a particular row of the referenced table. Therefore, it is required that the foreign key is equal to the candidate key in some row of the primary table, or else have no value (the NULL value). This is called a referential integrity constraint between the two tables. Because violations of these constraints can be the source of many database problems, most database management systems provide mechanisms to ensure that every non-null foreign key corresponds to a row of the referenced table. Consider following simple example:

As far as I know, the following storage engines for MariaDB and/or MySQL support foreign keys:

MariaDB foreign key syntax is documented at (and MySQL at While most of the syntax is parsed and checked when the CREATE TABLE or ALTER TABLE clause is parsed, there are still several error cases that can happen inside InnoDB. Yes, InnoDB has its own internal foreign key constraint parser (in dict0dict.c function dict_create_foreign_constraints_low()).

However, the error messages shown in CREATE or ALTER TABLE, and SHOW WARNINGS in versions of MariaDB prior to 5.5.45 and 10.0.21 are not very informative or clear. There are additional error messages if you issue SHOW ENGINE INNODB STATUS, which help, but were not an ideal solution. In this blog I’ll present a few of the most frequent error cases using MariaDB 5.5.44 and how these error messages are improved in MariaDB 5.5.45 and 10.0.21. I will use the default InnoDB (i.e. XtraDB) but innodb_plugin works very similarly.

Constraint name not unique

Foreign name constraint names must be unique in a database. However, the error message is unclear and leaves a lot unclear:

These messages are not very helpful because there are two foreign key constraints. Looking into SHOW ENGINE INNODB STATUS we get a better message:

In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:

No index

There should be an index for columns in a referenced table that contains referenced columns as the first columns.

Fine but again we have no idea which foreign key it was. As before, there is a better message in the SHOW ENGINE INNODB STATUS output:

In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:

Referenced table not found

A table that is referenced on foreign key constraint should exist in InnoDB data dictionary. If not:

Both messages are first referring to an internal table name and the foreign key error message is referring to an incorrect name. In MariaDB 5.5.45 and 10.0.21, the message is clearly improved:

Temporary tables

Temporary tables can’t have foreign key constraints because temporary tables are not stored to the InnoDB data dictionary.

These error messages do not really help the user, because the actual reason for the error is not printed and the foreign key error references an internal table name. In MariaDB 5.5.45 and 10.0.21 this is clearly improved:

Column count does not match

There should be exactly the same number of columns in both the foreign key column list and the referenced column list. However, this currently raises the following error:

The error message is not clear and the foreign key error refers to an internal table name. In MariaDB 5.5.45 and 10.0.21 there is additional information:

Incorrect cascading

A user may define a foreign key constraint with ON UPDATE SET NULL or ON DELETE SET NULL. However, this requires that the referenced columns are not defined as NOT NULL. Currently, the error message on this situation is:

Both error messages are not very useful, because the first does not really tell how the foreign key constraint is incorrectly formed and later does not say which column has the problem. This is improved in MariaDB 5.5.45 and 10.0.21:

Incorrect types

Column types for foreign key columns and referenced columns should match and use the same character set. If they do not, you currently get:

But do we have an index for the referenced column f1 in the table t2? So if there are multiple columns in both the foreign key column list and the referenced column list, where do we look for the error? In MariaDB 5.5.45 and 10.0.21 this is improved by:


There are several different ways to incorrectly define a foreign key constraint. In many cases when using earlier versions of MariaDB (and MySQL), the error messages produced by these cases were not very clear or helpful. In MariaDB 5.5.45 and 10.0.21 there are clearly improved error messages to help out the user. Naturally, there is always room for further improvements, so feedback is more than welcome!