MariaDB, MySQL, and Percona with nosh

nosh pages:

There has been a sea-change in the worlds of MariaDB, MySQL, and Percona. They have, belatedly but still in a most welcome fashion, come around to the way of thinking that has been the norm in the daemontools world for two decades. Version 1.28 of the nosh toolset takes advantage of this thinking to bring service bundles for MariaDB, MySQL, and Percona closer to the daemontools norm.

The old mechanism

The (very) old way of doing things was for MariaDB, MySQL, and Percona to be started by an rc script, for either van Smoorenburg rc (on Linux operating systems of various flavours) or Mewburn rc (on FreeBSD, TrueOS, DragonFly BSD, NetBSD, and so forth). MariaDB directly included a script for Debian-flavour van Smoorenbug rc, named mysql.init. MariaDB, MySQL, and Percona all directly included an attempt at a more generic van Smoorenburg rc script, named mysql.server (as documented by Oracle). The FreeBSD (and NetBSD) "ports" tree contained the Mewburn rc script for MySQL, named mysql-server.

In both the Mewburn and van Smoorenburg rc systems the scripts would invoke the database server indirectly, via another script named mysqld_safe. They would read their service configuration information from an operating-system-specific expected place: /etc/rc.conf in the case of the BSDs; a messy choice between /etc/default, /etc/sysconfig, and /etc/conf.d in the case of the various van Smoorenburg rc flavours.

This was a rickety, byzantine, and unsafe mess. For example: It demanded that a "PID file" be used (a notoriously unsafe mechanism that we've known to avoid since the 1990s), and gave three different places where it might be set: a mysql_pidfile setting in /etc/rc.conf, a pid-file setting in /usr/local/etc/my.cnf (or /var/db/mysql/my.cnf, or /etc/mysql/my.cnf, or perhaps /etc/my.cnf, depending), or a mysqld_pid_file_path setting in one of the multiplicity of van Smoorenburg flavours' system configuration files. This necessitated running the my_print_defaults command, to read these options. This in turn necessitated finding out where my_print_defaults and the my.cnf file were. And the mysqld_safe script that was invoked went and duplicated these same steps.

Things only got worse from there, more on which later.

In versions 1.27 and earlier of the nosh toolset. there was a pre-supplied service bundle to run MariaDB, MySQL, or Percona, in /var/sv/mysql. The external configuration import subsystem would (say) import mysql_enable and the other mysql_XYZ into the nosh service management's enable setting and the XYZ variables set in that service bundle.

The administrator's experience would thus be fairly close on a nosh-managed system to a van Smoorenburg or Mewburn rc system. After importing the configuration into the nosh service bundles, an administrator could still run service mysql start to start the database service, initctl mysql status to see its status, and so forth, using the various shims that map these onto native nosh mechanisms. There were still a few rough edges, though.

Avoiding the Horror that is mysqld_safe

Looking more closely at mysqld_safe one finds that invoking it under systemd, or indeed any decent service manager, would qualify it as a candidate for the systemd House of Horror. For mysqld_safe is that classic of the systemd House of Horror: a Poor Man's dæmon supervisor written badly in shell script (as they always are). Consider how it operates:

  1. It tries to work out where the my_print_defaults program is.

  2. Using the my_print_defaults program, it parses my.cnf (from wherever it is located) to find the settings in the [mysqld_safe] section; translating them into command-line arguments for itself.

  3. It parses those command-line arguments, alongside any that it has been explicitly given. These command line arguments specify things like the location of a "PID file", the user account to run the server process as, process priority settings, open file descriptor limits, core file size limits, NUMA control, and auto-restart settings.

  4. It works out a sequence of chaining commands that applies all of the limits, accounts, priority settings, and so forth.

  5. It enters a loop, spawning an associated log dæmon and the MariaDB/MySQL/Percona server dæmon (connected via a pipe), waiting for the pipeline to terminate, and executing the restart logic. The MariaDB/MySQL/Percona server is run via the chain of commands calculated earlier on, as a child process of the mysqld_safe process.

People from the daemontools world will readily observe that this attempts to duplicate the logic of proper service management in shell script, but (as is ever the case with such Poor Man's service managers) does so badly.

It is a completely unnecessary layer.

The prompting for change

Users of daemontools, runit, and other such service managers have been pointing out for a decade and a half that this is completely wrongheaded, and is doing badly in an idiosyncratic service-specific script the job that general-purpose service management itself does well. They have long pushed for ways to run MySQL and MariaDB properly, with all of this stuff done by the actual service management mechanism. Here are just a few examples:

In 2009 Mario Limonciello of Ubuntu took the same direction, writing a mysql.conf job file for upstart that set the process limits and restart controls using upstart's own general-purpose native mechanisms and invoking mysqld directly. Six years later, Robie Basak observed that "under no circumstances should the code in the init.d script actually run on an upstart system". The thinking from the daemontools world was, after almost fifteen years, finally catching on.

The comparatively highly belated prompt for the change that came from systemd can be found in MySQL bug #65809 filed in 2012 by Honza Horak of RedHat, who pointed out that mysqld_safe should be done away with in 2014.

In the MariaDB world this continued with MariaDB development task #5713, and has culminated with improvements to how one starts the MariaDB dæmon under a service manager that are (unfortunately) to be found under the heading "systemd" in the MariaDB doco (rather than in a more general place applicable to all modern service managers from the 1990s onwards). These improvements started to manifest in mid-to-late 2015 with work such as this change by Daniel Black of IBM, and open discussions such as MariaDB PR #26 and MariaDB PR #83.

Oracle's response to the original bug was churlish and uninformative, in comparison. M. Horak's three-year-old bug report was simply closed after pretty much total silence on the matter with a curt "contribution rejected" and a pointer to where Oracle supposedly has done the work without saying what work has been done. Unlike MariaDB, there's no open discussion detailing the design decisions nor detailed documentation of the revised way of doing things.

What has changed

mysqld_safe is now not the way to start the servers, under any service manager, even under systemd and upstart. All of the things that it does are now done by the service management mechanisms, doing things the way that the daemontools world has pushed for all along.

in systemd and upstart

Additionally, in the systemd world there is now no longer a single fixed service unit defining the service — at least, for MariaDB. Instead, the package installation procedure runs a mariadb-service-convert command that reads my.cnf (once again using my_print_defaults) and writes out an override file for mariadb.service with all of the process limits, accounts, priorities, and suchlike turned into the native systemd mechanisms.

in nosh service management

Likewise, in nosh version 1.28, the /var/sv/mysql service bundle is now simply an alias. The external configuration import subsystem, whenever it is told to update its imported configuration, finds the MariaDB/MySQL/Percona configuration file(s) and instantiates one or more nosh service bundles, containing all of the process limit, user account, priority, NUMA control, and suchlike dæmon configuration options converted to nosh native mechanisms.

One bonus feature of doing things this way lies in "one or more". The nosh external configuration import subsystem recognizes the multiple-instance conventions employed by MariaDB, MySQL, and Percona (multiple [mysqldNN] sections in a single my.cnf file) and generates multiple nosh service bundles, with their own individual settings and logging.

PostgreSQL postscript

The PostgreSQL world lags the MariaDB, MySQL, and Percona worlds when it comes to doing things the way that they've been done with daemontools family service management for two decades. However, in 2015 Peter Eisentraut proposed doing away with PostgreSQL's idiosyncratic log management and just sending logs to standard error.

© Copyright 2016,2017 Jonathan de Boyne Pollard. "Moral" rights asserted.
Permission is hereby granted to copy and to distribute this WWW page in its original, unmodified form as long as its last modification datestamp information is preserved.