Using XtraBackup to backup a remote machine

I love Percona's XtraBackup utility.  It's basically a GPL answer to Oracle's proprietary (and expensive) MySQL Enterprise Backup.  Percona claims that it has even more features that Enterprise Backup.  I do not have access to Oracle's product, though, so I cannot evaluate that claim.

I have my backups set up in a particular way, for convenience and security.  I have a backup machine that has ssh access to the backup user on other machines.  It has terrabytes of spare disk space, and runs a script that uses rsync and other utilities to take backups of my other servers.

Since my backup server is where all backup scripts are run, I have previously used mysqldump to take a backup of one of my slave servers.  This worked well, but was not ideal.  About once a week, mysqldump dies for no apparent reason during the dump, so I end up without a good backup for that day.  It's also slow, and locks the entire database while running.

I've been wanting to switch to XtraBackup for a while.  I've used it when setting up new slaves, and not only is it faster, it does not lock up the entire database while it runs in order to get a consistent backup.  The only problem is that it doesn't fit into my current backup strategy; it is designed to run on the same host as mysqld.

Today, I figured out how to get it working in streaming mode, controlled by my backup server.  It uses little to no disk space on the MySQL server, and streams the backups over to my backup server.  It required a little sudo magic, but beyond that it fit right into my existing backup procedure.

In order to do this, Percona's XtraBackup will need to be installed on both servers.  I had the Percona repository added to my server already, so I just ran:

# yum install percona-xtrabackup

The sudo trick was necessary because I am using ssh to log into my MySQL server as the backup user, which cannot successfully run XtraBackup.  I created the following file as /usr/local/bin/backup-mysql.sh:

#!/bin/bash

/usr/bin/innobackupex --slave-info --stream=xbstream /data/tmp

Then I needed to make sudo allow the backup user to run this script as root without a password.  I added the following line to my /etc/sudoers:

backup ALL=(root) NOPASSWD: /usr/local/bin/backup-mysql.sh

Alternatively, I could have just had the sudoers file contain /usr/bin/innobackupex, and then called that directly.  By keeping it one step removed, I've made it so that only a specific innobackupex command can be run, which lowers the security risk involved if any bugs were found in innobackupex in the future.

On my backup server, I added this to my backup script:

/usr/bin/ssh \
    backup@mysqlserver \
    "/usr/bin/sudo /usr/local/bin/backup-mysql.sh" \
    | xbstream -x -C /backup/daily/0/mysql

/usr/bin/innobackupex \
    --host=mysqlserver \
    --user=root \
    --password=rootpassword \
    --apply-log \
    /backup/daily/0/mysql

The username and password is used to detect the version of MySQL being used, so it can use the correct XtraBackup binary.  I could also have just told it which binary to use, but this is more forward-compatible.

MyISAM's "table lock" problem, and how InnoDB solves it

Most serious users of MySQL have moved their tables to InnoDB years ago.  For those who haven't, let's discuss why InnoDB is a more scalable solution than MyISAM.

MyISAM was designed to be very fast for read queries.  It does not handle higher loads of writes very well.  It also suffers a more serious flaw: it isn't crash-safe.  In other words, you better have frequent backups.

MyISAM tables have a read queue and a write queue.  Queries are placed into one of those two queues to be processed.  The write queue has higher priority than the read queue, but the table can only process one write query at a time.  Multiple read queries can occur at once, so the read queue will often be empty.

If a single query is added to the write queue, the read queue will block additional queries from starting.  All existing read queries will continue to run until they're finished, then any queries in the write queue will be processed.  Then, and only then, the read queue will flush its queries.

This queue system works very well for short queries.  As long as no queries run for a significant amount of time, things will generally perform well.  A long-running write query will lock the table the entire time it's running.  Additionally, a long-running read query will do the same, if a write query (even a short one) is waiting for it.

InnoDB avoids this table-locking problem by using Multi-Version Concurrency Control, or MVCC.  This is the same method used by many other enterprise database solutions, such as Oracle, MS SQL Server, and PostgreSQL.

As its name implies, MVCC allows multiple versions of the same table to exist in parallel.  Once a SELECT query begins, it will continue to see the table as it existed at the time the query (or transaction) began.  Another query can update a row in that table without affecting the results returned to the first query.

No longer will a long-running query take down your server!  There are still some concerns, however.  Even with MVCC, we still have to worry about multiple write queries overwriting each other, and making sure things are done in the correct order.  InnoDB uses row-level locking to ensure that multiple transactions don't write to the same row of your table.  It also has deadlock detection, which aborts queries if the server thinks two transactions are waiting for each other.  For most workloads, you won't notice those very often, but you should be aware of them.

Also of note is the server's isolation level.  Which isolation level you use depends entirely on what risks you're willing to take with your data.  They're described in the MySQL manual.

Slides from OpenWest 2013 presentations

Thanks to all those who attended my presentations at the 2013 OpenWest Conference!  Please take the time to leave feedback on Joind.in.

PHP Security: It doesn't have to be an oxymoron

Download the slides
Leave feedback

Database Optimization for Web Developers

Download the slides
Leave feedback

Query in a loop?

I ran across this gem recently on StackOverflow:

$queryxyzzy12=("SELECT * FROM visitorcookiesbrowsing ORDER by id ASC");
$resultxyzzy23=mysql_query($queryxyzzy12) or die(mysql_error());

while($ckxf = mysql_fetch_assoc($resultxyzzy23)){
    $querycrtx=("SELECT * FROM cart WHERE userkey='$ckxf[usercookie]' ORDER by datebegan  DESC");
    $resultcrtx=mysql_query($querycrtx) or die(mysql_error());
    ------ snip ------
}

Besides the interesting variable names used, it's also doing a query inside a loop, which is very inefficient. This would be better written as a single JOIN query:

SELECT 
  v.*, 
  c.* 
FROM 
  visitorcookiesbrowsing v 
  LEFT JOIN cart c ON c.userkey=v.usercookie 
ORDER BY 
  v.id ASC, 
  c.datebegan DESC

The details of JOIN vs. LEFT JOIN depend on the actual intent of the code, which wasn't apparent from the snippet.

If at all possible, never do a query inside a loop.  The overhead of doing multiple queries is generally far greater than any possible benefit.  If you're using an ORM framework, you need to be aware of how it is loading data from the database.  If you are instantiating objects in a loop, you may really be executing a query (or sometimes more!) for each object you instantiate.

This advice applies to update queries as well.  It's generally best to create an array of the values that need updating, and then update them all at once.  With MySQL, this is fairly easy for INSERT and DELETE queries.

UPDATE queries can be a bit trickier, since you can't easily specify different updates for each value.  I've solved this a few different ways, depending on circumstances.  If you know that the rows you are updating exist, then you can cheat and do an INSERT ... ON DUPLICATE KEY UPDATE.  If you aren't 100% sure the rows exist, then it may be best to create a temporary table, insert all of the update values into it (with a single query), and then do a multi-table UPDATE query.

If you're running MariaDB or Percona Server, you could also try performing your update over the HandlerSocket interface.  This would eliminate much of the overhead of running multiple queries, even though you are, in effect, running one query per update.

As always, the performance of these different ways of doing things depends greatly on your data, and what you're doing with it.  Always run tests to see how these different methods work with your data, server, and application.

Speaking at the OpenWest Conference

I'm presenting two talks at the OpenWest Conference next month.  The first talk, on May 2nd, will be about PHP security.  The second talk, given on May 3rd, will be about database optimization, geared towards web developers.  I'll discuss some of the same things that I have discussed on this blog.

We're going to have some great speakers at that conference.  On the PHP front, we'll have Rasmus Lerdorf giving a keynote as well as another presentation.  From the MySQL community, Mark Callaghan from Facebook will be giving another keynote, and we'll also have Colin Charles from MariaDB.

It should be an exciting conference!

Percona Server to ship with jemalloc

Joseph Scott pointed me to a little tidbit hidden in the latest Percona Server release notes: "Percona Server for MySQL will now be shipped with the libjemalloc library."  Percona published the results of some testing of various malloc libraries on their MySQL High Performance Blog last year, and it looks like this will have a very positive impact on performance.

I'm currently using MariaDB, so I'm hoping they pick up this change as well.

How to identify an unnecessary index

Let's look at the index from the wp_posts table in a standard WordPress installation.

SHOW KEYS FROM wp_posts;
+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name         | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_posts |          0 | PRIMARY          |            1 | ID           | A         |        1772 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_name        |            1 | post_name    | A         |        1772 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date |            1 | post_type    | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date |            2 | post_status  | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date |            3 | post_date    | A         |        1772 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date |            4 | ID           | A         |        1772 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_parent      |            1 | post_parent  | A         |         147 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_author      |            1 | post_author  | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | yarpp_title      |            1 | post_title   | NULL      |         590 |     NULL | NULL   |      | FULLTEXT   |         |               |
| wp_posts |          1 | yarpp_content    |            1 | post_content | NULL      |        1772 |     NULL | NULL   |      | FULLTEXT   |         |               |
+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.00 sec)

How can we tell which indexes are good, and which are bad?

To begin with, let's look at the cardinality.  Cardinality is a measure of how many unique values there are for that column.  A column with very low cardinality is not very useful as an index, since it does not narrow down queries very much.

The post_type and post_status columns have a fairly low cardinality, so we'd ordinarily be concerned about them, but they're part of a multi-column index.  Since there are four columns to the index, their own low cardinality will probably be masked by the other columns.  As always, you know your own data better than I do, so you should know whether that applies in your circumstances.  What we're looking at is the cardinality of the combination of columns, so if the values of the columns are related to each other, you could still end up with low cardinality.

On the other hand, post_author has very low cardinality, and is a single-column index.  This column is potentially useful for blogs with many authors.  For most blogs, it ends up being more of a hindrance than a help, as it negatively affects write performance.

Going back to the type_status_date index, we noticed that the first two columns have low cardinality, but you'll notice that the third column's cardinality is equal to the primary key's cardinality.  This means that once we use the third column of the key (having necessarily already used the first two columns), we have narrowed it down to a single row.  This means that the fourth column in the index probably does not add any useful information.

We'll demonstrate this with a few queries.

EXPLAIN SELECT * FROM wp_posts WHERE post_type IN ('post', 'page') AND post_status='publish' AND post_date > '2013-01-01 00:00:00' ORDER BY ID;
+------+-------------+----------+-------+------------------+------------------+---------+------+------+---------------------------------------+
| id   | select_type | table    | type  | possible_keys    | key              | key_len | ref  | rows | Extra                                 |
+------+-------------+----------+-------+------------------+------------------+---------+------+------+---------------------------------------+
|    1 | SIMPLE      | wp_posts | range | type_status_date | type_status_date | 132     | NULL |   34 | Using index condition; Using filesort |
+------+-------------+----------+-------+------------------+------------------+---------+------+------+---------------------------------------+
1 row in set (0.01 sec)

EXPLAIN SELECT * FROM wp_posts WHERE post_type='post' AND post_status='publish' AND post_date > '2013-01-01 00:00:00' ORDER BY ID ;
+------+-------------+----------+-------+------------------+------------------+---------+------+------+---------------------------------------+
| id   | select_type | table    | type  | possible_keys    | key              | key_len | ref  | rows | Extra                                 |
+------+-------------+----------+-------+------------------+------------------+---------+------+------+---------------------------------------+
|    1 | SIMPLE      | wp_posts | range | type_status_date | type_status_date | 132     | NULL |   33 | Using index condition; Using filesort |
+------+-------------+----------+-------+------------------+------------------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

We're specifying the post_type, post_status, and post_date in both queries.  You'll notice that the "rows" doesn't change much when we just select posts instead of posts and pages.  Even though the cardinality of post_type is a whopping 6, even that is a bit misleading, as almost all of the rows have type "post", making the real-world cardinality even lower than that.

If we specify an exact date and time, rather than a range, then we narrow it down to a single row.

EXPLAIN SELECT * FROM wp_posts WHERE post_type='post' AND post_status='publish' AND post_date = '2013-02-05 10:53:57' ORDER BY ID ;
+------+-------------+----------+------+------------------+------------------+---------+-------------------+------+------------------------------------+
| id   | select_type | table    | type | possible_keys    | key              | key_len | ref               | rows | Extra                              |
+------+-------------+----------+------+------------------+------------------+---------+-------------------+------+------------------------------------+
|    1 | SIMPLE      | wp_posts | ref  | type_status_date | type_status_date | 132     | const,const,const |    1 | Using index condition; Using where |
+------+-------------+----------+------+------------------+------------------+---------+-------------------+------+------------------------------------+
1 row in set (0.00 sec)

How often are we going to query the database for an exact date and time, though?  Generally, we'll either be specifying a date/time range, or ordering by the date/time.  In either case, we cannot use the portion of the index after post_date.

So does that mean the ID portion of the key is useless?  In all likelihood, yes.  I can't imagine any scenario where it would be useful.

As an aside, when using the InnoDB storage engine, secondary indexes merely point back to the primary key, so the ID would already be referenced in the index.

Interesting MySQL optimizer case

Jaime at the MySQL Performance Blog had an interesting post regarding some unexpected behavior from the MySQL optimizer.  Although this particular case probably doesn't affect most people, it does give some insight into how the optimizer works, and how subtle changes to a query can change performance.

Asynchronous MySQL queries in PHP

I was made aware today by a post on the MySQL Performance Blog that the mysqlnd driver for PHP has support for asynchronous PHP queries.  At the Midwest PHP Conference, I gave a talk on database optimization for web developers, and in a follow-up discussion with an attendee, I apparently misinformed him.

Midwest PHP Presentation Notes

Thanks for attending my presentation at the Midwest PHP conference!

Slides as PDF
Leave feedback on joind.in

Useful links:

• Explain Analyzer - for those times that you can't figure out what EXPLAIN is telling you
• Percona Toolkit - lots of useful tools for managing a MySQL server
• query-digest-helper - my utility for formatting the results of pt-query-digest 
• HandlerSocket - a presentation about HandlerSocket by Akira Higuchi
• Percona Configuration Generator -  helpful tool for generating an optimal my.cnf for your server

If all else fails, cache it

Sometimes, despite all your efforts to optimize your queries, you have one that just takes a while.  It requires a full table scan, and there's just no getting around it.  In this case, your best option may be to look for ways to use a cache to speed up your query.  There are two kinds of caches that are most useful for these situations: a result cache, and a data cache.

A result cache is fairly simple to implement, and is most often used for reporting.  When the exact same query is run over and over again, then it might make sense to store the result in a cache table.  This is especially true when up-to-the-second accuracy isn't important.  Every company I've worked for has had marketing or other administrative reports that were important to the business, but took a long time to run.  The people viewing the reports only cared about results up to yesterday, so the report really only needed to be run once a day.  Running a 5-minute query once a day isn't really a big deal, especially if you can schedule it to run when your site isn't very busy.

A data cache is a little more complicated, and depends entirely on the nature of your data and the queries you run.  At each company I've worked for, we've had fairly large tables that were queried often in ways that defied indexing.  The specifics were very different in each case, but our solutions held to a common pattern.

First, we were able to significantly limit the data set based on a criteria generally involving what constituted an "active" record.  Second, we were able to de-normalize the data so that JOINs weren't necessary.  Third, we were sometimes able to shard the cached data, since the lookups would only happen across some subset of the data.  Last, we were often able to add strategic indexes that were not possible or feasible on the full data set.  This included geospatial indexes that are only possible with MyISAM tables, while our main data set used InnoDB.

Once you determine your data caching schema, the next big question is how often to cache it?  This is something that you'll need to determine based on (a) how long it takes to rebuild the cache, and (b) how fresh your data needs to be.  The first is a technical constraint, while the latter is a business constraint.  If you keep track of updates, deletes, and inserts to your main data set, then you may be able to just update your cache instead of completely rebuilding it.  I'd still recommend completely rebuilding it occasionally, since it is possible that some edge case was missed when determining what has been updated.

One trick that may help with maintaining your cached data is the RENAME TABLES command.  You can atomically rename multiple tables with a command such as "RENAME TABLES table to table_new, table_old to table".  This allows you to build (or update) a new cache table without impacting the current cache table.

How your database can sometimes use two (or more) indexes

Ernie Souhrada has a great writeup called The Optimization That (Often) Isn’t: Index Merge Intersection. It talks about how MySQL can perform an "index merge" in order to use more than one index.

For example, if you have an OR in your WHERE clause, and each of the conditions are indexable, MySQL can perform each index lookup separately, and then do a union on the results.  Similarly, if you have an AND in your WHERE clause, and again each condition is indexable, MySQL can do an intersection of the results of the two indexes.  MySQL will decide whether to use this optimization based on the statistics it keeps of your table's indexes.

The problem that Ernie identified happened because the customer had an index on a column with very little variance.  Almost all of the rows had the same value, and the optimizer made an incorrect assumption when evaluating indexes. 

For this reason, you should be very cautious about indexing columns with very little variance.  It can make sense sometimes; perhaps almost all of the rows have a 1 in that column, but you need to find the few that have a 0.  This is common when you have to process new rows, and then mark them as "processed".  In this case, I will often use a separate queue table; this negates the possibility of the optimizer choosing to use a very unbalanced index on the main table.

How do JOINs work?

For beginning web developers, JOINs are a scary thing.  Simple queries are easy enough, but once you add a second table to the mix, it becomes a lot more complex.  In reality, JOINs aren't really all that hard to understand, but you do need to pay attention to what you're doing.

By default, JOINs in SQL are Cartesian; for each row in table A, you join it with every single row in table B, so the number of output rows equals the number of rows in A multiplied by the number of rows in B.  This is generally not the desired outcome.  In fact, the Drizzle project has broken SQL compatibility to make it so that Cartesian JOINs need to be specified explicitly.  If no JOIN conditions are given, the query simply generates an error.

I remember a web developer showing me a query that he thought would work like a UNION.  He had 20 "cache" tables that each contained several tens of thousands of rows of information.  He wanted to get all of that information with a single query, rather than querying each table individually.  This is how he wrote his query:

SELECT * FROM cache1, cache2, cache3, cache4, cache5, cache6, cache7, 
> cache8, cache9, cache10, cache11, cache12, cache13, cache14, cache15,
> cache16, cache17, cache18, cache19, cache20;

As you can imagine, the query never returned, and I think the database server eventually ran out of swap space.

Most web developers I've worked with tend to craft their JOIN queries with the condition in the WHERE clause.  While this does (usually) work, it is not as easy for the next poor schlep reading your code to know how the JOIN works.  It also is easier to mistakes when you do this, as you may forget to specify the condition for one of your JOINs.

JOIN conditions are given using the keywords ON or USING.  The USING keyword lets you specify one or more columns that are identical across multiple tables, like so:

SELECT field1, field2 FROM table1 JOIN table2 USING (field3, field4);

The field(s) in the USING condition must have the same name in both tables.  If this doesn't work with your schema, or if you have more complicated JOIN conditions, you should use the ON keyword:

SELECT field1, field2 FROM table1 JOIN table2 ON table1.field3=table2.field4;

When we see an ON or USING condition in a query, that tells us immediately that the JOIN is using that condition to filter the possible rows in the second table. 

Ideally, as discussed in The evils of the "full table scan" previously, we should only find a single row in the second table for each row in the first table.  Additionally, the column in the second table that we are using for filtering the JOIN will have an index on it.  This will allow the query to use that index to find the matching rows, greatly speeding up the execution of our query.

If you can, it is also beneficial to eliminate as many rows as possible from the first table, so that fewer lookups happen on the second table.  This is appropriately done in the WHERE clause.

By putting our JOIN conditions in the appropriate place, it is now easier for us to glance at our query and be able to tell how the tables connect together:

SELECT
 MONTH(STR_TO_DATE(t2.value, '%H:%i:%S %b %d, %Y ')) AS payment_month,
 COUNT(t1.transid) AS num,
 SUM(t3.value) AS total,
 SUM(t4.value) as shipping,
 SUM(t5.value) AS fee,
 SUM(t6.value) AS tax
FROM
 new_transactions t1
 JOIN new_transactions t2 ON t2.transid=t1.transid AND t2.field = 'payment_date'
 JOIN new_transactions t3 ON t3.transid=t1.transid AND t3.field = 'mc_gross'
 JOIN new_transactions t4 ON t4.transid=t1.transid AND t4.field = 'mc_shipping'
 JOIN new_transactions t5 ON t5.transid=t1.transid AND t5.field = 'mc_fee'
 JOIN new_transactions t6 ON t6.transid=t1.transid AND t6.field = 'tax'
WHERE
 t1.field = 'txn_type'
 AND t1.value = 'cart'

GROUP BY
 payment_month
WITH ROLLUP

A complex JOIN query, but we can easily glance at it and tell that all of the JOINs have appropriate conditions on them.

The evils of the "full table scan"

Those three words are guaranteed to make any DBA shudder.  When you EXPLAIN a query, and it tells you that it is not using any index, it has to read (or scan) the entire table to find the row(s) you want.  This may not be noticeable with 84 rows, but with 84 million it can take a while.

There are times when this may be unavoidable.  Those are exceptions, and we'll talk about how to deal with them in a different post.  For now, we'll discuss how to identify full table scans, and how to avoid them.

MariaDB [test]> EXPLAIN SELECT * FROM presentations WHERE room=403;
+------+-------------+---------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table         | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+---------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | presentations | ALL  | NULL          | NULL | NULL    | NULL |   84 | Using where |
+------+-------------+---------------+------+---------------+------+---------+------+------+-------------+

Since the "key" is "NULL", we are unable to use a key for this query.  This is a simple query, and it's easy to identify that adding a key to "room" will take care of the problem.  What if we needed to determine if a room is double-booked?

MariaDB [test]> EXPLAIN SELECT a.id, b.id, a.ts, a.room FROM presentations a JOIN presentations b ON b.id > a.id
> AND b.ts=a.ts AND b.room=a.room;
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                          |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
|    1 | SIMPLE      | a     | ALL  | PRIMARY       | NULL | NULL    | NULL |   84 |                                                |
|    1 | SIMPLE      | b     | ALL  | PRIMARY       | NULL | NULL    | NULL |   84 | Range checked for each record (index map: 0x1) |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+

Now we have a real problem.  Not only are we doing a full table scan, we're doing it twice!  And that's just at first glance.  When you understand how the database is processing the join, you realize that it needs to examine 84 * 84 rows.  This is because for each row in the first table, we need to examine every row in the second table.  To determine how costly a query is, you actually need to multiply all values in the "rows" column of the EXPLAIN output.  In this case, it's not quite that bad because of the range check -- it will actually be about 84 * 42, if I remember my math correctly.  MySQL isn't sure of the exact details, so it puts the full amount for the second table (84) and a note in the Extras.

Again, we're dealing with a small table; 3528 rows really isn't a huge deal.  But you can see how as the table scales up, this query gets exponentially harder.  There is a real world application of this principle.  Many developers use test databases for their development, and then deploy their code to production.  Test databases typically have very little data, so unless you EXPLAIN your queries, you'll probably never catch problems like this before your production site breaks.

Back to our bad query, the full table scan for table "a" is actually unavoidable.  We really do want to examine every presentation to ensure it has not conflicts.  When you EXPLAIN a JOIN, however, you don't want to ever see anything besides "1" for the "rows" after the first table.  We need to make sure that the JOIN conditions for the 2nd table are well-indexed, to avoid having to look up more than one row in the second table per row in the first table.  In this case, we want to add an index across "ts" and "room".  Now, let's try that EXPLAIN again:

MariaDB [test]> EXPLAIN SELECT a.id, b.id, a.ts, a.room FROM presentations a JOIN presentations b
> ON b.id > a.id AND b.ts=a.ts AND b.room=a.room;
+------+-------------+-------+------+-----------------+---------+---------+-----------------------+------+-------------+
| id   | select_type | table | type | possible_keys   | key     | key_len | ref                   | rows | Extra       |
+------+-------------+-------+------+-----------------+---------+---------+-----------------------+------+-------------+
|    1 | SIMPLE      | a     | ALL  | PRIMARY,ts_room | NULL    | NULL    | NULL                  |   84 |             |
|    1 | SIMPLE      | b     | ref  | PRIMARY,ts_room | ts_room | 11      | test.a.ts,test.a.room |    9 | Using where |
+------+-------------+-------+------+-----------------+---------+---------+-----------------------+------+-------------+

Unfortunately,we still don't have 1 for the 2nd table, but because of the nature of the query, there's not much we can do to help that.  It's still a lot better than the original query.  The 9 is actually an estimate based on the database's internal statistics; if we don't have any conflicts, it will actually be 1.  We can change the index to a UNIQUE index, and we'll actually see the 1:

MariaDB [test]> EXPLAIN SELECT a.id, b.id, a.ts, a.room FROM presentations a JOIN presentations b
> ON b.id > a.id AND b.ts=a.ts AND b.room=a.room;
+------+-------------+-------+--------+-----------------+---------+---------+-----------------------+------+-------------+
| id   | select_type | table | type   | possible_keys   | key     | key_len | ref                   | rows | Extra       |
+------+-------------+-------+--------+-----------------+---------+---------+-----------------------+------+-------------+
|    1 | SIMPLE      | a     | ALL    | PRIMARY,ts_room | NULL    | NULL    | NULL                  |   84 |             |
|    1 | SIMPLE      | b     | eq_ref | PRIMARY,ts_room | ts_room | 11      | test.a.ts,test.a.room |    1 | Using where |
+------+-------------+-------+--------+-----------------+---------+---------+-----------------------+------+-------------+

Of course, that just negated the need for the query looking for conflicts.

Other kinds of indexes

B-Tree Index

As MySQL's default index type, a b-tree index is reasonably fast to insert new items into, and is fast at looking up individual items or even ranges.  The index is stored as a branching tree, so looking up any one item is not an expensive operation.  Each level of branching allows an order of magnitude more items.  It's not always the best solution, but in some cases it may be the only one.  B-tree indexes are supported on all standard storage engines.

Hash Index

Hash indexes are similar to associative arrays (or hashes) in many programming languages.  They are very fast for looking up individual items, but cannot look up ranges.  They are stored as a series of "buckets", and a hash algorithm is used for determining which bucket to put an item into.  Good hash algorithms are very inexpensive, and spread input values evenly among the buckets.  It is because they are spread out that range operations become impossible.  Hash indexes are currently only supported for MEMORY and NDB (MySQL Cluster) storage engines.

Fulltext Index

Fulltext indexes are good for searching out individual words inside a long block of text.  "Good" is a relative term; MySQL's fulltext indexes perform better than no index, but for decent performance on larger tables, you'll probably want to use a dedicated search engine such as Sphinx, Solr, Lucene, or other similar applications.  I personally use Sphinx, because you can connect to it from MySQL using the SphinxSE storage engine.  Fulltext indexes are only available in the MyISAM storage engine, which is another reason to look into other solutions.

Spatial Index

If you're working with spatial data, such as latitude and longitude, you might be able to get a speed boost with spatial indexes.  Be careful with the spatial functions though; if you're not using MySQL 5.6 or MariaDB 5.5 (or higher), the functions that test relationships between polygons operate in "minimum bounding rectangle" mode.  Spatial indexes are only available in the MyISAM storage engine, although spatial data can be stored in InnoDB.  In order to protect your data, I would recommend storing your data in InnoDB, and creating a cache table in MyISAM for doing spatial lookups on.

Roll Your Own Index

Sometimes, it makes sense to create your own indexing system.  I'm not talking about hacking one into the MySQL source code, but rather finding other ways to index your data.  In a thread on the MySQL mailing list from 2001, I discussed the benefits of using a separately calculated hash column to index a column containing URLs.  The crux of it is that you don't want the length of your indexes to be too long, because you want MySQL to be able to fit all of your indexes in memory.  It's also more expensive to write longer indexes to disk.

At another company, we found that as fast as spatial indexes are, integer b-tree indexes are faster.  We rounded our latitude and longitude and put them together to create a grid, and then we were able to just request the items in a list of grid squares.  We also included the more precise fields in our where clause, in order to avoid getting extra points back from our query.

So if the default index types aren't working for you, try thinking outside the box.  There may be a solution that nobody else has thought of before, that will increase your index speed substantially.

Redundant Indexes

One of the more egregious forms of "too many indexes" is redundant indexes.  For example, let us consider the following table:

MariaDB [test]> desc presentations;
+---------------+-----------------------+------+-----+---------+----------------+
| Field         | Type                  | Null | Key | Default | Extra          |
+---------------+-----------------------+------+-----+---------+----------------+
| id            | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| ts            | datetime              | NO   | MUL | NULL    |                |
| room          | mediumint(8) unsigned | NO   |     | NULL    |                |
| ss_presid     | int(10) unsigned      | NO   |     | NULL    |                |
| first_name    | varchar(255)          | NO   |     | NULL    |                |
| last_name     | varchar(255)          | NO   |     | NULL    |                |
| title         | varchar(255)          | NO   |     | NULL    |                |
| len_min       | mediumint(8) unsigned | NO   |     | NULL    |                |
| votes         | int(10) unsigned      | NO   |     | NULL    |                |
| timesid       | int(10) unsigned      | NO   |     | NULL    |                |
| num_conflicts | int(10) unsigned      | NO   |     | NULL    |                |
+---------------+-----------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

Notice that the "Key" column doesn't really give us a ton of information.  We know that "ts" has multiple indexes, but nothing more than that.  As an aside, MySQL uses the terms "key" and "index" interchangeably.  To get better information about our keys or indexes, use the "SHOW KEYS FROM" query:

MariaDB [test]> SHOW KEYS FROM presentations;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| presentations |          0 | PRIMARY  |            1 | id          | A         |          84 |     NULL | NULL   |      | BTREE      |         |               |
| presentations |          0 | ts_room  |            1 | ts          | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| presentations |          0 | ts_room  |            2 | room        | A         |          84 |     NULL | NULL   |      | BTREE      |         |               |
| presentations |          1 | ts       |            1 | ts          | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

You'll notice that this is much more informative than the DESC command, at least as far as indexes are concerned.  The format takes some getting used to, though.  Use the "Key_name" column to determine which key we're talking about, as indexes can cross multiple columns.

Knowing that, we can tell that there are three keys: "PRIMARY", "ts_room", and "ts".  The "ts_room" index has two columns.  The "Seq_in_index" column tells us which comes first, although the "SHOW KEYS FROM" command will always return them in the proper order.

We have the primary key ("PRIMARY") which covers just the "id" column.  Primary keys are always unique indexes, which means that no two rows are allowed to have the same value for that key.  The "ts_room" index spans the "ts" and "room" columns, and is also unique.  Last, the "ts" index just covers the "ts" column, and is not unique.

So where is the redundant index?  First, we must understand how indexes work.  The left-most prefix of any index can be used by itself.  If we have an index on a varchar column, a SELECT query asking for rows with a value LIKE 'prefix%' will use the index on that column; a similar query looking for rows with a value LIKE '%suffix' will not use the index, since the left-most part is unspecified.

In multi-column indexes, this property also applies.  The columns in the index are processed in order, so a query on our presentations table with just the "room" specified would not be able to use the "ts_room" index.  However, a query with just the "ts" specified COULD use the "ts_room" index, since the "ts" is the leftmost part of it.

So now we've identified the redundant index: "ts" will never be needed, since its duties can be handled by "ts_room".  So, how do we get rid of it?

MariaDB [test]> ALTER TABLE presentations DROP INDEX ts;
Query OK, 84 rows affected (0.01 sec)              
Records: 84  Duplicates: 0  Warnings: 0

When too many indexes can be a bad thing

Many web developers I've worked with believe that the solution to every database slowness problem is to add an index.  This is due to an incomplete understanding of how databases use indexes.  The explanation is fairly simple, so this will be a short post.

Any time you insert a new row into a table, or delete a row, all indexes will need to be updated.  If you update a row, only the affected indexes will need to be updated -- in some cases, that may still be all of them.  Thus, while too few indexes can slow down your read queries, too many indexes can slow down your write queries.  Depending on the workload for that table, this may not be an issue.  In fact, there are cases where it makes sense to have the space required for a table's indexes exceed the size of the table data.

In many cases, though, I've seen indexes that are unlikely to ever be used.  I've also seen the same index added multiple times, probably by different developers.  It's helpful to know what commands can be used to determine what indexes already exist, and how useful they are.

SHOW INDEXES FROM times;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| times |          0 | PRIMARY  |            1 | id          | A         |         120 |     NULL | NULL   |      | BTREE      |         |               |
| times |          1 | ts       |            1 | ts          | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| times |          1 | room     |            1 | room        | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| times |          1 | size     |            1 | size        | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

The SHOW INDEXES FROM command can be used to see what indexes already exist on a table.  This will help you determine when to add new keys.  You should also use the EXPLAIN command to determine if your index is being used.  You will need a sample SELECT query.  You can use the MariaDB site's Explain Analyzer to better understand the output of the EXPLAIN command.

If you are using a graphical SQL query tool, you'll need to consult the documentation for your tool to determine how to view indexes and explain your queries.

Intentionally lagging a MySQL slave

pt-slave-delay is another great utility in the Percona Toolkit.  Percona recently published a quick guide to using it, including how it can be helpful when somebody screws up.

http://www.mysqlperformanceblog.com/2012/09/11/how-to-lag-a-slave-behind-to-avoid-a-disaster

Visualization tools for pt-query-digest

There's a great post over at the MySQL Performance Blog about visualization tools for pt-query-digest.  If you haven't heard of the Percona Toolkit, you should get to know more about it.  It is the successor to Maatkit and Aspersa, and is a great collection of utilities for MySQL database administrators.  pt-query-digest can parse the slow query log, the general query log, the binlog, TCP dump files of the MySQL protocol, and even PostgreSQL, memcached, and HTTP packet dumps.  It will aggregate the queries it finds into similar queries where possible, and has several report formats.  It's a great tool for finding out what queries are really going to your database, so you can find and remove bottlenecks.

There are lots of other great tools in the Percona Toolkit, which I'll discuss in later posts.

The Explain Analyzer

If you're having trouble reading the output of the EXPLAIN command in MySQL, just copy and paste it into the Explain Analyzer hosted over at the MariaDB site.  You then click on the various fields displayed, and it will explain what they mean.

For those who don't know, MariaDB is a fork of MySQL.  The project is headed by Monty Widenius, the original creator of MySQL.