MariaDB 10.0 upgrade goes smoothly

I have been meaning to update some systems to MariaDB 10.0 and finally had a bit of time to get around to that.  The documentation of specifics of what’s needed to go from MariaDB 5.5 to 10.0 can be found here and while it’s not very long it seems there’s little to actually do.

Having already upgraded some servers from MySQL 5.5 to 5.6 the process and appropriate configuration changes were very similar so all in all a rather non event.

One thing which is always a concern if systems can not be down for long is the time to do the upgrade. While you see many blog posts talking about taking a backup via mysqldump and then loading it all back this is not really an option on many systems I manage and a replacement of binaries, adjustment of /etc/my.cnf  and restart of the server with the new binaries followed by running mysql_upgrade is what I usually do.  That usually works fine.

One server I had to upgrade had quite a few files (15,000) and the database occupied 2 TB. The run time of mysql_upgrade on such a system takes a couple of hours of checking tables after which the actual system changes take almost no time at all. So that is something to be aware of if your dataset is similar.

It seems I was confused by Colin’s post on performance_schema being disabled in MariaDB 10.0.12 and later. It seems that actually it’s disabled on startup, so can be easily configured if desired by setting performance_schema = 1 in /etc/my.cnf prior to starting mysqld. I had thought that it was not compiled into the binaries at all, which is what’s been done in WebScaleSQL. That is not the case.

There’s a lot of talk about performance_schema overhead, even recently from colleagues, so this is a subject which needs looking at in more detail and if there is indeed an unacceptable overhead then that needs looking at. I’m sure Oracle or MariaDB would appreciate reports of specific issues as otherwise there’s just too much fud out there.

Anyway the MariaDB 10.0 servers I upgraded did have p_s and my configuration enabled it. That’s nice as now I can see where some of the load and performance points are, and had thought that would not be possible. I also tried Mark Leith’s mysql_sys and was not sure if it would work in MariaDB 10.0 but a quick look seems to indicate it does which is helpful.

The views in this sys schema are very useful but some care is needed when using them as several tables are joined in performance_schema and if the number of rows involved is high, and given performance_schema has no indexes, this can make queries be very costly, taking minutes to run. That’s not been mentioned, so take a little care. Under normal usage this does not seem to be an issue, it really depends on different use cases. Longer term I think that P_S will need indexes, even if these are only “memory tables” …

The upgrade went smoothly, so now it’s time for me to check the new MariaDB 10.0 features and see how they fare.

 

Published by

Simon J Mudd

Born in England, I now live in Spain, but spent a few years living in the Netherlands. I previously worked in banking (financial markets) both in IT and as a broker, but IT has always had a stronger influence. Now working at booking.com as a Senior Database Administrator. Other interests include photography, and travel. Simon is married, with two children and lives in Madrid.

Leave a Reply