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, 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.



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

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 (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 (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:

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 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: 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:

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:
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.

  • 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 …



  • 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).

Do we need a MySQL Cookbook?

January 24th, 2013

The blog title says it all: Do we need a MySQL Cookbook? I tend to think so.

This seems to be something that is missing with current MySQL documentation. There is lots of information available but finding the appropriate bit can be quite tedious and it often requires looking in multiple places.

A lot of other software has such books, but for some reason MySQL seems to be missing one.

A recent example comes from a “documentation feature request” I posted today: MySQL 5.6 provides a way to “move InnoDB tables” from one server to another. There are many reasons why you may want to do it, but the documentation is currently rather sparse. A simple “example recipe” for this would be good, as would an equivalent recipe for other engines where you can do this such as MyISAM. This is just an isolated example.

As far as I know there are no “best practices” on how to setup or manage a MySQL server, whether that come from Oracle, or the community. A common community view would probably be better as it makes “all vendors” work to a common goal, even if they compete in other areas. Look at the LSB as an example, adopted to some extent by all Linux distributions.  And again often there may be several ways to solve some problems so a Cookbook would be quite nice as it would suggest how to solve common tasks and let you use those as the basis for real implementations.

So what things could these Best Practices or Cook book contain? Things that come to mind are:

  • How to move tables from one instance to another (the example above)
  • How to clone an instance, whether to make a slave, or simply for copying to a development environment. It’s clear that here there are multiple solutions depending on factors such as database size, whether you are allowed to take the instance down or not, etc.
  • How to run multiple instances on the same server. This is something I used to do, but now try to avoid as often these instances interfere with each other. However, many people still do this so having common ways for setting up each instance and managing them (starting stopping, accessing each one) would be good. Here a common filesystem layout would be helpful too so that people do things a similar way but still have enough flexibility to adapt as needed.
  • How to run multiple versions of MySQL on the same server. Often could be useful in a development environment, or even in production, but at least on some versions of Linux the package managers make the assumption that only a single version of the “binaries” can be installed, thus preventing multiple versions running at once.  Sometimes this is needed and to have to work outside of the package managers is rather a pain. A common agreement on how to resolve that would be nice.
  • Backup and restore procedures, whether simple full database dumps, to using some of the other commercial tools.
    • That includes doing full and incremental backups, where this is possible, and also partial restores, or restores to a certain point in time.
  • Replication. Plenty to talk about here.
    • How to set it up from scratch
    • How to make more slaves (see cloning above)
    • Different replication features such as replication specific databases, the benefits or otherwise of row or statement based replication
    • Differences between the different versions, especially as MySQL 5.6 brings lots of new features such as minimal logging of changes to reduce binlog sizes
    • How to diagnose and recover from common replication issues
  • Monitoring
    • Again there are lots of options here, and many do it yourself ways, but also both free and commercial solutions. Often you first need to determine what you want to check, some checks may be technical ( mysql server running, the number of connections is not too close to max_connections, the number of running connections is not over a certain threshold, the SQL thread’s usage is not too high, replication is working, replication delay is not too high, etc). For many of these problems there are already checks that do the right thing. For many others you have to write your own.
    • All of the above is great but really people care about the application working, so on top of these check you need to add business checks etc.  Are the number of widgets we made in the last hour > 0?, …,
  • Grant management.  If we had a single server then you only set things up once. If you have more than one server then it gets more complicated. MySQL allows you to specify network ranges or hostnames from where a clients can connect, so all of a sudden managing this can be hard. I’m not sure if there’s a best way but it would certainly be interesting knowing how different people solve this problem, especially as the environment grows.  Replication can be used to help here and that works pretty well but you do have to be careful of gotchas with replicated grants if anything gets out of sync.  So this is certainly a topic of interest to most DBAs, whatever the chosen solution.
  • Setting up a new server. Simple things like choice of OS, MySQL version, MySQL Engine to store the data, expected profiles of the queries to the server, and then basic settings for a couple of these common setups.
  • Configuration. A huge topic but in many ways there are general things you should do, and probably that you should not do. A cookbook is probably not the place to go into huge detail about anything, which is where existing books may well be better but often helps get you going and provides indications on how to approach the problem and see that things look right.
  • Upgrade procedures for MySQL, a critical procedure for any DBA. We never stay on the same version, so we are always going to upgrade to a newer version. There are things to do in order to make that upgrade process work smoothly and this can get more complicated in a replicated environment, so talking about this and how it should be best done would be very good.  I have found the MySQL upgrade documentation to be sometimes rather incomplete or at least not written in a cookbook fashion of follow these steps to complete your upgrade, and hence you need to move all over the upgrade documentation trying to figure out if special steps are needed.  A minor version upgrade may not be an issue, but when doing a major version upgrade as many people will be doing shortly to MySQL 5.6 is something if you have important data in your database that you do not want to go wrong, so a cookbook style guide would be good for this.
  • Troubleshooting. I have had problems with the mysql servers I manage and some of these problems come up again and again, recognising them and having solutions to the common problems might be good, though recognising which problems are the most common is something that I guess can only be done by reading mail list archives, though Oracle/SkySQL/Percona support probably have a good idea here.

What other things am I missing for a cook book?

I’m sure this list could include many other things and maybe there’s a need to avoid too many details, referring to existing documentation where appropriate for details, but just provide the recipes and the explication of why, how etc would be good for a large number of people.  I for one have found from time to time that I could not find such a recipe to do a particular task, and I am sure there must be others like me with the same problem. Existing books are very good, but the focus seems to be slightly different and may go into a lot of detail when in some cases that detail may not be needed initially.

So would a cook book like this solve a need for you, and if so what might be missing from the suggestions I have made above?

CentOS 6.3 now installs on Mac Mini (mid-2011)

January 19th, 2013

After lots of pain and trying to get CentOS 6 to work on my Mac Mini mid-2011 and failing, I finally find that CentOS 6.3 does indeed install.  Previous versions did not work for me, but I’m delighted to see that RedHat have done great work and the work they did that made it work on Fedora 17 has obviously been ported to CentOS 6.  The issue in the past had been the Mac would not boot the  install DVDs.

So all that was needed was for me to burn the (new?) CentOS-6.3-x86_64-netinstall-EFI.iso image, press ALT on boot and the DVD booted fine, and the install went along the same way as any normal CentOS/RHEL installation.  In fact I’ve been able to keep my original Fedora setup as the Volume Group I’d previously had setup on my disk was recognised so all that I needed to do was to create a new “root” logical volume and install on to that.

So thanks RedHat. You now make it much easier for me to migrate my “main” but older system on to this Mac.

On MySQL Memory Usage and Configuration

January 15th, 2013

I saw a post on profiling memory usage today and this reminds me of several discussions I have had with different people.

Why would you want to profile the memory usage? Usually to see where memory is allocated and for what purposes, and usually you only care when memory usage is higher than expected. That is a DBA normally wants to use all available memory on a server for mysqld, whatever that size may be.

Configuration parameters may be inappropriate and need adjusting, so having a way to determine the range of memory usage based on those parameters would be most helpful.  However, the configuration parameters as a whole put no limit on memory used, so different workloads can quite easily lead to memory being insufficient on the OS, triggering swapping or perhaps the mysqld process to be killed by the kernel’s OOM. None of this is ideal.

So I would like to configure a cap on the maximum amount of memory that can be used and indeed that is what most RDBMSes do.  That is the allocation is made on startup, ensuring the memory is available, and any memory requests are taken out of this pool. This technique _should_ avoid you swapping (though I still see issues on NUMA architecture boxes). It also means that at some points in time the database server may not be able to honour memory requests and therefore must fail, wait until those needed resources are available, or it must free existing resources to make space for the requested new resource.

Doing this well is probably hard enough. Doing it in MySQL with different storage engines is probably harder still as each engine does things its own way.

Some mechanism like that would certainly help avoid strange behavioural issues when the query profiles change and this causes the server to behave in unexpected ways, such as swapping itself to death. The only solution right now seems to be to configure the server to ensure that these “edge cases” can not happen by using less memory, thus wasting valuable resources on the server.

Currently it is also pretty hard to see how memory is used. If you use InnoDB you know that the buffer pool takes up a sizeable portion of memory, but there is still a significant amount of other memory that’s used by each individual connection and this can vary significantly depending on the query profiles. It would be really nice to dynamically look inside MySQL and see how memory is used, and also see how adjusting different parameters may adjust this.

Of course none of this is in MySQL at the moment. I would certainly like to see first better information on current memory usage, I would guess some more tables in performance_schema, and at a later stage some way to cap memory usage to a desired level, and hope that when those limits get reached mysqld will figure out how to free up some resources in order to continue, wait or fail “gracefully”.

Until then we will have to continue playing the game of “configure mysqld”. See if it works and then ensure that queries to the system do not change enough to break the system and require us to go back and do it all again.

Note: there are several feature requests for this, so I do not think I am the only one in wishing for more visibility and configurability of the memory usage.

Update: 2013-01-17

Some feature requests that seem related to all this are shown below:


Setting up “cloud services” at home sounds easy but proves to be hard

December 9th, 2012

I have been using virtualisation at home for some time, since the days of vmware-server on Linux. It works well and if you have a sufficiently powerful box is a great way to experiment without destroying your own server or installing software which a few hours later you may want to remove again.

I also recently bought a Mac mini 2011, which I put in 16 GB of RAM and was hoping to set it up as a host to contain various virtual machines.  This was going to run Linux. I wanted to run the host as light as possible and do the real tasks inside my virtual machines.  I also wanted to use this server to migrate from an older PC I use for my day to day services (web, dns, email, ftp services etc).

Getting Linux installed on the Mac was a bit trickier than expected. I have been using RedHat since version 3.0.3 so prefer that to Debian or Ubuntu, if only because it is an environment that I am more familiar with. I tried in vain to get CentOS 6 to install on the Mac but it seems the required EFI boot loader does not work. Others have had problems too I think. Less surprising is that Ubuntu installed first time (credit to them for getting it to work on more exotic hardware), and when I tried Fedora 17 I saw that also worked, so that is what I am using. I would still prefer to switch to CentOS as it stays stable for longer, and playing the upgrade game with Fedora gets to be a pain.  Maybe some time soon…

There is a lot of noise around now about cloud computing and I had wanted to use something a bit more sophisticated than virt-manager on my PC.  This requires you to organise the exact setup of the boxes, network, disk images and other cloud platforms seem to make this management easier for you.

oVirt seemed promising and there is even a Fedora 17 repo and it is supposed to work out of the box according to various blogs and articles, but I had trouble getting it all up and running. If you run it on a single host it does require a lot of packages and software to be installed.  So after various attempts I gave up.

OpenStack is also pretty hot and has quite good documentation, though it seems complex perhaps because of the scale that it runs on in some deployments.  Again I read the manuals and documentation and had trouble getting the whole environment to work.  The configuration is quite complex, and it seems easy to make a mistake. If you do that then figuring out what is broken or why is pretty hard. Again this is a shame especially as this seems to be perhaps the most sophisticated of the 3 cloud environments I tried.

Family circumstances mean I have a lot less time than I would like to investigate but I did do quite a bit of homework before starting so this has been quite frustrating.

I then tried OpenNebula, version 3.8.1.  It seems that the software is still evolving quite quickly so has evolved quite a lot over time.  It also seems that this is the smallest package: the tar ball is tiny and at least on Fedora 17 it did not take me long to get the software installed (including sunstone-server).  I see this is tagged to be included in Fedora 17 but could not find actual packages to install.

There are quite a few howtos and tutorials but for OpenNebula, but some of these are for older versions, and if you follow them thing will not work as expected. (Differences in the templating options etc.) This is confusing for a new user.

I use Fedora 17 but do not see packages for OpenNebula. Working packages for Fedora, CentOS, Ubuntu etc would be most welcome as this would probably ensure that the different components were configured correctly with the OS components like virt-manager etc.

Most of the OpenNebula daemons have start and stop commands but no status command.  This would at least allow you check that the configuration was correct and the daemons correctly started, especially when doing manual installs.

I would like to see some sort of “check installation” command to allow for a more complete set of checks to be done to see if the installation is correct and complete, for each of the different components that makes up or is needed by OpenNebula.  In many places the documentation says “Assuming you have a correct kvm setup …”, “Assuming you have a correct isci setup …”, etc, yet provides no way for you to check if the setup of these base components actually works as required by OpenNebula.  Whether that documentation is included in the documentation or on a wiki somewhere “How to setup kvm on Fedora X for OpenNebula….” I do not really mind but I have had problems with PolicyKit not allowing oneadmin access to the virtual machine, with the virtual machines not seeing their disks (I think due to user/group permissions, as on Fedora 17 kvm seems to assign file ownership as qemu:qemu.), with the VNC connections in Sunstone not working, and I am pretty sure that most of these issues are “trivial” to fix if you have done this before and understand what OpenNebula expects and what the host operating system is doing “incorrectly”.

For initial testing including various standard template files which would useful, if only to allow helping to confirm simple things like:

    • booting from a cd image works
    • booting from a minimal hard disk works
    • booting from a hard disk with networking work (perhaps bridging or NATing out from the host’s network)
    • how to do a boot from a cdimage which will allow someone to install a new image on to a hard disk. This is a very typical usage pattern, and the exact Operating System or Linux distribution / version may vary.

I was confused by the network documentation and exactly how that relates to the underlying host’s network configuration.   This does not seem to be well documented but “assumed”.

To summerise these technologies are here and being used quite widely, but I would guess in larger environments where the people using them have time to investigate and set things up properly. I was hoping that to set these up at home would not be that hard, but have found it much harder than I had anticipated to do this just reading the documentation and trying on my own.  I think that all these platforms would be used more widely if they were more plug and play, that is they worked out of the box on most of the popular Linux distributions.

Having said that things have obviously progressed since the first virtualisation environments were created (on the x86 architecture) and probably in no time at all these technologies or similar will just work out of the box and be the norm.   In the meantime I am going to keep poking away and hope to finally get one of these environments working for me.

So if you read this and have some useful pointers I would be most interested in using them to get up and running.

Update: 2013-01-02

I see the OpenNebula work originally planned for Fedora 17 is now delayed (see:, but there are RPMs for CentOS 6 which can be found or built from here:!opennebula.git. So it looks to me as if it’s likely I will soon be able to get some packaged version of OpenNebula working on my CentOS 5 and Fedora 17 home PCs some time soon.  Still trying to make progress in the meantime.