MySQL Troubleshooting (2012)

---

^[5^] We will discuss in great detail how to troubleshoot replication failures in Chapter 5, so I’m skipping the explanation here.

Slow Queries

One common issue with SQL applications is performance regression. In this section, I’ll describe some basic actions to take when you hit performance problems. Don’t worry about the details; just pick up the essential ideas. As your knowledge deepens, you’ll find yourself using them in a more educated and therefore more effective manner.

When I considered which problems to include in this chapter, I questioned whether I should mention performance problems at all. There are a lot of sources describing performance issues in detail, starting with the wonderful “Optimization” chapter in the MySQL Reference Manual and extending to books published by O’Reilly. I will add a short overview of useful sources at the end of this book. One can easily spend one’s career on this subject or drown in the flood of available information.

I will be speaking here mostly about SELECT queries. At the end of this section, I briefly address how to deal with a slow query that modifies data.

Three main techniques will help you work on slow queries: tuning the query itself, tuning your tables (including adding indexes), and tuning the server. Let’s look at them in detail.

Tuning a Query with Information from EXPLAIN

The most powerful tool for query tuning is our old acquaintance EXPLAIN. This statement provides information about how the server actually executes a query. Details of MySQL EXPLAIN are covered quite well in the MySQL Reference Manual, and I will not repeat that information here. Rather, I’ll pick out what I’ve found to be the most important and useful elements of the output.

The first lines you should look at are type, which actually shows the type of join performed, and rows, which shows an estimate of how many rows were examined during the query. (For instance, if the query had to scan the whole table, the number of rows would equal the number of rows in the table.) Multitable joins have to examine a Cartesian product of the number of rows examined in each table. Thus, if the query examines 20 rows in one table and 30 in another, the join performs a total of 600 examinations. EXPLAIN will contain a row for each table in the JOIN. We will see this in the following examples.

EXPLAIN reports a join even when you operate on a single table. This may sound a bit strange, but the MySQL optimizer internally treats any query as a join, even if it’s a join on one table.

Let’s look into the EXPLAIN output from the previous section again:

mysql> EXPLAIN EXTENDED SELECT count(*) FROM items WHERE id IN

(SELECT iid FROM items_links)\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: items

type: index

possible_keys: NULL

key: PRIMARY

key_len: 4

ref: NULL

rows: 10

filtered: 100.00

Extra: Using where; Using index

*************************** 2. row ***************************

id: 2

select_type: DEPENDENT SUBQUERY

table: items_links

type: index_subquery

possible_keys: iid,iid_2

key: iid

key_len: 5

ref: func

rows: 1

filtered: 100.00

Extra: Using index; Using where

2 rows in set, 1 warning (0.48 sec)

The number of examined rows is 10 times 1 because the subquery executes once for every row in the outer query. The type of the first query is index, which means that the whole index will be read. The type of the second query is index_subquery. This is an index lookup function that works similar to the ref type. So, in this example, the optimizer will read all index entries from the items table and one row from the items_links table for each of the ten matching rows found from items.

How can we find out if this is a good plan for the query? First, let’s repeat the query results and check how long the query actually took:

mysql> SELECT count(*) FROM items WHERE id IN (SELECT iid FROM items_links);

+----------+

| count(*) |

+----------+

| 4 |

+----------+

1 row in set (0.08 sec)

The MySQL server examined 10 rows and returned 4. How fast is it? To answer this question, let’s count the number of rows in both tables:

mysql> SELECT count(*) FROM items;

+----------+

| count(*) |

+----------+

| 10 |

+----------+

1 row in set (0.11 sec)

mysql> SELECT count(*) FROM items_links;

+----------+

| count(*) |

+----------+

| 6 |

+----------+

1 row in set (0.00 sec)

We have 10 rows in the items table, each with a unique ID. The items_links table has 6 rows with nonunique IDs (iid). For the current amount of rows, the plan looks good, but at the same time, it shows a potential problem. Right now we have fewer links than items. Currently the difference in these numbers is not high, but it will be more noticeable if their numbers diverge.

To test this guess and to show you an example of query tuning, I will insert a few rows into the items table. The id is defined as INT NOT NULL AUTO_INCREMENT PRIMARY KEY, so we are guaranteed that no existing link will refer to a newly inserted row. This allows us to imitate a realistic situation that arises when a user needs to get a small number of links (six in our case) from a large table. The following statements are just a quick hack to create a lot of rows by repeatedly selecting all the rows in the table and inserting more:

mysql> INSERT INTO items( short_description , description,

example, explanation, additional) SELECT short_description , description,

example, explanation, additional FROM items;

Query OK, 10 rows affected (0.17 sec)

Records: 10 Duplicates: 0 Warnings: 0

<Repeat this query few times>

mysql> INSERT INTO items( short_description , description,

example, explanation, additional) SELECT short_description , description,

example, explanation, additional FROM items;

Query OK, 2560 rows affected (3.77 sec)

Records: 2560 Duplicates: 0 Warnings: 0

Now let’s see whether our query plan changed:

mysql> EXPLAIN EXTENDED SELECT count(*) FROM items WHERE id IN

-> (SELECT iid FROM items_links)\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: items

type: index

possible_keys: NULL

key: PRIMARY

key_len: 4

ref: NULL

rows: 5136

filtered: 100.00

Extra: Using where; Using index

*************************** 2. row ***************************

id: 2

select_type: DEPENDENT SUBQUERY

table: items_links

type: index_subquery

possible_keys: iid,iid_2

key: iid

key_len: 5

ref: func

rows: 1

filtered: 100.00

Extra: Using index; Using where

2 rows in set, 1 warning (0.09 sec)

The query execution plan is the same—and it is going to examine 5,136 rows for just six links! Is there any way to rewrite the query so it will execute faster?

The type of subquery is index_subquery. This means that the optimizer uses an index lookup function that replaces the subquery completely. The output from SHOW WARNINGS shows how the query has been rewritten:

mysql> SHOW WARNINGS\G

*************************** 1. row ***************************

Level: Note

Code: 1003

Message: select count(0) AS `count(*)` from `collaborate2011`.`items` where

<in_optimizer>(`collaborate2011`.`items`.`id`,<exists>

(<index_lookup>(<cache>(`collaborate2011`.`items`.`id`) in

items_links on iid where (<cache>(`collaborate2011`.`items`.`id`) =

`collaborate2011`.`items_links`.`iid`))))

1 row in set (0.00 sec)

The output is intimidating, but at least we can see some kind of join here. What if we rewrite the query to be more explicit about the columns on which the join is performed? We will also rewrite the subquery into an explicit JOIN; with current versions of MySQL, this method can dramatically improve performance:

mysql> \W

Show warnings enabled.

mysql> EXPLAIN EXTENDED SELECT count(*) FROM items JOIN

items_links ON (items.id=items_links.iid)\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: items_links

type: index

possible_keys: iid,iid_2

key: iid_2

key_len: 5

ref: NULL

rows: 6

filtered: 100.00

Extra: Using index

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: items

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: collaborate2011.items_links.iid

rows: 1

filtered: 100.00

Extra: Using index

2 rows in set, 1 warning (0.05 sec)

Note (Code 1003): select count(0) AS `count(*)` from `collaborate2011`.`items`

join `collaborate2011`.`items_links` where (`collaborate2011`.`items`.`id` =

`collaborate2011`.`items_links`.`iid`)

The result looks encouraging because it does not scan all the rows from the items table. But does the query work correctly?

mysql> SELECT count(*) FROM items JOIN items_links ON

(items.id=items_links.iid);

+----------+

| count(*) |

+----------+

| 6 |

+----------+

1 row in set (0.10 sec)

We get six rows instead of four. This is because we asked the query to return all matches, even when the same link was in two matches. We can fix this by adding the DISTINCT keyword:

mysql> SELECT count(distinct items.id) FROM items JOIN items_links ON

(items.id=items_links.iid);

+--------------------------+

| count(distinct items.id) |

+--------------------------+

| 4 |

+--------------------------+

1 row in set (0.12 sec)

NOTE

You can use a query rewriting technique to confirm that DISTINCT is needed. Just replace count(*) with items.id to see the duplicate values.

With DISTINCT, is the query still fast? Let’s try EXPLAIN once again:

mysql> EXPLAIN EXTENDED SELECT count(distinct items.id) FROM items

JOIN items_links ON (items.id=items_links.iid)\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: items_links

type: index

possible_keys: iid,iid_2

key: iid_2

key_len: 5

ref: NULL

rows: 6

filtered: 100.00

Extra: Using index

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: items

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: collaborate2011.items_links.iid

rows: 1

filtered: 100.00

Extra: Using index

2 rows in set, 1 warning (0.00 sec)

Note (Code 1003): select count(distinct `collaborate2011`.`items`.`id`) AS

`count(distinct items.id)` from `collaborate2011`.`items` join

`collaborate2011`.`items_links` where (`collaborate2011`.`items`.`id` =

`collaborate2011`.`items_links`.`iid`)

It still examines six rows. So we can consider the query to be optimized for this particular data set. I will explain later in this chapter why the structure of the data and its size matter.

In our example, the data set was small, so I could not make it run really slowly, even on my laptop. Still, the execution times of the original and optimized queries differ substantially. This was the original query:

mysql> SELECT count(*) FROM items WHERE id IN (SELECT iid FROM

items_links );

+----------+

| count(*) |

+----------+

| 4 |

+----------+

1 row in set (0.21 sec)

And here is the optimized query:

mysql> SELECT count(distinct items.id) FROM items JOIN items_links

ON (items.id=items_links.iid);

+--------------------------+

| count(distinct items.id) |

+--------------------------+

| 4 |

+--------------------------+

1 row in set (0.10 sec)

We achieved a two-fold improvement, even for such a small data set! For our test, this is just 0.11 sec, but for millions of rows, the improvement can be way better.

§ You just learned a basic query tuning technique with the help of EXPLAIN: reading information about what currently happens and comparing it to what you wish to reach. A similar process can tune any query, from the simplest to the most complex.

Table Tuning and Indexes

In the previous section, we introduced the process of tuning queries. In all the examples, EXPLAIN output contained information about indexes. But what if a table has no index at all? Or if the indexes are not being used? How should you choose when, where, and which index to add?

The MySQL server uses indexes when results can be limited. Therefore, having indexes on columns listed in WHERE, JOIN, and GROUP BY can speed up queries. Having an index on the column in an ORDER BY clause can make sense as well because it will allow the server to do more effective sorting.

With those principles in mind, adding indexes becomes a simple task. Consider tables from the previous example, but without any indexes:

mysql> CREATE TEMPORARY TABLE items SELECT * FROM items;

Query OK, 5120 rows affected (6.97 sec)

Records: 5120 Duplicates: 0 Warnings: 0

mysql> CREATE TEMPORARY TABLE items_links SELECT * FROM items_links;

Query OK, 6 rows affected (0.36 sec)

Records: 6 Duplicates: 0 Warnings: 0

mysql> SHOW CREATE TABLE items;

+-------+----------------------------------------------------------------------+

| Table | Create Table |

+-------+----------------------------------------------------------------------+

| items | CREATE TEMPORARY TABLE `items` (

`id` int(11) NOT NULL DEFAULT '0',

`short_description` varchar(255) DEFAULT NULL,

`description` text,

`example` text,

`explanation` text,

`additional` text

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+----------------------------------------------------------------------+

1 row in set (0.10 sec)

mysql> SHOW CREATE TABLE items_links;

+-------------+----------------------------------------------------------------+

| Table | Create Table |

+-------------+----------------------------------------------------------------+

| items_links | CREATE TEMPORARY TABLE `items_links` (

`iid` int(11) DEFAULT NULL,

`linkid` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------------+----------------------------------------------------------------+

1 row in set (0.00 sec)

As you can see, no index is specified. Let’s try an unoptimized query on these tables, and then improve it:

mysql> EXPLAIN EXTENDED SELECT count(distinct items.id) FROM items JOIN

items_links ON (items.id=items_links.iid)\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: items_links

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 6

filtered: 100.00

Extra:

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: items

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 5137

filtered: 100.00

Extra: Using where; Using join buffer

2 rows in set, 1 warning (0.00 sec)

Note (Code 1003): select count(distinct `collaborate2011`.`items`.`id`) AS

`count(distinct items.id)` from `collaborate2011`.`items` join

`collaborate2011`.`items_links` where (`collaborate2011`.`items`.`id` =

`collaborate2011`.`items_links`.`iid`)

The type became ALL, the most expensive type, because it means all rows will be read. The query is examining 6*5,137 = 30,822 rows now. This is even worse than the query we considered to be slow in our earlier example.

Let’s examine the query in detail:

SELECT count(distinct items.id)...

This query returns the number of unique not-null values in the result set. It would make sense to add an index on items.id, so that this search could use it.

Another part of the same query:

...FROM items JOIN items_links ON (items.id=items_links.iid)

The join refers to the id column from items and the iid column from items_links. So it makes sense to add indexes to both of those columns.

mysql> ALTER TABLE items ADD INDEX(id);

Query OK, 5120 rows affected (4.78 sec)

Records: 5120 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE items_links ADD INDEX(iid);

Query OK, 6 rows affected (0.04 sec)

Records: 6 Duplicates: 0 Warnings: 0

Now we can see how this affects the query plan:

mysql> EXPLAIN EXTENDED SELECT count(distinct items.id) FROM items

JOIN items_links ON (items.id=items_links.iid)\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: items_links

type: index

possible_keys: iid

key: iid

key_len: 5

ref: NULL

rows: 6

filtered: 100.00

Extra: Using index

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: items

type: ref

possible_keys: id

key: id

key_len: 4

ref: collaborate2011.items_links.iid

rows: 1

filtered: 100.00

Extra: Using index

2 rows in set, 1 warning (0.00 sec)

Note (Code 1003): select count(distinct `collaborate2011`.`items`.`id`) AS

`count(distinct items.id)` from `collaborate2011`.`items` join

`collaborate2011`.`items_links` where (`collaborate2011`.`items`.`id` =

`collaborate2011`.`items_links`.`iid`)

This looks much better than before, with a single exception: the ref type for table items is worse than the eq_ref we got in the previous section. This type is used because we added a simple index, whereas the original table had a unique index on the same column. We can easily change the temporary table too, because IDs are unique and are supposed to be so:

mysql> EXPLAIN EXTENDED SELECT count(distinct items.id) FROM items

JOIN items_links ON (items.id=items_links.iid)\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: items_links

type: index

possible_keys: iid

key: iid

key_len: 5

ref: NULL

rows: 6

filtered: 100.00

Extra: Using index

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: items

type: eq_ref

possible_keys: id_2,id

key: id_2

key_len: 4

ref: collaborate2011.items_links.iid

rows: 1

filtered: 100.00

Extra: Using index

2 rows in set, 1 warning (0.00 sec)

Note (Code 1003): select count(distinct `collaborate2011`.`items`.`id`) AS

`count(distinct items.id)` from `collaborate2011`.`items` join

`collaborate2011`.`items_links` where (`collaborate2011`.`items`.`id` =

`collaborate2011`.`items_links`.`iid`)

Now, when the faster-executing type eq_ref is used, we can drop the redundant index on items.id. This is especially important if you care about the speed of queries that modify data because updating each index takes time. We will discuss when query tuning should be stopped in following section.

You just learned how indexes affect query execution and when it makes sense to add them.

CHOOSING YOUR OWN EXECUTION PLAN

There are cases when an index actually slows down a query. In this case, you can drop the index or use an IGNORE INDEX clause if you need the index for other queries. You can also use FORCE INDEX to let the optimizer know which index you want to use. These clauses can also be very useful for query tuning when you want to see how a particular index can affect performance. Just tryEXPLAIN using the clauses, and analyze the output.

Although use of IGNORE INDEX and FORCE INDEX may sound appealing, you should avoid using them in production when possible, unless you are ready to examine every query with such a clause during each following upgrade.

This is required because the optimizer always tries to choose the best plan for execution. Improving from version to version, it can create different plans for the same JOINs. When you don’t force or ignore an index, it will just create a plan as best as it can. But if you explicitly specify how it should use indexes in one of the tables from a multitable JOIN, such a rule can affect others, and the final execution plan could change in the new version to something worse than it was before.

Using IGNORE INDEX and FORCE INDEX in queries that access only one table is relatively safe. In all other cases, you must check to make sure the query execution plan was not changed during the upgrade.

Another problem with using IGNORE INDEX and FORCE INDEX in production is that the best execution plan for particular tables depends on the data they store. In normal operation, the optimizer checks table statistics and adjusts the plan as necessary, which it can’t do when you use IGNORE INDEX and FORCE INDEX. If you use these clauses, you must regularly check whether they are still effective throughout the application’s life.

When to Stop Optimizing

The previous sections discussed simple queries. Even there we found ways to improve the queries, sometimes with increasingly better results over several iterations of tuning. When you work with complicated queries with a lot of JOIN conditions, a lot of columns in WHERE clauses, andGROUP BY clauses, you have even more choices. It’s possible to imagine that you could always find a way to make performance even faster and that these improvements can continue forever. So the question is when to consider a query properly optimized and put a stop to the research.

An in-depth knowledge of performance techniques could help you choose the proper solution. But there are still basic considerations that can help you to stop, even if you don’t consider yourself an expert.

First, you need to find out what the query does. For example, the following query:

SELECT * FROM contacts

always returns all columns and rows from the table, and no optimization can be applied to it.

But even if you extract all columns, adding a JOIN can change the situation:

SELECT * FROM child JOIN parent ON (child.pid=parent.id)

This can be optimized because the ON condition limits the result set. The same analysis can be applied to queries with WHERE and GROUP BY conditions.

The second thing you need to look at is the join type from the EXPLAIN output. Although you will be trying to get the best possible JOIN types, keep in mind the limitations of your data. For example, a condition on a nonunique row can never lead to types eq_ref or better.

Your data is very important when you optimize queries. Differences in data can lead to completely different results for the same execution plan. The most trivial example is to compare results for a single row in a table to results for a table where more than 50% of the rows have the same value. In these cases, using indexes can decrease performance rather than increase it.

§ Here is another rule: do not rely only on the EXPLAIN output; make sure to measure the actual query execution time.

Another thing you should keep in mind is the effect of indexes on changes to the table. Although indexes usually improve the speed of SELECT queries, they slightly decrease the speed of queries that modify data, especially INSERT statements. Therefore, it can sometimes be sensible to live with slow SELECT queries if it speeds up the execution of inserts. Always keep in mind the overall performance of your application, not just a single query.

Effects of Options

Suppose you have completely optimized your query and can’t find any ways to tune it better, but it’s still slow. Can you do anything to improve its performance? Yes. There are server options that allow you to tune factors affecting the query, such as the size of temporary tables in memory, buffers for sorting, and so on. Some options specific to a particular storage engine, such as InnoDB, can also be useful for query optimizing.

I will describe these configuration options in more detail in Chapter 3. Here I’ll give an overview of how to use them to improve performance.

Tuning server options is a somewhat global activity because a change can potentially affect every query on the server (or in the case of engine-specific options, every query that refers to a table using that storage engine). But some options are used for particular kinds of optimization, and if your query does not meet the right conditions, it remains unaffected.

The first options to check are buffer sizes. Each memory buffer is allocated for specific reasons. The general rule is that large buffers mean higher performance—but only if the query can use the larger size for the particular role played by that buffer.

And of course there are trade-offs when you increase buffer sizes. Here are some of the issues large buffers can cause. I don’t want to dissuade you from setting large buffers, because under the right circumstances it’s a great way to improve performance significantly. You just need to keep the following issues in mind and adjust sizes reasonably.

Swapping

A large buffer may lead to swapping at the operating-system level and therefore slow performance, depending on the size of RAM on your system. In general, the MySQL server works fast if all the memory it needs sits in physical RAM. When it starts swapping, performance degrades dramatically.

Swapping can happen when you allocate more memory to buffers than your server has physically in RAM. Please note that some buffers are allocated for each user thread. To determine how much memory the server allocates for such buffers, use the formula max_connections *buffer_size. Calculate the sum of this product for all buffers, and make sure it is less than the amount of memory the mysqld server can use. This calculation is not decisive, because mysqld can actually allocate more memory than you explicitly specify.

Startup time

The more memory mysqld needs to allocate, the longer it takes to start.

Stale data

There are also scaling issues, mostly for caches shared between threads. Scaling the buffers that do the caching in these cases can lead to memory fragmentation. You will generally notice the fragmentation problem after hours of running the server, when old data needs to be removed from the buffer in order to make room for new data. This can cause a fast server to suddenly slow down. I show this in an example in Chapter 3.

After finishing with buffers, consider other options discussed in Chapter 3. Pay attention not only to options explicitly designated for performance tuning, such as optimizer options, but also to options that control high availability. The safer you make a transaction, the more checks are needed and the more slowly the query executes. But be careful with such options; tune them when and only when you can sacrifice safety for performance.

When you tune options, it is especially important to consider performance as a whole, because every option affects the whole server. For example, there is no sense in tuning engine-specific options if you don’t use that engine. This might appear too obvious, but I have seen way too many installations where MyISAM options were huge when only the InnoDB storage engine was used, or vice versa. This is especially important to remember if you take some generic configuration as a template.

The MySQL server allows you to change most of its configuration dynamically. This is valuable for performance testing. Change options and rerun queries to make sure you get good results before applying the changes in the configuration file. It is also always a good idea to apply changes step by step, so that you can attribute bad effects to the right option and easily go back. We will discuss this technique in detail in Chapter 6.

Queries That Modify Data

We discussed effects on the performance of SELECT queries, and in this section, we turn to tuning queries that modify data. UPDATE and DELETE queries can use the same conditions as SELECT to limit the number of rows affected. Therefore, the same rules for tuning can be applied to these queries.

We saw in When the Problem May Have Been a Previous Update how to convert UPDATE and DELETE queries to SELECT queries and run EXPLAIN on them. You can use this technique to troubleshoot performance issues on versions prior to 5.6.3, which introduced EXPLAIN for INSERT,UPDATE, and DELETE queries, but remember that UPDATE and DELETE are sometimes executed slightly differently from the corresponding SELECT.

Check whether indexes were used by querying the Handler_% status variables before and after query execution:

mysql> SHOW STATUS LIKE 'Handler_%';

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| Handler_commit | 0 |

| Handler_delete | 0 |

| Handler_discover | 0 |

| Handler_prepare | 0 |

| Handler_read_first | 0 |

| Handler_read_key | 0 |

| Handler_read_last | 0 |

| Handler_read_next | 0 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 19 |

| Handler_rollback | 0 |

| Handler_savepoint | 0 |

| Handler_savepoint_rollback | 0 |

| Handler_update | 0 |

| Handler_write | 17 |

+----------------------------+-------+

16 rows in set (0.00 sec)

Here I used SHOW STATUS, which is a synonym of SHOW SESSION STATUS and shows the status variables for the current session.

NOTE

It is convenient to reset these variables before testing using a FLUSH STATUS query.

We’ll talk about particular variables in the preceding list as we continue. You should be aware, though, that these are cumulative values, so they increase as you issue each query. Now let’s tune our example query from When the Problem May Have Been a Previous Update so that it will update null-able columns:

mysql> UPDATE items SET description = 'no description', additional

= 'no additional comments' WHERE description IS NULL;

Query OK, 0 rows affected (6.95 sec)

Rows matched: 0 Changed: 0 Warnings: 0

This changed no rows because we corrupted the data in an earlier step: we have 0 in each field instead of NULL now. But the query runs very slowly. Let’s look at the handler variables:

mysql> show status like 'Handler_%';

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| Handler_commit | 1 |

| Handler_delete | 0 |

| Handler_discover | 0 |

| Handler_prepare | 0 |

| Handler_read_first | 1 |

| Handler_read_key | 2 |

| Handler_read_last | 0 |

| Handler_read_next | 0 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 5140 |

| Handler_rollback | 0 |

| Handler_savepoint | 0 |

| Handler_savepoint_rollback | 0 |

| Handler_update | 0 |

| Handler_write | 17 |

+----------------------------+-------+

16 rows in set (0.01 sec)

What jumps out is the high value of Handler_read_rnd_next, which shows how often the next row in a datafile was read. A high value typically means that a table scan was used, which is not good for performance. Handler_read_key is a related variable showing the number of index read requests. It shouldn’t be so low in relation to Handler_read_rnd_next, because that means a lot of rows were read instead of using an index. In addition, the values of Handler_commit and Handler_read_first have increased slightly. These refer respectively to the number of commits and the number of times the first entry in an index was read. Finally, the unobtrusive 1 in Handler_read_first shows that we asked the server to read a first entry in the index, which can be a symptom of a full index scan.

Hopefully, this run-through of a few Handler_% status variables has shown you how they can be used to check how queries are running. I’ll leave the question of whether the speed of this query can be improved as homework for you.

I’ll just spend a little space on INSERT queries. They have no conditions that limit the set of rows affected, so the presence of indexes in a table merely slows them down because each insert has to update the indexes. The performance of inserts should be tuned using server options. Here especially, the options offered by the InnoDB storage engine can be helpful.

One way to speed up inserts is to combine many of them in one statement, also called “bulk insert”:

insert into t1 (f1, f2, f3, ...) values (v1, v2, v3, ...), (v1, v2, v3, ...), ...

But please note that inserts block table rows or even whole tables, so other queries are denied access while the insert runs. So I’ll end this section by repeating the general rule:

§ Keep the performance of the whole application in mind while tuning any single query.

No Silver Bullet

We just learned how tuning server options can dramatically improve performance. We’ve also seen in this chapter how to tune a particular query so it runs really fast. Tuning a query and tuning a server are often alternative solutions to performance problems. Is there any general rule about which kind of tuning you should start with?

I am afraid not. Tuning server options looks so promising that many people think finding and changing the right option will make mysqld run like a rocket. If you believe that too, I have to disappoint you: badly written queries will still be a drain on server resources. And you may enjoy good performance for a few hours after restarting the server only to see it decrease again, because every query will need a lot of resources and your caches will fill up. At times, the server will be inundated with millions of queries that want more and more resources.

However, tuning every single query might not be an option. Some of them are called rarely, so there is no need to spend human resources on them. Others may query all the rows in a table, which defeats attempts to optimize them.

I usually advocate some kind of “mixed” mode. Tune server options first, paying particular attention to options specific to your storage engine, then tune queries. After the important queries are tuned, go back to the server options and consider what you can tune more, then back to the rest of the queries, and so on, until you are happy with performance.

You can also start with the slowest queries in your application or find ones that can obviously benefit from trivial optimizations, then turn to server options. Consult the status variables, as shown earlier. I will describe them in more detail in Chapter 6.

Last but not least: use numerous information sources on performance tuning to create your own strategy.

When the Server Does Not Answer

Occasionally a MySQL client gets the catastrophic message "Lost connection to server during query" or "Server has gone away." Although I hope you will never face this problem, it’s good to be prepared in case it happens. There are two main reasons for this problem caused by the MySQL installation itself: server issues (most likely a crash) or the misuse of connection options (usually timeout options or max_allowed_packet).

We will discuss the configuration of connections in Chapter 3. Problems caused by hardware and third-party software will be touched on in Chapter 4. Here I want to briefly describe what to do if the server crashes.

First, determine whether you really had a server crash. You can do this with the help of process status monitors. If you run mysqld_safe or another daemon that restarts the server after a failure, the error log will contain a message indicating the server has been restarted. When mysqld starts, it always prints something like this to the error logfile:

110716 14:01:44 [Note] /apps/mysql-5.1/libexec/mysqld: ready for connections.

Version: '5.1.59-debug' socket: '/tmp/mysql51.sock' port: 3351 Source distribution

So if you find such a message, the server has been restarted. If there is no message and the server is up and running, a lost connection is most likely caused by the misuse of connection options, which will be discussed in Chapter 3.

NOTE

If you remember when your MySQL server was originally started, you can use the status variable uptime, which shows the number of seconds since the server started:

mysql> SHOW GLOBAL STATUS LIKE 'uptime';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Uptime | 10447 |

+---------------+-------+

1 row in set (0.00 sec)

This information will also help when you want to check whether mysqld failure was not caused by an operating system restart. Just compare the value of this variable with the operating system uptime.

The reason why I rely on error logfiles comes from my job experience with cases when customers notice a problem hours after a server crash and even some time after a planned mysqld restart.

If you confirm that the server has restarted, you should examine the error log again and look for information about the crash itself. Usually you can derive enough information about the crash from the error log to avoid the same situation in the future. We’ll discuss how to investigate the small number of difficult cases you may encounter in Chapter 6. Now let’s go back to the error logfile and see examples of its typical contents in case of a server crash. I’ll list a large extract here:

Version: '5.1.39' socket: '/tmp/mysql_sandbox5139.sock' port: 5139

MySQL Community Server (GPL)

091002 14:56:54 - mysqld got signal 11 ;

This could be because you hit a bug. It is also possible that this binary

or one of the libraries it was linked against is corrupt, improperly built,

or misconfigured. This error can also be caused by malfunctioning hardware.

We will try our best to scrape up some info that will hopefully help diagnose

the problem, but since we have already crashed, something is definitely wrong

and this may fail.

key_buffer_size=8384512

read_buffer_size=131072

max_used_connections=1

max_threads=151

threads_connected=1

It is possible that mysqld could use up to

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338301 K

bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

thd: 0x69e1b00

Attempting backtrace. You can use the following information to find out

where mysqld died. If you see no messages after this, something went

terribly wrong...

stack_bottom = 0x450890f0 thread_stack 0x40000

/users/ssmirnova/blade12/5.1.39/bin/mysqld(my_print_stacktrace+0x2e)[0x8ac81e]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(handle_segfault+0x322)[0x5df502]

/lib64/libpthread.so.0[0x3429e0dd40]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN6String4copyERKS_+0x16)[0x5d9876]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Item_cache_str5storeEP4Item+0xc9)

[0x52ddd9]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN26select_singlerow_subselect9send_

dataER4ListI4ItemE+0x45)

[0x5ca145]

/users/ssmirnova/blade12/5.1.39/bin/mysqld[0x6386d1]

/users/ssmirnova/blade12/5.1.39/bin/mysqld[0x64236a]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN4JOIN4execEv+0x949)[0x658869]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN30subselect_single_select_

engine4execEv+0x36c)[0x596f3c]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Item_subselect4execEv+0x26)[0x595d96]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN24Item_singlerow_subselect8val_

realEv+0xd)[0x595fbd]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Arg_comparator18compare_real_

fixedEv+0x39)[0x561b89]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN12Item_func_ne7val_intEv+0x23)[0x568fb3]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN4JOIN8optimizeEv+0x12ef)[0x65208f]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_

LISTjR4ListIS1_ES2_jP8

st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xa0)

[0x654850]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z13handle_selectP3THDP6st_lexP13select_

resultm+0x16c)

[0x65a1cc]

/users/ssmirnova/blade12/5.1.39/bin/mysqld[0x5ecbda]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z21mysql_execute_commandP3THD+0x602)

[0x5efdd2]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x357)

[0x5f52f7]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z16dispatch_command19enum_server_

commandP3THDPcj+0xe93)

[0x5f6193]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z10do_commandP3THD+0xe6)[0x5f6a56]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(handle_one_connection+0x246)[0x5e93f6]

/lib64/libpthread.so.0[0x3429e061b5]

/lib64/libc.so.6(clone+0x6d)[0x34292cd39d]`)

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort...

thd->query at 0x6a39e60 = select 1 from `t1` where `c0` <>

(select geometrycollectionfromwkb(`c3`) from `t1`)

thd->thread_id=2

thd->killed=NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains

information that should help you find out what

The key line indicating the reason for the crash is:

091002 14:56:54 - mysqld got signal 11 ;

This means the MySQL server was killed after it asked the operating system for a resource (e.g., access to a file or RAM), getting an error with code 11. In most operating systems, this signal refers to a segmentation fault. You can find more detailed information in the user manual for your operating system. Run man signal for Unix and Linux. In Windows, similar cases will usually generate a log message like “mysqld got exception 0xc0000005.” Search the Windows user manual for the meanings of these exception codes.

The following is the excerpt from the log about a query that was running in the thread that crashed the server:

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort...

thd->query at 0x6a39e60 = SELECT 1 FROM `t1` WHERE `c0` <>

(SELECT geometrycollectionfromwkb(`c3`) FROM `t1`)

thd->thread_id=2

thd->killed=NOT_KILLED

To diagnose, rerun the query to see whether it was the cause of the crash:

mysql> SELECT 1 FROM `t1` WHERE `c0` <> (SELECT

geometrycollectionfromwkb(`c3`) FROM `t1`);

ERROR 2013 (HY000): Lost connection to MySQL server during query

NOTE

When I recommend repeating problems, I assume you will use the development server and not your production server. We will discuss how to safely troubleshoot in an environment dedicated to this purpose in Sandboxes. Please don’t try to repeat this example; it is based on a known bug #47780 that’s fixed in current versions. The fix exists since versions 5.0.88, 5.1.41, 5.5.0, and 6.0.14.

At this point, you have identified the source of the crash and confirmed this was really the source, but you have to rewrite the query so it does not cause a crash next time. Now we can get help from the backtrace that was printed in the log:

Attempting backtrace. You can use the following information to find out

where mysqld died. If you see no messages after this, something went

terribly wrong...

stack_bottom = 0x450890f0 thread_stack 0x40000

/users/ssmirnova/blade12/5.1.39/bin/mysqld(my_print_stacktrace+0x2e)[0x8ac81e]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(handle_segfault+0x322)[0x5df502]

/lib64/libpthread.so.0[0x3429e0dd40]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN6String4copyERKS_+0x16)[0x5d9876]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Item_cache_str5storeEP4Item+0xc9)

[0x52ddd9]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN26select_singlerow_subselect9

send_dataER4ListI4ItemE+0x45)

[0x5ca145]

/users/ssmirnova/blade12/5.1.39/bin/mysqld[0x6386d1]

/users/ssmirnova/blade12/5.1.39/bin/mysqld[0x64236a]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN4JOIN4execEv+0x949)[0x658869]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN30subselect_single_select_engine4execEv

+0x36c)[0x596f3c]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Item_subselect4execEv+0x26)[0x595d96]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN24Item_singlerow_subselect8val_realEv

+0xd)[0x595fbd]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Arg_comparator18compare_real_fixedEv

+0x39)[0x561b89]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN12Item_func_ne7val_intEv+0x23)[0x568fb3]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN4JOIN8optimizeEv+0x12ef)[0x65208f]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_

LISTjR4ListIS1_ES2_jP8

st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xa0)

[0x654850]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z13handle_selectP3THDP6st_lexP13select_

resultm+0x16c)

[0x65a1cc]

/users/ssmirnova/blade12/5.1.39/bin/mysqld[0x5ecbda]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z21mysql_execute_commandP3THD+0x602)

[0x5efdd2]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x357)

[0x5f52f7]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z16dispatch_command19enum_server_

commandP3THDPcj+0xe93)

[0x5f6193]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z10do_commandP3THD+0xe6)[0x5f6a56]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(handle_one_connection+0x246)[0x5e93f6]

/lib64/libpthread.so.0[0x3429e061b5]

/lib64/libc.so.6(clone+0x6d)[0x34292cd39d]`)

The relevant lines are the calls to Item_subselect and Item_singlerow_subselect:

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Item_subselect4execEv+0x26)[0x595d96]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN24Item_singlerow_subselect8val_realEv

+0xd)[0x595fbd]

How did I decide these were the culprits? In this case, I recognized the calls from my previous troubleshooting. But a good rule of thumb is to start from the top. These first functions are usually operating system calls, which can be relevant, but are of no help in these circumstances, because you cannot do anything with them, and then follow calls to the MySQL library. Examine these from top to bottom to find which ones you can affect. You can’t do anything with String4copy or Item_cache_str5store, for instance, but you can rewrite a subselect, so we’ll start from there.

Here, even without looking into the source code for mysqld, we can play around to find the cause of the crash. It’s a good guess that the use of a subquery is the problem because subqueries can be converted easily to JOIN. Let’s try rewriting the query and testing it:

mysql> SELECT 1 FROM `t1` WHERE `c0` <> geometrycollectionfromwkb(`c3`);

Empty set (0.00 sec)

The new query does not crash, so all you need to do is change the query in the application to match.

§ You just learned something important about MySQL troubleshooting: the first thing to check in case of an unknown error is the error logfile. Always have it turned on.

Here I want to add a note about bugs. When you are faced with a crash and identify the reason, check the MySQL bug database for similar problems. When you find a bug that looks like the one you hit, check whether it has been fixed, and if so, upgrade your server to the version where the bug was fixed (or a newer one). This can save you time because you won’t have to fix the problematic query.

If you can’t find a bug similar to one you hit, try downloading the latest version of the MySQL server and running your query. If the bug is reproducible there, report it. It’s important to use the latest versions of stable general availability (GA) releases because they contain all current bug fixes and many old issues won’t reappear. Chapter 6 discusses how to safely test crashes in sandbox environments.

Crashes can be caused not only by particular queries, but also by the environment in which the server runs. The most common reason is the lack of free RAM. This happens particularly when the user allocates huge buffers. As I noted before, mysqld always needs slightly more RAM than the sum of all buffers. Usually the error logfile contains a rough estimation of the RAM that can be used. It looks like this:

key_buffer_size=235929600

read_buffer_size=4190208

max_used_connections=17

max_connections=2048

threads_connected=13

It is possible that mysqld could use up to

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 21193712 K

-----

21193712K ~= 20G

Such estimations are not precise, but still worth checking. The one just shown claims that mysqld can use up to 20G RAM! You can get powerful boxes nowadays, but it is worth checking whether you really have 20G RAM.

Another issue in the environment is other applications that run along with the MySQL server. It is always a good idea to dedicate a server for MySQL in production because other applications can use resources that you expect MySQL to use. We will describe how to debug the effects of other applications on mysqld in Chapter 4.

Issues with Solutions Specific to Storage Engines

Actually, any problem this book discusses could have specific nuances based on the storage engine you use. We will touch upon these aspects throughout the book. In this section, I want to show a few features of storage engines that are independent of other problems. We’ll cover a few basic problems that use tools specific to MyISAM or InnoDB because these are the most popular and frequently used storage engines. If you use a third-party storage engine, consult its user manual for useful tools.

Errors related to a storage engine are either reported back to the client or recorded in the error logfile. Usually the name of the storage engine appears in the error message. In rare cases, you will get an error number not known by the perror utility. This is usually a symptom of an issue with a storage engine.

One common storage engine issue is corruption. This is not always the fault of the storage engine, but can have an external cause such as disk damage, a system crash, or a MySQL server crash. For example, if somebody runs kill -9 on the server’s process, she is almost asking for data corruption. We will discuss here what to do in case of MyISAM and InnoDB corruption. We will not discuss how to fix corruption of a third-party storage engine; consult its documentation for guidance. As a general recommendation, you can try CHECK TABLE, which many storage engines support. (CHECK TABLE for the MyISAM storage engine is explained in Repairing a MyISAM table from SQL.)

Corruption is a difficult problem to diagnose because the user might not notice it until the MySQL server accesses a corrupted table. The symptoms can also be misleading. In the best case, you will get an error message. However, the problem might be manifested by incorrect execution of queries or even a server shutdown. If problems crop up suddenly on a particular table, always check for corruption.

NOTE

Once you suspect corruption, you need to repair the corrupted table. It’s always a good practice to back up table files before doing a repair so you can go back if something goes wrong.

MyISAM Corruption

MyISAM stores every table as a set of three files: table_name.frm contains the table structure (schema), table_name.MYD contains the data, and table_name.MYI contains the index. Corruption can damage the datafile, the index file, or both. In such cases, you will get an error like"ERROR 126 (HY000): Incorrect key file for table './test/t1.MYI'; try to repair it" or "Table './test/t2' is marked as crashed and last (automatic?) repair failed" when you access the table. The error message can vary, but check for the words “repair” or “crashed” as a clue that the table is corrupted.

The SQL statements CHECK TABLE and REPAIR TABLE troubleshoot corruption. From the operating system shell, you can also used the myisamchk utility for the same purpose. One advantage of myisamchk is that you can use it without access to a running MySQL server. For instance, you can try to repair a table after a crash before bringing up the server again.

Repairing a MyISAM table from SQL

CHECK TABLE without parameters shows the current table status:

mysql> CHECK TABLE t2;

+---------+-------+----------+------------------------------------------------------+

| Table | Op | Msg_type | Msg_text |

+---------+-------+----------+------------------------------------------------------+

| test.t2 | check | warning | Table is marked as crashed and last repair failed |

| test.t2 | check | warning | Size of indexfile is: 1806336 Should be: 495616 |

| test.t2 | check | error | Record-count is not ok; is 780 Should be: 208 |

| test.t2 | check | warning | Found 780 key parts. Should be: 208 |

| test.t2 | check | error | Corrupt |

+---------+-------+----------+------------------------------------------------------+

5 rows in set (0.09 sec)

This is an example of output for a corrupted table. Your first resort is to run REPAIR TABLE without parameters:

mysql> REPAIR TABLE t2;

+---------+--------+----------+----------------------------------------+

| Table | Op | Msg_type | Msg_text |

+---------+--------+----------+----------------------------------------+

| test.t2 | repair | warning | Number of rows changed from 208 to 780 |

| test.t2 | repair | status | OK |

+---------+--------+----------+----------------------------------------+

2 rows in set (0.05 sec)

This time we were lucky and the table was repaired successfully. We can run CHECK TABLE again to confirm this:

mysql> CHECK TABLE t2;

+---------+-------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+---------+-------+----------+----------+

| test.t2 | check | status | OK |

+---------+-------+----------+----------+

1 row in set (0.02 sec)

If a simple REPAIR TABLE run does not help, there are two more options. REPAIR TABLE EXTENDED works more slowly than the bare REPAIR TABLE, but can fix 99% of errors. As a last resort, run REPAIR TABLE USE_FRM, which does not trust the information in the index file. Instead, it drops and then recreates the index using the description from the table_name.frm file and fills the key with values from the table_name.MYD file.

NOTE

For the very same purpose, you can use a utility named mysqlcheck. This program works by sending CHECK and REPAIR statements to the server. It also has very nice options, such as --all-databases, which can help you perform table maintenance effectively.

mysqlcheck connects to the MySQL server as any other client does, and thus can be used remotely.

Repairing a MyISAM table using myisamchk

All of these steps can also be performed using myisamchk, which has a lot of additional table maintenance options. I won’t describe all the features of the utility here, but instead concentrate on those specific to table repair.

myisamchk directly accesses table files and does not require the MySQL server to be started. This can be very useful in some situations. At the same time, myisamchk requires exclusive access to table files, and you should avoid using it when the MySQL server is running.

WARNING

If you have to use myisamchk while the server is running, issue the queries FLUSH TABLES and LOCK TABLE table_name WRITE, then wait until the latest query returns a command prompt, and then run myisamchk in a parallel session. If other processes besides myisamchk access the table while myisamchk is running, even worse corruption can occur.

A basic recovery command is:

$myisamchk --backup --recover t2

- recovering (with sort) MyISAM-table 't2'

Data records: 208

- Fixing index 1

- Fixing index 2

Data records: 780

The --backup option tells myisamchk to back up the datafile before trying to fix the table, and --recover does the actual repair. If this command is insufficient, you can use the --safe-recover option. The latter option uses a recovery method that has existed since very early versions of MySQL and can find issues that the simple --recover option cannot. An even more drastic option is --extend-check.

You can also use the option --sort-recover, which uses sorting to resolve the keys even when the temporary file is very large.

Among other options, which I recommend you study carefully, is the very useful --description option, which prints a description of the table. Taken together with -v or its synonym, --verbose, it will print additional information. You can specify the -v option twice or even three times to get more information.

InnoDB Corruption

InnoDB stores its data and indexes in shared tablespaces. If the server was started with the option --innodb_file_per_table at the moment of table creation, it also has its own datafile, but the table definition still exists in a shared tablespace. Understanding how table files are stored can help to effectively maintain the data directory and backups.

InnoDB is a transactional storage engine and has internal mechanisms that automatically fix most kinds of corruption. It does this recovery at server startup. The following excerpt from the error log, taken after a backup by MySQL Enterprise Backup (MEB) using the mysqlbackup --copy-back command, shows a typical recovery^[6^]:

InnoDB: The log file was created by ibbackup --apply-log at

InnoDB: ibbackup 110720 21:33:50

InnoDB: NOTE: the following crash recovery is part of a normal restore.

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

110720 21:37:15 InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

InnoDB: Last MySQL binlog file position 0 98587529, file name ./blade12-bin.000002

110720 21:37:15 InnoDB Plugin 1.0.17 started; log sequence number 1940779532

110720 21:37:15 [Note] Event Scheduler: Loaded 0 events

110720 21:37:15 [Note] ./libexec/mysqld: ready for connections.

Version: '5.1.59-debug' socket: '/tmp/mysql_ssmirnova.sock' port: 33051

Source distribution

But sometimes corruption is extremely bad and InnoDB cannot repair it without user interaction. For such situations, the startup option --innodb_force_recovery exists. It can be set to any value from 0 to 6 (0 means no forced recovery, 1 is the lowest level, and 6 is the highest level). When recovery is successful, you can run certain types of queries against the table that was repaired, but you’re prevented from issuing certain commands. You can’t issue operations that modify data, but the option still allows certain SELECT select statements, as well as DROP statements. At level 6, for instance, you can run only queries of the form SELECT * FROM table_name with no qualifying condition—no WHERE, ORDER BY, or other clauses.

In case of corruption, try each level of --innodb_force_recovery, starting from 1 and increasing, until you are able to start the server and query the problem table. Your prior investigation should have uncovered which table is corrupted. Dump it to a file using SELECT INTO OUTFILE, then recreate it using DROP and CREATE. Finally, restart the server with --innodb_force_recovery=0 and load the dump. If the problem persists, try to find other tables that are corrupted and go through the process until the server is fine again.

If you need to begin your repair of a database by using a positive value for --innodb_force_recovery, the error log often mentions it explicitly through messages such as this:

InnoDB: We intentionally generate a memory trap.

InnoDB: Submit a detailed bug report to http://bugs.mysql.com.

InnoDB: If you get repeated assertion failures or crashes, even

InnoDB: immediately after the mysqld startup, there may be

InnoDB: corruption in the InnoDB tablespace. Please refer to

InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html

InnoDB: about forcing recovery.

You will also find information there about unsuccessful automatic recovery and startup failure.

NOTE

InnoDB writes checksums for data, index, and log pages immediately before writing actual data, and confirms the checksums immediately after reading from the disk. This allows it to prevent a majority of problems. Usually when you encounter InnoDB corruption, this means you have issues with either the disk or RAM.