We haven’t posted any Windows benchmarks for a while, and MariaDB for Windows contains some specific improvements which might not be widely know since we haven’t talked much about them yet. This post is an attempt to fix that. We’ll also share current MySQL 5.5 numbers.

My setup is an 8 core 2 socket server (yes, a little bit dated for today, but it is the best machine I have at my disposal), 10K SAS disks with RAID1. I ran sysbench 0.4 single table / 1,000,000 records. I ran the benchmark over a network, with the number of concurrent clients ranging from 4 to 4096.

Here is what OLTP-readonly throughput looks like:

  • For most of the tests, MariaDB’s throughput is approx 10% higher than MySQL’s
  • For 4096 concurrent clients, MariaDB’s throughput is better than MySQL 5.5 by 476% (2382 vs 413 TPS).

Many people rightfully remark that throughput does not necessarily represent performance as a whole, and that in OLTP benchmarks, fairness also matters. It actually often matters more than throughput. I agree, so below are the results for 95% of response time, meaning that 95% transactions were finished under the given time.

OLTP Readonly response time, 95th percentile

Concurrent clients 4 8 16 32 64 128 256 512 1024 2048 4096
MariaDB 4.87 6.81 8.83 12.35 22.12 43.56 90.35 180.57 619.05 1003.88 1965.77
MySQL 4.86 7.14 9.96 16.21 37.39 101.33 238.89 499.63 971.07 2241.83 25215.29

As the above table shows, MariaDB 5.5 outperforms MySQL 5.5 in both row throughput, and in fairness. Fairness (response time) results are in fact more impressive than the row throughput.

But, why does MariaDB perform better than MySQL 5.5 on Windows in readonly tests? Why it is at all better than 5.5? It is, after all, based on the same codebase, so performance should be about equal. The answer to this is not our optimizer enhancements, nor XtraDB serving as InnoDB in MariaDB. The answer is the MariaDB threadpool. This is my feature in 5.5 and I’m glad it performs so well, and I’m glad that it is switched “on” by default on Windows (so, Windows users, you do not have to do anything to switch it on).

Still, why does using threadpool result in such better performance? The answer is that the implementation delegates all responsibility for sizing the pool and running callbacks to the Windows native threadpool, so it is a sort of black box inside the OS that just delivers good results. The heart of the Windows native threadpool, if you want to know, is the good old IO completion port, something that Windows has had since the NT 3.5 days. It’s a unique Windows feature, and something that any server application running on this platform is using or should be using. And the tricks that make it run well are:

  • It does not let too many threads run “on CPU” at the same time (this reduces context switches). Reducing context switches is, in my experience, the single most important factor to increasing throughput.
  • It activates threads waiting on completion LIFO order — hot threads remain hot, which reduces cache misses
  • IO completions are processed in FIFO order — this is why fairness performance is so good
  • And yes, it naturally reduces contention on hot locks.

Enough about readonly performance already, and about threadpool.

The next interesting question is whether MariaDB can do as well on write workloads. So let’s run the sysbench in the ultimate “write-only” mode, also known as update_non_index (every query only increments a non-indexed int column). To maximize the write throughput, I set the innodb_flush_log_at_trx_commit parameter to 0, so the log is flushed once a second rather than each time at transaction commit.

Here are the results:

OLTP write-only (update_non_index/flush_log=0) throughput:

This looks pretty good. The difference could be a combination of many factors. XtraDB write performance, group commit, and threadpool could all have a positive (for MariaDB) influence on the results. However I think the Windows version of MariaDB’s asynchronous IO optimization (it, again, uses completion ports) is the main reason for the big difference here. I, actually, first saw the difference when I was implementing the feature.

All IO related parameters and Innodb parameters in the test above are default. The result looks almost too good to be true, so I really wanted to bring MySQL to the point it can achieve better IO throughput by varying innodb_io_capacity and/or innodb_write_io_threads, to no avail. Does someone know the right trick here?

OLTP writeonly (update_non_index/flush_log=0) response time, 95 percentile

Concurrent clients 4 8 16 32 64 128 256 512 1024 2048
MariaDB 0.33 0.63 0.75 1.06 1.94 3.85 8.25 21.38 129.79 274.40
MySQL 0.32 0.61 0.73 1.61 7.62 26.82 96.45 219.29 661.19 2723.36

For completeness, here are database parameters I’m using.

[mysqld]
sql-mode="NO_ENGINE_SUBSTITUTION"
back_log=500
user=root
port=3306
max_connections=4096
max_connect_errors=5000
max_prepared_stmt_count=50000
table_cache=2048
transaction_isolation=REPEATABLE-READ
loose-skip-external-locking
innodb_status_file=0
innodb_data_file_path=ibdata1:200M:autoextend
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
innodb_log_file_size=650M
innodb_log_buffer_size=100M
innodb_support_xa=0
innodb_doublewrite=0
innodb_flush_log_at_trx_commit=0
query-cache-size=0
query-cache-type=0
symbolic-links=0
skip-grant-tables

We’re quite happy that we’ve released four major releases that are production ready (better known as generally available or GA in the MySQL world) in the last 26 months. That is just a little over two years, and a whole lot of features. In that same time, MySQL has seen one GA release (MySQL 5.5) and we’re all eagerly awaiting the upcoming MySQL 5.6.

You’ll note that we built MariaDB 5.1, 5.2, and 5.3 based on the MySQL 5.1 codebase. A significant number of features went into MariaDB 5.3 (our biggest GA release to date), with the biggest changes in the optimizer in over a decade. There were also many replication based changes included like the now famous group commit for the binary log. Our Knowledgebase has a summary of MariaDB 5.3 features.

Work on MariaDB 5.3 started long before MySQL 5.5 went GA. It was a huge task to move all these 5.3 features into MariaDB 5.5 and at the same time merge MariaDB 5.5 with MySQL 5.5. It caused a significant delay in us getting a release of MariaDB 5.5 out there as production ready software. By now it must be clear that we included all changes in MariaDB 5.5 from 5.3, 5.2, and 5.1. We spent the time developing new features and keeping it current against current versions of MySQL.

We released MariaDB 5.5 in April and we have always aimed for short release cycles where possible to keep up with rapidly changing distributions. With this in mind many have been thinking about the release cycle from now onwards.

What will the next release of MariaDB, which we are working on, be called? We want to release our new features in a GA version soon and not wait for MySQL 5.6 to reach GA quality. But if we release a GA version before MySQL 5.6 is GA, it will be very confusing to call our release 5.6. In addition, this time there are no free version numbers between 5.5 and 5.6 like there were between 5.1 and 5.5 when we could use 5.2 and 5.3.

We are thinking of calling it MariaDB 10.0. It will include stable GA-ready features from MySQL 5.6 (these will be backported), as well as encompass some of our plans for the next release. It will be based on the MySQL 5.5 codebase. Then we plan to release MariaDB 10.1, MariaDB 10.2 and so on.

What happens when MySQL 5.6 is GA-ready? We’ll release a MariaDB version 11.0. It will include all the features of MariaDB 10, and encompass the features from the MySQL 5.6 codebase (that weren’t already backported into MariaDB in a previous release).

Does this mean we are veering away from being a backward compatible branch to MySQL? Of course not. We will be feature complete. We’re just in the lull of time between MySQL releases, in a similar fashion to what we did for MariaDB 5.2 and MariaDB 5.3. Astute followers will note that there is no MySQL 5.2 and 5.3.

Essentially this is just a change in the numbering scheme. A change which allows us to release more often than MySQL does. You are invited to contribute to the conversation on the maria-discuss mailing list.

The explain statement can be an important tool for understanding how a query is being executed and what you can do to make it run better.  Although the output of EXPLAIN is relatively straightforward it can be confusing to inexperienced users or can be mangled by terminal wrapping.

To help with these problems as well as provide a pastebin for MariaDB developers to share explains during development we created The MariaDB/MySQL Explain Analyzer. This tool:

  1. Helps unmangle explains (both vertical and tabular format)
  2. Displays explains in an easy-to-read format.
  3. Highlights and provides explanations for some terms.
  4. Links to KB articles for different optimization techniques.
  5. (Optionally) Allows you to save the explain for sharing.

This is the first release so there are still improvements to make. If you have any suggestions, feature requests or bug reports please let us know.

For more information, please see this KB article.