MySQL slow query analysis

MySQL slow query analysis is essential for optimizing database slow query statements when we perform systematic optimization, especially for e-commerce-type severe MYSQL applications. Let's take a look at how to analyze MYSQL slow queries. 1. Enable MYSQL slow query log www.2cto.com first in my. add the slow query configuration to the cnf configuration, create the slow query log file, modify the user and group to mysql, and restart mysqld. Vim/etc/my. cnf # Add the following lines under the configuration file [mysqld]: log-slow-queries =/var/log/mysql-slow. loglong_query_time = 0.01 # indicates that all queries whose query time exceeds 10 ms are considered as slow queries log-queries-not-using-indexes # indicates that queries without indexes also record log touch/var/log/ mysql-slow. logchown mysql. mysql/var/log/mysql-slow. log/etc/init. d/mysqld restart and test whether the slow query is effective, you can access phpmyadmin or run a select sleep (1), and then cat/var/log/mysql-slow.log, if a record is displayed, the setting is successful. However, generating slow query logs only faithfully records every slow query, which is not convenient for us to analyze. 2. Install mysqlsla slow query and analysis tool wget http: // hackmysql. com/scripts/mysqlsla-2. 03. tar. gztar xzf mysqlsla-2.03. tar. gzcd mysqlsla-2.03 www.2cto.com perl Makefile. PLmakemake install # installation information # Installing/usr/local/share/perl5/mysqlsla. pm # Installing/usr/local/share/man/man3/mysqlsla.3pm # Installing/usr/local/bin/mysqlsla # Appending installation info to/usr/lib/perl5/perllocal. pod file/usr/local/bin/mysqlsla # Actually yes A perl script #/usr/local/bin/mysqlsla: a/usr/bin/perl-w script text executable3, slow query statistics # count the first 10 slow queries with the most frequent occurrences mysqlsla-lt slow/var/log/mysql-slow. log-top 10-sort c_sum> top10_count_sum.log # calculate the total execution time of the first 10 slow queries. mysqlsla-lt slow/var/log/mysql-slow. log-top 10-sort t_sum> top10_time_sum.log # count the first 10 slow queries with the longest average execution time (commonly used) mysqlsla-lt slow/var/log/mysql-slow. log-top 10-sort t_avg> top10_time_avg.log A log statistics file, you will see: www.2cto.com Report for slow logs:/var/log/mysql-slow.log slow query log file analyzed 40 queries total, 12 unique 40 queries; in addition to the repeat, there are 12 Sorted queries by 't_ avg' Sorted by average query Time Grand Totals: Time 4 s, Lock 0 s, Rows sent 236, rows Examined 8.63k ______________________________________________________________ 001 ___ Count: 1 (2.50%) This SQL appears once, accounting for 2.5% of the total SQL Time: 588.994 MS total execution Time sum, 588.994 MS avg average time per Query, 588.994 ms shortest Time to 588.994 ms max longest Time (13.78%) Lock Time (s): 91 μs total, 91 μs avg, 91 μs to 91 μs max (2.34%) Rows sent: 30 avg, 30 to 30 max (12.71%) Rows examined: 899 avg, 899 to 899 max (10.41%) Database: users: coreseektest @ localhost: 100.00% (1) of query, 100.00% (40) of all users Query abstract: SET timestamp = N; SELECT * FROM ecm_goods WHERE goods_name LIKE's 'order BY ecm_goods.brand _ Id asc limit n, N; Query sample: SET timestamp = 1341467496; SELECT * FROM 'ecm _ goods 'WHERE goods_name like 'refrigerator 'order BY 'ecm _ goods '. 'brand _ id' ASCLIMIT 0, 30, 28.69 MS avg average Time per query, 27.503 MS shortest Time to 29.877 MS max longest Time (1.34%) Lock Time (s): 134 µs total, 67 μs avg, 64 μs to 70 μs max (3.44%) Rows sent: 3 avg, 3 to 3 max (2.54%) Rows examined: 3 avg, 3 to 3 max (0.07%) Database: Users: www.2cto.com coreseektest @ localhost: 100.00% (2) of query, 100.00% (40) of all usersQuery abstract: SET timestamp = N; SELECT * FROM documents limit n, N; Query sample: SET timestamp = 1341399487; SELECT * FROM 'documents' LIMIT 0, 30 ;... Other omitted... For more complex statistics, see the official documentation: http://hackmysql.com/mysqlsla_guide If you want to generate slow query statistics at intervals, such as one day, you can write a shell script and put it in/etc/crontab. In this way, you can perform regular query optimization. Author alex. wu