When the system performance reaches the bottleneck, it is necessary to find out those operations on the performance impact of the system is relatively large, here you can use the database slow query log function to record some more time-consuming data can be manipulated to determine where the need for optimization.
Here are some common commands for using slow query logs
1. To see if the slow query log is turned on:
Show variables like '%slow% ';
2, set up queries that do not use indexes to log in
Set global log_queries_not_using_indexes=on;
3, see how long the SQL is logged to the slow query log
Show variables like ' Long_query_time '; 4, set more than how long SQL is logged to slow query log set global long_query_time=0;5, turn on slow query log
Set global slow_query_log=on;
6. View the log record location
Show variables like ' Slow_query_log_file '
7. View the log (this is the command under Linux)
Tail-50/home/mysql/data/mysql-slow.log
7. View detailed Configuration Items
Show variables like '%log% ';
Here's how to read the log content
Host information for executing SQL
#[email Protected]:root[root] @localhost []
Execution information for SQL
# query_time:0.004883 lock_time:0.000000 rows_sent:1 rows_examined:1use mblog;
SQL execution Time (timestamp)
SET timestamp=1463926469;
Contents of SQL
SHOW COLUMNS from ' mblog '. ' Tb_item ';
MySQL Slow scan log analysis tool:
1 Mysqldumpslow (MySQL comes with the tool, the analysis is not too detailed)
2 pt-query-digest (recommended, moon or foreign circle, this analysis is better) http://www.percona.com/get/pt-query-digest
Here is a brief explanation of the usage of pt-query-digest
1 syntax
Create-review-table when you use the--review parameter to output the analysis results to a table, it is created automatically if no tables are available.
Create-history-table when you use the--history parameter to output the analysis results to a table, it is created automatically if no tables are available.
Filter the input slow query is matched and filtered by the specified string before parsing
Limit limits the output percent or number, the default value is 20, the slowest 20 statements are output, if 50% is the total response time from the largest to the small sort, the output to the sum of 50% position cutoff.
Host MySQL server address
User MySQL username password mysql user password
History saves the analysis results to the table, the analysis results are detailed, and the next time you use--history, if there is the same statement, and the query is in a different time period than the historical table, it will be recorded in the datasheet. You can compare the historical changes of a type of query by querying the same checksum.
Review Save the analysis results to a table, this analysis only to the query criteria parameterized, a type of query a record, relatively simple. The next time you use--review, if you have the same statement analysis, it will not be recorded in the datasheet.
Output analysis result type, values can be report (standard analysis Reports), Slowlog (Mysql slow log), JSON, Json-anon, general use report for readability.
Since from when to parse, the value is a string, can be specified in a "yyyy-mm-dd [HH:MM:SS]" format point in time, or it can be a simple time value: s (seconds), H (Hours), M (minutes), D (days), such as 12h means starting from 12 hours ago statistics.
Until cut-off time, with-since can be analyzed for a period of time slow query.
2 The meaning of each of the statistical results Overall: How many queries are in total. Time range: The timeframe for query execution. Unique: The number of unique queries, that is, after the query criteria are parameterized, the total number of different queries. Total: min: min Max: Max avg: Average 95%: All values are arranged from small to large, and the position is in the number 95%, which is generally the most valuable reference. Median: Median, arranges all values from small to large and positions in the middle number. Response: Total response time. Time: This query is accounted for in the overall period of the analysis. Calls: number of executions, that is, the total number of this analysis of this type of query statements. R/call: The average response time per execution. Item: Query object Databases: library name users: number of times per user (%) query_time distribution: The query time distribution, the length reflects the interval ratio, in this case 1s-10s between the number of queries is twice times more than 10s. Tables: Table Explain that is involved in the query: example
Objects that need to be optimized usually have
1, more queries and a long time consuming SQL
First few queries with pt-query-digest analysis
2,io Large SQL
Note the rows examine item in the Pt-query-digest analysis
3, bit-hit-indexed SQL
Note the comparison of rows examine and rows send in the pt-query-digest analysis.
MySQL Slow query log usage
