Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the slow query of MySQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the slow query of MySQL".

MySQL slow query analysis

When we do system performance tuning, the optimization of slow query statements in the database is essential, especially the heavy MYSQL application type of e-commerce type.

Let's take a look at how to do a good slow query analysis of MYSQL.

1. Enable the slow query log of MYSQL

Www.2cto.com

First, add the slow query configuration to the my.cnf configuration, then create the slow query log file, change the users and groups to mysql, and finally restart mysqld.

Vim / etc/my .cnf

# add the following lines under [mysqld] in the configuration file

Log-slow-queries= / var/log/mysql-slow .log

Long_query_time=0.01 # indicates that any query time longer than 10ms is considered a slow query

Log-queries-not-using-indexes # indicates that queries that do not use indexes are also logged

Touch / var/log/mysql-slow .log

Chown mysql.mysql / var/log/mysql-slow .log

/ etc/init .d / mysqld restart

Then test whether the slow query is valid. You can visit myadmin or run a select sleep (1), and then cat / var/log/-slow.log. If you see a record, the setting is successful. However, the generation of slow query log only faithfully records every slow query, which is not convenient for us to do analysis.

2. Install mysqlslaslow query analysis tool

Wget http: / / hackmysql .com / scripts/mysqlsla-2 .03. Tar .gz

Tar xzf mysqlsla-2.03. Tar .gz

Cd mysqlsla-2.03

Www.2cto.com

Perl Makefile.PL

Make

Make 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

# it's actually a perl script

# / usr/local/bin/mysqlsla: a / usr/bin/perl-wscript text executable

3. Slow query statistics

# count the top 10 slow queries with the largest number of occurrences

Mysqlsla-lt slow / var/log/mysql-slow. Log-top10-sort c_sum > top10_count_sum.log

# the sum of the top 10 slow queries of statistical execution time

Mysqlsla-lt slow / var/log/mysql-slow. Log-top10-sort t_sum > top10_time_sum.log

# Statistics of the top 10 slow queries with the longest average execution time (commonly used)

Mysqlsla-lt slow / var/log/mysql-slow. Log-top10-sort t_avg > top10_time_avg.log

Open one of the log statistics files and you will see:

Www.2cto.com

Report for slow logs: / var/log/mysql-slow.log the analyzed slow query log file

40 queries total, 12 unique 40 queries; except for duplicates, there are 12 queries

Sorted by'tasking avg 'sorts 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: total execution time of 588.994 ms total, average time per query of 588.994 ms avg, minimum time of 588.994 ms, to 588.994 ms max maximum 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` ASC

LIMIT 0, 30

_ 002 _

Count: 2 (5.00%) this SQL appears twice, accounting for 5% of the total SQL

Time: the total execution time of 57.38 ms total, the average time per query of 28.69 ms avg, the shortest time of 27.503 ms, the longest time of to 29.877 ms max (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 omissions...

If you need more complex statistics, you can refer to the official document: http://hackmysql.com/mysqlsla_guide

If you want to do slow query statistics at regular intervals, such as a day, you can write a shell script and put it in / etc/crontab. In this way, query optimization can be done on a regular basis.

Thank you for your reading, these are the contents of "what is the slow query of MySQL". After the study of this article, I believe you have a deeper understanding of what the slow query of MySQL is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.