In this post, I’ll cover the new MySQL monitoring plugins we created for Nagios, and explain their features and intended purpose.

I want to add a little context. What problem were we trying to solve with these plugins? Why yet another set of MySQL monitoring plugins?

The typical problem with Nagios monitoring (and indeed with most other monitoring systems) is the alert spam. I’ve spoken to a lot of users with monitoring systems over the years, and everyone says that they get a lot of false alarms, and that they have email filters set up for alerts. Recently I spoke to one person who gets about thirty thousand alerts per week (!!!) in his email. I’m sure you can see that this makes the monitoring system much less effective. The usual cause of such noisy monitoring is when you alert on things that don’t really indicate problems.

…

Webcast: Thursday 3/1/12 @ 10 am PT/ 1 pm ETWebcast: Tuesday 3/6/12 @ 15:00 CET/14:00 GMTLearn how the next version of Tungsten Enterprise will help you push the envelope in multi-master, multi-site MySQL database clusters. We will be introducing the following features:Setting up MySQL clusters with a single installation commandAutomatic master failover without data lossBuilding disaster recovery

Today a little history lesson.

I was on a quest to find the origin of this line in include/mysql_com.h:

#define CLIENT_LONG_PASSWORD    1       /* new more secure passwords */

“new” ? “more secure” ?

These CLIENT_* flags are used between client and server to negotiate what capabilities they support. It allows the server to add new features to the protocol and let’s the client add support for it along the way. MySQL 5.5.x has 21 of these flags.

But back to CLIENT_LONG_PASSWORD and its wonderful comment. What would one expect if someone reads “new” and “more secure”?

new

Well, let’s check the file history:

$ bzr blame include/mysql_com.h
2            bk@work | #define CLIENT_LONG_PASSWORD     1 …

Today a little history lesson.

I was on a quest to find the origin of this line in include/mysql_com.h:

#define CLIENT_LONG_PASSWORD    1       /* new more secure passwords */

"new" ? "more secure" ?

These CLIENT_* flags are used between client and server to negotiate what capabilities they support. It allows the server to add new features to the protocol and let's the client add support for it along the way. MySQL 5.5.x has 21 of these flags.

But back to CLIENT_LONG_PASSWORD and its wonderful comment. What would one expect if someone reads "new" and "more secure"?

new

Well, let's check the file history:

$ bzr blame include/mysql_com.h
2            bk@work | #define CLIENT_LONG_PASSWORD 1   /* new more secure passwords */
$ bzr log …

bad idea..

tmpdir..
..on tmpfs

upgrade..
..to 5.5

default engine..
..innodb

create..
..temp tables statements

optimizing my.cnf...
..io_direct sounds good..

tmpfs..
..and io_direct
do not..
..play well together

..lucky...
..just slave..
..had backup

It seems that whenever I have a cross-continent flight, Mondrian gets a new feature. This particular flight was from Florida back home to California, and this particular feature is a time-dimension generator.

I was on the way home from an all-hands at Pentaho's Orlando, Florida headquarters, where new CEO Quentin Gallivan had outlined his strategy for the company. I also got to spend time with the many smart folks from all over the world who work for Pentaho, among them Roland Bouman, formerly an evangelist for MySQL, now with Pentaho, but still passionately advocating for open source databases, open source business intelligence, and above all, keeping it simple.

Roland and I got talking about how to map Mondrian onto operational schemas. Though …

I found Baron’s reasoning on why the Percona Nagios plugins do not use caching interesting. On the surface, the logic is sound – you do not want to cache when you want real-time monitoring.
I have not yet had time to look at the Percona plugins for Nagios, though I do want to, because back at PalominoDB I helped write a Nagios plugin for MySQL that allows you to do arbitrary calculations. By “arbitrary calculations” I mean you can have a calculation like “Threads_connected/max_connections*100″ and set a threshold of “>80″. You can mix and match MySQL status variables and system variables, and use any perl functions as well, including basic arithmetic.
We put a caching …

DBD::mysql 4.014 breaks pt-table-checksum 2.0.  The cause is unknown, but the effect is a lot of errors like:

DBD::mysql::st execute failed: called with 2 bind variables when 6 are needed [for Statement "..." with ParamValues: ...] at ./pt-table-checksum line 7216.

The fix is simple: upgrade (or even downgrade) DBD::mysql to any version except 4.014. To see which version of DBD::mysql a system has, execute:

perl -MDBD::mysql -e 'print $DBD::mysql::VERSION, "\n";'

This bug may affect other Percona Toolkit tools, but currently pt-table-checksum 2.0 is the only victim. This bug does not affect pt-table-checksum 1.0, and it cannot be worked around in pt-table-checksum 2.0 because the bug in in DBD::mysql.

This bug affects …

Last weekend Vadim from Percona published his MariaDB 5.3.4 benchmark results. As the new benchmark guy at Monty Program I take this oportunity to add some more results of my own.

One question in the comments to Vadim was if it is fair to compare MariaDB-5.3 with MySQL-5.5. Or if this comparison should be done with MySQL-5.1. The answer is: it does not matter much. MySQL-5.5 and MySQL-5.1 show very similar results in the Sysbench OLTP benchmark.

So I created a Sysbench environment pretty much like Vadims and tested the following versions of the MySQL Server:

• MariaDB-5.3.4 – the Monty Program release candidate, both with XtraDB and the InnoDB plugin
• Percona-Server 5.1.61 because it is based on the same XtraDB version as MariaDB-5.3
• Percona-Server 5.5.20 – the current Percona flagship …

Oracle is pleased to be a Bronze Sponsor of DrupalCon Denver.
 
DrupalCon will be held the Colorado Convention Center in Denver on  March 19-23, 2012. There are community events scheduled throughout the entire week, some officially by DrupalCon, others by sponsors and other community members.

Come visit Oracle Booth #400 and find out the latest information about MySQL and Linux!  I will be at the booth so please stop by and say hello.

For event information and to register, click here.

 Oracle Events page with additional information can be found here.   …