Percona-Tookit工具包之pt-table-usage
2024-10-16 21:02:09
Preface
There always be some table join operations in our SQL statement.Although we can know details of table join information from explain opertion by json format.Whatif you are not using MySQL 5.7?Is there a tool which can tell us how the tables are used in join operations?
Introduce
pt-table-usage is simply used to anaylyze how the queries use tables.It can indicate the data flow by the contexts in which table appear.We can either get these information from query statement directly(using "--query" option) or from a log file(It should be slow log format) insetead.
Procedure
Usage
pt-table-usage [OPTIONS] [FILES]
Common parameter
--constant-data-value //Specify the table to print as the source for constant data(default "DUAL").
--continue-on-error //It won't stop when getting errors(default "yes").
--create-table-definitions //Specify a file to read definitions in it.
--explain-extended //Specify a server to avoid ambiguous names of columns and tables.
--id-attribute //Specify a format to identify each event(default query ID).
--progress //Specify the way to print progress(default time,30s).
--query //Speicy reading from a qeury instead of log file.
Example
Print table usage relevant with constant query(insert,update).
//Create a test table.
(zlm@192.168.1.101 )[zlm]>create table test_table_usage(
-> id int,
-> name char()
-> ) engine=innodb;
Query OK, rows affected (0.02 sec) [root@zlm2 :: ~]
#pt-table-usage --query='insert into zlm.test_table_usage values(1,'zlm');'
Query_id: 0x4467805469FEF40B. //The
INSERT zlm.test_table_usage
SELECT DUAL //When specifying the constant value,it will print "DUAL" after "SELECT". [root@zlm2 :: ~]
#pt-table-usage --query='update zlm.test_table_usage set id=2 where name='zlm';'
Query_id: 0xB2EE79AD4DA99C2B.
UPDATE zlm.test_table_usage
SELECT DUAL //When specifying the constant value,it will print "DUAL" after "SELECT".
WHERE zlm.test_table_usage //There's a condition in query statment.It will be printed here. (zlm@192.168.1.101 )[zlm]>select * from test_table_usage;
Empty set (0.00 sec) //There're no records changed at all in the test table.Because those two queries are just query operations which won't be really executed.
Print table usage relevant with query(select).
[root@zlm2 :: ~]
#pt-table-usage --query='select t1.id,t1.pad from sbtest1 as t1 join sbtest2 as t2 where t1.pad=t2.pad;'
Query_id: 0x016CE309DD3D9FA3.
SELECT sbtest1 //This time,it shows the specific table name which your query data in.
TLIST sbtest1 //"TLIST" means full table scan will be used in the above query.
TLIST sbtest2
WHERE sbtest1 [root@zlm2 :: ~]
#pt-table-usage --query='select t1.id,t2.pad from sbtest1 as t1 join sbtest2 as t2 where t1.pad=t2.pad;'
Query_id: 0x016CE309DD3D9FA3.
SELECT sbtest1
SELECT sbtest2 //Have you seen the difference?The column "pad" belongs to the table "sbtest2" in the query,So there comes the line.
TLIST sbtest1 //"TLIST" means full table scan will be used in the above query either.
TLIST sbtest2
WHERE sbtest1
Print table usage according to the slow log file.
(zlm@192.168.1.101 )[sysbench]>show variables like '%slow%';
+---------------------------+----------+
| Variable_name | Value |
+---------------------------+----------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | ON |
| slow_launch_time | |
| slow_query_log | ON | //Make sure the slow log is functional.
| slow_query_log_file | slow.log |
+---------------------------+----------+
rows in set (0.00 sec) (zlm@192.168.1.101 )[sysbench]>show variables like 'long_query%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 | //Check out the threashold of slow log.
+-----------------+----------+
row in set (0.00 sec) (zlm@192.168.1.101 )[sysbench]>set long_query_time=;
Query OK, rows affected (0.00 sec) (zlm@192.168.1.101 )[sysbench]>show variables like 'long_query%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 0.000000 | //Let the slow log record every SQL statement.
+-----------------+----------+
row in set (0.00 sec) (zlm@192.168.1.101 )[sysbench]>show tables;
+--------------------+
| Tables_in_sysbench |
+--------------------+
| sbtest1 |
| sbtest2 |
+--------------------+
rows in set (0.00 sec) (zlm@192.168.1.101 )[sysbench]>select count(*) from sbtest1; //Query 1.
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec) (zlm@192.168.1.101 )[sysbench]>select count(*) from sbtest2; //Query 2.
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec) (zlm@192.168.1.101 )[sysbench]>select t1.id,t2.pad from sbtest1 as t1 join sbtest2 as t2 where t1.k<t2.k; //Query 3.
Killed //The mysql client is killed,because the query time is too long. [root@zlm2 :: ~]
#ps aux|grep mysql
mysql 1.5 3.0 pts/ Sl : : mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
root 0.0 0.0 pts/ S+ : : grep --color=auto mysql [root@zlm2 :: ~] //Slow log shows the details.
[root@zlm2 :: /data/mysql/mysql3306/data]
#tail -f slow.log # Time: --26T03::30.313395+:
# User@Host: zlm[zlm] @ zlm2 [192.168.1.101] Id:
# Query_time: 0.000170 Lock_time: 0.000070 Rows_sent: Rows_examined:
SET timestamp=;
show tables;
# Time: --26T03::43.141016+:
# User@Host: zlm[zlm] @ zlm2 [192.168.1.101] Id:
# Query_time: 0.002259 Lock_time: 0.000061 Rows_sent: Rows_examined:
SET timestamp=;
select count(*) from sbtest1; //Query 1.
# Time: --26T03::57.593601+:
# User@Host: zlm[zlm] @ zlm2 [192.168.1.101] Id:
# Query_time: 0.001549 Lock_time: 0.000060 Rows_sent: Rows_examined:
SET timestamp=;
select count(*) from sbtest2; //Query 2.
# Time: --26T03::39.471206+:
# User@Host: zlm[zlm] @ zlm2 [192.168.1.101] Id:
# Query_time: 33.401928 Lock_time: 0.000084 Rows_sent: Rows_examined: //Too many rows are examinted.It's a cartesian join.
SET timestamp=;
select t1.id,t2.pad from sbtest1 as t1 join sbtest2 as t2 where t1.k<t2.k; //Query 3. //Let's see what will show in pt-table-usage.
[root@zlm2 :: ~]
#pt-table-usage /data/mysql/mysql3306/data/slow.log
Query_id: 0x999ECD050D719733. //Query 1.
SELECT sbtest1 Query_id: 0x999ECD050D719733. //Query 2.
SELECT sbtest2 Query_id: 0x923C5317557357E2. //Query 3.
SELECT sbtest1
SELECT sbtest2
JOIN sbtest1
JOIN sbtest2 //No "WHERE" condition instructions after this line(Is that because of the killing operation?I'm not sure about it). //Let's check out the execution plan.
(zlm@192.168.1.101 )[sysbench]>explain select t1.id,t2.pad from sbtest1 as t1 join sbtest2 as t2 where t1.k<t2.k;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------------------+
| | SIMPLE | t1 | NULL | index | k_1 | k_1 | | NULL | | 100.00 | Using index |
| | SIMPLE | t2 | NULL | ALL | k_2 | NULL | NULL | NULL | | 33.33 | Range checked for each record (index map: 0x2) |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------------------+
rows in set, warning (0.00 sec) (zlm@192.168.1.101 )[sysbench]>explain format=json select t1.id,t2.pad from sbtest1 as t1 join sbtest2 as t2 where t1.k<t2.k\G
*************************** . row ***************************
EXPLAIN: {
"query_block": {
"select_id": ,
"cost_info": {
"query_cost": "19747226.04" //It's really an amazingly tremendous cost of the query.No wonder why it was killed.
},
"nested_loop": [
{
"table": {
"table_name": "t1",
"access_type": "index",
"possible_keys": [
"k_1"
],
"key": "k_1",
"used_key_parts": [
"k"
],
"key_length": "",
"rows_examined_per_scan": ,
"rows_produced_per_join": ,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "161.00",
"eval_cost": "1987.20",
"prefix_cost": "2148.20",
"data_read_per_join": "5M"
},
"used_columns": [
"id",
"k"
]
}
},
{
"table": {
"table_name": "t2",
"access_type": "ALL",
"possible_keys": [
"k_2"
],
"rows_examined_per_scan": ,
"rows_produced_per_join": ,
"filtered": "33.33",
"range_checked_for_each_record": "index map: 0x2",
"cost_info": {
"read_cost": "258.64",
"eval_cost": "6580948.13",
"prefix_cost": "19747226.04",
"data_read_per_join": "16G" //What a big size!
},
"used_columns": [
"k",
"pad"
]
}
}
]
}
}
row in set, warning (0.00 sec)
Summary
- It's very simple to use pt-table-usage,there're only several options of it.
- pt-table-usage can be used in either a query statement by specifying "--query" or a log file such as slow log by specifying the path of it.
- If your MySQL version is below 5.7,you can consider using pt-table-usage to analyze the join information of tables in queries.
最新文章
- Hadoop HDFS 用户指南
- 关于升级xcode8
- storm基础系列之一----storm并发度概念剖析
- C语言通过timeval结构设置周期
- 自己动手做 UEStudio/UltraEdit 的语法高亮文件 (*.uew)
- GCD其他实用场景
- 开源 自由 java CMS - FreeCMS1.8 网上申报
- C#获取本机所有用户名
- CDOJ 1272 Final Pan&#39;s prime numbers
- php中 0 与 字符串比较的问题
- 移动端开发利器vConsole.js,app内嵌H5开发时调试用
- 【SPOJ】NUMOFPAL - Number of Palindromes(Manacher,回文树)
- SpringBoot2.0+ DataSourceInitializer不生效的问题
- input 属性radio中设置checked 不生效
- laydate控制之前的日期不可选择
- 收到新信息,弹出popup窗口提示
- tomcat上部署CGI
- Win32 文件拖拽
- USACO 6.1 Postal Vans(一道神奇的dp)
- 博客(第0次作业)—— New Starting Point