Sun, 11 Sep 2005

Monitoring MySQL Replication Slaves

Drawing inspiration from the replication chapter of Jeremy and Derek's MySQL book, I recently set up monitoring to measure how far a MySQL slave is behind the master.

I created a simple table to store a timestamp.

CREATE TABLE `heartbeat` (
  `timestamp` datetime NOT NULL default '0000-00-00 00:00:00'
);
INSERT INTO heartbeat SET timestamp = NOW();
Master

The master updates the timestamp every second using a simple PHP script called write_mysql_heartbeat.php.

<?php

$dbHost = 'dbhost';
$dbUser = 'mysql_admin';
$dbPass = 'PASSWORD';
$dbName = 'mysql_admin';

$conn = mysql_connect($dbHost, $dbUser, $dbPass) or die('Failed to connect to database');
mysql_select_db($dbName) or die('Failed to select database: ' . $dbName);

define_syslog_variables();
openlog('mysql_heartbeat', LOG_PERROR | LOG_PID, LOG_DAEMON);

$sql = 'UPDATE heartbeat SET timestamp = now()';
while (true) {
        while (mysql_ping($conn) === false) {
                syslog(LOG_WARNING, 'Lost connection to database.  Will retry in 30 seconds.');
                sleep(30);
        }
        if (! mysql_query($sql, $conn)) {
                syslog(LOG_ERR, 'Failed to insert record: ' . mysql_error());
                die();
        }
        sleep(1);
}

I have restartd make sure that write_mysql_heartbeat.php keeps running with the following entry in /etc/restartd.conf:
write_mysql_heartbeat write_mysql_heartbeat "php4 -q /usr/local/bin/write_mysql_heartbeat.php"

Slave

On the slave side, I wrote a simple munin plugin to check how far the slave is behind the master. Munin is also configured to send alerts through nagios if the slave gets too far behind.

Here's /etc/munin/plugins/mysql_slave_delay:

#!/usr/bin/php4
<?php

$dbHost = 'slave-1';
$dbUser = 'mysql_admin';
$dbPass = 'PASSWORD';
$dbName = 'mysql_admin';

if ($argv[1] == 'config') {
        # The host name this plugin is for. (Can be overridden to have
        # one machine answer for several)

        # The title of the graph
        echo "graph_title MySQL Slave Delay\n";
        # Arguments to "rrdtool graph". In this case, tell it that the
        # lower limit of the graph is '0', and that 1k=1000 (not 1024)
        echo "graph_args --base 1000 -l 0\n";
        # The Y-axis label
        echo "graph_vlabel seconds\n";
        # We want Cur/Min/Avg/Max unscaled (i.e. 0.42 seconds instead of
        # 420 milliseconds)
        echo "graph_scale no\n";
        # Graph category. Defaults to 'other'
        echo "graph_category mysql\n";
        # The fields. "label" is used in the legend. "label" is the only
        # required subfield.
        echo "seconds.label seconds\n";
        # These two are optional. They are only used if you have
        # configured your munin to tell a Nagios-server about any
        # problems
        echo "seconds.warning 30\n";
        echo "seconds.critical 120\n";
        # This one is purely to add an explanation to the web page. The first
        # one is for the graph itself, while the second one is for the field
        # "seconds".
        echo "graph_info This graph shows how many seconds this MySQL slave is behind the master\n";
        echo "seconds.info Number of seconds behind master\n";

        # Last, if run with the "config"-parameter, quit here (don't
        # display any data)
        exit;
}

$conn = mysql_connect($dbHost, $dbUser, $dbPass) or die('Failed to connect to database');
mysql_select_db($dbName) or die('Failed to select database: ' . $dbName);

define_syslog_variables();
openlog('mysql_heartbeat', LOG_PERROR | LOG_PID, LOG_DAEMON);

$sql = 'SELECT UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(timestamp) from heartbeat';
if (false === ($result = mysql_query($sql, $conn))) {
        syslog(LOG_ERR, 'Failed to get record: ' . mysql_error());
        die();
}

if (mysql_num_rows($result) != 1) {
        syslog(LOG_ERR, 'Failed to find record for heartbeat: ' . $hostname);
        die();
}
list($delay) = mysql_fetch_array($result);

echo "seconds.value $delay\n";
Output



The slave is falling behind while backups are running. I still need to do something about that.

tech | Comments | Permanent Link

Pentax S5i

I bought my first digital camera last week. It's a Pentax S5i. I got it at Costco for $300, plus $90 for the 1GB SD card.

I wanted a camera that was small enough to carry around easily, but that takes great-looking pictures. The Pentax fits the bill. It has a ton of features that I haven't figured out how to use properly yet. I'll need to get a book on photography to learn when to adjust the white balance or exposure. Out of the box, it takes very pretty pictures though.

The macro mode allows me to take pictures very close-up.

I'm also enjoying the black-and-white filter and sunset mode.

Works with Linux

The camera plays nicely with Linux. It came with a USB cable, and upon plugging it in, it is detected as a scsi device using the usb-storage module, just like the iPod Shuffle.

misc | Comments | Permanent Link

Drunk Drivers Suck

We were rear-ended in a hit-and-run last night. The driver was apparently drunk. He hit us while we were driving about 35 mph on Washington Blvd in Culver City. We pulled over to the side of the road, and he floored it and took off. I then sped up to get his license plate number. We didn't have to pursue him for very long: he slammed into a parked car a few hundred meters from where he hit us, and we pulled over about 30 feet behind him.

I called the police, and while on hold, the driver got out of his car, and started walking towards us. Not wanting to confront a potentially violent drunk, I pulled in front of him. He got back in in his truck and sped off again.

The police arrived about 10 minutes later to take a report. Since there wasn't any damage to our car other than a little scratched paint and no injuries, the police officer asked if we really wanted to file a report. I wanted to make sure that we filed an official report in case we were needed as witnesses in the event of a subsequent vehicular manslaughter or something. If this guy didn't kill anybody last night, he got very lucky. Leaving the scene of an accident is also a crime, but unfortunately, he probably won't be prosecuted.

Overall, we were lucky not to have been hurt. I just hope the police can get this idiot off the road.

culture | Comments | Permanent Link

The state is that great fiction by which everyone tries to live at the expense of everyone else. - Frederic Bastiat