Possible Improvements to MySQL Administration

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.

Tags: , ,

9 Responses to “Possible Improvements to MySQL Administration”

  1. Hi,
    root access is not needed for mysqld_safe.
    Rather than mysqld_multi, you may want to try MySQL Sandbox (http://mysqlsandbox.net), which installs multiple servers, either single or in replication, each of them shielded from other instances. All of them in normal user space, without root access at all. (mysqld_safe is not optional at the moment, but it will be in the next version)

    Cheers

    Giuseppe

  2. sjmudd says:

    Hi. Maybe root access is not required but at least CentOS rpms run that way:


    [root@mad06 ~]# ps auwx | grep mysqld_safe
    root 3184 0.0 0.0 65924 1364 ? S Jul05 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid
    root 9580 0.0 0.0 61184 748 pts/5 S+ 00:21 0:00 grep mysqld_safe
    [root@mad06 ~]# rpm -qa | grep mysql
    mysql-devel-5.0.45-7.el5
    mysql-5.0.45-7.el5
    mysql-server-5.0.45-7.el5
    [root@mad06 ~]#

    These are RH provided rpms but MySQL Community and Enterprise rpms behave the same.

    We’ve spoken before about MySQL Sandbox and it does sound promising. I’m not entirely sure it’s designed to cover all the things I mention and it’s certainly not possible yet to incorporate it into the “standard” packages and startup scripts. However perhaps it’s a promising move to make MySQL management better than it is at the moment.

  3. Arjen Lentz says:

    Simon; apart from some practicalities, excellent ideas. And they can be put in practice in 5.1, if you get involved with MariaDB and the OurDelta builds.
    See the respective projects and mailing lists on Launchpad, and the Freenode IRC channels #maria and #ourdelta.
    (toss me an email if you have difficulty finding something)

  4. Mark Callaghan says:

    You describe interesting issues. There is an opportunity for MySQL (official and the community) to provide a much better wrapper to manage mysqld. This is more of an issue when you run many instances. I have experienced a few cases where restarts of critical servers were missed and there were no alerts on Uptime going backwards.

    The value added mysqld wrapper can do more than just start mysqld and alert on crashes. It can also perform administrative tasks.

  5. Matt Reid says:

    Some of these reasons and more are specifically why I despise running multiple instances of MySQL on one server. Aside from budget reasons I’ve never understood why anyone would want to run more than one instance per server. Resource allocation and troubleshooting are a major hassle on multiple instance servers, in addition to the annoyances you’ve mentioned in the article.

    If you need multiple instances on one physical server, install VMWare ESXi on the server and run one instance per virtual host. That’s much simpler and much less hassle for everyone involved.

    Good write up though. :)

  6. sjmudd says:

    Yes, I mainly agree with you. We previously had some production boxes running multiple instances and it’s clear that they interfered with each other in ways which were quite unhelpful. Since then things have changed and production boxes work as you describe: 1 instance 1 box.

    We break this rule in development where for the most part performance is really not an issue and it would be a waste of resources to do things differently. However it is still inconvenient to have to use different procedures on dev boxes compared to production boxes. That’s why I specifically mention the “multi-instance” issue.

  7. Sheeri says:

    The biggest reason I see for mysqld_safe to run as root is so that it can spawn mysqld and mysqld can inherit certain properties, such as ulimit values, from the root user.

    For example, file descriptors. I believe on Linux the default ulimit for non-root users for file descriptors is 1024 — Just yesterday I ran into a client who had 595 Open_tables, a table_cache of 2048, and Opened_tables was rapidly increasing. Why? Because they couldn’t do too many more than about 600 open tables without closing one, because they were running mysql with a user that had a ulimit -n of 1024.

    So that will definitely need to be taken into consideration.

  8. sjmudd says:

    Perhaps what you say has some truth. That doesn’t mean that a non-root user can not be configured to have the right values.

    If that means setting things up correctly for the mysql user then so be it. Look at Sybase, or better Oracle, which are quite precise about the rights required to run correctly. Running as root is a bit lazy as you have all privileges and can change anything you want. mysqld_safe is really meant only to restart a crashed process. Hence leave the script to do that alone. I’d prefer to restart the system myself as perhaps mysqld won’t recover properly or I want to investigate the cause of the crash. I’d like that option to be configurable from within my.cnf so that I can choose, and I don’t mind if default behaviour is to enable mysqld_safe rather than just start up the binary without this safety net.

  9. KB says:

    One feature of the admin environment I am in is our use of m4 to help us generate configs for several instances and make it very easy to template our setups.

    KB

Leave a Reply