Feed aggregator

MySQL 5.6.16 Overview and Highlights

Chris on MySQL - February 11, 2014 - 7:52pm GMT

MySQL 5.6.16 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here:


As opposed to the latest 5.5 release, this latest 5.6 release has quite a few more bug fixes, but that’s expected since 5.5 has been GA for much longer.

There were 2 minor functionality changes:

  • Previously, ALTER TABLE in MySQL 5.6 could alter a table such that the result had temporal columns in both 5.5 and 5.6 format. Now ALTER TABLE upgrades old temporal columns to 5.6 format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. This conversion cannot be done using the INPLACE algorithm, so specifying ALGORITHM=INPLACE in these cases results in an error. (Bug #17246318)
  • CMake now supports a -DTMPDIR=dir_name option to specify the default tmpdir value. If unspecified, the value defaults to P_tmpdir in . Thanks to Honza Horak for the patch. (Bug #68338, Bug #16316074)

I counted 80 bug fixes, 32 of which were InnoDB-related (and some of thoese also memcached-related), 2 partitioning-related, 8 replication-related, and the remaining covered a variety of areas.

I won’t go into all 80 bugs, but if you’re running memcached and/or InnoDB with MySQL 5.6, you should definitely consider upgrading to the latest 5.6.16. And if not, but you’re still running 5.6, I’d recommend reviewing the full changelogs, to see whether you should upgrade asap or not.

Lastly, there was one “known limitation” added, which is:

  • Building MySQL from source on Windows using Visual Studio 2008 fails with an identifier not found error. Later versions of Visual Studio are unaffected. The workaround is to set the CMake build option, INNODB_PAGE_ATOMIC_REF_COUNT, to OFF. This option is ON by default.

So this is very minor, and only related to those building MySQL on Windows using VS 2008. VS 2008 is quite old anyway, but there is the work-around if you’re still using t.

The full changelogs can be viewed here:


And in case you missed it, there is a great post from Morgan Tocker giving thanks to each community member involved in each bug reported/fixed in 5.6.16! What a nice combination of both old and new names and folks from all different companies! That sure shows how active and thriving the MySQL ecosystem really is!


MySQL 5.5.36 Overview and Highlights

Chris on MySQL - February 9, 2014 - 8:12pm GMT

MySQL 5.5.36 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:


I was reading through the changelogs to review the changes and fixes, and to summarize, I must say this release is mostly uneventful.

There was one new feature added (for building, so not really applicable to everyone), and only 17 bugs fixed.

The new feature is this:

  • CMake now supports a -DTMPDIR=dir_name option to specify the default tmpdir value. If unspecified, the value defaults to P_tmpdir in . Thanks to Honza Horak for the patch. (Bug #68338, Bug #16316074)

Out of the 17 bugs, there was only 1 I thought worth mentioning (because it is a wrong results bug):

  • COUNT(DISTINCT) sometimes produced an incorrect result when the last read row contained a NULL value. (Bug #68749, Bug #16539979, Bug #71028, Bug #17867117)

Therefore, if you run COUNT(DISTINCT) and you allow NULL values, then you should consider upgrading so you’re not affected by this.

For reference, the full 5.5.36 changelog can be viewed here:


Hope this helps.


MariaDB 5.5.35 Overview and Highlights

Chris on MySQL - February 6, 2014 - 10:07pm GMT

MariaDB 5.5.35 was recently released (it is the latest MariaDB 5.5), and is available for download here:


This is a maintenance release, and so there are not too many big changes of note. However, there are a couple items worth mentioning:

  1. Includes all bugfixes and updates from: MySQL 5.5.35
  2. Includes all bugfixes and updates from: XtraDB from Percona-Server-5.5.35-rel33.0
  3. New SQL_MODE: OLD_MODE – to emulate behavior from old MySQL/MariaDB versions

You can read more about OLD_MODE here, if interested:


Also, there is more about the 5.5.35 release here:


And the full list of fixed bugs and changes in MariaDB 5.5.35 can be found here:


Hope this helps.


Exploring MySQL Metadata Lock Instrumentation in Closer Detail

Chris on MySQL - February 5, 2014 - 10:42pm GMT

I recently wrote a post on tracking metadata locks (MDL) in MySQL 5.7, and I wanted to take a moment to expand on it by explaining a couple of the associated variables in more detail.

First off, once you have enabled the performance_schema *and* the metadata lock instrumentation, you can verify it with:

mysql> SELECT * FROM performance_schema.setup_instruments
    -> WHERE NAME = 'wait/lock/metadata/sql/mdl';
| NAME                       | ENABLED | TIMED |
| wait/lock/metadata/sql/mdl | YES     | YES   |

“ENABLED” will report “YES” if it is enabled properly, and “NO” if not.

“TIMED” (referring to event timing) reports “YES” if it was enabled via the my.cnf or my.ini file, and it reports “NO” if it is enabled dynamically *and* you don’t explicitly set TIMED to YES. If you want to set it dynamically, *and* you want to enable event timing, then use this statement to enable the instrumentation:

UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

As for variables:

mysql> show global variables like '%metadata%';
| Variable_name                         | Value |
| ...                                   | ...   |
| metadata_locks_cache_size             | 1024  |
| metadata_locks_hash_instances         | 8     |
| performance_schema_max_metadata_locks | 10000 |

The first two are metadata_locks_cache_size and metadata_locks_hash_instances. In looking up more about these two variables, I found out they are deprecated as of 5.7.4. So, they were only in 5.7.3. Therefore, no need to worry about these either. This sometimes happens with new features.

For both variables, the manual says:

“In MySQL 5.7.4, metadata locking implementation changes make this variable unnecessary, so it is deprecated and will be removed in a future MySQL release.”


That only leaves us with performance_schema_max_metadata_locks, which the manual says this:

“The maximum number of metadata lock instruments. This value controls the size of the metadata_locks table. If this maximum is exceeded such that a metadata lock cannot be instrumented, the Performance Schema increments the Performance_schema_metadata_lock_lost status variable.”


And here is the status variable the above refers to (incremented if performance_schema_max_metadata_locks is reached/exceeded), and note it is the only metadata-related status variable:

mysql> show global status like '%metadata%';
| Variable_name                         | Value |
| Performance_schema_metadata_lock_lost | 0     |

So if this is ever greater than 0, you’ll need to increase performance_schema_max_metadata_locks. However, the default is 10,000 which is quite high, so unless you have very high traffic loads, this should probably not ever be an issue (or perhaps it would indicate some larger issue at hand).

I searched the page for any possible new variables, and didn’t see any mention of any. However, with it being such a new feature, I’ll probably keep an eye out for any changes.

Lastly, the only other item I can think to cover here is the actual output from the metadata_locks table. Here is one full sample row (generated from a “BEGIN”):

mysql> select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
               SOURCE: sql_parse.cc:5542
       OWNER_EVENT_ID: 14
1 row in set (0.00 sec)

And here is output from a metadata lock hang with several rows:

mysql> select * from metadata_locks;
| TABLE       |..| SHARED_READ         | TRANSACTION   |..|              21 |..|
| GLOBAL      |..| INTENTION_EXCLUSIVE | STATEMENT     |..|              22 |..|
| SCHEMA      |..| INTENTION_EXCLUSIVE | TRANSACTION   |..|              22 |..|
| TABLE       |..| SHARED_UPGRADABLE   | TRANSACTION   |..|              22 |..|
| TABLE       |..| EXCLUSIVE           | TRANSACTION   |..|              22 |..|
| TABLE       |..| SHARED_READ         | TRANSACTION   |..|              23 |..|

I think the rows are all described well, and self-explanatory, but I wanted to post the outputs for completeness. The second example above gives you an idea of the various LOCK_TYPES you might see, as well as LOCK_DURATIONS. I think it’s worth noting the OWNER_THREAD_ID corresponds to THREAD_ID in the performance_schema.threads table, in case you need to correlate the Process Id to the thread id listed here.

Hope this helps.


MaxScale – We’d Love to Know Your Thoughts

Chris on MySQL - February 5, 2014 - 4:07pm GMT

As you’ve heard, MaxScale was recently released, and we’d love for you to try it out and let us know your thoughts.

Anders Karlsson and Ivan Zoratti have written some excellent posts on downloading and setting it up, so if you’re intersted in that, please see their respective posts for quick, detailed instructions.

I just wanted to take a moment and encourage you to try it out if you’re looking for load balancing out of the box, and if you do, we’d love to get your feedback.

If you have any feature requests you’d like to see, or run into any bugs while testing, please report them here:


Or, if you want, feel free to leave any comments here.

Lastly, if you’re interested in MaxScale (and/or MariaDB Enterprise), please don’t forget to attend Ivan’s webinar this Thursday, 2/6/2014, at noon EST (9am PST):


We look forward to hearing from you.


MariaDB 10.0.7 Overview and Highlights

Chris on MySQL - February 5, 2014 - 4:05pm GMT

MariaDB 10.0.7 was recently released (it is the latest MariaDB 10.0), is currently “beta”, and is available for download here:


I just wanted to note a couple of the main highlights, which are:

  1. XtraDB storage engine was upgraded to the 5.6 version. Now one can use XtraDB with MariaDB 10.0. Unlike MariaDB 5.5, in 10.0 XtraDB is not the default engine, the default is InnoDB, and XtraDB is available as a dynamic plugin.
  2. OQGraph storage engine was upgraded to the version 3. Unlike OQGraph v2, that stores all the data in memory, new OQGraph v3 stores them in a table of another storage engine, on disk. Which makes your graphs persistent and also can support much larger graphs. See the OQGraph documentation for details.
  3. A new plugin metadata_lock_info was added. It implements a new table INFORMATION_SCHEMA.METADATA_LOCK_INFO that shows active metadata locks.


And for reference, the full 10.0.7 changelog can be viewed here:


Hope this helps.

MaxScale for the rest of us - Part 3

Karlsson on databases and stuff - February 4, 2014 - 6:54pm GMT
This third post in this series of blogs about MaxScale is finally getting where you want to go: Install and configure MaxScale. The first blog in this series was an overview of what MaxScale is and the second about how to set up a Cluster of MariaDB servers, using MariaDB Replication, for MaxScale to access. But now it's time to introduce MaxScale.

If you skipped the second post as you already know how to set up MariaDB with Replication and all that, be remineded that I will use the same Linux server setup as outlined there even for the MaxScale server and for a client to do some testing, and I recommend you stick with that for now (for MariaDB itself you can use any relevant setup you want, MaxSCale doesn't really care, but MaxScale is pretty new and has still not been tested on that many platforms, so try to stick to the CentOS 6.5 setup I propose.

Installing MaxScaleStart by setting up a CentOS 6.5 server as outlined in the previous blog in this series and then log in a root. Set up the server to run on IPADDR When logged in as root, create an installation directory for MaxScale, download it and install it:
# cd /usr/local
# mkdir skysql
# cd skysql
# curl https://downloads.skysql.com/files/SkySQL/MaxScale/maxscale.preview.0.4.tar.gz > maxscale.preview.0.4.tar.gz
# tar xvfz maxscale.preview.0.4.tar.gz
# cd maxscale
Now we have MaxScale downloaded, but for maxscale to work, the MAXSCALE_HOME environment variable has to be set up, and to achieve this we, let's create a shell-script that starts MaxScale for us.
# vi maxstart.sh
Then set up this script like this:
export MAXSCALE_HOME=/usr/local/skysql/maxscale/MaxScale

Once we have that script, let's make it executable:
# chmod +x maxstart.sh

Configuring MaxScale The next step then is to configure MaxScale. The default configuration file for MaxScale is called MaxScale.cnf and is located in the etc directory under where MAXSCALE_HOME is located. In this case we will edit:
# vi /usr/local/skysql/maxscale/MaxScale/etc/MaxScale.cnf
In this file, each module has it's own section, as well as each server and there is also a section for MaxScale itself. Let's begin with MaxScale which has just one variables that controls the # of threads MaxScale uses:

Following this, we set up the servers we are to manage here, of which there are three. For every configuration section, except the one for MaxScale core, we have to tell what type of entity we are defining here, in this case it is server:





As you can see, we define the ip address of the server and the port that MariaDB runs on. In addition we define which protocol module to use, and in this case there is not much else than MySQLBackend to choose from. As you can see, we do not define the master or slave properties of the servers, instead we let MaxScale figure that out for us by using a Monitor module, so now is the time to define that.

For the monitor to work, it will connect to the respective MySQL servers so we need to define a username and password for this. In the previous post I created a user for this called maxuser using the password maxpwd. Also, to simplify matters I created this user with full privileges, and this really isn't recommended practice for production use. Last, with MaxScale you have the option to obfuscate the passwords used in the configuration file for added security, but I am not using that feature here as I want to keep things simple. So, this is how we define this monitor:
[MySQL Monitor]

This should be pretty straightforward, right? The module variable is a reference to the monitoring module that we are to use. With this in place, we now needs to set up the actual router, and there are two sections for this, one for the listener and one for the router. The router we are about to use is the read-write split router, which manages routing writes to the master and reads to the slaves. Let's start with the router that is the central point here:

The reason we need to have a username and password even for the router is that this needs to authenticate users connecting to MaxScale itself, and to do that it needs to connect to the server it manages, connect to the mysql database and get the user authorization data from there, which is why we need an account that can access the mysql database.

Now we are close to getting started with MaxScale, what is left is to set up a listener for the router we defined above:

That concludes the configuration of MaxScale!
Testing MaxScale # ./maxstart.sh
Now we should be able to connect to MaxScale and test that it works. We do this by setting up a MariaDB Client server. This is set up just like our previous servers, only that we only install the MariaDB client. So set up a server like before, set IPADDR to and run, as root:
# yum install MariaDB-client
Now we are real close! The rest of this post assumes that you are connected to the MariaDB Client on

I here assume that you have created the t1 table that we used to test replication in the previous post, if not, create it and populate it now:
# mysql -h -u maxuser -pmaxpwd test -e "create table t1(c1 int)"
# mysql -h -u maxuser -pmaxpwd test -e "insert into t1 values(1)"
Now we have a table to test with, so let's see what happens, and let's check that our SELECTs are being routed to our two slaves. We can do that by using the @@server_id variable, which is different one these two of course:
# mysql -h -u maxuser -pmaxpwd test -e "select c1, @@server_id from t1"
| c1   | @@server_id |
|    1 |          12 |

Cool, this ended up with one of the slaves, so if I try it again, it shoudl end up with the other slave, right:
# mysql -h -u maxuser -pmaxpwd test -e "select c1, @@server_id from t1"
| c1   | @@server_id |
|    1 |          11 |

Whoa!, it actually seems to work! Also, not that if you executed the create table above, then this got routed to the master! We are all set, right? Well, no, there are a few things left to do. And if you didn't get the MaxScale to work, like seeing this in the error log /usr/local/skysql/maxscale/MaxScale/log/skygw_err1.log:
Couldn't find suitable Slave from 3 candidates.
Or if you can't connect to MaxScale, then the most likely issue is that you didn't stop iptables:
# /etc/init.d/iptables stop
But hopefully things work now, but that it works doesn't mean we are finished, there are some things with this configuration that needs fixing, both on the servers and in MaxScale itself, and there is also one thing to watch for in the replication setup. So the next post in the series will be about fine-tuning the MaxScale installation and the MariaDB Cluster it is accessing.


Tracking Metadata Locks (MDL) in MariaDB 10.0

Chris on MySQL - February 3, 2014 - 11:42pm GMT

I recently blogged about tracking metadata locks in the latest MySQL, and now I want to discuss how to track these metadata locks in MariaDB.

In MySQL 5.7, there is a table named `metadata_locks` added to the performance_schema (performance_schema must be enabled *and* the metadata_locks instrument must be specifically enabled as well.

In the MariaDB 10.0 implementation (as of 10.0.7), there is a table named METADATA_LOCK_INFO added to the *information_schema*. This is a new plugin, so the plugin must be installed, but that is very simple with:

INSTALL SONAME 'metadata_lock_info';

Then, you will have the table.

To see it in action:

Connection #1:

mysql> create table t (id int) engine=myisam;
mysql> begin;
mysql> select * from t;

Connection #2:

mysql> alter table t add index (id);
... <-- Hangs

Connection #3:

mysql> show full processlist;
| Id |..| State                           | Info                         |
|  2 |..|                                 | NULL                         |
|  3 |..| Waiting for table metadata lock | alter table t add index (id) |
|  4 |..| init                            | show full processlist        |

mysql> select * from metadata_lock_info;
|         3 | MDL_INTENTION_EXCLUSIVE  |..|              |            |
|         3 | MDL_INTENTION_EXCLUSIVE  |..| test         |            |
|         2 | MDL_SHARED_READ          |..| test         | t          |
|         3 | MDL_SHARED_NO_READ_WRITE |..| test         | t          |

Unlike the MySQL implementation, the THREAD_IDs here match up with those from SHOW FULL PROCESSLIST, so there is no extra SELECT needed to find the offending thread, which is quite nice.

For reference, the MariaDB manual page about this is:


Hope this helps, and happy troubleshooting.


MaxScale for the rest of us - Part 2

Karlsson on databases and stuff - February 3, 2014 - 6:59pm GMT
The first blogpost in this series did a quick introduction to MaxScale, but now it is time start getting our hands dirty. We will get a more practical view on MaxScale and begin to put it to work. The following is based on a simple Proof of Concept that I did recently. The application is a mid-size web-based online shop where scalability is becoming an issue. They use PHP and with an old and rather inflexible framework, so even though changing how the database is used is possible, it's not easy and having database routing in the application code isn't a very good idea either.

The scalability issues affects reads, but as all traffic is directed to one database server, when this server gets slow, writes, like when entering an order, gets very slow, and this is unacceptable. So what was needed is a way to redirect reads to one or more slaves and to retain all writes in one server. That the slaves might be slightly delayed due to the asynchronous nature of MariaDB replication was wasn't determined to be a problem, but one task of this POC was to get a system where this could be tested. But before we come that far, we need to set up MaxScale.

This blogpost is really meant to show how to set up a MariaDB Cluster using MariaDB Replication. If you already know all this, then you might just have a quick look at this, and wait for part 3 of this series, where I configure MaxScale to run on the Cluster we are to setup here. Even if you know how to set up MariaDB and Replication and all that, have a look here for the server setup and IP addresses and stuff like that if you are to come along to part 3.

Setting up the servers All servers we are going to use are running CetnOS 6.5, and this is set up a a Basic Server. Once this is done I log in a root and run a few extra things to make testing easier. First I update all the CentOS packages to the latest version
# yum update
Following this I set up all servers to use static IP. For this I need to set up two files:
I guess you understand what is going on here and how up set this up for your own purposes, the only line affected is that I have added the gateway. The second networking file to set up will set up the network on the network adapter eth0:
Here I have modified BOOTPROTO and NETMASK added IPADDR. Of course IPADDR is different for each server. To use these new network setting I need to restart CentOS networking:
# /etc/init.d/network restart
Following this, there is only one more generic CentOS setting to be done, which is to add the MariaDB repository to the yum package manager configuration. This is by adding a MariaDB repository file:
# vi /etc/yum.repos.d/MariaDB.repo
And then make this file look like this:
# MariaDB 5.5 CentOS repository list - created 2014-02-02 09:27 UTC
# http://mariadb.org/mariadb/repositories/
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
Before we finish with the server setup, make sure that the Linux iptables firewall isn't running and that it isn't started on reboot by issuing the commands:
# /etc/init.d/iptables stop
# chkconfig iptables off
This concludes the generic server setup, we are now ready to set up the first server whcih will act as the master.
Setting up the MariaDB master serverFollowing the generic server setup as outlined above, setting the IPADDR to in my case, we are ready to do specific setup. First we need to install MariaDB server and client:
# yum install MariaDB-server
# yum install MariaDB-client
Then we need to set up MariaDB as a master before we start it, we do this by modifying the MariaDB server configuration file:# vi /etc/my.cnf.d/server.cnf
And in the [mysqld] section add:

This concludes the initial master configuration, so now we can start the server to enable us to do some more basic configuration:
# /etc/init.d/mysql start
We are going to add slaves on and and we are to run MaxScale on node, so we need to create a user that can access the master appropriately:
# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.35-MariaDB-log MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'' identified by 'repl';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'' identified by 'repl';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on *.* to 'maxuser'@'' identified by 'maxpwd';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show master status;
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| localhost-bin.000001 |      245 |              |                  |

1 row in set (0.00 sec)
This concludes the master configuration, we are now ready to start setting up the slaves.
Setting up the MariaDB slave serversThe slave servers will be set up on nodes and To begin with, these have the same basic setup as the master above, except that the IPADDR is different of course. Then we install MariaDB server and client:
# yum install MariaDB-server
# yum install MariaDB-client
Then we need to do some basic server configuration, in this case we need to set up the server_id of the slaves, they will be set to 11 and 12 respectively for this test:
# vi /etc/my.cnf.d/server.cnf
And on we set the [mysqld] section to:

And on to:

With that done, we can start MariaDB on both servers:
# /etc/init.d/mysql start
and then we configure these servers as slaves of the master we configured above, execute the following of both and
# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> change master to master_host = '', master_user = 'repl', master_password = 'repl', master_log_file = 'localhost-bin.000001', master_log_pos = 245;
Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)

Now we have a master server and two slaves set up. Before we finish this up and move on to setting up MaxScale itself, let's test that replication work. On the master, do this:
# mysql test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.35-MariaDB-log MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> create table t1(c1 int);
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

This means we now have a table, in the test database, that should be replicated to the two slaves. Run this command on the two slaves to check;
# mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> select * from t1;
| c1   |
|    1 |
1 row in set (0.00 sec)

MariaDB [test]> exit

This concludes the setup of the master/slave cluster that we are to use with MaxScale, in the next blog in this series, that is exactly what we are to do: Set up MaxScale, finally!


What is the ibtmp1 file in MySQL 5.7?

Chris on MySQL - February 1, 2014 - 2:05pm GMT

If you’re running MySQL 5.7, you might have noticed the ibtmp1 file located in the datadir, and you might be wondering exactly what this file is.

In 5.7, InnoDB added a separate tablespace for all non-compressed InnoDB temporary tables. This new tablespace is named ibtmp1 and is located in the datadir by default.

“The new tablespace is always recreated on server startup. … A newly added configuration file option, innodb_temp_data_file_path, allows for a user-defined temporary data file path. For related information, see InnoDB Temporary Table Undo Logs.”

You can read that, and the full changelog entry, here:


And the full variable description is here:


Hope this helps.


Tracking Metadata Locks (MDL) in MySQL 5.7

Chris on MySQL - February 1, 2014 - 12:41am GMT

I’ve blogged about metadata locks (MDL) in the past (1 2 3) and in particular discussed how best to track them down and troubleshoot threads stuck waiting on metadata locks.

If you’ve had any experience with these, you’ll know finding them isn’t always the most straight-forward task.

So I was glad to see metadata lock instrumentation added to MySQL 5.7.3 as part of performance_schema, which makes tracking these down a breeze! (Note this is only in 5.7.3 currently, and therefore is some time from being GA as of today)!

To use these, performance_schema must be enabled (i.e., performance_schema=1 in your config file).

But, also, the metadata_locks instrument is disabled by default, so even if you enable the performance_schema *and* you see the `metadata_locks` table exist in the performance_schema, you will never see any results in that table until you enable it specifically.

This is not necessarily intuitive, so be sure you enable it to begin with, otherwise it won’t be useful for you the first time you need it (though now `table_handles` *might* give you a clue in a pinch).

To do this dynamically (which does *not* persist after a restart):

UPDATE setup_instruments SET ENABLED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

Otherwise, to set it in your config file, add:


Now, for a real world illustration. In one transaction, I simply start a transaction (can be done via BEGIN, START TRANSACTION, or SET AUTOCOMMIT=0), and then select from a MyISAM table. Then attempt a DDL statement on the same table from a different connection, and there you will have a hang due to waiting on a metadata lock.

Connection #1:

mysql> create table t (id int) engine=myisam;
mysql> begin;
mysql> select id from t;

Connection #2:

mysql> alter table t add index (id);
... <-- Hangs

Connection #3:

mysql> show full processlist;
| Id |..| State                           | Info                         |
|  1 |..|                                 | NULL                         |
|  2 |..| Waiting for table metadata lock | alter table t add index (id) |
|  3 |..| init                            | show full processlist        |

mysql> select * from metadata_locks;
| TABLE       |..| SHARED_READ         | TRANSACTION   |..|              21 |..|
| GLOBAL      |..| INTENTION_EXCLUSIVE | STATEMENT     |..|              22 |..|
| SCHEMA      |..| INTENTION_EXCLUSIVE | TRANSACTION   |..|              22 |..|
| TABLE       |..| SHARED_UPGRADABLE   | TRANSACTION   |..|              22 |..|
| TABLE       |..| EXCLUSIVE           | TRANSACTION   |..|              22 |..|
| TABLE       |..| SHARED_READ         | TRANSACTION   |..|              23 |..|

Notice that the OWNER_THREAD_ID is not a direct correlation to the Id in the SHOW FULL PROCESSLIST output, but you can correlate them with a select from the performance_schema.threads table:

mysql> select PROCESSLIST_ID from performance_schema.threads WHERE THREAD_ID=21;
|              1 |

And as I mentioned, should you not have metadata_locks enabled, then you can select from `table_handles`, and perhaps get a clue from there (and if you find a thread or two you suspect, you can obtain the Id from the threads table, as we did above):

mysql> select * from table_handles;
| TABLE       | test          | t           |..|              22 |..|

Hope this helps.

And if interested in further reading:


5.7 Upgrade and Resolving ERROR 1130 Host ‘localhost’ is Not Allowed to Connect

Chris on MySQL - January 30, 2014 - 1:31am GMT

I recently upgraded an instance to 5.7.3 the other day, and ran into an error, so I wanted to share the resolution for it here.

In my case, I was upgrading 5.7.1 to 5.7.3. However, this will apply to anyone wanting to upgrade from pre-5.7.2 (including 5.6/5.5) to 5.7.2+.

I performed the upgrade, in-place, and restarted mysqld. This was fine. However, then I attempted to connect via the command-line, and received the following error:

shell> mysql -uroot -ppass -P3310
ERROR 1130 (HY000): Host 'localhost' is not allowed
to connect to this MySQL server

Searching the net, you’ll mostly find RTM replies, which were all accurate as far as I could tell. In all of those prior reported cases, the issues were expected behavior and the issues were ultimately user error.

Of course I double-checked my config and data files. I knew I didn’t change anything in the user table, or any system table, for that matter. And I only upgraded from 5.7.1, which was a new instance at the time (i.e., the data and tables were not from a previous version).

I then ran mysql_upgrade thinking that surely would fix it. However, my initial mysql_upgrade attempt failed:

shell>mysql_upgrade -uroot -ppass -P3310
Looking for 'mysql.exe' as: C:\MySQL Server 5.7\bin\mysql.exe
Looking for 'mysqlcheck.exe' as: C:\MySQL Server 5.7\bin\mysqlcheck.exe
FATAL ERROR: Upgrade failed

I tried a couple more things, just ot make sure I wasn’t crazy, and then decided to checkout the changelogs (I know, I should have done this *beforehand* anyway). There wasn’t any mention of this in the 5.7.3 changelog, but aha!, there it was in the 5.7.2 changelog.

The full change entry is a bit long for me to post in full (which is a great thing – the detail is most appreciated), but I’ll post the most relevant part here:

“Incompatible Change: Previously, account rows in the mysql.user table could have an empty plugin column value. In this case, the server authenticated such an account using either the mysql_native_password or mysql_old_password plugin, depending on whether the password hash value in the Password column used native hashing or the older pre-4.1 hashing method. With the deprecation of old-format password hashes in MySQL 5.6.5, this heuristic for deciding which authentication plugin to use is unnecessary and it is desirable that user table rows always specify explicitly which authentication plugin applies.

To that end, the plugin column is now defined to be non-NULL with a default value of ‘mysql_native_password’, and associated server operations require the column to be nonempty. In conjunction with this plugin column definition modification, several other changes have been made…”


The page goes on to say that mysql_upgrade does fix this issue. However, you must start mysqld with the –skip-grant-tables. Now, mysql will start without the privilege tables being used, and thus you can connect with a client and you can run mysql_upgrade.

Note your server is unprotected while –skip-grant-tables is enabled, so you should also run it with the –skip-networking option, so outside connections cannot connect, and also disable any local apps that may attempt to access while you perform the upgrade.

As 5.7 becomes used more and more, and more versions are released over time, I suspect this will become a much more popular error, and there will be many looking for the fix.

Hope this helps.


Upcoming MariaDB Enterprise and MaxScale Webinar

Chris on MySQL - January 30, 2014 - 12:12am GMT

As many of you know, both MariaDB Enterprise and MaxScale have been released and are now available for use.

Since they are both so new, I just wanted to let everyone know Ivan Zoratti will conducting a webinar next week discussing both of these technologies.

I’m looking forward to it, and should anyone out there be interested in either MDBE or MaxScale, we hope you’ll attend, and get any questions you might have answered.

When: February 6, 2014 – 6:00pm CET

Sign up now here:


MaxScale for the rest of us - Part 1

Karlsson on databases and stuff - January 29, 2014 - 11:08am GMT

MaxScale for the rest of us - Part 1 SkySQL released MaxScale as Open Source on github a few weeks ago. For some instructions on how to install and configure it, Ivan Zoratti has written a blog about that, and Mark Riddoch has written about why we need MaxScale here, but what I will try in a series of blogs is to describe what MaxScale is, in terms of what you can do with it, and to put it into context, so that you have an idea why you would want to install it in the first place. I will also show how to configure and manage MaxScale. This first post is a quick introduction to MaxScale.

So what is MaxScale then? Mark, and many others, call it a Proxy, and that is partly true, if you download MaxScale as it stands and start using it, it sure is a proxy, but that's underestimating the abilities of MaxScale a bit. The power of MaxScale largely lies in that is is extensible and in how effective the core of MaxScale is.
 There are 5 different types of plugins, with different properties. Some of these are always required, some are optional, but they all have some common properties:
  • The MaxScale core is written in C and the Plugin API is a C API. This doesn't mean a plugin is limited to C, but the interface to MaxScale is.
  • MaxScale is multi-threaded, so plugins need to be threadsafe. This is a good thing, not a restriction!
  • They depend on eachother, to a large extent.
  • The MaxScale core handles a lot of the basics and the routing between modules.
This means that there are a lot of things that MaxScale can do, all you need is to develop a module or two, right? Well, actually it's easier than that as there are some Modules that are already included with MaxScale.
Current MaxScale modulesThe currently available modules are of two types, the ones used for development, debugging and monitoring of MaxScale and the ones that can be used for real database work.
Protocol modulesProtocol modules are key to MaxScale, these are the nodules that enable MaxScale to talk to just about anything. Currently the most important modes are the ones that implement the MySQL client and server protocol (yes, this is two separate modules). For testing and managing MaxScale there are also telnet and http protocol modules.
Authentication modulesThese modules do authentication, but if you look at the current sourcecode, you realize there are no authentication modules. But authentication still takes place, as part of the MySQL protocol modules.
Monitor modulesThese modules monitor the servers and keep MaxScale informed about the status of these, like which one is a master, which is running, which is a slave etc.. This information is then used by the routing modules to know to to route client traffic. There are 2 monitor modules available right now, one that monitors the master/slave status of servers and one that monitors the status of the nodes in a MariaDB Galera Cluster setup.
Filter modulesThe purpose of this kind of plugin should be obvious. This module API is currently not yet implemented.
Router modulesThis is the most interesting type of module, and this is where the logic of routing database traffic takes place. There are currently 3 router modules included with MaxScale, one that does read/write splitting on a cluster master/slave servers, using the monitor to keep track of which is master and slave, one that does simple load balancing and one that handles a MariaDB Galera Cluster.
MaxScale usesAs can be seen from above, MaxScale can be used for many thing potentially. What determines what it can be used for depends largely on what modules are available, potentially it can be used for protocol conversion, query filtering, high availability and load balancing, for example. As it stands with the currently available modules though, is the MySQL protocol and for query routing we can do a couple of different variations on load balancing and HA using Galera.

To be continuedThe next post in this series of blogs on MaxScale will show an actual use-case and how we can solve a real-world problem using MaxScale.


MariaDB Enterprise 1.0 is Here

Chris on MySQL - January 25, 2014 - 5:38am GMT

I know it was officially announced Monday, but I just wanted to take a moment and let everyone know MariaDB Enterprise 1.0 is now available, in case you missed the previous article.

What does MariaDB Enterprise consist of?

“MariaDB Enterprise is composed of several components including MariaDB Manager, which is a set of management tools and an API with which you can easily provision, monitor, and manage a highly available MariaDB Galera Cluster for multi-master, synchronous replication. Galera is a powerful technology that can eliminate single points of failure for your database infrastructure, but it is relatively new and can be a challenge to configure for administrators who aren’t familiar with it.”

https://mariadb.com/..MariaDB Enterprise – Getting Started Guide…pdf

How does it do this?

“MariaDB Enterprise has an API layer as its foundation. The GUI tool which you use to provision, monitor and manage your cluster does all of its work through calls to the RESTful API, including provisioning cluster nodes on bare metal Linux boxes or newly instantiated virtual machines or AMIs. By packaging up the product’s capabilities in this API, we’ve both made it easy to manage Galera Clusters through our GUI, and also provided a powerful tool to simplify the automation of high availability within popular automation and configuration management frameworks.”


There is much more information about MariaDB Enterprise in the above 2 links as well, if interested.

And if you’d like, you can download it from here:



MySQL Workbench Stuck in Fetching Mode

Chris on MySQL - January 20, 2014 - 2:00am GMT

Another obscure issue I ran into not long ago was when using MySQL Workbench, and clicking on a table, it became stuck in fetching mode.

What triggered the issue was a recent MySQL upgrade, but MySQL itself, not Workbench.

After checking the error log, we saw an error like:

Incorrect definition of table mysql.proc: expected column
'comment' at position 15 to have type text, found type char(64)

Instantly, I knew mysql_upgrade needed to be ran in order to fix the “Incorrect definition” issue, and turns out that is the root cause for Workbench getting stuck in the “fetching” mode.

So the solution is to run mysql_upgrade. Should that not fix the table for some reason, then you can also fix it alternatively with:

ALTER TABLE mysql.proc MODIFY `comment` text

Hope this helps.


Be careful if you use file-level symbolic links and myisamchk

Chris on MySQL - January 18, 2014 - 2:33am GMT

I ran into a rather obscure bug the other day, but while uncommon, it can cause damage you would not otherwise expect if you use file-level symbolic links. So this is just a warning about that.

Specifically, if you create a table with the .MYI and .MYD files in a different directory, using symbolic links – either manually or using CREATE TABLE .. INDEX DIRECTORY=”" DATA DIRECTORY=”", and then run myisamchk on the table and specify .MYI, you will corrupt the table.

Creating these manually is not so common, but the CREATE TABLE .. INDEX DIRECTORY=”" DATA DIRECTORY=”" is much more common, which creates file-level symbolic links (for the .MYI and .MYD files, respectively) in the datadir and stores the actual file(s) in the location specified. So it leaves you with this setup.

Therefore, if you later run myisamchk on one of these files, do not specify .MYI in the command invocation. If you invoke myisamchk –help, you will see specifying the table name or the table name appended by .MYI are both acceptable.

shell>myisamchk --help
Usage: myisamchk [OPTIONS] tables[.MYI]

Here is an invocation without specify the .MYI, which runs fine, and displays the expected behavior:

chris@chris-linux:~/$ ../../bin/myisamchk -rqa t1
- check record delete-chain
- recovering (with keycache) MyISAM-table 't1'
Data records: 0

If you do specify the .MYI, it fails *and* corrupts the table:

chris@chris-linux:~/$ ../../bin/myisamchk -rqa t1.MYI
- check record delete-chain
- recovering (with keycache) MyISAM-table 't1.MYI'
Data records: 0
myisamchk: error: Couldn't fix table with quick recovery:
Found wrong number of deleted records
myisamchk: error: Run recovery again without -q
MyISAM-table 't1.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force
(-f) option or by not using the --quick (-q) flag
mysql> select * from t1;
ERROR 144 (HY000): Table './test/t1' is marked as crashed
and last (automatic?) repair failed

I have filed this bug here:


However, until it is fixed, please be careful if you use file-level symbolic links, and run myisamchk.

Hope this helps.


Some myths on Open Source, the way I see it

Karlsson on databases and stuff - January 8, 2014 - 9:24am GMT
The Open Source movement is full of myths, there are different myths from inside the movement (i.e. those who live and breath Open Source or at least thinks it's a good thing) and outside (i.e. those who do not think Open Source really is a good idea to those who thinks Open Source is the work of the devil in a bad way (as I think some people think that something that is the work of the devil has to be a good thing. And they have a point, but let's not get into that right now).

I want to cover some of those myths here and how I look at them here. Also, I am aware that I might be stomping on one or another sensitive Open Source toe here.
Open Source software creates better codeNo, I don't think this is so. But I do think that using an Open Source model properly can potentially help us create better code. But this is not same as saying that OSS always means better code. I am old enough to have been a developer when C was a "hot" programming language, and one of the virtues of C was that it made it possible to create portable code (yes, this is true, this was a supposedly big advantage). But I am also old enough to have been developing when nearly all code you wrote was for a specific machine.

But I still believe that Open Source has the potential to create better code, but then the project needs to be managed properly. I am sorry that I don't fully understand the wonders of the Internet, and I still don't fully see why taking some half-good piece of code and publishing it will suddenly make it better. Good code is good code, be it closed or open, and bad code is just plain bad, both in closed and open shape. Admittedly, there are advantages to code being open, for example that you can see that it is bad, but that assumes that you or someone else knows the code and the domain and the application well enough to be able tell that this is the case: If some code is a stinker, that's what it is, closed or open source!

Peer reviews created by opening up your code creates better codeIn some cases, this is so, in others it doesn't matter. And again for this to work, the field where the code we are looking at is wide enough so more that a few people know it. As for something like MySQL, Linux or JBoss, this is simple. For something like an Open Source ERP system, things start to get more tricky. There are of course OSS ERP systems, but I have to assume that the community involvement is limited.
MySQL is a great Open Source ProjectAgain I am sorry, but I don't think so. As far as code, adoption and reaching out to create an SQL-based RDBMS that anyone can afford, MySQL / MariaDB has been immensely successful. But as an Open Source project, something being developed together with the community where everyone work on their end with their skills to create a great combined piece of work, MySQL has failed. This is sad, but on the other hand I'm not so sure that it would have as much influence and as wide adoption if the project would have been a "clean" Open Source project.

Let's try to image something: Someone finds a flaw in the GPL license and suddenly it is not allowed to share this code as we used to, instead it would be acquired by a corporation that owns and drives the project as Closed Source. What would happen to Linux? I think that would be devastating if say, Red Hat or IBM or someone was driving this by themselves. What about MySQL? I don't think much would change. And hence I think that MySQL just isn't that reliant on the community and is not really a community driven project. I think Linux to a much larger extent is community driven, and I also firmly believe that this is not related to the size of the project.

And to be clear, what we are talking about here is the OSS attributes of MySQL. As far as an RDBMS goes, MySQL is a fine piece of software.
Anyone can read your Open Source code and that makes you a better programmerThis I think is largely true, and this is one of the things I like best with Open Source. But even this has it's drawbacks. Compare these two developers for some project:
  • A great developer but with limited domain knowledge.
  • Someone who know the field we are developing code for really well, better than most, but is not a top notch developer, but still OK.
That someone reads the code that the latter wrote and determines it's n good, and it might even be inferior, doesn't mean that the code in terms of how it works and get's the job done right, maybe even to a greater extent then the former.
Open Source means Software FreedomOh yeah, load up with tomatoes and get ready to throw them now. That the code can be seen by anyone sure means more freedom, right? Well, in some ways it does, but I have two issues with this. First, this software freedom assumes that we are all developers. And not only that, it assumes that we are not only application developers, but also, for example, Database Software Developers with knowledge in C, C++ and it helps to know yacc / bison. Well, I don't have a problem with that, I write C on a daily basis and have done my share of C++ as well as yacc, but then I'm not your average user of MySQL. I am not saying that there is something wrong with being a software developer not being proficient in C, just that C is not very useful for an application developer today, although C probably still one of the big technologies for infrastructure (databases, servers of different kinds, operating system etc). For someone not knowing C, C++ etc, MySQL is just a convoluted piece of software as Oracle. And yes, you can learn C or hire someone who does, but why should you do that? The deal is that we need to separate between the Open Source developers, i.e. those who developer OSS, and Open Source Users, who actually don't get that much more "software freedom", but on the other hand, they probably do not ask for it either.

The other issue with "Software Freedom" that I have is that even though we who are somewhat "in" the OSS Developer Community have a few issues with it. When an OSS developer somewhere out there want's to know how a piece of OSS software works, so he can modify or extend it, he can just read the code, that's all there is to it? Read the Source, Luke! Well, honestly, this isn't by far as good as it sounds, to begin with, the source code doesn't tell you much about what a piece of OSS software does, but it tells you how it does it, but not even how it's supposed to be done. In effect, without proper documentation, OSS provides too much information in some cases. In addition, sometimes I get the feeling that as the source is available for anyone to read, some people think that documentation isn't necessary. But documenting how something works often tells you little about how it is supposed to be used, using the sourcecode to figure out how something is supposed to be used is akin to learning how to drive by dissecting an engine.
My conclusionThe reason I wrote this was not that I don't like Open Source as a development model, that I don't appreciate the hard work of all the OSS developers or that I prefer closed source software. But at the same time, even though OSS in my mind, for many, even most, things, is the best way of developing, distributing, licensing and using software, it's far from perfect. But it is sure is the best model I know, although it is not without it's flaws. And there are areas where OSS really isn't a good model, and I think we have to appreciate that.


A Close Encounter with MaxScale

Ivan's Blog - January 8, 2014 - 9:08am GMT

MaxScale is the new proxy server from the SkySQL/MariaDB team. It provides Connection Load Balancing (CLB) and Statement Load Balancing (SLB) out of the box. This post is a [relatively] quick “how to” install, configure and test SLB with the read/write splitting module.

Step 1 - Server preparationIf you do not have many HW resources, you may run everything on a single Linux instance, but the best way to test MaxScale is to use at least 4 servers: one for MaxScale and for the client apps, one as Master and two as slaves - so, 4 in total. In this post I am going a bit further, I will use 5 servers:
Max 0 - For client apps (
Max 1 - The master server (
Max 2 - The first slave (
Max 3 - The second slave (
Max 4 - The third slave (
Max 6 - The MaxScale server (

In order to do proper tests (i.e. you may want to test performance and read scalability), bare metal servers are recommended, but just for a taste of how you can use MaxScale, 5 VMs or instances on your favourite cloud provider will suffice.
Because of my nomadic behaviour, I have prepared everything on my laptop using VirtualBox. I have created one VM with CentOS 6.5, using 512MB RAM and I have now 5 copies of the same machine. The IP addresses assigned in this test are listed above within brackets.

If you use VMs, make sure they are in the same network and they can connect to each other (in VirtualBox, if you use a host-only network, you should set Allow All in the Promiscous Mode setting. If you use a host-only adapter, you should also have a NAT or a Bridge adapter to connect to the Internet and download the necessary packages.

Adapter 1 is used to connect to the internet and download the packages needed for the test:

Adapter 2 is used to connect to the other VMs with a fixed IP address:
Step 2 - InstallationFirst of all, you must install your favourite version of MySQL/MariaDB/Percona on your servers. In this test, I used MariaDB 10.0.7, downloaded from a yum repo. You can refer to https://downloads.mariadb.org/mariadb/repositories to find out the right repository and download the package, or simply go to this page for the other downloads.
In this test, the repo file is:
[root@Sky0 ~]# cat /etc/yum.repos.d/MariaDB.repo# MariaDB 10.0 CentOS repository list - created 2013-12-31 11:11 UTC# http://mariadb.org/mariadb/repositories/[mariadb]name = MariaDBbaseurl = http://yum.mariadb.org/10.0/centos6-amd64gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDBgpgcheck=1

The software installed on the 5 VMs is listed here:
On Max 0:
  • MariaDB Client

[root@Max0 ~]# yum install MariaDB-client
On Max 1 to 4:
  • MariaDB Client
  • MariaDB Server

[root@Max1 ~]# yum install MariaDB-server MariaDB-client
On Max 6:
  • MaxScale

[root@Max6 ~]# curlhttps://downloads.skysql.com/files/SkySQL/MaxScale/maxscale.preview.0.4.tar.gz >maxscale.preview.0.4.tar.gz[root@Max6 ~]# cd /usr/local/[root@Max6 local]# mkdir skysql[root@Max6 local]# cd skysql[root@Max6 skysql]# tar xzvf ~/maxscale.preview.0.4.tar.gzmaxscale/maxscale/Documentation/maxscale/Documentation/MaxScale Configuration And Usage Scenarios.pdf...maxscale/SETUP[root@Max6 skysql]#
If you prefer to compile your own version of MaxScale, you can get the source code from GitHub.
Step 3 - ConfigurationMax 1 to 4 must run MariaDB Replication, with Max 1 as master and Max 2, 3 & 4 as slaves.
The configuration file should have the server-id and the binary log setup.
[root@Max6 ~]# tail -5 /etc/my.cnf.d/server.cnf
[mariadb-10.0]server-id=1   <<<— and 2, 3 & 4 for Max 2, Max 3 and Max 4log-bin 
Start the four servers in the usual way:[root@Max6 ~]# /etc/init.d/mysql start
Some DB users are needed for Replication and for MaxScale. In this case we have:
  • repluser - for MariaDB Replication
  • maxuser - generic user for MaxScale
  • maxmon - user for the MaxScale Monitoring module

In this test we do not care much about security, we simply create the users as:
MariaDB [test]> create user repluser identified by 'maxpwd';Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> grant replication slave on *.* to repluser@'%';Query OK, 0 rows affected (0.00 sec) 
MariaDB [test]> create user maxuser identified by 'maxpwd';Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> grant all on *.* to maxuser@'%';Query OK, 0 rows affected (0.00 sec) 
MariaDB [test]> create user maxmon identified by 'maxpwd';Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> grant replication client on *.* to maxmon@'%';Query OK, 0 rows affected (0.00 sec) 

Now you can set Replication between Max 1 and the other three servers.
On Max 2, 3 and 4 you can execute these two commands:
MariaDB [(none)]> change master to master_host='',                  master_port=3306,                  master_user='repulser',                  master_password='repluser',                  master_use_gtid=slave_pos;Query OK, 0 rows affected (0.01 sec)  
MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.01 sec) 
On Max 6, the most important step is to create the MaxScale configuration file. The file is located by default in $MAXSCALE_HOME/etc, in our test is /usr/local/skysql/maxscale/MaxScale/etc and the file is MaxScale.cnf.
The file has various sections. 
MaxScale SectionThis is a generic section. for the moment we simply tell MaxScale to use a single thread (for users)

Monitor SectionThis section is used to set the monitoring module. The servers to monitor are named here and they will be configured in other sections.
[MariaDB10 Monitor]type=monitormodule=mysqlmonservers=max1,max2,max3,max4user=maxmonpasswd=maxpwd

SLB SectionThis section is used to set the Statement Load Balancer. We will use the readwritesplit module available with MaxScale.
[RW Split Router]type=servicerouter=readwritesplitservers=max1,max2,max3,max4user=maxuserpasswd=maxpwd

HTTP SectionThis section is used to provide a RESTful API and it is still experimental.
[HTTPD Router]type=servicerouter=testrouteservers=max1,max2,max3,max4

Debug SectionWe will use this section to administer and debug MaxScale. MaxScale is still at early stage and at the moment the debug module is also used as administration module.
[Debug Interface]type=servicerouter=debugcli

Listener SectionsWe set three listeners, for the SLB module, for the Debug module and for the HTTP module.
[RW Split Listener]type=listenerservice=RW Split Routerprotocol=MySQLClientport=4006
[Debug Listener]type=listenerservice=Debug Interfaceprotocol=telnetdport=4442
[HTTPD Listener]type=listenerservice=HTTPD Routerprotocol=HTTPDport=6444

Servers SectionsFinally, we set the 4 servers:

Putting all together, the file looks like this:
## Number of server threads# Valid options are:#      threads=[maxscale]threads=1
## Define a monitor that can be used to determine the state and role of# the servers.## Valid options are:##      module=#      servers=,,...#      user =#                          slave client privileges>#      passwd=[MariaDB10 Monitor]type=monitormodule=mysqlmonservers=max1,max2,max3,max4user=maxmonpasswd=maxpwd
## A series of service definition## Valid options are:##      router=#      servers=,,...#      user=#      passwd=## Valid router modules currently are:#      readwritesplit, readconnroute and debugcli[RW Split Router]type=servicerouter=readwritesplitservers=max1,max2,max3,max4user=maxuserpasswd=maxpwd
[HTTPD Router]type=servicerouter=testrouteservers=max1,max2,max3,max4
[Debug Interface]type=servicerouter=debugcli
## Listener definitions for the services## Valid options are:##      service=#      protocol=#      port=[RW Split Listener]type=listenerservice=RW Split Routerprotocol=MySQLClientport=4006
[Debug Listener]type=listenerservice=Debug Interfaceprotocol=telnetdport=4442
[HTTPD Listener]type=listenerservice=HTTPD Routerprotocol=HTTPDport=6444
# Definition of the servers[max1]type=serveraddress=
Step 4 - Running MaxScaleYou can start MaxScale in many ways, but I would recommend to create a script like this:
[root@Max6 ~]# cat bin/maxscaleMAXSCALE_HOME=/usr/local/skysql/maxscale/MaxScaleLD_LIBRARY_PATH=/usr/local/skysql/maxscale/lib/usr/local/skysql/maxscale/bin/maxscale 
For the administration commands, the script would simply be:[root@Max6 ~]# cat bin/maxscale_admintelnet localhost 4442
Now we are ready to test MaxScale. 
[root@Max6 ~]# maxscale
SkySQL MaxScale     Sun Jan  5 21:10:33 2014------------------------------------------------------Info :  MaxScale will be run in a daemon process.        See the log from the following log files :
Error log     :     /usr/local/skysql/maxscale/MaxScale/log/skygw_err1.logMessage log   :     /usr/local/skysql/maxscale/MaxScale/log/skygw_msg1.logTrace log     :     /usr/local/skysql/maxscale/MaxScale/log/skygw_trace1.logDebug log     :     /usr/local/skysql/maxscale/MaxScale/log/skygw_debug1.log
Listening MySQL connections at http connections at telnet connections at

You can execute this command on Max 0:
[root@Max0 ~]# mysql -u maxuser -h192.168.56.26 -P4006 -pmaxpwdWelcome to the MariaDB monitor.  Commands end with ; or \g.Your MySQL connection id is 1608Server version: 5.5.22-SKYSQL-0.1.0 MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> \s--------------mysql  Ver 15.1 Distrib 10.0.7-MariaDB, for Linux (x86_64) using readline 5.1
Connection id:         1608Current database:    Current user:          maxuser@skycluster6SSL:                   Not in useCurrent pager:         stdoutUsing outfile:         ''Using delimiter:       ;Server:                MySQLServer version:        5.5.22-SKYSQL-0.1.0 MariaDB ServerProtocol version:      10Connection:   via TCP/IPServer characterset:   latin1Db     characterset:   latin1Client characterset:   latin1Conn.  characterset:   latin1TCP port:              4006Uptime:                2 hours 55 min 2 sec
Threads: 6  Questions: 150  Slow queries: 0  Opens: 1  Flush tables: 1  Open tables: 63  Queries per second avg: 0.014--------------
MySQL [(none)]>

The server and server versions are old names that do not provide any meaning and they will fixed in the next release - but they refer to MaxScale.

In order to check if MaxScale is working, check the process list on each MariaDB Server:
Max 1:MariaDB [test]> show processlist;+----+---------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+| Id | User    | Host              | db   | Command     | Time | State                                                                 | Info             | Progress |+----+---------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+|  3 | root    | localhost         | test | Query       |    0 | init                                                                  | show processlist |    0.000 ||  4 | root    | skycluster2:34549 | NULL | Binlog Dump | 3066 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |    0.000 ||  5 | root    | skycluster4:59562 | NULL | Binlog Dump | 2941 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |    0.000 ||  6 | root    | skycluster3:48031 | NULL | Binlog Dump | 2936 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |    0.000 || 11 | maxmon  | skycluster6:32784 | NULL | Sleep       |    7 |                                                                       | NULL             |    0.000 || 13 | maxuser | skycluster6:32791 | NULL | Sleep       |   45 |                                                                       | NULL             |    0.000 |+----+---------+-------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+6 rows in set (0.00 sec)
Max 2:MariaDB [test]> show processlist;+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+| Id | User        | Host              | db   | Command | Time | State                                                                       | Info             | Progress |+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+|  4 | system user |                   | NULL | Connect | 3143 | Waiting for master to send event                                            | NULL             |    0.000 ||  5 | system user |                   | NULL | Connect | -952 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 ||  6 | root        | localhost         | test | Query   |    0 | init                                                                        | show processlist |    0.000 ||  9 | maxmon      | skycluster6:54821 | NULL | Sleep   |    5 |                                                                             | NULL             |    0.000 |+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+4 rows in set (0.00 sec)
Max 3:MariaDB [(none)]> show processlist;+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+| Id | User        | Host              | db   | Command | Time | State                                                                       | Info             | Progress |+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+|  3 | root        | localhost         | NULL | Query   |    0 | init                                                                        | show processlist |    0.000 ||  4 | system user |                   | NULL | Connect | 3058 | Waiting for master to send event                                            | NULL             |    0.000 ||  5 | system user |                   | NULL | Connect | -905 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 ||  8 | maxmon      | skycluster6:57298 | NULL | Sleep   |    9 |                                                                             | NULL             |    0.000 ||  9 | maxuser     | skycluster6:57302 | NULL | Sleep   |  167 |                                                                             | NULL             |    0.000 |+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+5 rows in set (0.00 sec)
Max 4:MariaDB [test]> show processlist;+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+| Id | User        | Host              | db   | Command | Time | State                                                                       | Info             | Progress |+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+|  3 | root        | localhost         | test | Query   |    0 | init                                                                        | show processlist |    0.000 ||  4 | system user |                   | NULL | Connect | 3112 | Waiting for master to send event                                            | NULL             |    0.000 ||  5 | system user |                   | NULL | Connect |  655 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 || 13 | maxmon      | skycluster6:32771 | NULL | Sleep   |    8 |                                                                             | NULL             |    0.000 |+----+-------------+-------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+4 rows in set (0.00 sec)
As you can see from the process list, the connection from Max 0 has been set to Max 1 for R/W and to Max 3 for R/O operations. 
Step 5 Testing MaxScaleA super-simple test would be to check the number of queries and connections on each server before, during and after a mysqlslap session.
First, let’s prepare a basic command like:[root@Sky1 ~]# cat mon.sqlselect * from information_schema.global_status where variable_name in ( 'COM_SELECT', 'COM_INSERT', 'THREADS_CONNECTED’ );
[root@Sky1 ~]# cat monwatch --interval=1 'mysql < mon.sql'
So you can run ./mon on each MariaDB/MySQL server, you should see a page like this, refreshing every second:
Every 1.0s: mysql < mon.sql     Tue Jan  7 23:11:17 2014
VARIABLE_NAME   VARIABLE_VALUECOM_INSERT               54856COM_SELECT               85798THREADS_CONNECTED            2
The threads on the master server are, as you have already seen in the process list, the replication threads and the MaxScale monitor. At least one of the thread on the slave servers is the MaxScale Monitor. On these servers, you will see the value of the COM_SELECT variable stepping +2 every second because the MaxScale monitor checks the status of the database every half second.
Now, if we run mysqlslap with 128 concurrent connections from Max 0:[root@Sky0 ~]# mysqlslap -a -umaxuser -h192.168.56.26 -P4006 -pmaxpwd --create-schema=slap1 -c128Benchmark     Average number of seconds to run all queries: 6.792 seconds     Minimum number of seconds to run all queries: 6.792 seconds     Maximum number of seconds to run all queries: 6.792 seconds     Number of clients running queries: 128     Average number of queries per client: 0
Running the test on my laptop, the time is pretty irrelevant. What is relevant though is the increment that you can notice with the watch command on the DB servers:The number of connected threads on Max 1 (the master node) should go a bit beyond 128 connections;
  • The number of connected threads on Max 2, 3 and 4 (the slave nodes) should be a bit more than 43;
  • On Max 1, the number of COM_INSERT increases significantly, COM_SELECT keeps increasing at the pace of 1 every 0.5 second;
  • On Max 2, 3 and 4, both the number of COM_INSERT and COM_SELECT increases. COM_INSERT increases because the replication thread is adding writing data. The number of COM_SELECT will increase equally on all the slaves because MaxScale is balancing the read queries on all the available slaves.

More tests and in-depth to come. In the meantime, please help us by testing maxscale, provide feedback, comments, suggestions, and submit bugs.
The MaxScale project is on GitHub, here: https://github.com/skysql/MaxScale

It can be a bright 2014

Ivan's Blog - January 2, 2014 - 5:12pm GMT

In many parts of the MySQL world, whether you have users, developers or administrators of MySQL, the season holidays are for family, relax, prayers, travel, or they are simply a moment where you can enjoy what you like or care most about.

For me, this time is dedicated to my family, but also to deeper thoughts around the strategies to adopt in short and long term. My work nowadays, as the work of many others, is ruled by quick decisions, by the "time to market” - whatever “market" means in a specific context. Decisions must be made in meetings that are time-boxed in one hour or even less. In the end, you accumulate so much work and high priority tasks that you do not have enough time to prepare the topics adequately.

I thought I could summarise my thoughts for the past year and for the near future, from a technical and from a business perspective.

A transient 2013To me, 2013 was a transient year. Technically, the most important achievement has been the release of MySQL and Percona 5.6. The first GA release was fairly disappointing, but later versions fixed many issues and today we have a pretty solid version that provides great performance and scalability. In my humble opinion, the way Replication has been implemented in 5.6 still lacks essential features. We must wait for 5.7 and MariaDB 10 to see a more robust and feature-rich solution, but apart from Replication, 5.6 is now a great version that users should consider for their production databases.

Beyond the core MySQL server, the two other significant achievements in 2013 have been the release of Galera Replication 3 and NDB 7.3. Galera has been enriched with features that make it a serious HA solution, although there is still a lot of work to do on the administration side. NDB 7.3 now has foreign keys. I do not think foreign keys where the most important feature for NDB, but I know that it has been requested for a long time by many Oracle customers, and it can certainly help in many cases.

On the business side, the merge of SkySQL and Monty Program has probably been the most important event in 2013. For two years, the two organisations were like one company split in two entities, one made only by software engineers, and the other one more service-centric. The merge became natural and it attracted the necessary funding to improve products and to speed up the growth of the new company.

Oracle, on the other hand, has demonstrated to support MySQL with a significant investment in Engineering resources. The last year at Sun and the first two years at Oracle have not been easy. Now, under Tomas Ulin’s leadership, at least from an external point of view, it looks like the Engineering team is more focused and is making good progress in many areas of the core server and connectors.

Improvements neededIn 2013 we had great technical achievements, but database solutions are not completed yet. 5.6 is a great version per se, but if you look at a database strategy as a whole, there is still significant work to do to provide real improvements in a sector that is seriously overcrowded. In the past, MySQL had to compete with feature-rich, but also with extremely expensive commercial RDBMSs. Migrations have always been complex projects and very few were financially justifiable, especially in companies that are risk adverse when it comes to technology. IT managers prefer to review their applications in the light of more agile technologies, and when they do that, they have plenty of options to manage their data. MySQL is now squashed between the legacy RDBDMS heritage and a large number of NoSQL technologies that claim to be the new and best solution to all the problems - it is not an easy position to maintain. Despite some analysts being ready to swear the opposite, it is quite clear that the number of new services and applications that are created with MySQL today are very few compared to the overall number. 3 to 5 years ago, MySQL was the default DB choice for any new application.

I do not think much business will come from large migrations of legacy RDBMSs - that is a discussion for another post. If we want MySQL to maintain the credibility of a solution that can compete with NoSQL technologies and win the hearts and minds of developers and startups, we need to work not only on the core, but also on many side aspects of the product. It is quite clear that it is not a task that can and should be achieved only by Oracle. It is a joint effort that requirers the involvement of all the players in the MySQL ecosystem. Not only we need to improve performance and scalability in the core server, we also need improvements and innovation in areas like data distribution, replication, integration and interoperability. Companies like Continuent, Scalebase and others are definitely called to participate. We should also look at different and more stable storage engines - not just “a better InnoDB”, but engines that can make MySQL useful in areas that are not its usual sweet spot. Although I think that MySQL should be focused on what it is really good at, that is managing "small data” and transaction-based applications, we should consider that many companies want to deal with a single DBMS and they are happy to use one product that fits very well for their core business and is “ok" in other areas. Even more importantly, we need to seriously consider how we can make MySQL the obvious choice for developers. For example, it is not a sin to look at the way MongoDB makes things easy and fast for developers. We must consider some of these ideas for MySQL and think of tools and features to develop - also this topic is material for another post.

What to expect in 2014?Without doubt, there is a lot of interest around MariaDB 10, and for very good reasons. 10.0.7 has been released only a week ago, many bugs have been fixed, especially in the new storage engines. MariaDB 10 is the first real fork in the MySQL world: its development comes with extreme challenges, but also with exciting opportunities. More importantly, MariaDB 10 will raise the bar again, which is the positive aspect of a healthy technical competition, where users can really benefit from. So, let’s also see what 5.7 can bring this year and what we can expect as next version. SkySQL (outside MariaDB 10), Continuent, Codership and Percona should also bring more. There is a lot of work to do to increase the scalability of solutions around MySQL and this is a great opportunity for these companies.

Cloud opportunities stand between technical and business challenges in 2014. On one side, the risk is that most of the revenues in this area will be absorbed by cloud providers, and this is not very good news for MySQL at the moment, because these revenues will not be reinvested to make a better MySQL. The contribution from Cloud providers to MySQL has been minimal, if non-existent in some cases. They have mostly used what was available and they have added the minimum set of features for MySQL to work better in their cloud offering. No real innovation has been added to the product to work better in the Cloud - this is what I call “The false promise of the elasticity in the Cloud”, but again it is a topic for a more specific post.

From a business perspective, the consolidated marriage of SkySQL + Monty Program should show clear benefits in the new year. We may take this for granted and be focused on an even more important aspect. As part of the ecosystem, we need to work on two main business points.
The first point requires a joint effort of users and providers of the MySQL technology. Core users - Facebook, Twitter, Google and others - should maintain and increase their MySQL footprint. Of course this is not a request, it is an opportunity that providers have to make, but also one that users have to feed. In some cases NoSQL technologies provide a good solution to a problem, but in others MySQL, with very little improvements, may be the best solution. In other words, we should see NoSQL technologies flourish when they fit in a project for their core features, not when MySQL lacks only some side aspects.

Finally, providers in the MySQL ecosystem must find new ways to monetise their investments in products. It is an issue for MySQL today, but it will also affect NoSQL products soon. With few exceptions, licensing for open source databases does not protect well enough the companies that are heavily investing in MySQL and in NoSQL development. In other words, the business may be sustainable today, but it may be at risk in long term. We must find a way to guarantee good revenues for MySQL providers, without undermining the values of open source and by leaving to the end user the choice of using the software by paying for services or for free.

These are my thoughts for the past and the new year. Let’s enjoy the ride and draw a line again in 365 days from now.

Syndicate content