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

 

 

Preface

Preface

 

 

    We used to use “find” command in linux
or AIX when we need to get a certain file but cannot rember the precise
name.We will execute “find /pathtobegin -name xxx” in our OS prompt.Is
there any tool can find a specific table of MySQL database which
similarly as “find” does?

    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

Introduce

 

 

*    pt-find is a very useful tool to find
out a sepcific table with a condition you’ve specified.It even can
execute a sql operation when finding it simutaneously.

*

*    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

Procedure

 

 

Usage

Usage

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

 

 

Common
parameters

Main
parameter

 1 Options:
 2 --day-start //Specify the meassure mothed of time when using "--cmin,--mmin,--ctime,--mtime,etc".
 3 --or //Change the combination test behavior as "or" instead of default "and".
 4 
 5 Actions:
 6 --exec //Specify the executing sql statement with each item found.
 7 --exec-plus //Specify the executing sql statement with all items at once.
 8 --print //Print the target database and table name.
 9 --printf //Print with a certain format.
10 
11 Tests:
12 --autoinc //Speicfy a value of auto_increment to test whether has auto_inrcrement column.
13 --avgrowlen //Specify the average 
14 --cmin //Specify the target table created n minutes ago.
15 --ctime //Specify the target table created n days ago.
16 --mmin //Specify the target table modified n minutes ago.
17 --mtime //Specify the target table modified n days ago.
18 --kmim //Specify the target table checked n minutes ago.
19 --ktime //Specify the target table checked n days ago.
20 --rowformat //Specify the row format of tables to match pattern.
21 --rows //Specify the rows the table contains.
22 --tablesize //Specify the size the table is.
23 --empty //Specify the talbe which has no rows.
24 --engines //Specify the engine of tabls.
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).

 

 

Example

Examples

 

 

Find
out all tables in all databases.

Create test environment.

  1 [root@zlm2 07:54:01 /data/mysql/mysql3308/data]
  2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm
  3 `mysql`.`columns_priv`
  4 `mysql`.`db`
  5 `mysql`.`engine_cost`
  6 `mysql`.`event`
  7 `mysql`.`func`
  8 `mysql`.`general_log`
  9 `mysql`.`gtid_executed`
 10 `mysql`.`help_category`
 11 `mysql`.`help_keyword`
 12 `mysql`.`help_relation`
 13 `mysql`.`help_topic`
 14 `mysql`.`innodb_index_stats`
 15 `mysql`.`innodb_table_stats`
 16 `mysql`.`ndb_binlog_index`
 17 `mysql`.`plugin`
 18 `mysql`.`proc`
 19 `mysql`.`procs_priv`
 20 `mysql`.`proxies_priv`
 21 `mysql`.`server_cost`
 22 `mysql`.`servers`
 23 `mysql`.`slave_master_info`
 24 `mysql`.`slave_relay_log_info`
 25 `mysql`.`slave_worker_info`
 26 `mysql`.`slow_log`
 27 `mysql`.`tables_priv`
 28 `mysql`.`time_zone`
 29 `mysql`.`time_zone_leap_second`
 30 `mysql`.`time_zone_name`
 31 `mysql`.`time_zone_transition`
 32 `mysql`.`time_zone_transition_type`
 33 `mysql`.`user`
 34 `performance_schema`.`accounts`
 35 `performance_schema`.`cond_instances`
 36 `performance_schema`.`events_stages_current`
 37 `performance_schema`.`events_stages_history`
 38 `performance_schema`.`events_stages_history_long`
 39 `performance_schema`.`events_stages_summary_by_account_by_event_name`
 40 `performance_schema`.`events_stages_summary_by_host_by_event_name`
 41 `performance_schema`.`events_stages_summary_by_thread_by_event_name`
 42 `performance_schema`.`events_stages_summary_by_user_by_event_name`
 43 `performance_schema`.`events_stages_summary_global_by_event_name`
 44 `performance_schema`.`events_statements_current`
 45 `performance_schema`.`events_statements_history`
 46 `performance_schema`.`events_statements_history_long`
 47 `performance_schema`.`events_statements_summary_by_account_by_event_name`
 48 `performance_schema`.`events_statements_summary_by_digest`
 49 `performance_schema`.`events_statements_summary_by_host_by_event_name`
 50 `performance_schema`.`events_statements_summary_by_program`
 51 `performance_schema`.`events_statements_summary_by_thread_by_event_name`
 52 `performance_schema`.`events_statements_summary_by_user_by_event_name`
 53 `performance_schema`.`events_statements_summary_global_by_event_name`
 54 `performance_schema`.`events_transactions_current`
 55 `performance_schema`.`events_transactions_history`
 56 `performance_schema`.`events_transactions_history_long`
 57 `performance_schema`.`events_transactions_summary_by_account_by_event_name`
 58 `performance_schema`.`events_transactions_summary_by_host_by_event_name`
 59 `performance_schema`.`events_transactions_summary_by_thread_by_event_name`
 60 `performance_schema`.`events_transactions_summary_by_user_by_event_name`
 61 `performance_schema`.`events_transactions_summary_global_by_event_name`
 62 `performance_schema`.`events_waits_current`
 63 `performance_schema`.`events_waits_history`
 64 `performance_schema`.`events_waits_history_long`
 65 `performance_schema`.`events_waits_summary_by_account_by_event_name`
 66 `performance_schema`.`events_waits_summary_by_host_by_event_name`
 67 `performance_schema`.`events_waits_summary_by_instance`
 68 `performance_schema`.`events_waits_summary_by_thread_by_event_name`
 69 `performance_schema`.`events_waits_summary_by_user_by_event_name`
 70 `performance_schema`.`events_waits_summary_global_by_event_name`
 71 `performance_schema`.`file_instances`
 72 `performance_schema`.`file_summary_by_event_name`
 73 `performance_schema`.`file_summary_by_instance`
 74 `performance_schema`.`global_status`
 75 `performance_schema`.`global_variables`
 76 `performance_schema`.`host_cache`
 77 `performance_schema`.`hosts`
 78 `performance_schema`.`memory_summary_by_account_by_event_name`
 79 `performance_schema`.`memory_summary_by_host_by_event_name`
 80 `performance_schema`.`memory_summary_by_thread_by_event_name`
 81 `performance_schema`.`memory_summary_by_user_by_event_name`
 82 `performance_schema`.`memory_summary_global_by_event_name`
 83 `performance_schema`.`metadata_locks`
 84 `performance_schema`.`mutex_instances`
 85 `performance_schema`.`objects_summary_global_by_type`
 86 `performance_schema`.`performance_timers`
 87 `performance_schema`.`prepared_statements_instances`
 88 `performance_schema`.`pxc_cluster_view`
 89 `performance_schema`.`replication_applier_configuration`
 90 `performance_schema`.`replication_applier_status`
 91 `performance_schema`.`replication_applier_status_by_coordinator`
 92 `performance_schema`.`replication_applier_status_by_worker`
 93 `performance_schema`.`replication_connection_configuration`
 94 `performance_schema`.`replication_connection_status`
 95 `performance_schema`.`replication_group_member_stats`
 96 `performance_schema`.`replication_group_members`
 97 `performance_schema`.`rwlock_instances`
 98 `performance_schema`.`session_account_connect_attrs`
 99 `performance_schema`.`session_connect_attrs`
100 `performance_schema`.`session_status`
101 `performance_schema`.`session_variables`
102 `performance_schema`.`setup_actors`
103 `performance_schema`.`setup_consumers`
104 `performance_schema`.`setup_instruments`
105 `performance_schema`.`setup_objects`
106 `performance_schema`.`setup_timers`
107 `performance_schema`.`socket_instances`
108 `performance_schema`.`socket_summary_by_event_name`
109 `performance_schema`.`socket_summary_by_instance`
110 `performance_schema`.`status_by_account`
111 `performance_schema`.`status_by_host`
112 `performance_schema`.`status_by_thread`
113 `performance_schema`.`status_by_user`
114 `performance_schema`.`table_handles`
115 `performance_schema`.`table_io_waits_summary_by_index_usage`
116 `performance_schema`.`table_io_waits_summary_by_table`
117 `performance_schema`.`table_lock_waits_summary_by_table`
118 `performance_schema`.`threads`
119 `performance_schema`.`user_variables_by_thread`
120 `performance_schema`.`users`
121 `performance_schema`.`variables_by_thread`
122 `sys`.`host_summary`
123 `sys`.`host_summary_by_file_io`
124 `sys`.`host_summary_by_file_io_type`
125 `sys`.`host_summary_by_stages`
126 `sys`.`host_summary_by_statement_latency`
127 `sys`.`host_summary_by_statement_type`
128 `sys`.`innodb_buffer_stats_by_schema`
129 `sys`.`innodb_buffer_stats_by_table`
130 `sys`.`innodb_lock_waits`
131 `sys`.`io_by_thread_by_latency`
132 `sys`.`io_global_by_file_by_bytes`
133 `sys`.`io_global_by_file_by_latency`
134 `sys`.`io_global_by_wait_by_bytes`
135 `sys`.`io_global_by_wait_by_latency`
136 `sys`.`latest_file_io`
137 `sys`.`memory_by_host_by_current_bytes`
138 `sys`.`memory_by_thread_by_current_bytes`
139 `sys`.`memory_by_user_by_current_bytes`
140 `sys`.`memory_global_by_current_bytes`
141 `sys`.`memory_global_total`
142 `sys`.`metrics`
143 `sys`.`processlist`
144 `sys`.`ps_check_lost_instrumentation`
145 `sys`.`schema_auto_increment_columns`
146 `sys`.`schema_index_statistics`
147 `sys`.`schema_object_overview`
148 `sys`.`schema_redundant_indexes`
149 `sys`.`schema_table_lock_waits`
150 `sys`.`schema_table_statistics`
151 `sys`.`schema_table_statistics_with_buffer`
152 `sys`.`schema_tables_with_full_table_scans`
153 `sys`.`schema_unused_indexes`
154 `sys`.`session`
155 `sys`.`session_ssl_status`
156 `sys`.`statement_analysis`
157 `sys`.`statements_with_errors_or_warnings`
158 `sys`.`statements_with_full_table_scans`
159 `sys`.`statements_with_runtimes_in_95th_percentile`
160 `sys`.`statements_with_sorting`
161 `sys`.`statements_with_temp_tables`
162 `sys`.`sys_config`
163 `sys`.`user_summary`
164 `sys`.`user_summary_by_file_io`
165 `sys`.`user_summary_by_file_io_type`
166 `sys`.`user_summary_by_stages`
167 `sys`.`user_summary_by_statement_latency`
168 `sys`.`user_summary_by_statement_type`
169 `sys`.`version`
170 `sys`.`wait_classes_global_by_avg_latency`
171 `sys`.`wait_classes_global_by_latency`
172 `sys`.`waits_by_host_by_latency`
173 `sys`.`waits_by_user_by_latency`
174 `sys`.`waits_global_by_latency`
175 `sys`.`x$host_summary`
176 `sys`.`x$host_summary_by_file_io`
177 `sys`.`x$host_summary_by_file_io_type`
178 `sys`.`x$host_summary_by_stages`
179 `sys`.`x$host_summary_by_statement_latency`
180 `sys`.`x$host_summary_by_statement_type`
181 `sys`.`x$innodb_buffer_stats_by_schema`
182 `sys`.`x$innodb_buffer_stats_by_table`
183 `sys`.`x$innodb_lock_waits`
184 `sys`.`x$io_by_thread_by_latency`
185 `sys`.`x$io_global_by_file_by_bytes`
186 `sys`.`x$io_global_by_file_by_latency`
187 `sys`.`x$io_global_by_wait_by_bytes`
188 `sys`.`x$io_global_by_wait_by_latency`
189 `sys`.`x$latest_file_io`
190 `sys`.`x$memory_by_host_by_current_bytes`
191 `sys`.`x$memory_by_thread_by_current_bytes`
192 `sys`.`x$memory_by_user_by_current_bytes`
193 `sys`.`x$memory_global_by_current_bytes`
194 `sys`.`x$memory_global_total`
195 `sys`.`x$processlist`
196 `sys`.`x$ps_digest_95th_percentile_by_avg_us`
197 `sys`.`x$ps_digest_avg_latency_distribution`
198 `sys`.`x$ps_schema_table_statistics_io`
199 `sys`.`x$schema_flattened_keys`
200 `sys`.`x$schema_index_statistics`
201 `sys`.`x$schema_table_lock_waits`
202 `sys`.`x$schema_table_statistics`
203 `sys`.`x$schema_table_statistics_with_buffer`
204 `sys`.`x$schema_tables_with_full_table_scans`
205 `sys`.`x$session`
206 `sys`.`x$statement_analysis`
207 `sys`.`x$statements_with_errors_or_warnings`
208 `sys`.`x$statements_with_full_table_scans`
209 `sys`.`x$statements_with_runtimes_in_95th_percentile`
210 `sys`.`x$statements_with_sorting`
211 `sys`.`x$statements_with_temp_tables`
212 `sys`.`x$user_summary`
213 `sys`.`x$user_summary_by_file_io`
214 `sys`.`x$user_summary_by_file_io_type`
215 `sys`.`x$user_summary_by_stages`
216 `sys`.`x$user_summary_by_statement_latency`
217 `sys`.`x$user_summary_by_statement_type`
218 `sys`.`x$wait_classes_global_by_avg_latency`
219 `sys`.`x$wait_classes_global_by_latency`
220 `sys`.`x$waits_by_host_by_latency`
221 `sys`.`x$waits_by_user_by_latency`
222 `sys`.`x$waits_global_by_latency`
223 `zlm`.`t1`
224 `zlm`.`t2`
225 
226 //It shows all the tables in all databases one line each table.
 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)

 

 

Find
out all the MyISAM tables.

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

 1 [root@zlm2 09:28:33 /data/mysql/mysql3308/data]
 2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --engine=MyISAM
 3 `mysql`.`columns_priv`
 4 `mysql`.`db`
 5 `mysql`.`event`
 6 `mysql`.`func`
 7 `mysql`.`ndb_binlog_index`
 8 `mysql`.`proc`
 9 `mysql`.`procs_priv`
10 `mysql`.`proxies_priv`
11 `mysql`.`tables_priv`
12 `mysql`.`user`
 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)

 

 

*Find out all the tables in database “zlm” which does not
have auto_increment column.***

Execute a
SQL statement.

1 [root@zlm2 09:32:55 /data/mysql/mysql3308/data]
2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --dblike=zlm --autoinc=0
3 `zlm`.`t1`
4 `zlm`.`t2`
 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)

 

 

*Find out all the** ta**bles which is
empty.***

Check
the execute plan.

 1 [root@zlm2 09:33:14 /data/mysql/mysql3308/data]
 2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --empty
 3 `mysql`.`columns_priv`
 4 `mysql`.`event`
 5 `mysql`.`func`
 6 `mysql`.`ndb_binlog_index`
 7 `mysql`.`plugin`
 8 `mysql`.`procs_priv`
 9 `mysql`.`servers`
10 `mysql`.`slave_master_info`
11 `mysql`.`slave_relay_log_info`
12 `mysql`.`slave_worker_info`
13 `mysql`.`time_zone`
14 `mysql`.`time_zone_leap_second`
15 `mysql`.`time_zone_name`
16 `mysql`.`time_zone_transition`
17 `mysql`.`time_zone_transition_type`
18 `performance_schema`.`metadata_locks`
19 `performance_schema`.`prepared_statements_instances`
20 `performance_schema`.`replication_group_member_stats`
21 `performance_schema`.`replication_group_members`
 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)

 

 

Find out all the tables sorted descendingly by
size.

Check
slow log.

  1 [root@zlm2 09:38:22 /data/mysql/mysql3308/data]
  2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --printf "%T\t%D.%N\n" | sort -rn
  3 1671168    `mysql`.`help_topic`
  4 304624    `mysql`.`proc`
  5 180224    `mysql`.`help_keyword`
  6 81920    `mysql`.`help_relation`
  7 32768    `mysql`.`help_category`
  8 16384    `zlm`.`t2`
  9 16384    `zlm`.`t1`
 10 16384    `sys`.`sys_config`
 11 16384    `mysql`.`time_zone_transition_type`
 12 16384    `mysql`.`time_zone_transition`
 13 16384    `mysql`.`time_zone_name`
 14 16384    `mysql`.`time_zone_leap_second`
 15 16384    `mysql`.`time_zone`
 16 16384    `mysql`.`slave_worker_info`
 17 16384    `mysql`.`slave_relay_log_info`
 18 16384    `mysql`.`slave_master_info`
 19 16384    `mysql`.`servers`
 20 16384    `mysql`.`server_cost`
 21 16384    `mysql`.`plugin`
 22 16384    `mysql`.`innodb_table_stats`
 23 16384    `mysql`.`innodb_index_stats`
 24 16384    `mysql`.`gtid_executed`
 25 16384    `mysql`.`engine_cost`
 26 11110    `mysql`.`tables_priv`
 27 10053    `mysql`.`proxies_priv`
 28 6096    `mysql`.`db`
 29 5012    `mysql`.`user`
 30 4096    `mysql`.`procs_priv`
 31 4096    `mysql`.`columns_priv`
 32 2048    `mysql`.`event`
 33 1024    `mysql`.`ndb_binlog_index`
 34 1024    `mysql`.`func`
 35 0    `sys`.`x$waits_global_by_latency`
 36 0    `sys`.`x$waits_by_user_by_latency`
 37 0    `sys`.`x$waits_by_host_by_latency`
 38 0    `sys`.`x$wait_classes_global_by_latency`
 39 0    `sys`.`x$wait_classes_global_by_avg_latency`
 40 0    `sys`.`x$user_summary_by_statement_type`
 41 0    `sys`.`x$user_summary_by_statement_latency`
 42 0    `sys`.`x$user_summary_by_stages`
 43 0    `sys`.`x$user_summary_by_file_io_type`
 44 0    `sys`.`x$user_summary_by_file_io`
 45 0    `sys`.`x$user_summary`
 46 0    `sys`.`x$statements_with_temp_tables`
 47 0    `sys`.`x$statements_with_sorting`
 48 0    `sys`.`x$statements_with_runtimes_in_95th_percentile`
 49 0    `sys`.`x$statements_with_full_table_scans`
 50 0    `sys`.`x$statements_with_errors_or_warnings`
 51 0    `sys`.`x$statement_analysis`
 52 0    `sys`.`x$session`
 53 0    `sys`.`x$schema_tables_with_full_table_scans`
 54 0    `sys`.`x$schema_table_statistics_with_buffer`
 55 0    `sys`.`x$schema_table_statistics`
 56 0    `sys`.`x$schema_table_lock_waits`
 57 0    `sys`.`x$schema_index_statistics`
 58 0    `sys`.`x$schema_flattened_keys`
 59 0    `sys`.`x$ps_schema_table_statistics_io`
 60 0    `sys`.`x$ps_digest_avg_latency_distribution`
 61 0    `sys`.`x$ps_digest_95th_percentile_by_avg_us`
 62 0    `sys`.`x$processlist`
 63 0    `sys`.`x$memory_global_total`
 64 0    `sys`.`x$memory_global_by_current_bytes`
 65 0    `sys`.`x$memory_by_user_by_current_bytes`
 66 0    `sys`.`x$memory_by_thread_by_current_bytes`
 67 0    `sys`.`x$memory_by_host_by_current_bytes`
 68 0    `sys`.`x$latest_file_io`
 69 0    `sys`.`x$io_global_by_wait_by_latency`
 70 0    `sys`.`x$io_global_by_wait_by_bytes`
 71 0    `sys`.`x$io_global_by_file_by_latency`
 72 0    `sys`.`x$io_global_by_file_by_bytes`
 73 0    `sys`.`x$io_by_thread_by_latency`
 74 0    `sys`.`x$innodb_lock_waits`
 75 0    `sys`.`x$innodb_buffer_stats_by_table`
 76 0    `sys`.`x$innodb_buffer_stats_by_schema`
 77 0    `sys`.`x$host_summary_by_statement_type`
 78 0    `sys`.`x$host_summary_by_statement_latency`
 79 0    `sys`.`x$host_summary_by_stages`
 80 0    `sys`.`x$host_summary_by_file_io_type`
 81 0    `sys`.`x$host_summary_by_file_io`
 82 0    `sys`.`x$host_summary`
 83 0    `sys`.`waits_global_by_latency`
 84 0    `sys`.`waits_by_user_by_latency`
 85 0    `sys`.`waits_by_host_by_latency`
 86 0    `sys`.`wait_classes_global_by_latency`
 87 0    `sys`.`wait_classes_global_by_avg_latency`
 88 0    `sys`.`version`
 89 0    `sys`.`user_summary_by_statement_type`
 90 0    `sys`.`user_summary_by_statement_latency`
 91 0    `sys`.`user_summary_by_stages`
 92 0    `sys`.`user_summary_by_file_io_type`
 93 0    `sys`.`user_summary_by_file_io`
 94 0    `sys`.`user_summary`
 95 0    `sys`.`statements_with_temp_tables`
 96 0    `sys`.`statements_with_sorting`
 97 0    `sys`.`statements_with_runtimes_in_95th_percentile`
 98 0    `sys`.`statements_with_full_table_scans`
 99 0    `sys`.`statements_with_errors_or_warnings`
100 0    `sys`.`statement_analysis`
101 0    `sys`.`session_ssl_status`
102 0    `sys`.`session`
103 0    `sys`.`schema_unused_indexes`
104 0    `sys`.`schema_tables_with_full_table_scans`
105 0    `sys`.`schema_table_statistics_with_buffer`
106 0    `sys`.`schema_table_statistics`
107 0    `sys`.`schema_table_lock_waits`
108 0    `sys`.`schema_redundant_indexes`
109 0    `sys`.`schema_object_overview`
110 0    `sys`.`schema_index_statistics`
111 0    `sys`.`schema_auto_increment_columns`
112 0    `sys`.`ps_check_lost_instrumentation`
113 0    `sys`.`processlist`
114 0    `sys`.`metrics`
115 0    `sys`.`memory_global_total`
116 0    `sys`.`memory_global_by_current_bytes`
117 0    `sys`.`memory_by_user_by_current_bytes`
118 0    `sys`.`memory_by_thread_by_current_bytes`
119 0    `sys`.`memory_by_host_by_current_bytes`
120 0    `sys`.`latest_file_io`
121 0    `sys`.`io_global_by_wait_by_latency`
122 0    `sys`.`io_global_by_wait_by_bytes`
123 0    `sys`.`io_global_by_file_by_latency`
124 0    `sys`.`io_global_by_file_by_bytes`
125 0    `sys`.`io_by_thread_by_latency`
126 0    `sys`.`innodb_lock_waits`
127 0    `sys`.`innodb_buffer_stats_by_table`
128 0    `sys`.`innodb_buffer_stats_by_schema`
129 0    `sys`.`host_summary_by_statement_type`
130 0    `sys`.`host_summary_by_statement_latency`
131 0    `sys`.`host_summary_by_stages`
132 0    `sys`.`host_summary_by_file_io_type`
133 0    `sys`.`host_summary_by_file_io`
134 0    `sys`.`host_summary`
135 0    `performance_schema`.`variables_by_thread`
136 0    `performance_schema`.`user_variables_by_thread`
137 0    `performance_schema`.`users`
138 0    `performance_schema`.`threads`
139 0    `performance_schema`.`table_lock_waits_summary_by_table`
140 0    `performance_schema`.`table_io_waits_summary_by_table`
141 0    `performance_schema`.`table_io_waits_summary_by_index_usage`
142 0    `performance_schema`.`table_handles`
143 0    `performance_schema`.`status_by_user`
144 0    `performance_schema`.`status_by_thread`
145 0    `performance_schema`.`status_by_host`
146 0    `performance_schema`.`status_by_account`
147 0    `performance_schema`.`socket_summary_by_instance`
148 0    `performance_schema`.`socket_summary_by_event_name`
149 0    `performance_schema`.`socket_instances`
150 0    `performance_schema`.`setup_timers`
151 0    `performance_schema`.`setup_objects`
152 0    `performance_schema`.`setup_instruments`
153 0    `performance_schema`.`setup_consumers`
154 0    `performance_schema`.`setup_actors`
155 0    `performance_schema`.`session_variables`
156 0    `performance_schema`.`session_status`
157 0    `performance_schema`.`session_connect_attrs`
158 0    `performance_schema`.`session_account_connect_attrs`
159 0    `performance_schema`.`rwlock_instances`
160 0    `performance_schema`.`replication_group_member_stats`
161 0    `performance_schema`.`replication_group_members`
162 0    `performance_schema`.`replication_connection_status`
163 0    `performance_schema`.`replication_connection_configuration`
164 0    `performance_schema`.`replication_applier_status_by_worker`
165 0    `performance_schema`.`replication_applier_status_by_coordinator`
166 0    `performance_schema`.`replication_applier_status`
167 0    `performance_schema`.`replication_applier_configuration`
168 0    `performance_schema`.`pxc_cluster_view`
169 0    `performance_schema`.`prepared_statements_instances`
170 0    `performance_schema`.`performance_timers`
171 0    `performance_schema`.`objects_summary_global_by_type`
172 0    `performance_schema`.`mutex_instances`
173 0    `performance_schema`.`metadata_locks`
174 0    `performance_schema`.`memory_summary_global_by_event_name`
175 0    `performance_schema`.`memory_summary_by_user_by_event_name`
176 0    `performance_schema`.`memory_summary_by_thread_by_event_name`
177 0    `performance_schema`.`memory_summary_by_host_by_event_name`
178 0    `performance_schema`.`memory_summary_by_account_by_event_name`
179 0    `performance_schema`.`hosts`
180 0    `performance_schema`.`host_cache`
181 0    `performance_schema`.`global_variables`
182 0    `performance_schema`.`global_status`
183 0    `performance_schema`.`file_summary_by_instance`
184 0    `performance_schema`.`file_summary_by_event_name`
185 0    `performance_schema`.`file_instances`
186 0    `performance_schema`.`events_waits_summary_global_by_event_name`
187 0    `performance_schema`.`events_waits_summary_by_user_by_event_name`
188 0    `performance_schema`.`events_waits_summary_by_thread_by_event_name`
189 0    `performance_schema`.`events_waits_summary_by_instance`
190 0    `performance_schema`.`events_waits_summary_by_host_by_event_name`
191 0    `performance_schema`.`events_waits_summary_by_account_by_event_name`
192 0    `performance_schema`.`events_waits_history_long`
193 0    `performance_schema`.`events_waits_history`
194 0    `performance_schema`.`events_waits_current`
195 0    `performance_schema`.`events_transactions_summary_global_by_event_name`
196 0    `performance_schema`.`events_transactions_summary_by_user_by_event_name`
197 0    `performance_schema`.`events_transactions_summary_by_thread_by_event_name`
198 0    `performance_schema`.`events_transactions_summary_by_host_by_event_name`
199 0    `performance_schema`.`events_transactions_summary_by_account_by_event_name`
200 0    `performance_schema`.`events_transactions_history_long`
201 0    `performance_schema`.`events_transactions_history`
202 0    `performance_schema`.`events_transactions_current`
203 0    `performance_schema`.`events_statements_summary_global_by_event_name`
204 0    `performance_schema`.`events_statements_summary_by_user_by_event_name`
205 0    `performance_schema`.`events_statements_summary_by_thread_by_event_name`
206 0    `performance_schema`.`events_statements_summary_by_program`
207 0    `performance_schema`.`events_statements_summary_by_host_by_event_name`
208 0    `performance_schema`.`events_statements_summary_by_digest`
209 0    `performance_schema`.`events_statements_summary_by_account_by_event_name`
210 0    `performance_schema`.`events_statements_history_long`
211 0    `performance_schema`.`events_statements_history`
212 0    `performance_schema`.`events_statements_current`
213 0    `performance_schema`.`events_stages_summary_global_by_event_name`
214 0    `performance_schema`.`events_stages_summary_by_user_by_event_name`
215 0    `performance_schema`.`events_stages_summary_by_thread_by_event_name`
216 0    `performance_schema`.`events_stages_summary_by_host_by_event_name`
217 0    `performance_schema`.`events_stages_summary_by_account_by_event_name`
218 0    `performance_schema`.`events_stages_history_long`
219 0    `performance_schema`.`events_stages_history`
220 0    `performance_schema`.`events_stages_current`
221 0    `performance_schema`.`cond_instances`
222 0    `performance_schema`.`accounts`
223 0    `mysql`.`slow_log`
224 0    `mysql`.`general_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';

 

 

**Find out all the** ta**bles over
16K.**

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

 1 [root@zlm2 09:44:10 /data/mysql/mysql3308/data]
 2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --tablesize +16
 3 `mysql`.`columns_priv`
 4 `mysql`.`db`
 5 `mysql`.`engine_cost`
 6 `mysql`.`event`
 7 `mysql`.`func`
 8 `mysql`.`gtid_executed`
 9 `mysql`.`help_category`
10 `mysql`.`help_keyword`
11 `mysql`.`help_relation`
12 `mysql`.`help_topic`
13 `mysql`.`innodb_index_stats`
14 `mysql`.`innodb_table_stats`
15 `mysql`.`ndb_binlog_index`
16 `mysql`.`plugin`
17 `mysql`.`proc`
18 `mysql`.`procs_priv`
19 `mysql`.`proxies_priv`
20 `mysql`.`server_cost`
21 `mysql`.`servers`
22 `mysql`.`slave_master_info`
23 `mysql`.`slave_relay_log_info`
24 `mysql`.`slave_worker_info`
25 `mysql`.`tables_priv`
26 `mysql`.`time_zone`
27 `mysql`.`time_zone_leap_second`
28 `mysql`.`time_zone_name`
29 `mysql`.`time_zone_transition`
30 `mysql`.`time_zone_transition_type`
31 `mysql`.`user`
32 `sys`.`sys_config`
33 `zlm`.`t1`
34 `zlm`.`t2`
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 #

 

 

**Find out table in “zlm” database which
name is like “t3” then drop it.***

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)
 1 (zlm@192.168.1.101 3308)[zlm]>create table t3 like t2;
 2 Query OK, 0 rows affected (0.02 sec)
 3 
 4 (zlm@192.168.1.101 3308)[zlm]>show create table t3\G
 5 *************************** 1. row ***************************
 6        Table: t3
 7 Create Table: CREATE TABLE `t3` (
 8   `id` int(11) NOT NULL,
 9   `name` char(10) DEFAULT NULL,
10   PRIMARY KEY (`id`)
11 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
12 1 row in set (0.00 sec)
13 
14 (zlm@192.168.1.101 3308)[zlm]>show tables;
15 +---------------+
16 | Tables_in_zlm |
17 +---------------+
18 | t1            |
19 | t2            |
20 | t3            |
21 +---------------+
22 3 rows in set (0.00 sec)
23 
24 [root@zlm2 10:11:26 /data/mysql/mysql3308/data]
25 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --print zlm --tbllike=t3 --exec-plus "drop table %s"
26 `zlm`.`t3`
27 
28 (zlm@192.168.1.101 3308)[zlm]>show tables;
29 +---------------+
30 | Tables_in_zlm |
31 +---------------+
32 | t1            |
33 | t2            |
34 +---------------+
35 2 rows in set (0.00 sec)
36 
37 //The table has been droped directly by the last command in pt-find.

 

 

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

Summary

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 #
  • pt-find
    is rather useful when retrieving target table with flexible
    conditions you defined.
  • pt-find
    has a lot of options.We should be more careful when using ddl
    statement by “–exec” or “–exec-plus”.
  • We can
    use it to collect the important information about a bunch of tables
    easily which can increse the working efficiency.

 

 

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
    .*