pt-index-usage

Percona Toolkitのpt-index-usageはとても便利で、スロークエリログから実行されたクエリを読み取り、それらがインデックスをどのように使用しているかを分析するものだ。 https://www.percona.com/doc/percona-toolkit/LATEST/pt-index-usage.html

そのpt-index-usageに--databasesオプションというものがある。

公式を読むとこういう風に説明されている

https://www.percona.com/doc/percona-toolkit/LATEST/pt-index-usage.html#cmdoption-pt-index-usage-databases

--databases short form: -d; type: hash

Only get tables and indexes from this comma-separated list of databases.

(指定された)カンマ区切りのデータベースリストのテーブルとインデックスのみ用います。

なるほど、全部を見ないのは良い。

自分の使い方

ここで自分の使い方を説明しておくと、

まず、スロークエリログを手元にコピーしてきて、
かつ手元に本番と同じ定義のDBインスタンスを立て、
そのインスタンス上のDBとスロークエリログを突き合わせる形でpt-index-usageを回す。

理由としては 本番に負荷を一切与えたくない というその一点における。

だが、手元の環境で流した時にそれは起きた

以下のようなオプションで流したところ

#!/bin/bash
DATABASE=hogehoge_table1,hogehoge_table2,hogehoge_table3,hogehoge_table4.hogehoge_table5
pt-index-usage --user=root --password=xxxx --database=percona --databases=$DATABASE --port=3306 --host=xxxxxxxxx mysql-slow.log>>alter_table.sql

以下のようなエラーが発生した。

 DBD::mysql::db do failed: Unknown database 'hogehoge_table6' [for Statement "USE `hogehoge_table6`"] at /usr/bin/pt-index-usage line ...

これは 「スロークエリログの中にはhogehoge_table6というデータベースがあるが、突合せしているDBインスタンスにはないよ」 というものである。

解決方法

答えはシンプルで、hogehoge_table6を作ってやればいい。

CREATE DATABASE hogehoge_table6;

テーブル定義までは必要ない。

本来pt-index-usageは本番環境に対して行うのかもしれないが、自分のように本番に対して実行したくないなー、という人には上記のポイントだけ気を付けてチェックしてもらえばいいと思う。

ついでに

テーブルにスロークエリログの分析結果を入れる方法も記載しておく。あらかじめperconaデータベースを作ったうえで、--no-report　と--save-results-databaseを同時に指定すればいい。

CREATE DATABASE percona;

pt-index-usage --user=root --password=xxxxxxxx --database=percona --databases=$DATABASE --port=3306 --host=xxxxxx mysql-slow.log --no-report --save-results-database=xxxxxx

ここで注意。save-results-databaseにはperconaデータベースを作ったホスト名 or IPアドレスが入る。 save-results-databaseという名前に騙されないこと（僕は騙された）

例えば、以下のようなクエリを流せば不要と思われる未使用インデックスが抽出される。

use percona;

SELECT CONCAT_WS('.', i.db, i.tbl, i.idx) AS idx,
   alt.alternative_to, alt.queries, alt.cnt
FROM indexes AS i
   INNER JOIN (
      SELECT db, tbl, alt_idx, GROUP_CONCAT(DISTINCT idx) AS alternative_to,
         GROUP_CONCAT(DISTINCT query_id) AS queries, SUM(cnt) AS cnt
      FROM index_alternatives
      GROUP BY db, tbl, alt_idx
      HAVING COUNT(*) > 1
   ) AS alt ON i.db = alt.db AND i.tbl = alt.tbl
     AND i.idx = alt.alt_idx
WHERE i.cnt = 0;

41から始めました

pt-index-usageの--databasesオプションについて

pt-index-usage

自分の使い方

だが、手元の環境で流した時にそれは起きた

解決方法

ついでに