Pablowe

Nagios MySQL Plug-Ins

There currently exist many plugins for MySQL to use with Nagios. Many of them, however, are not version-independent, leaving organizations that use multiple versions of MySQL to either install multiple plugins or not monitor specific versions of MySQL. As such, I’ve compiled what I consider to be the most useful checks into a single plugin: check_mysql

Usage:
     check_mysql check_name [options]

     Options:
       --args|a     Optional arguments.  Comma-separated.  Check-specific.
       --critical|c The level at which a critical alarm is raised.  Check-specific.
       --database   The database to use (defaults to mysql)
       --help|?     Display this message and exit
       --hostname|H     The target MySQL server host (defaults to localhost)
       --password|p The password of the MySQL user
       --port       The port MySQL is listening on (defaults to 3306)
       --user|u     The MySQL user used to connect
       --version|V  Display version information and exit
       --warning|w  The level at which a warning is raised.  Check-specific.

     defaults are:

     ATTRIBUTE                  VALUE
     -------------------------- ------------------
     args                       No default value
     critical                   Check-specific
     database                   mysql
     help                       FALSE
     host                       localhost
     password                   No default value
     port                       3306
     timeout                    10 seconds
     user                       No default value
     verbose                    1 (out of 3)
     version                    FALSE
     warning                    Check-specific

Current Checks Supported:

* connect – Check to see whether or not one can connect to MySQL (USAGE)
* repl_io – Check to see whether on not the IO Replication thread is running (REPLICATION CLIENT)
* repl_sql – Check to see whether or not the SQL Replication thread is running (REPLICATION CLIENT)
* repl_sbm – Check how many seconds behind the master the slave is (REPLICATION CLIENT)
* mysql_query – Run a given query, test if it executes properly (SELECT)
* connections – Test if the percentage of used connections is over a given threshold (PROCESS)

I am open to requests for additional checks etc.

Drizzle

Last night I attended the San Francisco MySQL Meetup Group to hear Brian Aker talk about Drizzle. Heretofore, I was generally uninterested in Drizzle because I didn’t pay enough attention to what it was actually trying to accomplish. After his presentation, I am anxious to play with it in a meaningful way! The features that sold me:

• utf8 as the default (only?) charset
• No more SQL_MODE (behaving strict-like by default)
• Pluggable Authentication
• A much smarter communication protocol
• 64-bit only *(EDIT: Stewart has informed me that 32-bit is supported, but Drizzle has been optimized for 64-bit … I’m still counting it as a win)
• Did I mention Pluggable Authentication?
• Defaults to transaction-capable

One feature I’m hoping to see come up soon:

• Online Upgrades

Why would I want that? Upgrading is very easy, you think. Since drizzle is designed to be used with huge (by today’s standards) machines with 128+G RAM, 128+ CPU Cores, one must assume that the data sets used will correspondingly be huge. So huge, in fact, that it will take hours (days?) for the caches to warm up! An online upgrade capability may allow for caches to stay hot during the upgrade process rather than having to operate in a degraded state for a long time after an upgrade.

Nagios3 on CentOS: Quick Install Script

As part my job, I find myself doing Nagios installs on a somewhat regular basis. The following is a quick guide on installing Nagios 3 on CentOS, distilled from the official Nagios docs. It is meant to be copied and run as a shell script (you should only have to update the passwords):

#!/bin/sh

# Any Failing Command Will Cause The Script To Stop
set -e

# Treat Unset Variables As Errors
set -u

echo "***** Starting Nagios Quick-Install: " `date`
echo "***** Installing pre-requisites"
yum -y install httpd
yum -y install gcc
yum -y install glibc glibc-common
yum -y install gd gd-devel

echo "***** Setting up the environment"
useradd -m nagios
echo "INSERT_PASSWORD_HERE" |passwd --stdin nagios
groupadd nagcmd
usermod -a -G nagcmd nagios
usermod -a -G nagcmd apache

echo "***** Getting the Nagios Source and Plug-Ins"
cd /usr/local/src
wget http://prdownloads.sourceforge.net/sourceforge/nagios/nagios-3.2.0.tar.gz
wget http://prdownloads.sourceforge.net/sourceforge/nagiosplug/nagios-plugins-1.4.14.tar.gz
tar xzf nagios-3.2.0.tar.gz
tar xzf nagios-plugins-1.4.14.tar.gz

echo "***** Installing Nagios"
cd /usr/local/src/nagios-3.2.0
./configure --with-command-group=nagcmd
make all
make install
make install-init
make install-config
make install-commandmode
make install-webconf

echo "***** Setting up htpasswd auth"
htpasswd -nb nagiosadmin INSERT_PASSWORD_HERE > /usr/local/nagios/etc/htpasswd.users
service httpd restart

echo "***** Setting up Nagios Plug-Ins"
cd /usr/local/src/nagios-plugins-1.4.14
./configure --with-nagios-user=nagios --with-nagios-group=nagios
make
make install

echo "***** Fixing SELinux"
chcon -R -t httpd_sys_content_t /usr/local/nagios/sbin/
chcon -R -t httpd_sys_content_t /usr/local/nagios/share/

echo "***** Starting Nagios"
chkconfig --add nagios
chkconfig nagios on
service nagios start

echo "***** Done: " `date`

Hope you find this useful!

* EDIT: 2009-09-07 – Updated link to get Nagios 3.2.0
* EDIT: 2009-10-14 – Updated link to get Nagios Plugins 1.4.14

ATTN: WordPress Theme & Plug-In Developers

Please minify all JavaScript and CSS files. The size of the main page at pablowe.net was reduced by 20% by this simple, 30-second act.

CSS::Minify
JavaScript::Minifier

Thank You!

OmniSQL 0.0.7 Released

OmniSQL (a command line tool for DBAs needing to issue ad-hoc queries against sharded data) version 0.0.7 is officially released.

Instead of logging in separately to multiple databases to issue the same query, groups of databases can be specified in a configuration file and queries will be automatically issued against all targeted MySQL instances.

Let me know of any bugs found or features you would like to see in upcoming releases!

Download at http://code.google.com/p/omnisql/.

CHANGE LOG
- Fixed Bug #3: Script failure when only one group is defined
- Fixed Bug #4: Too Slow (now it forks)
- Fixed Bug #5: Host-Specific parameters don't work
- INCOMPATIBLE CHANGE: Changed format of config file
- INCOMPATIBLE CHANGE: Use XML::SimpleObject instead of XML::Simple
- Added group summary (Issue #6)

The Awkward Stage of Scaling

A lot of my clients are in a position where their database performance is deteriorating but they are not “big enough” (or not willing/able to) explore sharding all of their data structures. They’re too big for the solution to be adding another read slave, but too small to justify the resources for re-designing their architecture. They’ve often implemented memcache, re-factored schema, and tried other ways to improve database performance but are looking for quick wins without the hassles & risks of full-fledged sharding. As such, I find myself regularly recommending that customers explore basic functional partitioning and “mini sharding”. It is a great way to stave off that inevitable day when you have to re-architect the entire application:)

If you are lucky enough to have built-in debug code and know which module is taking the most time, by all means ignore this list and separate that module from the core architecture if possible. If not, read on.

Registration

Your user table probably has a column like the following:

`status` enum('PENDING','ACTIVE','DELETED') NOT NULL default 'PENDING'

Where a user is ‘PENDING’ until they complete email verification. If you do a:

SELECT `status`, COUNT(*) FROM `user` GROUP BY `status`;

(go ahead and do it … I’ll wait)

You will probably notice that a lot of your users are ‘PENDING’ (my guess is ~20% … am I right?). If, upon registration, user records are placed in a pending_user until successfully completing email verification, the following benefits can be realized:

1) The number of records in the main user table is smaller. It is common for as many as 15-25% of new registrations to never complete email verification. Keeping them out of the main user table will slow the data/index size growth rate.
2) Queries against the user table will not be invalidated as often. Because of the way the MySQL query cache works, inserts into a table invalidate queries against the target table. By not inserting as many records into the table (see benefit #1), the queries do not get invalidated as often.
3) Queries using “WHERE `status` != ‘PENDING’” now don’t have to use that where clause. Conversely, queries looking only for records where `status` = ‘PENDING’ can query the pending_user table directly and not interfere with the *real* tables.

The same goes for *anything* that goes through an approval process (media, posts, etc). Put it in a pending table so that queries don’t have to have that extra WHERE clause.

Archive server(s)

The logical extension of separating PENDING records is to do the same for DELETED records. If deleted records (whether users or posts) are only used in specific queries, consider setting up archive_user or archive_post tables. This way, they can easily be moved to separate nodes and not waste storage/index space on your primary database servers.

Search

If search isn’t killing your database now, it probably will in the future. This is one of the easiest modules to detach from the primary database servers (it should simply involve changing your Search.class). Check out Sphinx, Lucene, or Solr. Because search tends to be widely used, this should free up the database to serve a higher volume of other queries.

Click-Tracking

Everybody wants to record what their users do, and when they do it. This can be the backbone of providing recommendations, UX research, and user profiling. It is important. But it is also write-heavy and can cause replication lag. With a little bit of work, the raw data can be separated from the core database server and written to different nodes. Some general thoughts on how to approach this:

- Log the clicks directly from http logs (set up a lightweight daemon on a dedicated node)
- Log clicks to a file and then periodically write to a database that is separate from your application (consider using LOAD DATA INFILE)
- Group the raw data by day/week/month/year and use partitioning
- Move data that is not actively used to another node (usually one with larger, slower disks)

The above recommendations are all relatively easy to implement and can provide tremendous benefit to your application. 64993021DF5E7E3652226B74779DCB92

Managing MySQL Configuration Files

It is good practice to manage changes to MySQL configuration files (/etc/my.cnf) by using a version control system. I usually use a home-brewed (not brewed by me!) svn+cfengine application to propagate my.cnf (and other configuration file) changes to defined classes of machines (classes are based on application role, replication role, etc).

When managing hundreds of different database servers with dozens of roles, templating my.cnf becomes a necessity! If I have to change a variable for a given class, that can mean editing a dozen my.cnf files on as many servers. Not a productive use of time!!! Usually, the only parameters that differ between database hosts in the same class are the replication options. As such, I find it useful to have a base template for each class of machine and use the !include startup option to specify host-specific startup options.

Here is a sample of what I would have as a base my.cnf for billing-class MySQL Replication Slaves:

And then I would store host-specific replication options in a smaller file:

To re-iterate, templating MySQL configuration files can help ensure consistency between servers of the same class as well as making it easier to push changes to all relevant hosts instead of doing it individually.

The Dangers of Having status fields

Having a status column is very common in databases today. It can be used to denote a user status:

CREATE TABLE IF NOT EXISTS `user` (
`user_id` int(10) unsigned NOT NULL auto_increment,
`email` varchar(32) NOT NULL,
`pw_hash` char(40) NOT NULL COLLATE latin1_general_cs,
`status` enum('PENDING', 'ACTIVE', 'DISABLED') default 'PENDING',
`date_created` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`),
UNIQUE KEY `idx_email` (`email`)
);

or user-uploaded media status:

CREATE TABLE IF NOT EXISTS `media` (
`media_id` int unsigned NOT NULL auto_increment,
`owner_user_id` int(10) unsigned NOT NULL,
`title` varchar(32) NOT NULL,
`path` varchar(255) NOT NULL,
`description` varchar(128) NOT NULL,
`status` enum('PROCESSING', 'ACTIVE', 'FAILED', 'DISABLED') default 'PROCESSING',
`date_created` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`media_id`),
KEY `idx_owner` (`owner_user_id`)
);

Let’s say that we needed to retrieve a list of media associated with users. This can be easily accomplished with the following query:

SELECT SQL_NO_CACHE
`u`.`user_id`,
`m`.`media_id`
FROM `user` AS `u`
JOIN `media` AS `m` ON (`u`.`user_id` = `m`.`owner_user_id`);

If there are status columns involved, the query then becomes:

SELECT SQL_NO_CACHE
`u`.`user_id`,
`m`.`media_id`
FROM `user` AS `u`
JOIN `media` AS `m` ON (`u`.`user_id` = `m`.`owner_user_id`)
WHERE `u`.`status` = 'ACTIVE'
AND `m`.`status` = 'ACTIVE';

If non-active records are removed, the status columns dropped, and the first query run, there is a 15% increase in qps over the second query on the original tables in my test environment. The difference can becomes even more pronounced as the number of tables referenced in a JOIN increases!

If you must have status fields, choose the right data type. But if they can be avoided by archiving disabled records to “cold” databases and keeping “pending” records separate (say, until a user completes email verification), that is generally a better solution.

Must we always escape values?

One of the cardinal rules of writing web applications is to escape user-generated input with functions like PHP’s real_escape_string. This is a great rule, but one that can have a negative impact on your application’s performance if used unnecessarily. For instance, when querying data with an integer parameter that is passed internally (not user-generated):

$query = "SELECT SQL_NO_CACHE * FROM `user` WHERE `user_id` = '" .
$mysqli->real_escape_string ( self::$user_id ) . "'";
$res = $mysqli->query ( $query );


The above code takes an average of 0.000922918319702 seconds to execute.

Whereas:

$query = "SELECT SQL_NO_CACHE * FROM `user` WHERE `user_id` = " . self::$user_id;
$res = $mysqli->query ( $query );


takes an average of only 0.000418901443481 seconds to execute.

Although the improvement is small (~0.0005 seconds), when your site runs millions (or tens-of-millions) of queries per day, the benefits begin to add up.

Improving WordPress Performance with Basic MySQL Maintenance

If your blog is anything like mine, the vast majority of comments are spam. Most blogs have at least a 50% ratio of spam-to-valid comments, and Pablowe has a 99.4% ratio (which is probably why there are so many Anti-Spam plugins for WordPress).

One of the most oft-executed queries (based on the MySQL general log statistics) is:

SELECT DISTINCT ID, post_title, post_password, comment_ID,
comment_post_ID, comment_author, comment_date_gmt, comment_approved,
comment_type,comment_author_url,
SUBSTRING(comment_content,1,30) AS com_excerpt
FROM comments
LEFT OUTER JOIN posts ON (comments.comment_post_ID = posts.ID)
WHERE comment_approved = '1' AND comment_type = '' AND
post_password = ''
ORDER BY comment_date_gmt DESC
LIMIT 10;

In order to keep this (and other) queries performing well, I put the following script in cron and schedule it to execute weekly:

<?php
// This should match your values in wp_config.php
$table_prefix = '';
$db_host = '';
$db_name = '';
$db_user = '';
$db_password = '';
// How long (in days) before comments are purged
$purge_age = 7; // DEFAULT: One Week
if ( $mysqli = new mysqli( $db_host,$db_user,$db_password,$db_name) ) {
$purge_query = 'DELETE FROM ' . $table_prefix .
'comments WHERE comment_date < DATE_SUB(NOW(), INTERVAL ' .
$purge_age . " DAY) AND comment_approved = 'spam'";
if ( ! $mysqli->query ( $purge_query ) ) {
die ( "Could Not Issue Query: Please check configuration\n" );
}
$mysqli->close ( );
} else {
die ( "Could Not Connect: Please check configuration\n" );
}
?>

Will this make your blog Digg Proof? Not by itself, but it can play an important part in the overall performance and scalability of your blog.