Percona-Tookit工具包之pt-find

时间:2019-05-11 09:49来源:计算机教程
    Preface Preface         We used to use "find" command in linuxor AIX when we need to get a certain file but cannot rember the precisename.We will execute "find /pathtobegin -name xxx" in our OS prompt.Isthere any tool can find a s

 

 

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.

vnsc5858威尼斯城官网,Make sure "slow_query_on" has been set "on" and reduce the "long_query_time" into "0.01".

编辑:计算机教程 本文来源:Percona-Tookit工具包之pt-find

关键词: