mysql

For some time now I've been working on a Drupal site that consists mainly of scraped content from a proprietary, ASP based CMS from the late nineties. The Simple HTML Dom Parser, used from within a drush script, has been invaluable. It made scraping the old site content and importing it as Drupal nodes a relative breeze. (No access to the database used by the CMS, boo!)

Part of setting up the new site is importing users and their content profile nodes from a different Drupal site, that was setup a year or two ago to manage an event.

I had hoped there would be a way for me to export these users and their profile nodes from one Drupal to the other, but though I found modules to export one or the other, I might still end up with profile nodes that were no longer related to their users. Of course, that's pretty useless.

When I remembered I was also supposed to add all these users to a simplenews newsletter, the proverbial light bulb came on.

Some time ago I attended the "Optimisation by Design" course from Open Query¹. In it, Arjen teaches how writing better queries and schemas can make your database access much faster (and more reliable). One such way of optimising things is by adding appropriate query hints or flags. These hints are magic strings that control how a server executes a query or how it returns results.

An example of such a hint is SQL_CALC_FOUND_ROWS. You use it in a select query with a LIMIT clause. It instructs the server to select a limited numbers of rows, but also to calculate the total number of rows that would have been returned without the limit clause in place. That total number of rows is stored in a session variable, which can be retrieved via SELECT FOUND_ROWS();  That simply reads the variable and clears it on the server, it doesn't actually have to look at any table or index data, so it's very fast.

This is useful when queries are used to generate pages of data where a user can click a specific page number or click previous/next page. In this case you need the total number of rows to determine how many pages you need to generate links for.

The traditional way is to first run a SELECT COUNT(*) query and then select the rows you want, with LIMIT. If you don't use a WHERE clause in your query, this can be pretty fast on MyISAM, as it has a magic variable that contains the number of rows in a table. On InnoDB however, which is my storage engine of choice, there is no such variable and consequently it's not pretty fast.

Since I started doing the occasional consulting job for Open Query, I've seen a lot of MySQL servers that have been installed once and then forgotten about. This gave me the idea to do a short presentation about some basic MySQL server configuration. The first go was at DrupalCampMelbourne and I recently tried (and failed) to cram it into a three minute lightning talk slot at the LUV September meeting.

The title of the talk is (now) MySQL > YourSQL. I chose this not because I think that MySQL is better than the $other_database you use or because I may or may not run a newer version of MySQL on better hardware, but because I use InnoDB and not MyISAM as the default table format. More importantly, I do not run the server with the shipped default configuration.

These configuration tips are also included in the MySQL section of Pro Linux System Administration.

Most distributions ship MySQL with a configuration file that sees you running a server optimised for a system with 32MB of RAM. That was great in 1996, but these days your Nike+ shoes have more capacity than that, so it makes sense to optimise the configuration - or at least to make it not suck quite as badly.

I chose InnoDB as the default storage engine not because it gives me a magic increase in performance or transaction support, but due to the  improved reliability that comes from its ability to automatically recover from crashes better than MyISAM does.

All the following settings go in the [mysqld] section of your my.cnf file.

A while ago Arjen Lentz blogged about transient MySQL errors that can occur when using a transactional storage engine, like say InnoDB.

Since I'm a fan of the reliability and automated recovery that InnoDB provides, I use it for all the Drupals that I host.  However, on a very busy site, this may lead to deadlocks. These in turn lead to users seeing errors, which is something I'd like to avoid. Especially if the error could be prevented.