首页 > > 网络编程 > Mysql >

Percona-Tookit工具包之pt-index-usage

2018-06-26 01:10:37来源：博客园阅读 ()

Preface

There're many ways relevent with performance tuning.For example,using indexes properly is important in doing that.At the very beginning of releasing a project,we're probably supposed to create many different indexes(especially union index) to increase the efficiency of queries on target tables even if some of them are seldom or never used at all.We are sure about that it is not the more the better of indexes on a table.Indexes will occupy more disk space and will cost a lot in maintaining.Alternatively,we should reduce the indexes which are not usually used by freqeuntly cheking them.Therefore,I'll introduce a tool which can help us in the aspect.

Introduce

pt-index-usage(as what it is called) is a tool of Percona-Toolkit can provide a way to analyze your SQL statments in slow log(which means they're probably executed with bad performance).Afterward,you can know details about whether there're indexes not used properly and estimate whether to drop them in some time later.

Procedure

Usage

1 pt-index-usage [OPTIONS] [FILES]

Main parameter

1 --save-results-database -- Save output results into the specific tables of database.
2 --create-save-results-database -- Create a database with necessary tables if set "--save-results-database" but not exist.
3 --empty-save-results-tables -- Drop and recreate all the tables which are specified by "--save-results-database".
4 --create-views -- Create views for tables in database which is specified by "--save-results-database".
5 --no-report -- Don't generate a report but put results into tables for later analysis."--save-results-database" is indispensable when you set this option.
6 --report-format -- The only format is "drop_unused_indexes" now.
7 --drop -- Specify the type of index which you want to drop(Default value is non-unique).

Examples

Create test environment.

 1 (root@localhost mysql3306.sock)[zlm]10:32:04>create table if not exists test_index_usage(
 2     -> id int unsigned auto_increment not null,
 3     -> order_id int unsigned not null default 0,
 4     -> name varchar(10) not null default '',
 5     -> gender enum('male','female') not null,
 6     -> primary key(id)
 7     -> ) auto_increment=1 engine=innodb charset=utf8mb4;
 8 Query OK, 0 rows affected (0.04 sec)
 9 
10 (root@localhost mysql3306.sock)[zlm]10:32:14>delimiter $$
11 (root@localhost mysql3306.sock)[zlm]10:32:19>create procedure pro_index_usage (in n1 int,in s1 varchar(10),in s2 varchar(10))
12     -> begin
13     -> declare i int unsigned default 0;
14     -> start transaction;
15     -> while i < n1 do
16     -> insert into test_index_usage(order_id,gender,name) values(i,s1,s2);
17     -> set i=i+1;
18     -> end while;
19     -> commit;
20     -> end;
21     -> $$
22 Query OK, 0 rows affected (0.00 sec)
23 
24 (root@localhost mysql3306.sock)[zlm]10:32:19>delimiter ;
25 (root@localhost mysql3306.sock)[zlm]10:32:20>call pro_index_usage(100000,'male','zlm');
26 Query OK, 0 rows affected (5.59 sec)
27 
28 (root@localhost mysql3306.sock)[zlm]10:32:31>call pro_index_usage(100000,'female','aaron8219');
29 Query OK, 0 rows affected (5.38 sec)
30 
31 (root@localhost mysql3306.sock)[zlm]10:32:38>select count(*) from test_index_usage;
32 +----------+
33 | count(*) |
34 +----------+
35 |   200000 |
36 +----------+
37 1 row in set (0.05 sec)
38 
39 (root@localhost mysql3306.sock)[zlm]10:32:40>select * from test_index_usage limit 5;
40 +----+----------+------+--------+
41 | id | order_id | name | gender |
42 +----+----------+------+--------+
43 |  1 |        0 | zlm  | male   |
44 |  2 |        1 | zlm  | male   |
45 |  3 |        2 | zlm  | male   |
46 |  4 |        3 | zlm  | male   |
47 |  5 |        4 | zlm  | male   |
48 +----+----------+------+--------+
49 5 rows in set (0.00 sec)
50 
51 (root@localhost mysql3306.sock)[zlm]10:34:24>alter table test_index_usage add key idx_key1 (order_id,gender);
52 Query OK, 0 rows affected (0.64 sec)
53 Records: 0  Duplicates: 0  Warnings: 0
54 
55 (root@localhost mysql3306.sock)[zlm]10:35:12>alter table test_index_usage add key idx_key2 (order_id,gender,name);
56 Query OK, 0 rows affected (0.94 sec)
57 Records: 0  Duplicates: 0  Warnings: 0
58 
59 (root@localhost mysql3306.sock)[zlm]10:35:20>show keys from test_index_usage;
60 +------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
61 | Table            | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
62 +------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
63 | test_index_usage |          0 | PRIMARY  |            1 | id          | A         |      199601 |     NULL | NULL   |      | BTREE      |         |               |
64 | test_index_usage |          1 | idx_key1 |            1 | order_id    | A         |      100061 |     NULL | NULL   |      | BTREE      |         |               |
65 | test_index_usage |          1 | idx_key1 |            2 | gender      | A         |      199601 |     NULL | NULL   |      | BTREE      |         |               |
66 | test_index_usage |          1 | idx_key2 |            1 | order_id    | A         |       99905 |     NULL | NULL   |      | BTREE      |         |               |
67 | test_index_usage |          1 | idx_key2 |            2 | gender      | A         |      199601 |     NULL | NULL   |      | BTREE      |         |               |
68 | test_index_usage |          1 | idx_key2 |            3 | name        | A         |      199601 |     NULL | NULL   |      | BTREE      |         |               |
69 +------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
70 6 rows in set (0.00 sec)

Make sure "slow_query_on" has been set "on" and reduce the "long_query_time" into "0.01".

 1 (root@localhost mysql3306.sock)[zlm]10:35:25>show global variables like '%slow_query_log%';
 2 +---------------------+----------+
 3 | Variable_name       | Value    |
 4 +---------------------+----------+
 5 | slow_query_log      | ON       |
 6 | slow_query_log_file | slow.log |
 7 +---------------------+----------+
 8 2 rows in set (0.00 sec)
 9 
10 (root@localhost mysql3306.sock)[zlm]10:36:05>show global variables like '%long_query_time%';
11 +-----------------+----------+
12 | Variable_name   | Value    |
13 +-----------------+----------+
14 | long_query_time | 1.000000 |
15 +-----------------+----------+
16 1 row in set (0.00 sec)
17 
18 (root@localhost mysql3306.sock)[zlm]10:36:11>set global long_query_time=0.01;
19 Query OK, 0 rows affected (0.00 sec)
20 
21 (root@localhost mysql3306.sock)[zlm]10:36:15>show global variables like '%long_query_time%';
22 +-----------------+----------+
23 | Variable_name   | Value    |
24 +-----------------+----------+
25 | long_query_time | 0.010000 |
26 +-----------------+----------+
27 1 row in set (0.01 sec)

Execute a SQL statement.

 1 (root@localhost mysql3306.sock)[zlm]10:42:06>select * from test_index_usage where order_id>=1 and name='aaron8219';
 2 -- Omitted.
 3 | 199990 |    99989 | aaron8219 | female |
 4 | 199991 |    99990 | aaron8219 | female |
 5 | 199992 |    99991 | aaron8219 | female |
 6 | 199993 |    99992 | aaron8219 | female |
 7 | 199994 |    99993 | aaron8219 | female |
 8 | 199995 |    99994 | aaron8219 | female |
 9 | 199996 |    99995 | aaron8219 | female |
10 | 199997 |    99996 | aaron8219 | female |
11 | 199998 |    99997 | aaron8219 | female |
12 | 199999 |    99998 | aaron8219 | female |
13 | 200000 |    99999 | aaron8219 | female |
14 +--------+----------+-----------+--------+
15 99999 rows in set (0.16 sec)

Check the execute plan.

 1 (root@localhost mysql3306.sock)[zlm]10:43:16>explain select * from test_index_usage where order_id>=1 and name='aaron8219';
 2 +----+-------------+------------------+------------+-------+-------------------+----------+---------+------+-------+----------+--------------------------+
 3 | id | select_type | table            | partitions | type  | possible_keys     | key      | key_len | ref  | rows  | filtered | Extra                    |
 4 +----+-------------+------------------+------------+-------+-------------------+----------+---------+------+-------+----------+--------------------------+
 5 |  1 | SIMPLE      | test_index_usage | NULL       | range | idx_key1,idx_key2 | idx_key2 | 4       | NULL | 99800 |    10.00 | Using where; Using index |
 6 +----+-------------+------------------+------------+-------+-------------------+----------+---------+------+-------+----------+--------------------------+
 7 1 row in set, 1 warning (0.00 sec)
 8 
 9 (root@localhost mysql3306.sock)[zlm]10:43:42>explain format=json select * from test_index_usage where order_id>=1 and name='aaron8219'\G
10 *************************** 1. row ***************************
11 EXPLAIN: {
12   "query_block": {
13     "select_id": 1,
14     "cost_info": {
15       "query_cost": "40540.88"
16     },
17     "table": {
18       "table_name": "test_index_usage",
19       "access_type": "range",
20       "possible_keys": [
21         "idx_key1",
22         "idx_key2"
23       ],
24       "key": "idx_key2",
25       "used_key_parts": [
26         "order_id"
27       ],
28       "key_length": "4",
29       "rows_examined_per_scan": 99800,
30       "rows_produced_per_join": 9980,
31       "filtered": "10.00",
32       "using_index": true,
33       "cost_info": {
34         "read_cost": "38544.88",
35         "eval_cost": "1996.00",
36         "prefix_cost": "40540.88",
37         "data_read_per_join": "545K"
38       },
39       "used_columns": [
40         "id",
41         "order_id",
42         "name",
43         "gender"
44       ],
45       "attached_condition": "((`zlm`.`test_index_usage`.`order_id` >= 1) and (`zlm`.`test_index_usage`.`name` = 'aaron8219'))"
46     }
47   }
48 }
49 1 row in set, 1 warning (0.00 sec)

Check slow log.

1 [root@zlm2 10:45:31 /data/mysql/mysql3306/data]
2 #cat slow.log
3 
4 # Time: 2018-06-25T08:44:20.974728Z
5 # User@Host: root[root] @ localhost []  Id:    25
6 # Query_time: 0.161343  Lock_time: 0.000087 Rows_sent: 99999  Rows_examined: 199998
7 SET timestamp=1529916260;
8 select * from test_index_usage where order_id>=1 and name='aaron8219';

Execute pt-index-usage(create database and tables & views).

1 [root@zlm2 11:25:20 /data/mysql/mysql3306/data]
2 #pt-index-usage -h192.168.1.101 -P3306 -urepl -prepl4slave -Dzlm --create-views --no-report --create-save-results-database --save-results-database h=192.168.1.102,P=3306,u=repl,p=repl4slave,D=index_usage /data/mysql/mysql3306/data/slow.log
3 
4 [root@zlm2 11:26:37 /data/mysql/mysql3306/data]
5 #

Check the tables & views in database "index_usage" on remote node zlm3.

  1 (root@localhost mysql3306.sock)[(none)]11:26:32>show databases;
  2 +--------------------+
  3 | Database           |
  4 +--------------------+
  5 | information_schema |
  6 | index_usage        |  -- This is the newly create database which contains tables created by pt-index-usage.
  7 | mysql              |
  8 | performance_schema |
  9 | sys                |
 10 | zlm                |
 11 +--------------------+
 12 6 rows in set (0.00 sec)
 13 
 14 (root@localhost mysql3306.sock)[(none)]11:26:56>use index_usage;
 15 Reading table information for completion of table and column names
 16 You can turn off this feature to get a quicker startup with -A
 17 
 18 Database changed
 19 (root@localhost mysql3306.sock)[index_usage]11:27:06>show tables;
 20 +---------------------------------+
 21 | Tables_in_index_usage           |
 22 +---------------------------------+
 23 | index_alternatives              |
 24 | index_usage                     |
 25 | indexes                         |
 26 | queries                         |
 27 | tables                          |
 28 | view_index_alternates           |
 29 | view_index_has_alternates       |
 30 | view_index_usage                |
 31 | view_query_uses_several_indexes |
 32 | view_required_indexes           |
 33 | view_unused_index_alternates    |
 34 +---------------------------------+
 35 11 rows in set (0.00 sec)
 36 
 37 (root@localhost mysql3306.sock)[index_usage]11:29:26>select * from index_alternatives;
 38 +---------------------+-----+------------------+----------+----------+-----+
 39 | query_id            | db  | tbl              | idx      | alt_idx  | cnt |
 40 +---------------------+-----+------------------+----------+----------+-----+
 41 | 4638883468153013157 | zlm | test_index_usage | idx_key2 | idx_key1 |   1 |
 42 +---------------------+-----+------------------+----------+----------+-----+
 43 1 row in set (0.00 sec)
 44 
 45 (root@localhost mysql3306.sock)[index_usage]11:29:31>select * from index_usage;
 46 +---------------------+-----+------------------+----------+-----+
 47 | query_id            | db  | tbl              | idx      | cnt |
 48 +---------------------+-----+------------------+----------+-----+
 49 | 4638883468153013157 | zlm | test_index_usage | idx_key2 |   1 |
 50 +---------------------+-----+------------------+----------+-----+
 51 1 row in set (0.00 sec)
 52 
 53 (root@localhost mysql3306.sock)[index_usage]11:29:41>select * from indexes;
 54 +-------+---------------------------+-----------+-----+
 55 | db    | tbl                       | idx       | cnt |
 56 +-------+---------------------------+-----------+-----+
 57 | mysql | columns_priv              | PRIMARY   |   0 |
 58 | mysql | db                        | PRIMARY   |   0 |
 59 | mysql | db                        | User      |   0 |
 60 | mysql | engine_cost               | PRIMARY   |   0 |
 61 | mysql | event                     | PRIMARY   |   0 |
 62 | mysql | func                      | PRIMARY   |   0 |
 63 | mysql | help_category             | name      |   0 |
 64 | mysql | help_category             | PRIMARY   |   0 |
 65 | mysql | help_keyword              | name      |   0 |
 66 | mysql | help_keyword              | PRIMARY   |   0 |
 67 | mysql | help_relation             | PRIMARY   |   0 |
 68 | mysql | help_topic                | name      |   0 |
 69 | mysql | help_topic                | PRIMARY   |   0 |
 70 | mysql | ndb_binlog_index          | PRIMARY   |   0 |
 71 | mysql | plugin                    | PRIMARY   |   0 |
 72 | mysql | proc                      | PRIMARY   |   0 |
 73 | mysql | procs_priv                | Grantor   |   0 |
 74 | mysql | procs_priv                | PRIMARY   |   0 |
 75 | mysql | proxies_priv              | Grantor   |   0 |
 76 | mysql | proxies_priv              | PRIMARY   |   0 |
 77 | mysql | servers                   | PRIMARY   |   0 |
 78 | mysql | server_cost               | PRIMARY   |   0 |
 79 | mysql | tables_priv               | Grantor   |   0 |
 80 | mysql | tables_priv               | PRIMARY   |   0 |
 81 | mysql | time_zone                 | PRIMARY   |   0 |
 82 | mysql | time_zone_leap_second     | PRIMARY   |   0 |
 83 | mysql | time_zone_name            | PRIMARY   |   0 |
 84 | mysql | time_zone_transition      | PRIMARY   |   0 |
 85 | mysql | time_zone_transition_type | PRIMARY   |   0 |
 86 | mysql | user                      | PRIMARY   |   0 |
 87 | sys   | sys_config                | PRIMARY   |   0 |
 88 | zlm   | checksums                 | PRIMARY   |   0 |
 89 | zlm   | checksums                 | ts_db_tbl |   0 |
 90 | zlm   | indexes                   | PRIMARY   |   0 |
 91 | zlm   | index_alternatives        | db        |   0 |
 92 | zlm   | index_alternatives        | db_2      |   0 |
 93 | zlm   | index_alternatives        | query_id  |   0 |
 94 | zlm   | index_usage               | query_id  |   0 |
 95 | zlm   | queries                   | PRIMARY   |   0 |
 96 | zlm   | tables                    | PRIMARY   |   0 |
 97 | zlm   | test_ddl                  | PRIMARY   |   0 |
 98 | zlm   | test_index_usage          | idx_key1  |   0 |
 99 | zlm   | test_index_usage          | idx_key2  |   1 |
100 | zlm   | test_index_usage          | PRIMARY   |   0 |
101 | zlm   | test_innodb               | PRIMARY   |   0 |
102 | zlm   | test_myisam               | PRIMARY   |   0 |
103 +-------+---------------------------+-----------+-----+
104 46 rows in set (0.00 sec)
105 
106 (root@localhost mysql3306.sock)[index_usage]11:30:13>select * from queries\G
107 *************************** 1. row ***************************
108    query_id: 485931796342352545
109 fingerprint: create table if not exists tables ( db varchar(?) not ?, tbl varchar(?) not ?, cnt bigint unsigned not ? default ?, primary key (db, tbl) )
110      sample: CREATE TABLE IF NOT EXISTS tables (
111     db           VARCHAR(64) NOT NULL,
112     tbl          VARCHAR(64) NOT NULL,
113     cnt          BIGINT UNSIGNED NOT NULL DEFAULT 0,
114     PRIMARY KEY  (db, tbl)
115   )
116 *************************** 2. row ***************************
117    query_id: 4638883468153013157
118 fingerprint: select * from test_index_usage where order_id>=? and name=?
119      sample: select * from test_index_usage where order_id>=1 and name='aaron8219'
120 *************************** 3. row ***************************
121    query_id: 9976332422031889609
122 fingerprint: create table if not exists indexes ( db varchar(?) not ?, tbl varchar(?) not ?, idx varchar(?) not ?, cnt bigint unsigned not ? default ?, primary key (db, tbl, idx) )
123      sample: CREATE TABLE IF NOT EXISTS indexes (
124     db           VARCHAR(64) NOT NULL,
125     tbl          VARCHAR(64) NOT NULL,
126     idx          VARCHAR(64) NOT NULL,
127     cnt          BIGINT UNSIGNED NOT NULL DEFAULT 0,
128     PRIMARY KEY  (db, tbl, idx)
129   )
130 *************************** 4. row ***************************
131    query_id: 11154368342625992021
132 fingerprint: create table if not exists queries ( query_id bigint unsigned not ?, fingerprint text not ?, sample text not ?, primary key (query_id) )
133      sample: CREATE TABLE IF NOT EXISTS queries (
134     query_id     BIGINT UNSIGNED NOT NULL,
135     fingerprint  TEXT NOT NULL,
136     sample       TEXT NOT NULL,
137     PRIMARY KEY  (query_id)
138   )
139 *************************** 5. row ***************************
140    query_id: 17850505197851717537
141 fingerprint: create table if not exists index_alternatives ( query_id bigint unsigned not ?, db varchar(?) not ?, tbl varchar(?) not ?, idx varchar(?) not ?, alt_idx varchar(?) not ?, cnt bigint unsigned not ? default ?, unique index (query_id, db, tbl, idx, alt_idx), index (db, tbl, idx), index (db, tbl, alt_idx) )
142      sample: CREATE TABLE IF NOT EXISTS index_alternatives (
143     query_id      BIGINT UNSIGNED NOT NULL, -- This query used
144     db            VARCHAR(64) NOT NULL,     -- this index, but...
145     tbl           VARCHAR(64) NOT NULL,     --
146     idx           VARCHAR(64) NOT NULL,     --
147     alt_idx       VARCHAR(64) NOT NULL,     -- was an alternative
148     cnt           BIGINT UNSIGNED NOT NULL DEFAULT 1,
149     UNIQUE INDEX  (query_id, db, tbl, idx, alt_idx),
150     INDEX         (db, tbl, idx),
151     INDEX         (db, tbl, alt_idx)
152   )
153 5 rows in set (0.00 sec)
154 
155 (root@localhost mysql3306.sock)[index_usage]11:30:24>select * from tables;
156 +-------+---------------------------+-----+
157 | db    | tbl                       | cnt |
158 +-------+---------------------------+-----+
159 | mysql | columns_priv              |   0 |
160 | mysql | db                        |   0 |
161 | mysql | engine_cost               |   0 |
162 | mysql | event                     |   0 |
163 | mysql | func                      |   0 |
164 | mysql | help_category             |   0 |
165 | mysql | help_keyword              |   0 |
166 | mysql | help_relation             |   0 |
167 | mysql | help_topic                |   0 |
168 | mysql | ndb_binlog_index          |   0 |
169 | mysql | plugin                    |   0 |
170 | mysql | proc                      |   0 |
171 | mysql | procs_priv                |   0 |
172 | mysql | proxies_priv              |   0 |
173 | mysql | servers                   |   0 |
174 | mysql | server_cost               |   0 |
175 | mysql | tables_priv               |   0 |
176 | mysql | time_zone                 |   0 |
177 | mysql | time_zone_leap_second     |   0 |
178 | mysql | time_zone_name            |   0 |
179 | mysql | time_zone_transition      |   0 |
180 | mysql | time_zone_transition_type |   0 |
181 | mysql | user                      |   0 |
182 | sys   | sys_config                |   0 |
183 | zlm   | checksums                 |   0 |
184 | zlm   | indexes                   |   0 |
185 | zlm   | index_alternatives        |   0 |
186 | zlm   | index_usage               |   0 |
187 | zlm   | queries                   |   0 |
188 | zlm   | tables                    |   0 |
189 | zlm   | test_ddl                  |   0 |
190 | zlm   | test_ddl_no_pk            |   0 |
191 | zlm   | test_index_usage          |   1 |
192 | zlm   | test_innodb               |   0 |
193 | zlm   | test_myisam               |   0 |
194 +-------+---------------------------+-----+
195 35 rows in set (0.00 sec)

Execute pt-index-usage(output on screen derectly).

1 [root@zlm2 11:46:48 /data/mysql/mysql3306/data]
2 #pt-index-usage -h192.168.1.101 -P3306 -urepl -prepl4slave -Dzlm /data/mysql/mysql3306/data/slow.log
3 
4 ALTER TABLE `zlm`.`test_index_usage` DROP KEY `idx_key1`; -- type:non-unique
5 
6 [root@zlm2 11:46:50 /data/mysql/mysql3306/data]
7 #

summary

pt-index-usage provides merely analysis on slow log at the moment.
Usually We'll analyze SQL statements in slow log,and then put them into tables of database on remote server(which maybe not slave).
There will be a series of insert operations when using pt-index-usage(while not setting "--no-report").Thus,it really will I ncreast the load of product server.
On the other hand,It's possible to be misled when we get a probable bad execute plan which bypasses the useful indexes.