Detecting and Removing Unused Indexes in MySQL

Preface: The following post is a backup from a post first published on the Moviepilot Techblog, which is going to be replaced by the Moviepilot Labs Blog. The content is a bit outdated, as the way to go today is using MariaDB instead of OurDelta. The very content about the UserStats plugin and using it for detecting and removing unused indexes is still valid, though – and a nice way of getting rid of performance killers…

MySQL performance depends on a balanced usage of MySQL indexes. While it is easy to add an index and identify queries not using indexes via EXPLAIN during development or slow.log it is a lot harder to get rid of unused indexes. Finding and removing them might be crucial for your performance as indexes can create a remarkable cpu cycle and i/o overhead during updates to tables (INSERT/UPDATE/DELETE).

The default MySQL community edition server from mysql.com or your Linux/BSD distribution (which you shouldn’t use for a lot of reasons anyway) is not yet helpfull in this regard. There are however inofficial patches for advanced statistics that provide the details needed for optimizing your list of indexes. The easiest way to get started with a patched MySQL server is using a pre-patched binary. At Moviepilot an OurDelta’s pre-patchted MySQL 5.0 server that includes the UserStats patch is running fine for about a year now.

Let’s assume you already installed OurDelta’s MySQL 5.0, which is fairly more than adding and using an apt-source in Debian/Ubuntu or similar in rpm-based distributions. After installation the MySQL server behaves

Enable UserStats‘ Enhanced Statistics

As stated on the official patch originator’s (Percona) documentation, UserStats is enabled by setting the global variable „userstat_running“ to „on“. You can do this on the fly by entering your mysql command line interface and issuing „SET GLOBAL userstat_running = 1;“ as shown below:

mysql> SET GLOBAL userstat_running = 1;
Query OK, 0 rows affected (0.00 sec)

The UserStats counter is now running and only has a slight impact on your cpu performance. For us it’s fine to run it by default but you might enable it on an on-demand basis

Grab Statistics

The UserStats statistics can be retrieved in two ways. The simple way is using „SHOW INDEX_STATISTICS“. This will provide with an unsorted list of all indexes that have been used so far with count times.

mysql> show index_statistics;
+-------------+----------+--------------------------+---------+
|Table_schema |Table_name|Index_name                |Rows_read|
+-------------+----------+--------------------------+---------+
|de_moviepilot|broadcasts|movie_id_and_ends_at_index|  7244936|
|fr_moviepilot|place_keyw|lft_and_rgt               |    46965|
|de_moviepilot|mushes916 |index_mushes_on_user_id_an|   310538|
|de_moviepilot|mushes567 |top                       |   137855|
|de_moviepilot|mushes402 |PRIMARY                   |  3033119|
...
|pl_moviepilot|u_settings|index_user_settings_on_use|   469600|
|de_moviepilot|answers   |answerable_id_and_answerab| 11162446|
|es_moviepilot|cinema_the|PRIMARY                   |    76805|
|de_moviepilot|list_items|PRIMARY                   |    14208|
+-------------+----------+--------------------------+---------+
10689 rows in set (0.03 sec)

This table is already quite useful as it gives you handy details about your indexes. As „SHOW“ only processes WHERE-clauses, ignores LIKE-clauses and rejects ORDER you should rather query the virtual table in information_schema like this:

mysql> select * from information_schema.INDEX_STATISTICS\
ORDER BY Rows_read DESC LIMIT 0,10;
+-------------+----------+-------------------------+------------+
|TABLE_SCHEMA |TABLE_NAME|INDEX_NAME               |ROWS_READ   |
+-------------+----------+-------------------------+------------+
|de_moviepilot|images    |parent_id_and_thumbnail_o|138769917931|
|de_moviepilot|ratings   |PRIMARY                  |116200730622|
|de_moviepilot|ratings   |top_on_ratings           |111350089590|
|de_moviepilot|events    |index_events_on_parent_id| 97002618593|
|de_moviepilot|ratings   |movie_id_and_user_id_and_| 45962792087|
|de_moviepilot|neighbours|PRIMARY                  | 34403784465|
|de_moviepilot|plot_keywo|lft_and_rgt              | 30943317768|
|de_moviepilot|comments  |index_comments_on_comment| 26576184065|
|de_moviepilot|comments  |commentable_type_and_comm| 25467669528|
|moviepilot   |users     |type_and_id_idx          | 21950479057|
+-------------+----------+-------------------------+------------+
10 rows in set (0.02 sec)

You just got the list of the ten most used MyIsam/InnoDb indexes in your database. See tables TABLE_STATISTICS, CLIENT_STATISTICS and USER_STATISTICS in information_schema for further details on table, client and user stats. Feel free to check your InnoDb tables for ones with few writes that maybe should be migrated to MyIsam or heave write MyIsam tables vice versa.

Detect Unused Indexes

But our task for this post is detecting unused indexes. As you already might have noticed, INDEX_STATISTICS only shows indexes that have been used at least once. If you need a list of unused indexes, meaning indexes that have been accessed zero times, you can get them by comparing the list of available indexes and the list of used indexes on a per table base.

select disctinct(INDEX_NAME) from STATISTICS \
where INDEX_NAME != 'PRIMARY' and INDEX_SCHEMA = '${DB}' \
and table_name = '${TABLE}' and INDEX_NAME not in (select \
INDEX_NAME from index_statistics where INDEX_SCHEMA =
'${DB}' and table_name = '${TABLE}');

The variables are placeholders ${DB} and ${TABLE} for usage in shell scripts. Just replace them by a database and table name of your choice.

Putting it all together

As the query above only works on a table basis (I am sure, there are better queries for this issue), and you might want to run this on a regular basis, we wrote a little shell script called „unused_indexes.sh“, available on our snippets repo on github. The script checks all tables in all or a specific database:

$ ./unused_indexes.sh
usage: -d DATABASE (OR -a for all databases) [-f TABLENAMEFILTER]
# check all databases/tables
$ ./unused_indexes.sh -a
# check all tables in database "moviepilot"
$ ./unused_indexes.sh -d moviepilot

The output looks similar to

unused indexes in table moviepilot.stat_promo:
referrer_index mandant_index
---------------------------------------
unused indexes in table moviepilot.stat_promo_del:
c_i_m
---------------------------------------
unused indexes in table mp.comments:
comment_id meta

As we „sharded“ some large tables by splitting them we’d also like to be able to exclude tables:

# check all tables in all databases not matching "%mushes%"
$ ./unused_indexes.sh -a -f mushes
# check all tables in database "moviepilot" not matching "%mushes%"
$ ./unused_indexes.sh -d moviepilot -f mushes

Pitfalls

Please keep in mind that you should enable UserStats for a period long enough to grab statistics that show an average usage of your application and database setup. Also keep in mind that you might have indexes that are only used a few times when running scheduled jobs like importers and therefore might seem to be unused but are important anyway. Also consider flushing your statistics from time to time. As your application’s behaviour changes through deployments your index usage does, too. It might be a good idea to flush UserStats after every deployment.

The current version of unused_indexes.sh ignores all indexes that have been used at least once. It might be a good idea also checking indexes that have been used fewer than n times – just use the SELECT … ORDER BY from above.

slides from the ‚From MySQL to MariaDB‘ presentation

As announced, I held a short talk on switching from MySQL community edition (especially 5.1) to MariaDB (currently 5.2.6) at this years LinuxTag in Berlin.

Here are the (German) slides for reference:

(In case you cannot see the embedded presentation, you can also click here)

Please note: There are a lot of good English slides around. If you want give a talk on MariaDB, the „Beginner’s Guide“ might be a good start:

A Beginner’s Guide to MariaDB Presentation

Short talk on MariaDB at Linuxtag 2011

If you happen to be around at this years LinuxTag 2011 in Berlin/Germany, you are invited to attend my short talk on MariaDB as a drop-in replacement for MySQL. The talk focusses on differences between MySQL Community Edition and MariaDB (e.g. XtraDB, Aria, userstats), shows some features live and explains how to switch. I’ll probably post the slides here afterwards.

The talk will be held in German and is scheduled for Friday, the 13th of May, 16:30. The official announcement can be found here.

When backups fail: A mysql binlog race condition

Today I ran into my first MySQL binlog race condition: The initial problem was quite simple: A typical MySQL master->slave setup with heavy load on the master and nearly no load on the slave, which only serves as a hot fallback and job machine, showed differences on the same table on both machines. The differences showed up from time to time: entries that have been deleted from the master were still on the slave.

After several investigations I started examining the MySQL binlog from the master – a file containing all queries that will be transferred to the slave (and executed there if they don’t match any ignore-db-pattern). I grepped for ids of rows that have not been deleted on the slave as I’s interested if the DELETE statement was in the binlog. In order to read a binlog file just use „mysqlbinlog“ and parse the output with grep, less or similar. To my surprise I found the following entries:

$ mysqlbinlog mysql-complete-bin.000335 | grep 1006974
DELETE FROM `tickets` WHERE `id` = 1006974
SET INSERT_ID=1006974/*!*/;

As „SET INSERT_ID“ is a result of an INSERT statement it was clear, that MySQL wrote the INSERT => DELETE statements in the wrong order. As INSERT/DELETE sometimes occur quite fast after each other and several MySQL  threads are open in the same MySQL server, you might run into a rare INSERT/DELETE race condition as the master successfully executes them, while the slave receives them in the wrong order.

As a comparision this is a normal order of INSERT and DELETE (please note that the actual INSERT is not displayed here):

$ mysqlbinlog mysql-complete-bin.000336 | grep 1007729<br />SET INSERT_ID=1007729/*!*/;<br />DELETE FROM `tickets` WHERE `id` = 1007729<br />

Actually this all so far. Lesson learned for me: A mysql binlog might get you into serious trouble when firing a MySQL server with INSERT and DELETE on the same rows as the linear binlog file can fail the correct statement order, which might be a result of different MySQL threads and an unclean log behavior. I have not yet found a generic solution for the problem but I am looking forward to it.

A quick note on MySQL troubleshooting and MySQL replication

PLEASE NOTE: I am currently reviewing and extending this document.

While caring for a remarkable amount of MySQL server instances, troubleshooting becomes a common task. It might of interest for you which

Recovering a crashed MySQL server

After a server crash (meaning the system itself or just the MySQL daemon) corrupted table files are quite common. You’ll see this when checking the /var/log/syslog, as the MySQL daemon checks tables during its startup.

Apr 17 13:54:44 live1 mysqld[2613]: 090417 13:54:44 [ERROR]
  /usr/sbin/mysqld: Table './database1/table1' is marked as
  crashed and should be repaired

The MySQL daemon just told you that it found a broken MyISAM table. Now it’s up to you fixing it. You might already know, that there is the „REPAIR“ statement. So a lot of people enter their PhpMyAdmin afterwards, select database and table(s) and run the REPAIR statements. The problem with this is that in most cases your system is already in production – for instance is up again and the MySQL server already serves a bunch of requests. Therefore a REPAIR request gets slowed down dramatically. Consider taking your website down for the REPAIR – it will be faster and it’s definitely smarter not to deliver web pages based on corrupted tables.

The other disadvantage of the above method is, that you probably just shut down your web server and your PhpMyAdmin is down either or you have dozens of databases and tables and therefore it’s just a hard task to cycle through them. The better choice is the command line in this case.

If you only have a small number of corrupted tables, you can use the „mysql“ client utility doing something like:

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.75-0ubuntu10 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> REPAIR TABLE database1.table1;
+--------------------+--------+----------+----------+
| Table              | Op     | Msg_type | Msg_text |
+--------------------+--------+----------+----------+
| database1.table1   | repair | status   | OK       |
+--------------------+--------+----------+----------+
1 row in set (2.10 sec)

This works, but there is a better way: First, using OPTIMIZE in combination with REPAIR is suggested and there is a command line tool only for REPAIR jobs. Consider this call:

$ mysqlcheck -u root -p --auto-repair --check --optimize database1
Enter password:
database1.table1      OK
database1.table2      Table is already up to date

As you see, MySQL just checked the whole database and tried to repair and optimize it.

The great deal about using „mysqlcheck“ is, that it can also be run against all databases in one run without the need of getting a list of them in advance:

$ mysqlcheck -u root -p --auto-repair --check --optimize \
  --all-databases

Of course you need to consider if an optimize of all your databases and tables might just take too long if you have huge tables. On the other hand a complete run prevents of thinking about a probably missed table.

[update]

nobse pointed out in the comments, that it’s worth having a look at the automatic MyIsam repair options in MySQL. So have a look at them if you want to automate recovery:

option_mysqld_myisam-recover

Recovering a broken replication

MySQL replication is an easy method of load balancing database queries to multiple servers or just continuously backing up data. Though it is not hard to setup, troubleshooting it might be a hard task. A common reason for a broken replication is a server crash – the replication partner notices that there are broken queries – or even worse: the MySQL slave just guesses there is an error though there is none. I just ran into the latter one as a developer executed a „DROP VIEW“ on a non-existing VIEW on the master. The master justs returns an error and ignores. But as this query got replicated to the MySQL SLAVE, the slave thinks it cannot enroll a query and immediately stopped replication. This is just an example of a possible error (and a hint on using „IF EXISTS“ as often as possible).

Actually all you want to do now, is telling the slave to ignore just one query. All you need to do for this is stopping the slave, telling it to skip one query and starting the slave again:

$ mysql -u root -p
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;

That’s all about this.

Recreating databases and tables the right way

In the next topic you’ll recreate databases. A common mistake when dropping and recreating tables and databases is forgetting about all the settings it had – especially charsets which can run you into trouble later on („Why do all these umlauts show up scrambled?“). The best way of recreating tables and databases or creating them on other systems therefore is using the „SHOW CREATE“ statement. You can use „SHOW CREATE DATABASE database1“ or „SHOW CREATE TABLE database1.table1“ providing you with a CREATE statement with all current settings applied.

mysql> show create database database1;
+-----------+--------------------------------------------------------------------+
| Database  | Create Database                                                    |
+-----------+--------------------------------------------------------------------+
| database1 | CREATE DATABASE `database1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

The important part in this case is the „comment“ after the actual create statement. It is executed only on compatible MySQL server versions and makes sure, your are running utf8 on the database.

Keep this in mind and it might save you a lot of trouble.

Fixing replication when master binlog is broken

When your MySQL master crashes there is a slight chance that your master binlog gets corrupted. This means that the slaves won’t receive updates anymore stating:

[ERROR] Slave: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‚mysqlbinlog‘ on the binary log), the slave’s relay log is corrupted (you can check this by running ‚mysqlbinlog‘ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‚SHOW SLAVE STATUS‘ on this slave. Error_code: 0

You might have luck when only the slave’s relay log is corrupted as you can fix this with the steps mentioned above. But a corrupted binlog on the master might not be fixable though the databases itself can be fixed. Depending on your time you try to use the „SQL_SLAVE_SKIP_COUNTER“ from above but actually the only way is to setup

Setting up replication from scratch

There are circumstances forcing you to start replication from scratch. For instance you have a server going live for the first time and actually all those test imports don’t need to be replicated to the slave anymore as this might last hours. My quick note for this (consider backing up your master database before!)

slave: STOP SLAVE;
slave: RESET SLAVE;
slave: SHOW CREATE DATABASE datenbank;
slave: DROP DATABASE datenbank;
slave: CREATE DATABASE datenbank;

master: DROP DATABASE datenbak;
master: SHOW CREATE DATABASE datenbank;
master: CREATE DATABASE datenbank;
master: RESET MASTER

slave: CHANGE MASTER TO MASTER_USER="slave-user", \
MASTER_PASSWORD="slave-password", MASTER_HOST="master.host";
slave: START SLAVE

You just started replication from scratch, check „SHOW SLAVE STATUS“ on the slave and „SHOW MASTER STATUS“ on the master.

Deleting unneeded binlog files

Replication needs binlog files – a mysql file format for storing database changes in a binary format. Sometimes it is hard to decide how many of the binlog files you want to keep on the server possibly getting you into disk space trouble. Therefore deleting binlog files that have already been transferred to the client might be a smart idea when running low on space.

First you need to know which binlog files the slave already fetched. You can do this by having a look on „SHOW SLAVE STATUS;“ on the slave. Now log into the MySQL master and run something like:

mysql> PURGE BINARY LOGS TO 'mysql-bin.010';

You can even do this on a date format level:

mysql> PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

Conclusion

The above hints might save you same time when recovering or troubleshooting a MySQL server. Please note, that these are hints and you have – at any time – make sure, that your data has an up to date backup. Nothing will help you more.