Archive for July, 2009

Possible Improvements to MySQL Administration

Tuesday, July 21st, 2009

One thing you learn when you start to manage several servers is that life is easier if things are done the same way. I manage a few MySQL database instances on a few different hosts and here are a list of some things which I think could do with improvement. Note these views are from a UNIX point of view and for Windows MySQL DBAs many of the comments may not be appropriate. Also I’m not talking here about the administration of a single database, but the problems when you administer multiple instances.

Issues

First lets talk about the issues I notice in my day to day usage of MySQL. Then I’ll try and come up with some suggestions as to how these issues might be resolved.

  • Give up root earlier and expect to run as a non-root user. While it’s true that the mysqld process normally runs as the mysql user it’s also true that to be a MySQL DBA is almost impossible at the moment if you don’t have root access. To be clearer, at least that’s the case of most packaged UNIX versions of MySQL.
  • Using a package manager can save you a lot of time, but only if the packages cover your requirements. MySQL packages at least for RedHat work fine, but not if you intend to run multiple versions of MySQL, multiple mysqld instances or as stated above if you don’t want to manage MySQL as the root user.
  • Make it possible to manage multiple mysql instances the same way as single mysql instances. Currently that’s not possible and as such makes you choose one way or the other and perhaps differently on different servers. Not good.

Possible Solutions

When making changes it’s important to decide how to make that change. You can make things backwards compatible or you can from a certain software version change behaviour.  Currently MySQL 5.1 is GA which means that any changes if they were to take place would be not be until at least 6.0 as 5.4 seems pretty much on the way to be the next “GA” version shortly (end of the year)?

So here are my thoughts on making things “better”.

  • Give up root earlier. MySQL startup scripts should be able to run as a non-root user and work roughly the same. MySQL uses port 3306 so does not need to run as root to bind to this port, so there’s no real need to do anything as root. If that means running 2 startup scripts rather than one then so be it. Both Oracle and Sybase do this, and DB2 if I remember correctly.
  • Make mysqld_safe optional. While the idea of mysqld_safe is great, it restarts your server if it crashes, and it may have been necessary in the past, I’m not so convinced that it should be part of the default startup script. Currently I believe this needs to run as root. Again make it able to run as a non-root user, but also make the startup script use it optionally. If you don’t monitor a msyqld instance extensively you may never even know mysqld has crashed, as often the startup time may be pretty quick.
  • Make mysqld_safe optionally notify the sysadmin or dba if there’s been a crash. This means you don’t need to monitor in more sophisticated ways if there’s a problem. An email is fine, or if not provide hooks for talking to an external program.
  • Make it possible to install multiple MySQL packages at the same time. You can do this with the kernel and people don’t complain. Make it the same with MySQL. The focus of this is mainly for the server side where it may be that you want to upgrade. You can’t install a MySQL 5.0 and MySQL 5.1 rpm at the same time as the package’s file locations collide. That does mean doing things differently to how things are done now but it also means it’s much easier to run versions of MySQL on the same box. Once you’ve finished with a version and are no longer using it then the package can be erased as normal. There are packaging solutions to make sure that at least from the client side a specific version is the main version used, even if you may want to use a different version for specific purposes.
  • Make it possible to run multiple mysqld instances at the same time. In theory this is possible now, with mysqld_multi.
    • However this is not the default behaviour and also requires you to change the init script. It would seem better to make the init script “multi-instance” aware and if configured appropriately this would be the default behaviour.
    • Also if supporting multiple instances ensure that each instances configuration files (my.cnf) are not stored in a common /etc/my.cnf file but each in a separate location. This makes it much easier move the instance together with it’s configuration file about.
    • Support for each instance the ability to optionally start the instance. Something like /etc/oratab comes to mind, where instance name, instance my.cnf, optional startup, optional use of mysqld_safe, and user to run under are configured.
    • Make it possible from the init script to start/stop all instances or an instance by name. Names are so much easier to understand than numbers!
  • Maintain backwareds compatibility. This keeps everyone happy and makes the transition smoother.  MySQL has a lot of history so making big changes in an incompatible way mean that people get confused and upset. If the changes suggested above can be done in a way which allows current behaviour to be continued, but the new behaviour to be “enabled” with very little effort, then people who like me have to manage several boxes can take advantage of this new functionality.

It can be argued that if you write your own scripts that none of the above is necessary. It can be argued that if you build MySQL from source then you can install things how ever you want. If can be argued that it’s not MySQL’s problem, but the sysadmin administrator. It can be argued that every site is different. However to be honest while some of the smaller details may indeed be different and the scale may change in the end, one MySQL server is pretty much the same as the others. If MySQL, or Sun, or Oracle now make it easier to manage the boxes the way we need, and to do this consistently, and cover many of the use cases we won’t all be re-inventing the wheel. That’s what it seems we all do, or if not we put up with inconsistent setups on different servers that means we spend less time on tending the database and more on doing mundane things such as get things running the way we want.

I started using MySQL on a single server with just one instance running. That was some time ago. Others are likely to follow my steps and then run into some of the issues I am mentioning, or have come across this some time ago. The ideas I mention above are I think solvable but it would be much better if we could come up with a common solution rather than me implement my own or you implement yours.

So am I the only one who thinks these changes would be helpful, and am I missing some other points? Would love to know what you think.

Using an empty database (learn from your mistakes)

Saturday, July 18th, 2009

I’ve been working on various different MySQL related issues and maintenance procedures some of which have not gone according to plan.  Here is a recipe that may help you avoid wasting a lot of time, especially if your database is large.

In order to do some of these tests make tests against a server configured identically to the one you plan to work on but instead which has no data. That is the mysql database needs to be complete but the other databases need to be dumped with the –no-data or -d options.  Don’t forget to also include any triggers or stored routines.

Now run the “procedure” on this “emtpy instance”. As it has no data most things run very quickly. So if you have issues you can repeat the procedure in no time. Restoring the instance too is easy as it’s tiny. This makes the whole procedure scriptable and you can be confident in the results.

Once you are satisfied that it works you know what will happen and you can run the SAME procedure on the real instance with a lot more confidence.

This procedure, while it does require to you build an extra instance for testing, is actually a much safer way to do many tests. It doesn’t help for certain scenarios where the content of the tables is important but it does save you a lot of wasted time.

You still may need to estimate how LONG certain tasks will take and that must be done separately, but is usually easier to do once you know what you need to measure.

It would certainly have saved me a lot of time when doing various 5.0 to 5.1 upgrades, some of which have given me some problems and also a simple thing like a failed ALTER TABLE which was working on a 50GB table and failed at the end after running for 18 hours due to a foreign key constraint issue. This problem needs to be addressed by MySQL, but to be fair to them I shouldn’t complain about the 18 hours I wasted because I did not follow the procedure I suggest above.