mysql慢查询日志分析工具(python写的)
D:\NormalSoftware>python mysql_filter_slow_log.py ./mysql1-slow.log --no-duplic
ates --sort-avg-query-time --top=100 >> mysql_slow_test05.txt
469行要改为:
query_time = (float(numbers[1].split()[0]), float(numbers[2].split()[0]),
float(numbers[3].split()[0]), float(numbers[4]))
150 151行注释掉:
#locale.setlocale(locale.LC_NUMERIC,
# os.name == 'nt' and 'en' or 'en_US.ISO8859-1')
mysql_filter_slow_log.py
使用方法:(这里只介绍python的使用方法)
python mysql_filter_slow_log.py ./mysql1-slow.log --no-duplicates --sort-execution-count --top=10 >> mysql_slow_test.txt
备注:mysql1-slow.log 慢查询日志名称
--no-duplicates
--sort-execution-count
--top=10 取前十位
mysql_slow_test.txt 输出分析报告
附录:
官方给出的使用方法举例:
=====================================
1
2
3
4
5
6
7
8
9
|
# Filter slow queries executed for at least 3 seconds not from root, remove duplicates, # apply execution count as first sorting value and save first 10 unique queries to file. # In addition, remember last input file position and statistics. php mysql_filter_slow_log.php -T= 3 -eu=root --no-duplicates --sort-execution-count --top= 10 --incremental linux-slow.log > mysql-slow-queries.log # Start permanent filtering of all slow queries from now on: at least 3 seconds or examining 10000 rows, exclude users root and test tail -f -n 0 linux-slow.log | python mysql_filter_slow_log.py -T= 3 -R= 10000 -eu=root -eu=test & # (-n 0 outputs only lines generated after start of tail) # Stop permanent filtering kill `ps auxww | grep 'tail -f -n 0 linux-slow.log' | egrep -v grep | awk '{print $2}' ` |
====================================
官方给出的命令参数:
==================================
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
-T=min_query_time -R=min_rows_examined -ih, --include-host -eh, --exclude-host -iu, --include-user -eu, --exclude-user -iq, --include-query --date=date_first-date_last Include only queries between date_first (and date_last). Input: Date Range: 13.11 . 2006 -> 13.11 . 2006 - 14.11 . 2006 (exclusive) 13.11 . 2006 - 15.11 . 2006 -> 13.11 . 2006 - 16.11 . 2006 (exclusive) 15 - 11 - 2006 - 11 / 13 / 2006 -> 13.11 . 2006 - 16.11 . 2006 (exclusive) > 13.11 . 2006 -> 14.11 . 2006 - later 13.11 . 2006 - -> 13.11 . 2006 - later < 13.11 . 2006 -> earlier - 13.11 . 2006 (exclusive) - 13.11 . 2006 -> earlier - 14.11 . 2006 (exclusive) Please do not forget to escape the greater or lesser than symbols (><, i.e. '--date=>13.11.2006' ). Short dates are supported if you include a trailing separator (i.e. 13.11 .- 11 / 15 /). --incremental Remember input file positions and optionally --no-duplicates statistics between executions in mysql_filter_slow_log.sqlite3 --no-duplicates Powerful option to output only unique query strings with additional statistics: Execution count, first and last timestamp. Query time: avg / max / sum. Lock time: avg / max / sum. Rows examined: avg / max / sum. Rows sent: avg / max / sum. --no-output Do not print statistics, just update database with incremental statistics Default ordering of unique queries: --sort-sum-query-time [ 1 . position] --sort-avg-query-time [ 2 . position] --sort-max-query-time [ 3 . position] --sort-sum-lock-time [ 4 . position] --sort-avg-lock-time [ 5 . position] --sort-max-lock-time [ 6 . position] --sort-sum-rows-examined [ 7 . position] --sort-avg-rows-examined [ 8 . position] --sort-max-rows-examined [ 9 . position] --sort-execution-count [ 10 . position] --sort-sum-rows-sent [ 11 . position] --sort-avg-rows-sent [ 12 . position] --sort-max-rows-sent [ 13 . position] --sort=sum-query-time,avg-query-time,max-query-time,... You can include multiple sorting values separated by commas. --sort=sqt,aqt,mqt,slt,alt,mlt,sre,are,mre,ec,srs,ars,mrs Every long sorting option has an equivalent short form (first character of each word). --top=max_unique_query_count Output maximal max_unique_query_count different unique queries --details Enables output of timestamp based unique query time lines after user list (i.e. # Query_time: 81 Lock_time: 0 Rows_sent: 884 Rows_examined: 2448350 ). --help Output this message only and quit [multiple] options can be passed more than once to set multiple values. [position] options take the position of their first occurrence into account. The first passed option will replace the default first sorting, ... Remaining default ordering options will keep their relative positions. |
====================================
官方给出的配置文件中管理慢日志参数的配置
====================================
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
# I.e. you could add the following lines under the [mysqld] section of your my.ini or my.cnf configuration file: # Log all queries taking more than 3 seconds long_query_time= 3 # minimum: 1 , default : 10 # MySQL >= 5.1 . 21 (or patched): 3 seconds = 3000000 microseconds # long_query_time= 3.000000 # minimum: 0.000001 ( 1 microsecond) # Activate the Slow Query Log slow_query_log # >= 5.1 . 29 # log-slow-queries # deprecated since 5.1 . 29 # Write to a custom file name (>= 5.1 . 29 ) # slow_query_log_file=file_name # default : /data_dir/host_name-slow.log # Log all queries without indexes # log-queries-not-using-indexes # Log only queries which examine at least N rows (>= 5.1 . 21 ) # min_examined_row_limit= 1000 # default : 0 # Log slow OPTIMIZE TABLE, ANALYZE TABLE, and ALTER TABLE statements # log-slow-admin-statements # Log slow queries executed by replication slaves (>= 5.1 . 21 ) # log-slow-slave-statements # MySQL 5.1 . 6 through 5.1 . 20 had a default value of log-output=TABLE, so you should force # Attention: logging to TABLE only includes whole seconds information log-output=FILE ## Admin query for online activation is possible since MySQL 5.1 (without server restart) ## SET @ @global .slow_query_log= 1 ## SET @ @global .long_query_time= 1 ## Show current variables related to the Slow Query Log ## SHOW GLOBAL VARIABLES WHERE Variable_name REGEXP 'admin|min_examined|log_output|log_queries|log_slave|long|slow_quer' |
======================================
注意:在执行脚本的时候会报数据类型的错误,具体错误指定469行,经过查看,实际慢查询日志中的query_time是float类型,而在这个脚本工具中定义的确实int类型。于是自行修改!
默认:
======================
query_time = (int(numbers[1].split()[0]), int(numbers[2].split()[0]),
int(numbers[3].split()[0]), int(numbers[4]))
======================
修改为:
======================
query_time = (float(numbers[1].split()[0]), float(numbers[2].split()[0]),
float(numbers[3].split()[0]), float(numbers[4]))
Never give up !