MySQL Slave Scaling and more

July 16th, 2014

Jean-François talks about binlog servers. Take a look here: http://blog.booking.com/mysql_slave_scaling_and_more.html

Time to get some 128-bit types into MySQL?

June 30th, 2014

I think that getting 128-bit types into MySQL would be good. There are a few use cases for this and right now we have to work around them. That should not be necessary.  While not essential they would make things easier.

The headline is easy to understand, but is this really needed?

First we need to look to see where this might be used. I can think of three different 128-bit types which are missing at the moment:

  • IPv6 addresses
  • uuid values
  • a bigger value than (signed) bigint [64-bit numbers]

IPv6 Addresses

IPv6 addresses are 128-bit numbers, and having a native way to store them would be really helpful. Given this also includes an IPv4 representation then for those people who store IP addresses (client connections and other things) such a native type would be much better than the typical unsigned int or binary(4) which you might be using now. Is this an IPv4 address? Well it might be, but it also might not be.  The same applies to IPv6, and having a real IPv6 type makes this knowledge more explicit.

MySQL already provides support routines for IPv4 (even if the type does not exist) such as INET_ATON(), INET_NTOA() so a similar set of routines would be needed to support this type, converting between their text and numeric representation and also for converting between IPv4 and IPv6.

UUID Values

MySQL itself uses UUID values in 5.6 and above as the server_uuid, but it’s stored or seems to be as a string. Other software (MEM is a good example) also uses UUID values in various places.

Have a look on search engines for MySQL and UUID and you see lots of questions on how to best store these values in MySQL. So there is already a demand for this, and no good answers as far as I can see.

One common concern I have currently when storing such values as binary(16) is that the values are hard to visualise, especially if used as a primary key, and also from the DBA’s point of view who may want to “manually” access or modify data  it is not possible to do something similar to SELECT name FROM servers WHERE uuid = ‘cd2180ae-9b94-11e2-b407-e83935c12500′, as this just does not work. Casting could make this work magically but right now it’s much harder than it should be.  There is not a single UUID format but the basics are the same and if we had a uuid format any supporting routines (which would be needed) would be able to convert as needed.

Signed or unsigned integers

Yes, the (signed or unsigned) bigint type gives us 64-bits and that allows for huge numbers but one size bigger matches the use cases above, so it’s good to be able to convert between them depending on the usage.  That is if we’re going to have IPv6 and UUID type values, it makes sense to allow an integer equivalent representation and sometimes this might be needed when stripping out parts of a uuid, or parts of an IPv6 address.  The name of this type should be something a little better than we’ve seen before so hugeint (unsigned) would not be what I would suggest. Something as simple as int128 (unsigned) would be much easier to understand.

Conversion routines

Each of the three types above need routines to support their “native” usage and probably converting from / to numeric or text representations of the value.  Given the three types have the same size then it may also be useful to convert from one format to another. The actual content would not change, just it’s representation. Included with this would be a BINARY(16) so that people who might have had to use other MySQL times to represent these values have an easy way to convert more explicitly to them and if for any reason a conversion back is needed this is also possible.

ALTER TABLE should be aware of these equivalents too so if I have a table defined with a BINARY(16) I can convert it to an IPv6 address/type as a no-op operation (definition only change), in a similar way as can be done with some other conversions (ENUM being a common type that changes but if you add a new value there’s no need to check the table for existing values as the old definition was a subset of the new one).

No incompatible changes in minor versions please

A change such as this can not reasonably be added as a minor version change as if we would break many things.  Minor versions should really, really only included bug fixes, or performance improvements, and if a new feature really has to be added by default it must be disabled (for compatibility) and enabled with some sort of special option. Given there’s no agreed way to do this and it is likely to cause all sorts of issues, just do not do it.

That means that a feature such as this can only be added in a new version such as MySQL 5.7 or MariaDB 10.1 both of which are DEV versions, and so allowed to change in any way their authors deem reasonable. I have seen no indication of 5.7 including this functionality and given the time that 5.7 has been about I am inclined to think that an extra change such as this is unlikely to make it there. So MySQL 5.8 then? MariaDB 10.1 development has not been ongoing for that long so maybe such a feature might be considered there.

In the end we do need these new features and long lead times to make them available is a considerable source of frustration for those of us who have a number of systems to upgrade.  One thing is a new version going GA, but it’s something else to have all systems upgraded to use that version and thus make it available to developers.

Whatever happens it would be really helpful if the different “MySQL vendors” talk to each other, if they agree that this is a sensible path to take. Having various different interpretations of how these new types should be stored, converted and which associated functions etc are needed would be a user or developer’s nightmare. I understand there is competition, but for something like this it is really important to get it right.  The first implementor of such a feature would potentially have an advantage over the others but I would expect usage of this type of data types to be quite popular so agreeing generally on what to do should not be that hard and avoids the different forks from drifting off further apart, something which I think is bad for everyone concerned.

Conclusion

Some people I have spoken share the opinion that having such a set of 128-bit types would be good. It is something else of course to implement that.  For those looking for new features to develop in MySQL this is one which in theory is not absolutely necessary but which I think would not only be popular but would be used.  In the end MySQL is there to store data, and make it easy to retrieve and it seems clear to me that this type of data is one such usage which while it can be handled differently would really welcome “native” support. I hope that this will happen sometime soon.

Update 2014-07-03

MariaDB seems to have some support on its way for this. Referenced on maria-developers on 1st July, details can be found here:

If a plugin type is available for IPv4 that might be good as well.

This looks like work in progress and there’s no mention of a 128bit (unsigned) int, or how to convert between different values, but this looks like a good start. In fact if it’s possible to make these types available via a plugin interface this does seem to add the possibility of adding new special types even once MariaDB is working, so it makes it easier to expand functionality later.

In terms of routines that probably should be available in MySQL to support some of these types the following stand out:

  • INET_PTON() and INET_NTOP() to supplement the existing INET_ATON() and INET_NTOA() functions.
  • GETADDRINFO() and GETNAMEINFO() to convert between IPv4 or IPv6 addresses and names. existing INET_ATON() and INET_NTOA() functions.
  • Something like  UUID_LONG() to generate a 128-bit numeric equivalent of  UUID(), and functions to convert a text-based uuid into a number and a something to convert back again, STRING_TO_UUID() and UUID_TO_STRING() unless there already exists some standard function name for these tasks.

I think all of these look like useful routines to go with the types above. I’ll add more as I think of them.

Update 2014-07-11

I also see this very old bug referenced in the mysql bug list: http://bugs.mysql.com/bug.php?id=15940

webscalesql-5.6.17.69 RPMs available for CentOS 6

May 12th, 2014

A new commit b955fd46ee60b134c6935badb43eb838872cfbbf was pushed out to the webscalesql-5.6 so I’ve built some updated RPMs using my webscalesql-rpm scripts.  The new binaries if you want to try them can be found at http://ftp.wl0.org/webscalesql/.

The rpms are:

webscalesql-5.6.17.69-1.rhel6.src.rpm
webscalesql-client-5.6.17.69-1.rhel6.x86_64.rpm
webscalesql-devel-5.6.17.69-1.rhel6.x86_64.rpm
webscalesql-embedded-5.6.17.69-1.rhel6.x86_64.rpm
webscalesql-server-5.6.17.69-1.rhel6.x86_64.rpm
webscalesql-shared-5.6.17.69-1.rhel6.x86_64.rpm
webscalesql-test-5.6.17.69-1.rhel6.x86_64.rpm

Again these packages are work in progress, but feedback is welcome.

MMUG7: Madrid MySQL Users Group meeting to take place on 24th April 2014

April 22nd, 2014

Madrid MySQL Users Group will have its next meeting on the 24th of April. Details can be found on the group’s Meetup page.

We plan to talk about WebScaleSQL and I will give a short presentation on how to build WebScaleSQL RPMs on CentOS 6.  The meeting will be in Spanish.

We’ve changed the place that we’ll be holding the meeting. See the Meetup URL for details. Looking forward to seeing you there.

La próxima reunión de Madrid MySQL Users Group tendrá lugar el jueves 24 de abril. Se puede encontrar más detalles en la página del grupo.  Hablaremos sobre WebScaleSQL y ofreceré una breve presentación sobre como construir RPMS de WebScaleSQL para CentOS 6.  La reunión será en español.

Hemos cambiado el lugar donde se ubicará la reunión. Mirar la URL del Meetup para más detalles. Esperamos veros allí.

WebScaleSQL RPMs for CentOS 6

April 1st, 2014

Looks like this post was rather unclear. See the bottom for how to build the rpms quickly.

WebScaleSQL was announced last week. This looks like a good thing for MySQL as it provides a buildable version of MySQL which includes multiple patches from Facebook, Google, LinkedIn, and Twitter needed by large users of MySQL, patches which have not been incorporated into the upstream source tree.  Making this more visible will possibly encourage more of these patches to be brought into the code sooner.

The source is provided as a git repo at https://github.com/webscalesql/webscalesql-5.6 and as detailed at http://webscalesql.org/faq.html the documentation says there is currently no intention to provide binaries.

Instructions on building the binaries and the build requirements for WebScaleSql can be found at http://webscalesql.org/faq.html and do not look too hard. However, I prefer to install my software as rpms as this makes upgrading or removing it later much easier.

With that in mind I thought I’d try and build some webscalesql rpms.

As I’m currently using MySQL-5.6 rpms, downloaded from http://dev.mysql.com/downloads/mysql/, I wanted to build WebScaleSQL rpms which were compatible with these.  I’m aware of the Oracle-built “community” rpms which are downloadable directory from their yum repo (https://dev.mysql.com/downloads/repo/) and will probably use these when upgrading to MySQL 5.7 but moving over to that now requires changing internal infrastructure and is currently not worth the effort.

In order to build the WebScaleSQL rpms I did the following:

  • download the latest source rpm, MySQL-5.6-17-1.el6.src.rpm from http://dev.mysql.com/downloads/file.php?id=451516
  • extract the spec file: rpm -ivh MySQL-5.6.17-1.el6.src.rpm and look in the directory specified by rpm –eval ‘%{_specdir}’ for the spec file (mysql.spec)
  • install the devtools package in order to use GCC 4.7:

http://people.centos.org/tru/devtools-1.1/readme says:

sudo wget http://people.centos.org/tru/devtools-1.1/devtools-1.1.repo -O /etc/yum.repos.d/devtools-1.1.repo
sudo yum install devtoolset-1.1
  •  clone the webscalesql.git repo to any directory, let’s call it  $WSS_HOME
  • create a webscale-5.6.tar.gz tar ball and put it in the RPM SRC_DIR:
     cd $WSS_HOME/.. && tar cz --exclude-vcs -f $_sourcedir/$tarball $WSS_HOME
  • create a minimally changed webscalesql.spec file, based on mysql.spec, to include the new path for the compiler toolchain
  • build the rpm by cd’ing into the directory with the webscalesql.spec file and running:
echo "Building WebScaleSQL..."
MYSQL_BUILD_PATH=/opt/centos/devtoolset-1.1/root/usr/bin:$PATH \
MYSQL_BUILD_CC=/opt/centos/devtoolset-1.1/root/usr/bin/gcc \
MYSQL_BUILD_CXX=/opt/centos/devtoolset-1.1/root/usr/bin/c++ \
MYSQL_BUILD_CFLAGS= \
MYSQL_BUILD_CXXFLAGS= \
MYSQL_BUILD_LDFLAGS= \
MYSQL_BUILD_CMAKE= \
MYSQL_BUILD_MAKE_JFLAG= \
rpmbuild -ba --define "distro_specific 1" webscalesql.spec

Warning: no explict check is made for the devtools chain to be installed and compilation will break if you try to use the native gcc compiler.

  •  this built the following rpms:

[sjmudd@myhost PKG]$ ls -l webscalesql-*
-rw-rw-r-- 1 sjmudd sjmudd 30925511 Mar 30 13:21 webscalesql-5.6-0.20140317.155729.rhel6.src.rpm
-rw-rw-r-- 1 sjmudd sjmudd 19333116 Mar 30 13:21 webscalesql-client-5.6-0.20140317.155729.rhel6.x86_64.rpm
-rw-rw-r-- 1 sjmudd sjmudd  1962332 Mar 30 13:23 webscalesql-devel-5.6-0.20140317.155729.rhel6.x86_64.rpm
-rw-rw-r-- 1 sjmudd sjmudd 81725224 Mar 30 13:24 webscalesql-embedded-5.6-0.20140317.155729.rhel6.x86_64.rpm
-rw-rw-r-- 1 sjmudd sjmudd 54210064 Mar 30 13:21 webscalesql-server-5.6-0.20140317.155729.rhel6.x86_64.rpm
-rw-rw-r-- 1 sjmudd sjmudd  2062312 Mar 30 13:23 webscalesql-shared-5.6-0.20140317.155729.rhel6.x86_64.rpm
-rw-rw-r-- 1 sjmudd sjmudd 53369596 Mar 30 13:23 webscalesql-test-5.6-0.20140317.155729.rhel6.x86_64.rpm
[sjmudd@myhost PKG]$

These rpms should work for CentOS 6, RHEL 6 and other equivalent distributions.  I have not actually tried to use any of the packages except the webscalesql-server.

I have had very little time so far to play with this, but did replace the MySQL-server package with webscalesql-server on a development server and let it run for a few hours.

One thing I did notice is that the performance_schema* settings I had in /etc/my.cnf were not recognised by webscalesql-server and had to be commented out. That said performance_schema still seemed to be there.

I need to check further but guess that this may be due to differences between MySQL and webscalesql or potentially something I have not done correctly when building.

Other than that the server replicated fine and I saw no issues.

This has given me some basical rpms for testing.  I have not tested the package on anything other than CentOS 6 and it is likely that other changes are needed. I probably need to do a few other things like:

  • Clean up the package further maybe adjusting copyrights or other messages about the packages.
  • Obsolete the installed MySQL-server, … rpms so I can just do rpm -Uvh webscalesql-server …. rather than remove the MySQL-server package first.
  • Add a bit of scripting to incorporate the date of the latest webscalesql commit into the version/release settings in the spec file. This avoids having to manually change the different values and as updates happen a rerun of the build script should just build a new package transparently.

I have not yet had time to look at the patches that have been applied to WebScaleSQL. It would certainly be nice to have some sort of list of functional changes (such as the performance_schema difference I noted earlier, assuming this is not a build error) of WebScaleSQL compared to the upstream source and any new configuration settings. Perhaps that will happen later?

At least for those of you who want to run a quick test of the binaries, or look at my spec file, you can find them on my website: http://ftp.wl0.org/webscalesql/.  No guarantees of any kind as you can imagine but feedback and improvements to the current spec file or build procedure would be most welcome.

2014-04-03 Update

See: https://github.com/sjmudd/webscalesql-rpm/ which I have created as a quick helper script to do the build. It still probably needs quite a bit of work but avoids copying instructions and doing stuff by hand.

2014-04-22 Update 2

Basically all you need to do is:

Install the devtoolset-1.1 or devtoolset-2 rpms (as indicated above)
Ensure that all required rpm build directories are created.
$ git clone https://github.com/webscalesql/webscalesql-5.6.git # clone WebScaleSQL sources
$ git clone https://github.com/sjmudd/webscalesql-rpm.git      # clone my builder script repo
$ cd webscalesql-rpm
$ ./build ../webscalesql-5.6                                   # build the rpms

If you later need to build an updated version of the rpms:

$ cd ../webscalesql-5.6
$ git pull # update sources
$ cd ../webscalesql-rpm
$ git pull # update my builder script (if necessary)
$ ./build  # no need to pass the webscalesql-5.6 directory location as it is remembered.

MySQL 5.6 GTIDs: Evaluation and Online Migration

March 28th, 2014

A colleague and I have been looking at GTID on MySQL recently and you may be interested in the blog post that results from that. You can see it here. http://blog.booking.com/mysql-5.6-gtids-evaluation-and-online-migration.html.

 

MMUG6: Madrid MySQL Users Group meeting to take place on 20th March 2014

March 1st, 2014

Madrid MySQL Users Group will have its next meeting on 20th March. Details can be found on the group’s Meetup page.

I will be giving a presentation on MySQL replication hopefully aimed at all levels, but covering some details relevant to larger setups. The meeting will be in Spanish.

Look forward to seeing you there.

La próxima reunión de Madrid MySQL Users Group tendrá lugar el jueves 20 de marzo. Se puede encontrar más detalles en la página del grupo.  Ofreceré una presentación sobre replicación de MySQL dirigido a gente de todos los niveles, pero incluirá información relevante a entornos más grandes.  La presentación será en español.

Espero veros allí.

MySQL 5.6 GA one year – What is next?

February 9th, 2014

MySQL 5.6 has been GA for just over a year now. See MySQL 5.6.10 Release Notes.  Congratulations on your birthday! That is quite a long time. I was using it earlier in production because it worked and could do things that 5.5 could not do, but earlier versions were to use at your own risk, and indeed if prodded incorrectly would fall on the floor. That is fair enough because they were work in progress, yet if you poked them the right way they did a very good job.  Those dev versions have been long since upgraded which is good so they do not need quite as much care and attention.

So from where I see 5.6 it works very well. One big change that has made a large difference but which I think a lot of people may not really understand or use is the performance_schema database. That provides a huge amount of great and detailed information about what the server is doing, where it is busy and why, and allows you to know these things rather than twiddle and guess which in the past we have been forced to do. So a big thanks for that. It does require quite a lot of study and Mark Leith’s dbahelper goes a long way to making PS useful to the average DBA.  A recent issue came up and I was asked (by Oracle support) to use it to find out the causes: a few SELECTs and hey presto the answers were staring me in the face.  So Oracle is eating its own dog food and it tastes nice…  If you have not had time to look at performance_schema or dbahelper yet take a poke.

The current MySQL 5.6 GA version (5.6.16) is pretty good now. I am aware of a few bugs which I am waiting to get fixed, but all in all the version works pretty well and I am happy with it.  I have filed quite a few feature requests and the reason for these is often to make my day to day life a bit easier, as when MySQL breaks, and as you watch over more servers this happens, things which might not bother other people become more of a problem. This includes better logging, addition of counters to measure more things which help when problems occur or to diagnose how frequently a problem might be happening and now this information is missing.

I am still working on getting systems upgraded to MySQL 5.6 and have some work to go. It is surprising just how long this can take, but that is often due to changes in a growing business which means that new systems appear, changes happen to existing ones and the time needed to get the MySQL 5.6 upgrade tasks done gets a lower priority than ideally I might like. That said MySQL 5.5 works and works well but 5.6 does have new things I want and shortly those remaining systems will have been upgraded and the job will have been done.

Well not quite.  Recently I have been looking at 5.7 DEV. A lot of work is going on there. Some of this is to give us new features that I have wanted for some time:

  • multi-source replication comes to mind, but that is a parallel branch to 5.7 which I think is unfortunate:
    • I still think that pulling this out into a separate process would have made life much easier as I blogged about previously, and indeed is how Sybase replicates, but I guess that is too large a change to be considered.
    • It is not clear if this feature will make it into 5.7 GA but I hope it will (Oracle please add it, MariaDB 10 has this feature, and for some use cases it will be very attractive)
  • more dynamic replication configuration is a much wanted new feature. A number of times I have been unable to reconfigure a slave without restarting the server and this has been most frustrating. It may have meant that I could not do the required change at all and or had to find an alternative work around to solve the problem. So this new feature is clearly helpful.
  • more replication parallelisation, again one of the bottlenecks that many DBAs have come across, promises to make life better
  • improved P_S will carry on and give us more of the information that we need.

So a lot of these new features look quite juicy and more stuff will probably arrive in 5.7.4, so the GA version, when it arrives, is going to be good.

One thing that I have been looking at recently is GTIDs in MySQL. This is quite a topic in itself. Oracle has implemented this in 5.6 a certain way and in MariaDB 10 (currently DEV version) the implementation is approached differently, so that should prove quite interesting as neither mode is compatible.  The end result that DBAs want is to ensure that it is easier to synchronise the state of master and slaves, and to prevent the re-application of transactions that have already been applied to a system.  As it will soon be possible to replicate from multiple sources in MySQL and MariaDB then this topic will become more tricky and help from the “system” is much appreciated.

That said while I have used GTID a little on a few systems and it does seem to work and make life easier, it is not quite as nice and easy to use as one would like. Work seems to be ongoing in that direction to improve things and in helping us make the transition from a non-GTID environment to one which is GTID aware.  All that help is most appreciated.

For a replicated MySQL environment that has to run 24 x 7 x 365 downtime is painful to a DBA, and expensive to a business. Things do break. We expect that, whether that is due to bugs, hardware or even human failure.  We then need to be able to recover these systems as best as we can, as quickly as we can and once we have done that be confident that we understand what broke, what damage was done and what further work may or may not be required to complete the job.  So if replication uses GTID that focus has to be understood and taken into account by the developers, and support for GTID should cover those needs.  That is vitally important.

All in all I am looking forward to the preparation for the next GA version of MySQL even if it is still maybe more than 6 months away. It is a bit like planning your summer holiday in the cold winder. Lots of things to look forward to, plenty of interest in trying them, and the slight frustration you have to wait just a little bit longer before that time comes.  I’m looking forward to the sun already…

postfix 2.10.2 RPMs available

December 29th, 2013

I’ve just updated my RPMs to now support postfix-2.10. The Source and binary RPMs (for RHEL5 and RHEL6 x86_64) can be found at the following location http://ftp.WL0.org/official/2.10.

As you probably are aware I am not really following postfix development any longer, but I was recently asked about an issue for an older version, so if you still use these packages and see any issues please let me know and I will try to address them.

Next Madrid MySQL Users Group meeting to take place on 16th January 2014

November 27th, 2013

Yesterday we had our third Madrid MySQL users group meeting. That was quite interesting.  Thanks go to Juan for his presentation.

We plan the next meeting on January 16th after the New Year is out of the way. If you are interested in MySQL and happen to be in Madrid please consider coming to see us.

More information about the next meeting can be found on the group’s web page. Note: The meeting will be in Spanish. I look forward to seeing you.

MySQL RPMS and the new yum repository

November 7th, 2013

I was really pleased to see the announcement by Oracle MySQL yum repositories that they have now produced a yum repository from where the MySQL RPMs they provide can be downloaded. This makes keeping up to date much easier. Many companies setup internal yum repositories with the software they need as then updating servers is much easier and can be done with a simple command. For many people at home that means you set this up once and don’t need to check for updates and do manual downloads, but can do a quick yum update xxxx and you get the latest version. Great!  This new yum repository only covers RHEL6 did not include RHEL5 which is not yet end of life and still used by me and probably quite a lot of other people. I filed bug#70773 to ask for RHEL5 support to be considered, especially as it was being provided already, and asked for them to also consider including 5.7, but have been told that RHEL5 support will not be added. As you will see later perhaps it is now clear why.

I was somewhat surprised then to read a later post Updating MySQL using official repositories which showed that actually the rpms in this new repository are completely different to the previous ones (MySQL community rpms) that we have been downloading from http://dev.mysql.com/downloads/mysql/#downloads. The package names differ being MySQL-server…rpm, MySQL-client…rpm on the latest page whilst the package names in the new yum repository are named mysql-community-client…rpm, mysql-community-server…rpm, etc.  It looks like these packages are incompatible, thus for those of us already using the first set of RPMs the new yum repository is rather useless, and switching from one set of RPMs to another is quite a nuisance if you have a number of servers.

To avoid confusion in any later comments in this article I will refer to the ORIGINAL MySQL packages as the ones provided by Oracle previously and the NEW MySQL packages as these new ones provided in the new yum repo.

No mention of these differences was made by Oracle in their announcements, so obviously no reasons were given for why they might have chosen to name packages differently and not simply provided a yum repository containing the existing packages they have already built. That is what I had assumed they’d done and would have seemed to have been the most logical step to take.

Independently of the reasons that Oracle has decided to build two sets of RPMs, one thing is clear: They now have to maintain 2 different sets of package for the same distribution (RHEL6) in parallel. It also brings up the question of what will happen with MySQL 5.7 that is out there already being offered in the ORIGINAL packaging format and whether or not new 5.7 packages will be provided in ORIGINAL or NEW versions or both….  I have been trying out the current DEV version of 5.7 and indeed I know that 5.7 can change anyway Oracle chooses until they declare the version GA, so fair enough. The only thing it does is raise doubt about how to prepare for that moment as MySQL 5.7 has many interesting features and some of us way want to use those as soon as possible.

Anyway I digress. I thought that I would see what differences there may be between the src rpms of the ORIGINAL and NEW packages. As the src rpms are available I was able to download these packages to see. One would expect them to be pretty much the same, as after all the software is the same. It is also true that the NEW packages must provide support for Fedora releases so some changes would have been necessary to make that work correctly. Other than that I would not expect many differences.

I downloaded the src rpms from both locations, the original sets of MySQL community rpms has a package named MySQL-5.6.14-1.el6.src.rpm, and the new yum repo version is called mysql-community-5.6.14-3.el6.src.rpm. There’s a minor release difference here but that difference should be easy to see in the spec file.

I installed the src rpm which basically unpacks it ready for building.  Given I have setup my rpm directory structure to install the src packages into a directory named after the package it is quite easy to then compare the resulting files contained in the source rpm.  The source rpm normally contain the spec file which defines how the package should be built, the original source tarball files and any patches that may need applying to the original source files to build the final binaries.

The results can be seen below with the first listing being from the ORIGINAL src rpm and the second from the NEW src rpm:

$ ls -l MySQL/
total 35244
-rw-r--r-- 1 sjmudd sjmudd 80764 Sep 10 09:43 mysql.5.6.14.spec
-rw-r--r-- 1 sjmudd sjmudd 36005278 Sep 10 09:43 mysql-5.6.14.tar.gz
$ ls -l mysql-community/
total 81532
-rw-r--r-- 1 sjmudd sjmudd 160 Oct 16 13:11 filter-provides.sh
-rw-r--r-- 1 sjmudd sjmudd 159 Oct 16 13:11 filter-requires.sh
-rw-r--r-- 1 sjmudd sjmudd 919 Oct 16 13:11 my_config.h
-rw-r--r-- 1 sjmudd sjmudd 23994221 Oct 16 13:11 mysql-5.1.70.tar.gz
-rw-r--r-- 1 sjmudd sjmudd 8403 Oct 16 13:11 mysql-5.6.14-mysql-install.patch
-rw-r--r-- 1 sjmudd sjmudd 59388513 Oct 16 13:11 mysql-5.6.14.tar.gz
-rw-r--r-- 1 sjmudd sjmudd 37 Oct 16 13:11 mysql.conf
-rw-r--r-- 1 sjmudd sjmudd 607 Oct 16 13:11 mysql_config.sh
-rw-r--r-- 1 sjmudd sjmudd 1058 Oct 16 13:11 mysqld.service
-rw-r--r-- 1 sjmudd sjmudd 640 Oct 16 13:11 mysql-embedded-check.c
-rw-r--r-- 1 sjmudd sjmudd 56865 Oct 16 13:11 mysql.spec
-rw-r--r-- 1 sjmudd sjmudd 1422 Oct 16 13:11 mysql-systemd-start
$

As you see there are several differences just in the number of files that are being provided.  It is nice to see that the NEW package actually contains an older version of mysql (5.1.70) which I’m assuming is used to build the older “shared-compat” libraries. That was missing from the ORIGINAL package and actually meant that it is not completely possible to build the ORIGINAL package binaries only from the src.rpm.

Some of the files in the NEW package are clearly for Fedora’s new systemd, which is the replacement for the traditional init scripts.

I also diff’d the spec files, and there you see several differences. One surprising one is there’s a difference in the licensing: The ORIGINAL rpms have a GPL license specified, whilst the NEW rpms have a GPLv2 license. Oracle never changes things without a reason and I know there have been many discussions on the topic of GPLv2 vs GPLv3 so I wonder why this change exists.

There are quite a lot of other differences in the 2 spec files, some changes seems insignificant but others may not be. I have not had the time to investigate further.

Many of you may remember that we have had bugs related to Oracle releasing multiple tarballs of the same name but having different checksums. Unlike some other package managers, RPM is not good at noticing or recording these differences so they can go unnoticed. I actually filed a feature request to RH to see if this may get fixed. That is their bug#995822.  If you think this may be useful please tell RedHat.

That feature request refers to bug#69512 where this issue came up and I noticed it again in bug#69987 in September. Oracle said they would ensure they would not distribute source tar balls with the same name and different contents to avoid the confusion that can result from this. It seems they have already forgotten as I see:

$ md5sum MySQL/mysql-5.6.14.tar.gz
52224ce51dbf6ffbcef82be30688cc04 MySQL/mysql-5.6.14.tar.gz
$ md5sum mysql-community/mysql-5.6.14.tar.gz
c9d329b5eabf7127d60a1ea2c8e48377 mysql-community/mysql-5.6.14.tar.gz

So yes, it seems they have done this again. I know that they build from a VCS but if they tag something as 5.6.14 it really helps to have a single tar ball that refers to that tagged version. I haven’t bothered to do a diff between the 2 tarballs but can imagine the new tarball is somewhat more up to date than the last one. However, that is really no excuse. So time to file yet another bug report which I have done with bug#70847.

In the end it seems that Oracle is trying to do the right thing and make life easier for us. That is welcome. However, they seem to trip up making that effort. Things need not be this complicated.

2013/11/07 update

I had a quick check of the differences in the 2 tarballs which are hugely different in size:

$ diff -uNr ORIGINAL NEW | diffstat
 CMakeLists.txt | 2 
 Docs/ChangeLog | 57 
 Docs/INFO_SRC | 10 
 Docs/mysql.info |31803 +++++++-----
 INSTALL-SOURCE | 40 
 man/comp_err.1 | 4 
 man/innochecksum.1 | 4 
 man/msql2mysql.1 | 4 
 man/my_print_defaults.1 | 4 
 man/myisam_ftdump.1 | 4 
 man/myisamchk.1 | 7 
 man/myisamlog.1 | 4 
 man/myisampack.1 | 4 
 man/mysql-stress-test.pl.1 | 4 
 man/mysql-test-run.pl.1 | 4 
 man/mysql.1 | 4 
 man/mysql.server.1 | 4 
 man/mysql_client_test.1 | 4 
 man/mysql_config.1 | 4 
 man/mysql_config_editor.1 | 4 
 man/mysql_convert_table_format.1 | 4 
 man/mysql_find_rows.1 | 4 
 man/mysql_fix_extensions.1 | 4 
 man/mysql_install_db.1 | 4 
 man/mysql_plugin.1 | 4 
 man/mysql_secure_installation.1 | 6 
 man/mysql_setpermission.1 | 4 
 man/mysql_tzinfo_to_sql.1 | 4 
 man/mysql_upgrade.1 | 32 
 man/mysql_waitpid.1 | 4 
 man/mysql_zap.1 | 4 
 man/mysqlaccess.1 | 4 
 man/mysqladmin.1 | 4 
 man/mysqlbinlog.1 | 4 
 man/mysqlbug.1 | 4 
 man/mysqlcheck.1 | 4 
 man/mysqld.8 | 4 
 man/mysqld_multi.1 | 4 
 man/mysqld_safe.1 | 6 
 man/mysqldump.1 | 14 
 man/mysqldumpslow.1 | 4 
 man/mysqlhotcopy.1 | 4 
 man/mysqlimport.1 | 4 
 man/mysqlshow.1 | 4 
 man/mysqlslap.1 | 4 
 man/mysqltest.1 | 4 
 man/ndb-common-options.1 | 4 
 man/ndb_blob_tool.1 | 4 
 man/ndb_config.1 | 6 
 man/ndb_cpcd.1 | 4 
 man/ndb_delete_all.1 | 4 
 man/ndb_desc.1 | 4 
 man/ndb_drop_index.1 | 6 
 man/ndb_drop_table.1 | 4 
 man/ndb_error_reporter.1 | 216 
 man/ndb_index_stat.1 | 4 
 man/ndb_mgm.1 | 4 
 man/ndb_mgmd.8 | 4 
 man/ndb_print_backup_file.1 | 4 
 man/ndb_print_schema_file.1 | 4 
 man/ndb_print_sys_file.1 | 4 
 man/ndb_restore.1 | 12 
 man/ndb_select_all.1 | 4
 man/ndb_select_count.1 | 4
 man/ndb_setup.py.1 | 4
 man/ndb_show_tables.1 | 4
 man/ndb_size.pl.1 | 4
 man/ndb_waiter.1 | 4
 man/ndbd.8 | 4
 man/ndbd_redo_log_reader.1 | 4
 man/ndbinfo_select_all.1 | 4
 man/ndbmtd.8 | 6
 man/perror.1 | 4
 man/replace.1 | 4
 man/resolve_stack_dump.1 | 4
 man/resolveip.1 | 4
 mysql-test/collections/default.release.done | 2
 packaging/rpm-fedora/CMakeLists.txt | 36
 packaging/rpm-fedora/my.cnf | 31
 packaging/rpm-fedora/my_config.h | 30
 packaging/rpm-fedora/mysql-5.6-libmysqlclient-symbols.patch | 1038
 packaging/rpm-fedora/mysql-5.6.14-mysql-install.patch | 239
 packaging/rpm-fedora/mysql-embedded-check.c | 26
 packaging/rpm-fedora/mysql-systemd-start | 52
 packaging/rpm-fedora/mysql.conf | 1
 packaging/rpm-fedora/mysql.spec.in | 1623
 packaging/rpm-fedora/mysql_config.sh | 28
 packaging/rpm-fedora/mysqld.service | 48
 packaging/rpm-oel/CMakeLists.txt | 37
 packaging/rpm-oel/filter-provides.sh | 6
 packaging/rpm-oel/filter-requires.sh | 6
 packaging/rpm-oel/my.cnf | 31
 packaging/rpm-oel/my_config.h | 30
 packaging/rpm-oel/mysql-5.1.70.tar.gz |binary
 packaging/rpm-oel/mysql-5.6.14-mysql-install.patch | 239
 packaging/rpm-oel/mysql-embedded-check.c | 26
 packaging/rpm-oel/mysql-systemd-start | 52
 packaging/rpm-oel/mysql.conf | 1
 packaging/rpm-oel/mysql.init | 209
 packaging/rpm-oel/mysql.spec.in | 1558
 packaging/rpm-oel/mysql_config.sh | 28
 packaging/rpm-oel/mysqld.service | 48
 scripts/fill_help_tables.sql | 18
 sql/sql_yacc.cc | 4428 -
 sql/sql_yacc.h | 4
 105 files changed, 27417 insertions(+), 14925 deletions(-)

Despite what looks like a large change set it seems most of the changes are in the documentation (mysql.info and man files) and these seem to be due to the fact that the source files have been regenerated between the tar balls, most of the changes are thus spacing or generated timestamp differences. Also mysql-5.1.70.tar.gz is included in the new mysql-5.6.14.tar ball so really there’s no need for it to be included separately in the mysql-community 5.6.14 src.rpm. That is likely to be an oversight and will no doubt get corrected.

My conclusion is that simply the mysql-community version of the mysql-5.6.14.tar.gz tar ball is simply more up to date than the one provided in the MySQL-5.6.14 src rpm. So perhaps much ado about nothing.  That said a few questions remain unanswered so it will be interesting to see how things develop.

Second Madrid MySQL Users Group taking place tomorrow Thursday, 12th September

September 11th, 2013

If you happen to have some free time tomorrow and are in Madrid please come along to the second Madrid MySQL Users Group.

Details can be found here. The meeting will be in Spanish. I look forward to seeing you.

Google Chrome and CentOS 6

August 27th, 2013

I have been meaning to sit down and write about this for a while now but with the summer upon us have not had time.

I use CentOS 6 on my home PC for many reasons, but mainly because I have been using RedHat versions of Linux for quite a long time and am comfortable with it.  CentOS, like its upstream parent RHEL, is good because it is well tested and stable and it is not absolutely necessary to perform a major version update every few months like its cousin Fedora. I do not have time for that and most security issues will be fixed with normal updates so this works well.

The downside of course is that RedHat can not afford to run bleeding edge versions of software which may be rather unstable and that can be a nuisance, if you need or want to use newer more recent versions.

An example of that, from my DBA perspective, is the version of MySQL which is shipped with RHEL 6.4: MySQL 5.1.66 is very old and I would not really want to run that on a production machine taking anything but toy load.  However, in this case, others provide drop-in replacements: Oracle with MySQL 5.6 (current GA version) and both MariaDB and Percona versions are other good alternatives.

In the browser world I moved over some time ago to use Chrome. It seems to work well on my Mac and at the time I moved over it avoided me having to restart Firefox frequently as it was eating all my memory.  There have been versions of Chrome available for CentOS 6 until rather recently, provided by Google and that was really good. It made life easy and I could switch machine with little trouble and use the same browser.

There are versions of Chrome for other Linux versions: Debian, SuSE and Fedora, but I want to keep using CentOS 6 for the reasons outlined above. Judging from other posts I have seen others feel the same.  My understanding is that CentOS 6 support was dropped as newer libraries on which it depended were not available on CentOS 6: Requires: libstdc++.so.6(GLIBCXX_3.4.15)(64bit) says rpm when you try to install the latest version.

Upgrading the OS versions of these base libraries is just troublesome as it may break several things and while people sometimes do this to try and work around similar issues I do not think that is the way forward.  Something will eventually break. Yet if these libraries are needed why can they not be built and located like google-chrome itself in a different location to the system default, and then link google-chrome using RPATH settings to the expected directory.  I have done some stuff like this before in a previous job and unless I am mis-remembering the procedures this should work fine.  Rebuilding the required libstdc++ in a different location to the default, such as /opt/google/lib64, and naming it something like google-libstdc++, and then adjusting the google-chrome dependencies to require this package and not the system libstdc++ is not really that hard. Doing so would fix the issue for google-chrome, and potentially make the library usable for others who might also want this newer version on CentOS.  Given Google has already setup a yum repo for CentOS 6, the automatic install of these new dependencies would just work out of the box.

Others suggest moving over to Chromium, but I see it is not the same as Chrome and has given me a few issues since switching from Chrome. I would like to use the same browser on my different desktops and not have to worry.

It would also help if RedHat would recognise that some people will need to use newer versions of libraries that come with the OS, and if they do not want to make the effort to support this themselves, it would help if they would perhaps make recommendations on how this can be done by others, perhaps along the lines outlined above.  In the end for them this avoids people being frustrated that their “stable OS” is not out-of-date, and also avoids issues with people hacking things around themselves, or resorting to building from source, when the whole point of a package based system is that you should not need to do that and the packaged builds tend to be more consistent and complete.

So here is to keeping my fingers crossed that perhaps we will see Chrome on CentOS 6 return in the future and make a few people happier.

What do you think?

My MySQL bugs and feature requests

June 25th, 2013

My MySQL bugs is a list I recently created and intend to keep up to date with issues I have seen.

First Madrid MySQL Users Group scheduled for the 4th July

June 19th, 2013

As mentioned here and after talking to a few people we have created a meetup page, http://www.meetup.com/Madrid-MySQL-users-group/ and proposed the first meeting on Thursday, 4th July. If you are interested and in the area come along and say hello. It should be interesting to see a few others who work in the field.  If you can let us know you are coming so we have an idea of how much interest there is.

On operating system upgrades and a packager’s nightmare

June 18th, 2013

A fairy tale

Once upon a time I did an operating system upgrade, a minor one that should do no harm, but just get me up to date by fixing any bugs in the version I had been using. It seemed like a good idea.

All seemed to be fine. I use a package provided by an external vendor and not the one produced by the operating system provider as this vendor provides a newer version of the package and I need that. The vendor has to make his package fit in the os environment his package is built for and normally does a pretty good job.

I use automation to build my systems and when I built a new one some issues appeared. Related to the new version of the OS the provider had enhanced one of his packages and the installation pulled in new dependencies. The install of the external package I use then broke as it conflicted with the new dependency provided by the OS.  While a workaround is possible: uninstall this, install that, … the nice world the package manager provides you to make these problems a non-issue was suddenly broken. Horrible.

To make matters worse the external vendor can not easily fix this. If he builds a package which obsoletes this new dependency then in earlier versions of the OS he changes stuff that is not needed. If he does not do this the breakage remains in this latest version. Of course in the real world many people use a mixture of OS versions and expect a single package to do the right thing at least for the same major version of an operating system. An external vendor’s packagers life is not a happy one. It’s hard to provide a package that integrates seemlessly without it breaking something.

Back to real life

The story above is a real story relating to RHEL 5.9 versus the previous RHEL 5.8, or CentOS in my case and MySQL 5.5 and 5.6 rpms provided by Oracle. RedHat have enhanced the version of postfix they provide (a core component of the servers we use) and added MySQL library dependencies which were not there in RHEL 5.8. This pulls in the ‘mysql’ package as a dependency, and so installing the MySQL-server, MySQL-client and MySQL-shared-compat libraries I had been using breaks as these do not currently have an rpm “Obsoletes: mysql” entry needed to make the package install go nice and smoothly.

Real life means I do not run the latest version of MySQL 5.5 but a version which works on my systems and has been tested with my software, so if I use that on CentOS 5.9 now it will break. Oracle may not mind having to update their latest MySQL 5.5 rpm (for CentOS 5) but are unlikely to be interested in updating all their previous MySQL 5.5 or 5.6 rpms for this version of CentOS just in case I might be using one of their older versions. I also am not interested in upgrading all these MySQL 5.5 servers just to get a newer version which may have issues, so probably I will end up doing a work around “remove this package”, “add that one” in a adhoc way to avoid this problem. It should not be necessary.

It has always been stated when building software: do not make functional changes in a minor version upgrade as you will break something somewhere and upset someone. I understand that providing MySQL support to postfix (and perhaps other packages) seems like a good idea but this breaks this “rule”. I also think that having to provide a package within an existing OS framework, and try to avoid breaking any native OS packages is hard, but still think as I have commented in previous posts that coming up with a way of allowing external vendors a way to do this which does not interfere with “native OS” packages would be good. Recommend that these packages get named a certain way, do not get located in “native OS” locations, and do not conflict with “native OS” libraries.
If people want to use these packages show them how to link against them and add dependencies in a way which may favour these vendors’ packages if that’s a deliberate intention. Provide these rules and life will be easier for all of us. One world where this is done is the *BSD ports systems, which are intentionally independent of the “native OS” and located separately and people can use them if needed, or not at their convenience. In my opinion something like that in the rpm world would be nice indeed and external rpm repos would not have to worry so much about adding new functionality and breaking the OS.

I am not fully sure how I am going to work around this issue, but it has been causing problems for a while now and will require some poking. I will try and get in touch with the parties concerned to alert them to this change which has broken something for me.

Am I the only one who has noticed this?

Also likely to be an issue I guess for MariaDB and Percona rpms for RHEL 5/ CentOS 5 as they’ll likely implement the same dependency behaviour.

References:

  • http://blog.wl0.org/2009/08/a-packagers-thoughts-on-rpm8/
  • https://access.redhat.com/site/documentation/en-US/Red_Hat_Enterprise_Linux/5/pdf/5.9_Technical_Notes/Red_Hat_Enterprise_Linux-5-5.9_Technical_Notes-en-US.pdf

Update: 2013-06-19

This is what I saw:

2013-06-18 00:48:55 +0200 /Stage[main]/Mysql::Client/Package[MySQL-client]/ensure (err): change from absent to 5.5.23 failed: Could not update: Execution of '/usr/bin/yum -d 0 -e 0 -y install MySQL-client-5.5.23' returned 1: Transaction Check Error: file /usr/bin/mysql from install of MySQL-client-5.5.23-1.rhel5.x86_64 conflicts with file from package mysql-5.0.95-3.el5.x86_64 file /usr/bin/mysql_waitpid from install of MySQL-client-5.5.23-1.rhel5.x86_64 conflicts with file from package mysql-5.0.95-3.el5.x86_64

On a server that runs MySQL 5.5.23 (CentOS 5.8) I see:

[myuser@myhost ~]$ rpm -q --obsoletes MySQL-shared-compat
mysql-libs

which actually looks wrong. CentOS 5 does not have a mysql-libs package, but a package named mysql.  CentOS 6 does have such a named package and on early releases of the MySQL 5.5 rpms some packaging issues similar to the one described here did have to be ironed out.

Checking a later package I see things have changed but there still seems to be an issue.

[myuser@myhost ~]$ rpm -qp --obsoletes MySQL-shared-compat-5.5.32-1.rhel5.x86_64.rpm 
MySQL-shared-compat-advanced < 5.5.32-1.rhel5
MySQL-shared-compat < 5.5.32-1.rhel5
mysql-libs < 5.5.32-1.rhel5

The first two entries need fixing (first first needs no version and the second should not be there) as they cause other issues, but I had not noticed until now the package name mistake in the last line (which mentions a package in CentOS 6, not CentOS 5 and should be just ‘mysql’).

Maybe, given I am not using the latest version of MySQL, I will need to repackage this version myself. A quick change to the rpm spec file and a rebuild should give me the package I need. Given the src rpm is available it is good I can do that.   …. but no. Further inspection shows that the MySQL-shared-compat package is not built from the MySQL-X.Y.Z-r.src.rpm but is built independently of it and I think old libraries files are “bundled” in manually to make this package.  I vaguely remember that maybe the source tar ball has the spec file for this shared-compat package. Let me check as maybe I have to do some deeper magic to roll my own package.

Oracle: please do not do this. Provide enough of the source in the normal tar ball to be able to build the old version libraries, and build _everything_ from that single tar ball.  This would avoid all the magic you are doing now to build this package and others who need to adjust these packages can do the same.

Madrid MySQL Users Group worth creating?

June 6th, 2013

I’m interested in meeting up and sharing experiences about using MySQL and managing MySQL servers with people here locally in Madrid. I had a quick look around and could see no MySQL user groups locally, so it might be nice to create such a group and exchange ideas over a beer, coffee or cola every once in a while. If you’re in Madrid and are interested please let me know. I’ve created a temporary  email address: madrid-mysql-users-2013 AT wl0.org (careful with the domain), which you can contact me on to confirm an interest.  Oh and I’d expect these meet ups to be in Spanish, but that’s not a requirement.

Estoy interesado en reunirme y compartir experiencias sobre el uso de MySQL y administración de servidores de MySQL con la gente aquí en Madrid. He echado un vistazo en Internet y no he visto ningún grupo de usuarios de MySQL a nivel local, por lo que sería bueno crear un grupo e intercambiar ideas mientras tomamos una cerveza, un café o una coca-cola. Si estás en Madrid y estás interesado por favor házmelo saber. He creado una dirección de correo electrónico provisional: madrid-mysql-usuarios-2013 AT wl0.org (cuidado con el dominio), que puedes usar para ponerte en contacto conmigo para confirmar tu interés en asistir.  Estando aquí en España imagino hablaríamos en español.

 Update: 2013-06-19

Progress, slowly but surely. See: http://www.meetup.com/Madrid-MySQL-users-group/

Extend ansible hostnames

May 1st, 2013

I have been playing with ansible recently and quite like some of what I see.

One issue I have when setting up the ansible hosts file, which contains the inventory of all hosts known to ansible, is that at work my hosts have a slightly more complex pattern which ansible can not directly match, see http://ansible.cc/docs/patterns.html. So I patched 1.0 to cope with these.

This provides for:

  • multiple patterns in a single hostname
  • the optional use of a step in [1:99:3] to generate hosts which don’t increase by 1.

The patch can be found here: http://merlin.wl0.org/201305/01/ansible-1.0-extend-patterns.patch and this allows me to include several types of servers at work in a simpler pattern more easily.

My python is still a bit rusty but this seems to do the right thing. It would be nice to see this patch incorporated into ansible.

Update 2013-06-06

I’ve just upgraded ansible on my Mac to 1.1 (via macports) and this patch hasn’t been applied yet. I think 1.2 is out soon so I’ll see if I can poke the developers to get this patch included. See: https://github.com/ansible/ansible/issues/3135

On warming up a MySQL 5.6 server

April 4th, 2013

In the past…

One of the typical problems you have when restarting mysqld is that the InnoDB buffer pool (buffer pool from now on) is empty and consequently access to the database requires reading directly from disk. Performance suffers dramatically as a consequence.

So the common solution is to artificially warm upthe server by doing queries which will fill the buffer pool. Typical solutions might be to do: SELECT COUNT(*) FROM some_table FORCE INDEX (PRIMARY) LIMIT ... on a number of tables to fill up the pool on startup. Fitting this into the standard mysql init start script is somewhat tricky as no hooks are provided for this sort of post-start action. (It would be nice to have this for other tasks too.)

Of course choosing the right parameters here can be tricky as workload changes over time, and as the ratio of the size of the database to the size of the buffer pool increases, you need to be more selective in what you load in.  This is a tricky problem to solve well and solve generically, I am sure you’ll agree.

Then MySQL 5.6 comes to the rescue (almost)

So I was pleased to hear that MySQL 5.6 had a new feature to take the problem away.  When shutting down mysqld, it can be configured to save the list of pages in the buffer pool, and on startup you can use this list to populate the empty buffer pool, hence putting you in the same warm state you were in before.  See: http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html.
Good news, but…
As the systems I manage have got busier and technology has advanced, so has the size of the memory in my servers. Some servers I manage now have 192 GB of ram and we have tried set ups with even more memory.  The innodb buffer pool size is likely to be around 90% of that size.  So loading in 160 GB of data from hard disk, even if read 100% sequentially, is going to take some time, so doing this as efficiently as possible is essential.   Yet, perhaps loading in the full pool is not really necessary. Experimentation, depending on the different set ups I use, indicates that mysqld performs sufficiently well even if if buffer pool is only 25% full.  After that natural usage will pull in missing data and gradually performance will improve to expected levels.
The first systems I started using MySQL 5.6 on needed fast disks so were configured with SSDs. Here the absolute need for a warm up script was less important as SSDs are so much faster anyway, so it is only now that I have paid more attention to this new feature and see some potential weaknesses in its implementation. Having said that it’s a good first step, and for many people may be just what they need if they enable it.
Below are some suggestions for improvement, but they will probably have to wait for 5.7 as new features should not really go into the current GA release.
  • Optionally time-limit this warm up process, stopping when the time limit has exceeded
  • Optionally limit the number of pages loaded in, probably to a percentage of the current pool size. The pages loaded in however, should be in MRU first as these are the hotest and most needed pages.  Ensure the load sorts the page list to ensure that I/O is as sequential and fast as possible.
  • Optionally define the number of concurrent threads that can be doing warmup I/O. My experience is that depending on the underlying disk subsystem running several parallel warmup threads may reduce the warmup time considerably. I think the current implementation in 5.6 only uses a single thread to load in pages.
  • Stop warming up once the buffer pool is full. Other normal database activity on the server may trigger data being loaded into the buffer pool so it may fill up before the warmup process finishes. So if buffer pool page eviction starts there’s no point continuing the warmup process.
  • Take into account the fact that copying an on disk mysql image may result in the server being started not being the one that the buffer pool list was saved for and the configuration in terms of memory size and buffer pools may change.  If this makes a difference ensure that the warm up process still does the right thing of warming up the server with the right pages.
I have posted a feature request as Bug#68863 and hope some of these ideas will be considered.
So I still have to do more testing but think that I may well still be doing manual warm ups for while yet.

MySQL 5.6 is out, so what is next?

April 2nd, 2013

MySQL 5.6 is out now and that is good news. I have already been using pre-GA versions on some production servers with good success and now that the few wrinkles I have seen have been ironed out, I am sure a lot of people will find the new features in 5.6 well worth waiting for.

However, that does lead to the question of: “what next?”

I still have several things that I would like to see in MySQL in no specific order of preference such as:

  • Session transaction state exposed as a variable to allow you to determine if you have started a transaction or not, and thus allowing you to use BEGIN WORK, ROLLBACK or COMMIT as needed.  This information is available via the C API I believe but not via SQL.  Something like @@in_transaction = 1.  Makes modular programming easier.
  • Table space management. The default behaviour now in 5.6 is to move to innodb_file_per_table = 1, but really on a large system I’d prefer to not have to manage each individual file, or partition, but blocks of files containing one or more tables: that is mange table spaces.  This potentially should help reduce the number of file handles that are needed open and allow the DBA to manage the “database” or “instance” in blocks which are of a manageable size to him/her.
  • Better configuration management. If I want to compare the running configuration of the variables in SHOW GLOBAL VARIABLES with the [mysqld] section in /etc/my.cnf then there are quite a lot of inconsistencies in variables/parameter naming, some parameters are not visible in SHOW GLOBAL VARIABLES and vice versa. After seeing that the default performance_schema settings in MySQL 5.6 did not match my needs, I have had to use quite a bit of magic to setup /etc/my.cnf with the correct parameters. If you add the way you have to configure performance_schema then checking for consistency and / or adjusting the running configuration to match /etc/my.cnf is more of a challenge than really necessary. So clean this up please and make life for us easier.
  • More dynamic configuration. Many configuration settings require restarting MySQL.  Now that mysql is no longer a toy and people depend on it 24×7, any downtime is costly and really unhelpful, especially if it’s not necessary.
    • Many replication configuration settings are still static.
    • Many innodb settings are also static (innodb_{read,write}_io_threads, back_log comes to mind), so having to restart mysqld is really not helpful, just to see if a change will improve performance or not.
  • Move the SHOW ENGINE INNODB STATUS output to a tabular form (probably in multiple tables in I_S) so that the information is easy to parse and thus people can better use the information.  Right now if you want to see which threads are busy doing I/O or want information on the actively running transactions, locks or whatever parsing the current output is a real pain. It’s true that much of this information may well be now in P_S but not everything is visible there, and the information that is missing should move to a place where it is easy to extract and use.
  • Better Memory management.  I have some servers with 96 GB of RAM and a certain size innodb buffer pool, and some of these when busy with heavy queries use up too much memory and start to swap, whilst others use the full buffer pool and have plenty of memory left over. The end result is the wrong amount of memory is being used. Workload changes the memory usage completely and the change can make a server swap or run out of memory.  The memory in the system is there to be used by the database yet MySQL does not manage this in any coherent way, or allow you to control it to prevent overuse.    Most other RDBMSes handle memory quite differently, pinning the configured size into RAM and only using that memory and nothing else. This appears to be more efficient and something similar in MySQL would make life a lot simpler.
  • Better binlog/relay log space management.
    • Several servers I manage write out a lot of binlogs in a day, so space is an issue.  Nevertheless, keeping as many binlogs as space allows is also important.  Also many of the applications are configured to large INSERTS, UPDATES including using ODKU (up to our configured max_packet_size of 16M) and whether using RBR or SBR the resulting output is likely to be compressible.  Given that compressing the whole bin log/ relay log files means you can’t jump to any location, it struck me that just compressing the individual events as they are written, or when they are being read might be possible and might be a way to save 2-3 x the binlog space usage. (See: Bug#46435)
    • Related to this point is the binlog retention period.  expire_logs_days, configured as a number of days sounds quite nice until you realise that you may fill the disk with 1 or 2 day’s worth of binlogs, so it would be nice to be able to configure a decimal value here which would make smaller periods possible if needed. That or have a new setting to configure the retention size rather than the period, a new variable such as: binlog_retention_size = 200G.
    • I have filled up many servers disks after cloning a slave as it catches up and pulls over old relay logs from the master, writing them locally before being able to apply them.  There is a configuration setting to limit this size but again it is static.

So these are just a few of the ideas I personally would like to see. I’ll see if I can update the post later providing some of the feature request bug reports as reference.

However, this is just part of the story. I have my ideas of what I would like to see in MySQL 5.7 and you have yours, but there does not appear to be a way currently with MySQL to voice interests to Oracle in a place where different people can be heard and it is possible to see what is most wanted.

  • http://bugs.mysql.com does allow you to add feature requests and indeed I have added many, but finding them or voting on them is not possible. Oracle’s official feature request mechanism makes the request go into a black hole where any progress is completely invisible to the requestor!
  • Oracle obviously has its own agenda and has the inconvenience of competitors who are in the same arena.  That does mean that Oracle, MPAB and Percona therefore have to do their own thing and try to guess what people want (though they’re obviously influenced by their main customers), but we do not see what appears until the new release comes out.  I think that can lead to rough edges as developers do not always realise the real world use cases and problems that the DBA faces on a day to day basis, and thus may focus on stuff which in the end may not be the most important to us.
  • Some Open Source projects have a bounty mechanism where people can offer to pay to get a feature implemented. The idea sounds quite good and if several people want the same thing, they may be able to push towards a common goal more quickly. I am, however, not sure how that might work in practice with something like MySQL, but the idea sounds interesting, if only to help better gauge where there is interest.

My way of using MySQL may vary significantly to yours. My problems may also be quite different, but I would imagine that there is a lot of common ground. So what would you like to see in MySQL 5.7?

In the meantime I guess we will have to wait and see what will come out in 5.7 and where things are going to go. There’s likely I guess to be a 18 month wait for that, so plenty of time to do lots of new and exciting things.  In the meantime MySQL 5.6 needs deploying on new servers once it has been fully tested and any issues resolved and that is quite a bit of work to keep me busy for a few days …

 

Update:

  • 2013-04-08 Adding some more I would likes:
    • A working I_S (mysql data dictionary) to avoid the workarounds needed on servers with hundreds of thousands of tables.
    • A way to compress/uncompress MyISAM tables from the mysql command line. This avoids having to work around locking, blocking and flushing issues if doing this directly from a shell. I’m using compressed MyISAM tables still as they’re much smaller than compressed InnoDB tables (and easier to move around too).