New functionality in this release

This option can be used in environments where connections are created and closed in rapid succession. Often, it is not possible to create a socket in such environment after a while, since all local “ephemeral” ports are used up by TCP connections in TCP_WAIT state. Using tcpAbortiveClose works around this problem by resetting TCP connections rather (abortive or hard close) than doing an orderly close. It is accomplished by using socket.setSoLinger(true,0) for abortive close.

Bugs fixed in this release

MySQLStatement will now indicate there are no more results by returning -1 from getUpdateCount() and null from getResultSet(),as mandated by the standard (CONJ-14)

Introduced nullCatalogMeansCurrent parameter for compatibly with ConnectorJ, and make it default (CONJ-16)

Prior to this change, DatabaseMetadata.getTables() or other methods of DatabaseMetaData that accept catalog names and return result sets would treat null as prescribed by the JDBC standard (null means no restriction which catalog is used). This behavior is now changed for the sake of compatibility. Starting with 1.1.1 , null for catalog name will mean current catalog. To get JDBC standard behavior, one needs to set nullCatalogMeansCurrent=false.

DatabaseMedataData.getColumns() returned incorrect values in the “COLUMN_SIZE” column for character data.(CONJ-15))

Prior to the change, octet size was returned (length in bytes). Depending on the character set used, it could be 3 times bigger than the length in characters that was specified by CREATE or ALTER table. This behavior is corrected in 1.1.1

The behavior is now fixed and getColumns returns either DATE or SMALLINT depending on how the ‘yearIsDateType’ parameter is set.

ResultSetMetaData.getColumnName() returned an empty string in special cases (CONJ-17)

ResultSetMetaData.getColumnName() returned and empty string for “non-columns” in a result set (functions, aggregates like count(*), and so on). The fix is to return the column label to be returned if the column name is empty.

Oracle has now launched MySQL-5.6.10-GA, so it is time to come up with some new benchmark results. The test candidates in this benchmark run are

MySQL-5.5.29

MySQL-5.6.10

MariaDB-5.5.28a

MariaDB-10.0.1

The 5.5 versions are in because I wanted to check for any regressions. In the past we have often seen performance regressions in newer versions which were caused by new features.

This time the benchmark was run on a different box. The main difference is that this box does not have SSD but a high performance RAID-5 with 512M of battery-backed cache. Besides that the machine has 16 cores out of which 12 were used for mysqld and the other 4 for sysbench.

The benchmark uses sysbench-0.5 OLTP with 8 tables and 10G worth of data. InnoDB buffer pool was 16G, InnoDB log group capacity 4G (the maximum for MySQL-5.5). The different disk system required different InnoDB configuration:

innodb_io_capacity = 1000 (was 20000 for SSD)

innodb_flush_neighbors = 1 (was 0 for SSD)

Now for the results. OLTP read only comes first:

And here is the first surprise: MySQL-5.6 behaves significantly different. It competes well up to 8 threads, it even wins 16 threads. But at higher concurrency performance drops off rapidly, even compared to MySQL-5.5. MariaDB-10.0 shows also a slight drop in performance compared to MariaDB-5.5, but it’s much less pronounced.

The response time graph is nice and smooth though:

Both MySQL-5.6 and MariaDB-10.0 look a little better which means they distribute cpu cycles more evenly on concurrent requests.

Disclaimer: no thread pool was used in this benchmark. The Oracle implementation of the thread pool is closed source and thus cannot be benchmarked or used by anybody. It seemed a bit unfair to use the MariaDB thread pool under those cirumstances.

If you want to see the impact of the MariaDB thread pool, have a look at the benchmarks published previously:

The picture is very similar. Both MySQL-5.6 and MariaDB-10.0 show a performance drop, compared to the 5.5 versions. For MySQL the drop is more than 10% and thus rather heavy.

However it’s a well known fact that MySQL-5.5 exhibits severe write stalls under high load when InnoDB starts synchronous flushing. The response time graph is good to spot this:

This is the good news. While the 5.5 versions both show heavy write stalls at 64 threads and more, the behavior is much less pronounced with MySQL-5.6 and MariaDB-10.0. So it seems the new adaptive flushing algorithm is working well.

There is however one problem here: if you use multiple buffer pool instances, then you see write stalls more often. For the above results I have run the read-only tests with 16 buffer pools and the read-write tests with only 1.

Conclusions:

MySQL-5.6 shows a rather severe performance regression, especially at higher concurrency levels. This does not match the results published by Oracle. I can only speculate why the results are so different, but I guess it’s the (closed source) thread pool and maybe the fact that Oracle benchmarks were done on much bigger hardware.

with a single buffer pool you don’t have to be afraid of write stalls any more. Also MySQL-5.6 allows now up to 512G redo log capacity which further reduces the odds to run into synchronous flushing (MariaDB-5.5 lifted this limit with XtraDB already)

As always the scripts used for the benchmark as well as the results are available from launchpad:

In this post I walkthrough the features of the first two alpha versions of MariaDB 10.0. The first, 10.0.0-alpha, which was made available in November, and 10.0.1-alpha that saw daylight yesterday. I will go through the features by placing them in the following categories:

MariaDB 10.0-only Features (features that aren’t in MySQL 5.6)

MariaDB 10.0 Merged Features (features merged from MySQL 5.6)

MariaDB 10.0 Reimplemented Features (features reimplemented from features in MySQL 5.6)

MariaDB 5.x Features now in MySQL 5.6 (features introduced in earlier MariaDB versions which have now been introduced in MySQL 5.6)

MariaDB 5.x Features Backported from MySQL 5.6 (features introduced in earlier MariaDB versions which were backports of features from MySQL 5.6 development versions)

Some of the features will have links to the MySQL manual for the documentation Oracle has made available on the feature.

MariaDB 10.0-only Features

Features in this section are unique to MariaDB 10.0 and aren’t found in MySQL 5.6.

Available since 10.0.0

Multi-source replication is a longtime wish of many users. In scenarios where you partition your data over many masters you can then replicate the data from all masters onto one slave. Typical use cases are:

Data partitioned over many masters can be pulled together onto one slave for analytical queries

Many masters can replicate to the same slave and a complete backup can be done on the slave

Newer hardware usually provides more performance. Usually all hardware isn’t upgraded at once and multi-source can be used for replicating many masters to a powerful new slave.

Optimizer statistics is the collection of data that describe more details about the database and the objects in the database.

Statistics are now provided separately from storage engines. Before, statistics were supplied by the storage engines themselves and the quality of the statistics were usually quite poor. Also, since before this they were provided through the storage engine interface, a lot of restrictions were put on them.

These statistics are used by the query optimizer to choose the best execution plan for each SQL statement. Better statistics results in better execution plans and end users will experience faster results in general.

Statistics are collected also for non-indexed columns. InnoDB’s statistics, for example, were previously only for indexes.

Dynamic Columns has been in MariaDB for a while already. This feature allows you to store a different set of columns for every row in a table. In that manner Dynamic Columns can be called NoSQL-like.

Since MariaDB introduced Dynamic Columns there has been user feedback and research going on to improve it further. Dynamic Columns has some new capabilities that now are in mainline MariaDB:

Database interoperability: It’s pretty rare that companies use only a single type of database and even critical business systems are often built on several different types of databases. Usually the data from those different databases is combined in an upper application level. MariaDB introduces the possibility of doing this at a low level inside the MariaDB database. The first implementation of this is integration with Cassandra. With Dynamic Columns and the Cassandra Storage Engine you can now combine data residing in Cassandra with data inside MariaDB and this is done through normal looking queries on the MariaDB side.

Data interchange: JSON has become a very popular standard for data interchange. In Dynamic Columns one can now request a row in JSON format.

Now added in 10.0.1

Used to avoid specifying a large set of plugins in a single long argument

MariaDB 10.0 Reimplemented Features

These features are re-implementations of the corresponding functionality in MySQL 5.6. In future versions of MariaDB 10.0 there will be a few more features in this category. I’ll cover them in a future blog post.

In 10.0.1

Add full support for auto-initialized/updated timestamp and datetime

MariaDB 5.x Features now in MySQL 5.6

Earlier MariaDB 5.x versions included features that have now been introduced in MySQL 5.6. It should be noted that the corresponding features in MySQL 5.6 haven’t been merged from MariaDB. Oracle has re-implemented them.

Many of the optimizer enhancements, which have been in MariaDB since versions 5.3 and 5.5.