A Small Fix For mysql-agent

If you're already using an SNMP monitoring tool like OpenNMS, mysql-agent is a great way to add a number of graphics using Net-SNMP. However mysql-agent has a small bug that drove me crazy. I will try to highlight the process on how I discovered it (and hence fix it) since it involved learning about SNMP, how to diagnose it and eventually, once all the pieces came together, how simple it is to write your own agents.

Although versions are not that important, just for the sake of completeness we were using CentOS 5.5, MySQL 5.5.8 Community RPMs, Net SNMP version 5.3.22 and OpenNMS Web Console 1.8.7.

The Problem

I followed the directions on the mysql-agent blog only to find that I was facing the only open issue listed on mysql-agent's Github repository (spoiler alert, the solution is at the bottom). The set up has several components, which makes it difficult to diagnose:

• mysql-agent
• snmpd +  agentx
• OpenNMS server

Running snmpwalk on the MySQL host, as suggested in the mysql-agent article, worked fine (as far as we could tell). However, OpenNMS wasn't getting the data and the graphs weren't showing up.

It turns out that, once you completed the OpenNMS configuration as described in the article, it's a good idea to run snmpwalk remotely, from the server running OpenNMS, as well. You need to specify your MySQL hostname instead of localhost:

snmpwalk -m MYSQL-SERVER-MIB -v 2c -c public mysql-host enterprises.20267

In our case, it failed. Unfortunately the logs didn't offer much information and whatever was failing, it was inside agentx.

The Alternative

Since the NetSNMP Perl class hides a lot of the details of the Net SNMP API, we decided to use an alternative method to write the agent using pass_persist. The beauty of this method is that you only need to write a filter script: SNMP requests come through standard input (stdin) and the output needs to be printed to standard output (stdout). In consequence, the agent can be tested straight from the command line before implementing it. A nice article about pass_persist can be found here. The pass_persist protocol is fully documented in the snmpd.conf man page.

To follow this route we had to tweak the script a little. The tweaks included:

• No daemonize: Since the script used stdin/stdout, it needs to run interactively.
• All values need to be returned as strings. It was the only work around we found to deal with 64bits values that otherwise weren't interpreted correctly.
• stderr needed to be redirected to a file to avoid breaking the script's returned values ( add 2>/tmp/agent.log to the end of the command line) while you run it interactively.
• Use SNMP::Persist Perl module to handle the SNMP protocol.

Once the changes were implemented (I promise to publish the alternative mysql-agent script after some clean up) these are the steps I followed to test it (for now I'll leave the -v option out, along with the stderr redirection).

1. Invoke the agent as you would've done originally, keeping in mind that now it'll run interactively. On your MySQL server:
   

   mysql-agent-pp -c /path/to/.my.cnf -h localhost -i -r 30

2. Test if the agent is working properly (blue -> you type, red -> script output):
   

   PING
   PONG

3. Does it actually provide the proper values?
   

   get
   .1.3.6.1.4.1.20267.200.1.1.0
   .1.3.6.1.4.1.20267.200.1.1.0
   Counter32
   21
   getnext
   .1.3.6.1.4.1.20267.200.1.1.0
   .1.3.6.1.4.1.20267.200.1.2.0
   Counter32
   16

Note that case is important PING needs to be capitalized, get and getnext need to be in small caps. Once you know it works you'll need to add the pass_persist line to the snmpd.conf file and restart snmpd:

# Line to use the pass_persist method
pass_persist .1.3.6.1.4.1.20267.200.1 /usr/bin/perl /path/to/mysql-agent -c /path/to/.my.cnf -h localhost -i -r 30

Now execute snmpwalk remotely and if everything looks OK, you're good to go.

On our first runs, snmpwalk failed after the 31st value. Re-tried the specific values and a few other ones after those with get and getnext and it became obvious that for some, the responses weren't the expected ones.

The Bug and The Fix

So now, having identified the failing values, it was time to dig into the source code.

First the data gathering portion, which fortunately is well documented inside the source code. I found ibuf_inserts and ibuf_merged as the 31st and 32nd values (note that with get you can check other values further down the list, which I did to confirm that the issue was specific to some variables and not a generic problem). A little grepping revealed that these values were populated from the SHOW INNODB STATUS output, which in 5.5 didn't include the the line expected in the program logic, hence, the corresponding values stayed undefined. A patch to line 794 on the original script fixed this particular issue by setting the value to 0 for undefined values.

794c794
<             $global_status{$key}{'value'} = $status->{$key};
---
>             $global_status{$key}{'value'} = (defined($status->{$key}) and $status->{$key} ne '' ? $status->{$key} : 0);

This fix can be used for the original script and the new pass_persist one. I already reported it upstread in GitHub.

The original script still failed. OpenNMS still requires getbulk requests (explained in the Net-SNMP documentation) that agentx fails to convert into getnext. This can be reproduced using snmpbulkwalk instead of snmpwalk (Note: It took some tcpdump + wireshark tricks to catch the getbulk requests). The current beta of the pass_persist version of mysql-agent has been in place for a while without issues.

Conclusion

I'm not highlighting all the process since it was long and complicated, but I learned a few concepts in during this time the I'd like to point out

Look Around Before Looking for New Toys

If you're using OSS, you may already have in house most of what you need. This project started when we decided to use OpenNMS (already in place to monitor our infrastructure) and wanted to add to it the MySQL data we wanted to monitor closely. A simple Google search pointed us to mysql-agent right away.

Embrace OSS

All the tools that we used in this case are Open Source, which made it extremely easy to diagnose the source code when pertinent, try alternatives, benefit from the collective knowledge, make corrections and contribute them back to the community. A full evaluation of commercial software, plus the interaction with tech support to get to the point where we needed a patch would've been as involved as this one and the outcome wouldn't have been guaranteed either. I'm not against commercial software, but you need evaluate if it will add any real value as opposed to the open source alternatives.

SNMP is Your Friend

Learning about the SNMP protocol, in particular the pass_persist method was very useful. It removed the mystery out of it and writing agents in any language (even bash) is far from difficult. I'm looking forward to go deeper into MySQL monitoring using this technology.

I'm hoping this long post encourages you to explore the use of SNMP monitoring for MySQL on your own.

Credit: I need to give credit to Marc Martinez who did most of the thinking and kept pointing me in the right direction every time I got lost.

NOTE: I'm not entirely satisfied with the current pass_persist version of mysql-agent I have in place, although it gets the job done. Once I have the reviewed version, I plan ... actually promise to publish it either as a branch of the existing one or separately.

About InnoDB Index Size Limitations

This is mostly a reflection on a limitation in InnoDB that, in my opinion, has persisted for too long. I founded while reviewing the Amarok media player. The player uses MySQL in the backend, embedded or regular server, so it makes for a great source of real life data.

The Issue

By default, Amarok uses MyISAM tables. This means that if it crashes or stops unexpectedly (a logout while playing music may cause this), the latest updates to the DB are all lost. So I've been looking into using InnoDB instead to avoid loosing my playlists or player statistics.

The Problem

The limitation that bothers me is this one: "Index key prefixes can be up to 767 bytes" which has been in place for several years.
Take this Amarok table for example:

CREATE TABLE urls (
    id int(11) NOT NULL AUTO_INCREMENT,
    deviceid int(11) DEFAULT NULL,
    rpath varchar(324) COLLATE utf8_bin NOT NULL,
    directory int(11) DEFAULT NULL,
    uniqueid varchar(128) COLLATE utf8_bin DEFAULT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uniqueid (uniqueid),
    UNIQUE KEY urls_id_rpath (deviceid, rpath),
    KEY urls_uniqueid (uniqueid)
) ENGINE=MyISAM AUTO_INCREMENT=314
DEFAULT CHARSET=utf8 COLLATE=utf8_bin

The result of an ALTER TABLE to convert it to InnoDB:

alter table urls engine=InnoDB;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

The Rant

Note that the maximum key length is in bytes, not characters. So lets review the rpath column. This column stores the path to the media file in the catalog, which could easily be something like: /very/long/path/to/find/a/file/with/music.mp3. If it only uses English alphabet characters it's not very long, but as soon as you start using some multi-byte characters (ie: ñ, ç, ü, etc) the length of the string starts to increase in bytes (ie: ó = 4 bytes). A simple query shows the diference:

select id, rpath, bit_length(rpath) / 8 as bytes, 
char_length(rpath) as chars 
from urls limit 1;
+----+-----------------------------------------+---------+-------+
| id | rpath                                   | bytes   | chars |
+----+-----------------------------------------+---------+-------+
|  1 | ./home/gnarvaja/Music/Dodododódodo.mp3 | 41.0000 |    39 |
+----+-----------------------------------------+---------+-------+

So how big can the index be in bytes?

I let MySQL answer this question for me. I created a similar test table with only a PRIMARY and then recreated the index on rpath. Here's the command sequence and it's output:

CREATE TABLE urls_test (
  id int(11) NOT NULL AUTO_INCREMENT,
  rpath varchar(324) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE INDEX rpath_idx ON urls_test (rpath);
Query OK, 0 rows affected, 2 warnings (0.32 sec)

show warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
2 rows in set (0.00 sec)

SHOW CREATE TABLE urls_test\G
*************************** 1. row ***************************
       Table: urls_test
Create Table: CREATE TABLE `urls_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rpath` varchar(324) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `rpath_idx` (`rpath`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

So down to 255 characters from the original 324. In this case (a music player) it may not be a significant loss, but in a world where applications should support an increasing number of International character sets, in particular Asian ones, this limitation could potentially become serious.

I'm not a source code expert, so I'm not sure what it would take to remove, or at least expand the maximum key size. InnoDB's maximum key length (see URL quoted at the beginning of the article) seems like a good number: 3500 bytes ... or I may be overestimating the need for keys bigger than 255 bytes ... your call.

MySQL 5.5 - Upgrading From Previous Releases

MySQL 5.5 GA has finally arrived and, if you haven't done already, it's time to test it starting with the upgrade procedure. MySQL upgrades are usually easy and uneventful, not in this case so I decided to share my experience with the RH 5 RPMs.

Step 0 - Is the upgrade for you?

Do you use InnoDB? This is where most of the improvements have been made, and it could be the main reason why you may want to be an early adopter. For a complete reference of the improvements check the New Features of InnoDB 1.1 section of the official MySQL Documentation, Sheeri Cabral's and Ronald Bradford's blogs.

Step 1 - Read the manual

Ok, I'm the first one to admit that I usually skip this step, but after a couple of false starts I decided that in this case it might be a good idea to go through the documentation, in particular Upgrading from MySQL 5.1 to 5.5. The Incompatible change entries are quite important in this release, and as you will find out, make the upgrade process a little more complex than usual. You mileage may vary, but make sure you're review them carefully.

Step 2 - Take a backup

I usually trust (since we test them) the overnight backups, in this case, I'm a little more paranoid since you may not be able to do a straight upgrade and will need to remove the old version before you install the new one(see next step). Taking a new backup or double checking your last backup is a very good idea. Make sure you include your current configuration file(s) with the backup.

Step 3 - The upgrade process

If you have been using the InnoDB plugin, you need to disable the plugin options in your configuration file before you start the upgrade. The details are in the Upgrading from MySQL page I quoted in Step 1, it's the first Incompatible change.

Although it wasn't listed in the documentation, when tried to run an RPM upgrade (rpm -Uvh MySQL-server-5.5.7_rc-1.rhel5.x86_64.rpm) it failed with a message telling me that I needed to remove the previous version and run a regular install (rpm -i) instead. I usually get a little bit anxious with this kind of recommendations, but fortunately both steps ran smoothly.


Keep in mind that the first time you'll have to start the service with the --skip-grant option to run mysql_upgrade. If you're upgrading a production server, it highly recommended you add the --skip-networking otherwise you may get connections from your application(s), which you it's not a good idea until at this point. This is the 3rd Incompatible change. Once you're done, shutdown the MySQL service (ie: using mysqladmin shutdown, no need for username/password in this case) and restart it as you normally would (ie: sudo /etc/init.d/mysql start)

Done

At this point your installation should be ready for testing. As I mentioned before, make sure you double check the Incompatible change notes to make sure your database configuration and/or application doesn't need any further adjustments. I would also recommend following closely the posts in Planet MySQL for reviews and articles from the community.

MySQL Enterprise Backup and The Meaning of Included

During the MySQL Users Conference, Edward Screven did a keynote presentation that made many of us feel warm and fuzzy about Oracle's future plans for MySQL. If you advance 16m 25s into the presentation, it even gives something to rejoice the MySQL Enterprise customers: "Backup is now included". He didn't say much more after that. Asking around at the conference the days following this announcement, I couldn't get a straight answer about when and how would it be available for existing customers.

Now, 6 months later (give or take a couple of weeks), the MySQL Enterprise Features page has no signs of the now included MySQL Enterprise Backup (the utility previously known as InnoDB Hot Backup) and there has been no other news supporting Edward's announcement anywhere else (if I'm wrong, please point to it with a comment).

Has anybody any insight about what the definition of included is according to Oracle's dictionary? Maybe it's not included for existing customers and it will be when Oracle comes out with the new price list? This last statement will surely make existing customers pretty unhappy.

Maybe there was no reason to feel warm and fuzzy after all. What is your take on this particular issue?

A Replication Surprise

While working on a deployment we came across a nasty surprise. In hindsight it was avoidable, but it never crossed our minds it could happen. I'll share the experience so when you face a similar situation, you'll know what to expect.

Scenario

To deploy the changes, we used a pair of servers configured to replicate with each other (master-master replication). There are many articles that describe how to perform an ALTER TABLE with minimum or no downtime using MySQL replication. The simple explanation is:

1. Set up a passive master of the database you want to modify the schema. 
2. Run the schema updates on the passive master.
3. Let replication to catch up once the schema modifications are done.
4. Promote the passive master as the new active master.

The details to make this work will depend on each individual situation and are too extensive for the purpose of this article. A simple Google search will point you in the right direction.

The Plan

The binlog_format variable was set to MIXED. While production was still running on the active master, we stopped replication from the passive to the active master so we would still get all the DML statements on the passive master while running the alter tables. Once the schema modifications were over, we could switch the active and passive masters in production and let the new passive catch up with the table modifications once the replication thread was running again.

The ALTER TABLE statement we applied was similar to this one:

ALTER TABLE tt ADD COLUMN cx AFTER c1;

There were more columns after cx and c1 was one of the first columns. Going through all the ALTER TABLE statements takes almost 2 hour, so it was important to get the sequence of event right.

Reality Kicks In

It turns out that using AFTER / BEFORE or changing column types broke replication when it was writing to the binlog files in row based format, which meant that we couldn't switch masters as planned until we had replication going again. As a result we had to re-issue an ALTER TABLE to revert the changes and then repeat them without the AFTER / BEFORE.

The column type change was trickier and could've been a disaster, fortunately this happened on a small table (~400 rows which meant the ALTER TABLE took less than 0.3sec). In this case we reverted the modification on the passive master and run the proper ALTER TABLE on the active master. Should this have happened with a bigger table, there was no other alternative than either rollback the deployment or deal with the locked table while the modification happened.

Once this was done we were able to restart the slave threads, let it catch up and and everything was running as planned ... but with a 2hr delay.

Unfortunately, using STATEMENT replication wouldn't work in this case for reasons that would need another blog article to explain.

Happy Ending

After the fact, I went back to the manual and I found this article: Replication with Differing Table Definitions on Master and Slave. I guess we should review the documentation more often, the changes happened after 5.1.22. I shared this article with the development team, so next time we won't have surprises.

Simple Backup Server

I have not written an article in a while, I partially blame it on the World Cup and my day job. The time has come to share some of my recent experiences with a neat project to provide several teams internally with current MySQL backups.

When faced with these types of challenges is my first step is to look into OSS packages and how can they be combined into an actual solution. It helps me understand the underlying technologies and challenges.

ZRM Backup

I have reviewed Zmanda's Recovery Manager for MySQL Community Edition in the Fall 2008 issue of MySQL magazine. It remains one of my favorite backup tools for MySQL since it greatly simplifies the task and configuration of MySQL backups taking care of most of the details. Its flexible reporting capabilities came in handy for this project as I'll explain later. Some of the key settings:

We included the hostname in the ZRM backup-set to make it easier to locate. Linux shell example:

export BKUP_SET=`hostname -s`-logical

Following ZRM conventions, generate a HTML report in the main backup directory.

mysql-zrm-reporter --where backup-set=$BKUP_SET --type html \
   --show backup-status-info >/dir/to/backup/$BKUP_SET/report.html

The actual backup files live under the subdirectories:

/dir/to/backup/$BKUP_SET/<timestamp>/

where /dir/to/backup could be mounted on a NFS server

Please check the ZRM for MySQL documentation for details on its configuration and operation. Use the report format that best suits your needs, ZRM provides plenty of options and if none fits your needs exactly, you can still generate your own.

lighttpd HTTP Server

As a web server, lighty adds very little overhead so it can run on the same MySQL server and the backups can be served directly from it. If this is not acceptable and the backups are stored in an NFS volume, lighty can be installed on the NFS server, the configuration will remain very similar to the one I describe here.

For this example I’ll assume that the MySQL server host is named dbhost, in which case the /etc/lighttpd/lighttpd.conf file should include:

server.modules  = ( 
                                "mod_alias",
                                "mod_access",
                                "mod_accesslog" )

## Default root  directory set to the main backup set
server.document-root = "/dir/to/backup/dbhost-logical/"

## Set one alias per  backup type
alias.url            = ( "/logical/" => "/dir/to/backup/dbhost-logical/")
alias.url           += ( "/incremental/" => "/dir/to/backup/dbhost-incremental/")

## Enable logs for  diagnosis
server.errorlog      = "/var/log/lighttpd/error.log"
accesslog.filename   = "/var/log/lighttpd/access.log"

server.port          = 8088

## virtual directory listings enabled
dir-listing.activate = "enable"

##  enable debugging to facilitate diagnosis
debug.log-request-header    = "enable"
debug.log-response-header   = "enable"
debug.log-request-handling  = "enable"
debug.log-file-not-found    = "enable"

The server.document-root and alias.url settings should match the main directories for the ZRM backup sets.

Make sure that the user and / or group defined for the lighttpd process have proper permissions to access the backup set directory tree. The backups will be available as a directory listing when using the following URLs: http://dbhost:8088/logical/ or http://dbhost:8088/incremental/. Clicking on the report.html file in those directories (see the previous section in the article), the users have access to the backup reports and verify if any of them had errors. The files are also accessible using wget.

If you need to enable tighter security, lighty supports https and LDAP authentication the details are in its documentation and it takes less than 10 minutes to setup.

monit Monitoring

When you need a service to be running 24/7, monit is a great tool in the Linux arsenal to achieve it. It monitors a number of system and services variables and it will start, stop and/or restart any service under a number of conditions. For this POC, the goal is to keep lighty running, restarting it after an eventual crash. We are using the following configuration:

check process lighttpd
   with pidfile  "/var/run/lighttpd.pid"
   alert some@yourorg.com NOT { instance,  pid, ppid, action }
   start program = "/etc/init.d/lighttpd start" with timeout  60 seconds
   stop program = "/etc/init.d/lighttpd stop" with timeout 30 seconds
   if 2 restarts  within 3 cycles then timeout
   if failed port 8088 protocol http with  timeout 15 seconds within 3 cycles then restart

If the process crashes or port 8088 becomes unresponsive, monit will (re)start lighty automatically.

Conclusion (sort of)

Implement all these tools in a new server takes less than 1 hour. During the the first couple of implementations I learned a lot about how the packages interacted, security issues and users’ needs and expectations. As the users started looking into the solution, they also came up with newer use cases.

While we look for a more suitable solution, these free (as in freedom and beer) packages provided us with the opportunity to learn while still achieving our goals and delivering results to our users. Now we know what to look for if we decide to evaluate an open core or commercial solution.

Changes to the Blog

Hi all, it's really unfortunate that this is my first post after a while (the World Cup kept me busy/distracted over most of last month), but I need to make some changes to the blog. Lately I had to reject plenty of spam coming through the comments. I will now require to be a registered user and see if I don't have to deal with what I consider one of the worst outcomes of modern technology. I hope this won't stop the feedback I get to my articles. See you soon.