Finding extra indexes in MySQL

The database often becomes a bottleneck in a high load web-based applications. So even superfluous, unnecessary indexes can slow down MySQL. Check-unused-keys and MySQL

The easiest way to get rid of these indices is the utility check-unused-keys. It collects information about unused tables and indexes in MySQL using another tool User Statistics. The patch adds INFORMATION_SCHEMA tables (including INDEX_STATISTICS), several commands and variable userstat.

Installation

Utility requires User Statistics patch, you can download it via the link. And even better solution is Percona Server for MySQL, which supports user_stats out of the box.

When everything is ready, you need to use UserStats, which are disabled by default:

mysql> SET GLOBAL userstat_running = 1;

# Setting a global variable in MySQL

Now UserStats will collect all the statistics about the use of the index, the load on the CPU is minimal.

You can then install the check-unused-keys utility:

wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/check-unused-keys/check-unused-keys
chmod +x check-unused-keys

# Download to the directory, give permission to execute

Usage

When UserStats scraped together a sufficient number of statistics, you can use the utility:

check-unused-keys --help
Options:
       -d, --databases=<dbname>  Comma-separated list of databases to check
       -h, --help                Display this message and exit
       -H, --hostname=<hostname> The target MySQL server host
       --ignore-databases        Comma-separated list of databases to ignore
       --ignore-indexes          Comma-separated list of indexes to ignore
                                     db_name.tbl_name.index_name
       --ignore-tables           Comma-separated list of tables to ignore
                                     db_name.tbl_name
       --options-file            The options file to use
       -p, --password=<password> The password of the MySQL user
       -i, --port=<portnum>      The port MySQL is listening on
       -s, --socket=<sockfile>   Use the specified mysql unix socket to connect
       -t, --tables=<tables>     Comma-separated list of tables to evaluate
                                     db_name.tbl_name
       --[no]summary             Display summary information
       -u, --username=<username> The MySQL user used to connect
       -v, --verbose             Increase verbosity level
       -V, --version             Display version information and exit

# check-unused-keys output parameters

To start the utility, just run:

./check-unused-keys -u mysql -p password --print-unused-tables --create-alter

# Program execution with indication of username and password, print unused tables and Alter operator

Command displays the unused tables (with indices) with ALTER TABLE for each to remove unused indexes. If necessary, the analysis can be carried out only on the required database and tables.

The most important

Utility helps to get rid of unused indexes. Be careful — all the indexes used 0 times will be removed, too. So the tables for cron-jobs that weren’t executed during the collection of statistics, will be deleted.

Подпишитесь на Хайлоад с помощью Google аккаунта
или закройте эту хрень