MySQL Slow query log analysis tool uses

Reference: 5 kinds of MySQL log analysis tools to compete http://blog.csdn.net/gzh0222/article/details/10384475

1.mysql-log-filter Tool Script Usage instructions:

An analysis tool found on Google code. Two executable scripts for Python and PHP are available. http://code.google.com/p/mysql-log-filter/(need to move ladder climbing wall), 51cto download Link:

How to use: (This is the only way to use Python)

Python mysql_filter_slow_log.py./mysql1-slow.log--no-duplicates--sort-execution-count--top=10 >> mysql_slow _test.txt

Remark: Mysql1-slow.log Slow query log name

--no-duplicates

--sort-execution-count

--TOP=10 take top Ten

Mysql_slow_test.txt Output Analysis Report

Appendix:

An example of how to use the official method:

=====================================

# Filter Slow queries executed for at least 3 seconds no from root, remove duplicates,

# Apply execution count as first sorting value and save first 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 Roo T 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 $} '

====================================

The official command parameters are given:

==================================

-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 don't forget to escape the greater or lesser than symbols (><, i.e. '--date=>13.11.2006 ').

Short dates is 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_s Low_log.sqlite3

--no-duplicates powerful option to output is 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 [ten. Position]

--sort-sum-rows-sent [position]

--sort-avg-rows-sent [position]

--sort-max-rows-sent [position]

--sort=sum-query-time,avg-query-time,max-query-time,... You can include multiple sorting the values separated by commas.

--sort=sqt,aqt,mqt,slt,alt,mlt,sre,are,mre,ec,srs,ars,mrs Every long sorting option has a equivalent short form (first C Haracter 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 is passed more than once to set multiple values.

[Position] options take the position of their first occurrence into account.

The first passed option would replace the default first sorting, ...

Remaining default ordering options would keep their relative positions.

====================================

Configuration for managing slow log parameters in the official given configuration file

====================================

# i.e. 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 we 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

======================================

Note: When executing the script, the data type error is reported, the specific error specifies 469 lines, and after viewing, the query_time in the actual slow query log is the float type, and the exact int type is defined in this script tool. Then modify it yourself!

Default:

======================

Query_time = (int (numbers[1].split () [0]), int (numbers[2].split () [0]),

Int (numbers[3].split () [0]), int (numbers[4]))

======================

Modified to:

======================

Query_time = (Float (numbers[1].split () [0]), float (numbers[2].split () [0]),

Float (numbers[3].split () [0]), float (numbers[4]))

======================

This article is from "Dolphin Watching" blog, please be sure to keep this source http://swht1278.blog.51cto.com/7138082/1718661

MySQL Slow query log analysis tool uses