Percona-Tookit工具包之pt-index-usage
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
扫码关注我们
微信号：SRE实战
拒绝背锅 运筹帷幄
Procedure Usage
1 pt-index-usage [OPTIONS] [FILES]
Main parameter
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。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.
更多精彩
