MySQL slow query analysis _ MySQL

MySQL slow query analysis bitsCN.com
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 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 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 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 open one of the log statistics files. you will see To: Report for slow logs:/var/log/the slow query log file analyzed by the mysql-slow.log 40 queries total, 12 unique 40 queries; besides repeated, there are 12 queries Sorted 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, 588.994 ms avg average Time of each query, 588.994 ms minimum Time to 588.9 94 MS max 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 S Ample: 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 maximum 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: coreseektest @ localhost: 100.00% (2) of query, 100.00% (40) of all usersQuery abstract: SET timestamp = N; SELECT * FROM orders Ents 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. Prepared by alex.wubitsCN.com