How to tune SQL in a MySQL system: Find bottlenecks

This is Haraoka from Beyond.
Today I'll be talking about MySQL SQL tuning.

 

• The system is slow!
• The server suddenly became slow!

 

SQL tuning can have a dramatic effect in situations like this.
This time, we will show you how to find the bottleneck.

・Why SQL tuning?

About half (50%) of the reasons for a system suddenly slowing down are due to the database.
If there is a problem with the database, I think 70% of those problems are due to SQL.

100% × 0.5 × 0.7 = 35%

So, if you learn SQL tuning,
you should be able to solve about one-third of the problems that cause your system to suddenly slow down!!

*Based on past experience

・SQL tuning flow

Let's get started.
The general flow of SQL tuning is as follows:

① Identify heavy SQL
↓
② Analyze SQL
↓
③ Modify SQL or add index

Identifying heavy SQL

First, identify the heavy SQL statements

・slowlog output

slowlog and outputs heavy SQL.
This allows you to output SQL that takes more than a certain amount of time to the log.

To output Slowlog, write the following in my.cnf

[mysqld] slow_query_log = 1 --- Enables slowlog long_query_time = 1 --- Sets SQL output time

It's easiest to start with
a long_query_time This is because long_query_time can be set in 0.1 second increments, but setting it to 0.1 seconds can result in a large amount of output, so it
's best to start with SQL statements that are 1 second or longer.

Starting with the 5.1 series, the setting has become a global variable that can be changed without restarting the server, so it can be set while the server is running

mysql> set global slow_query_log = 1; mysql> set global long_query_time = 1; mysql> show variables like "%query%"; +--------------------------------+----------------+ | Variable_name | Value | +--------------------------------+----------------+ | long_query_time | 1.000000 |

If you only use "set global", the setting will be reset when you restart your computer, so you need to add it to the configuration file.
*The above does not work on the 5.0 series, so you will need to restart your computer after setting it.

$ vi /etc/my.cnf --------------------- [mysqld] slow_query_log = 1 long_query_time = 1 --------------------- $ service mysqld restrart

If you do not specify a file path, the data will be output to the data directory

$ ll /var/lib/mysql/ mysql-slow.log

・How to read Slowlog

If you look inside, you will see the following log output:

$ tail -100 mysql-slow.log

(1)# Time: 150926 4:30:04 (2)# User@Host: hogeuser[hogedb] @ [172.16.0.1] (3)# Query_time: 1.644283 Lock_time: 0.542217 Rows_sent: 3 Rows_examined: 9971 use hogedb; (4)SET timestamp=1424145644; SELECT 1 FROM aaa_history WHERE id = 26 AND code = 'bbbb';

The important thing here is Rows_examined (number of rows processed).
This is "how much data the database has read."
In other words, the higher this value, the more data has been read.

Furthermore , if you divide it by Rows_sent, you can find out how many rows were read to output one row.

Rows_examined: 9971 ÷ Rows_sent: 3 = 3323

So takes 3323 lines of data to output one line .

So, take a quick look at the logs and pick out SQL with a large Rows_examined.
SQL with a large Rows_examined value is often the bottleneck.

Running mysqldumpslow

If you want to collect statistics from the output slowlog and identify heavy SQL,
you can use the
mysqldumpslow Using this command makes it easy to find SQL that is slightly heavy and executed frequently

・Extracting logs

You can run mysqldumpslow on the slowlog directly, but
if logs have accumulated over a long period of operation, it is easier to identify the most recent heavy SQL by extracting the log to an appropriate number of lines.

$ tail -10000 /var/lib/mysql/mysql-slow.log > /home/hogeuser/160721_mysql-slow.log

・Run mysqldumpslow

Analyze the log extracted above with mysqldumpslow and write it to a separate file

$ mysqldumpslow -st /home/hogeuser/160721_mysql-slow.log > /home/hogeuser/160721_mysqldumpslow.log

-s
sort_type determines how the output is sorted.
The sorting options are as follows:

The above command will output the slowlog statistics

- Check the output results

If you check the file you just output, you will see that the queries are listed in descending order of execution time

$ more 160721_mysqldumpslow.log

①Count: 762 Time=14.12s (10761s) Lock=0.00s (0s) Rows=45.9 (34974), 5users@13hosts use aaa_db; SET timestamp=N; ②select * from aaa_history where id = N and mid = N and kid in (N,N,N,N,N,N,N,N,N,N,N,N,N) and status = N

①

② SQL
that is ranked high in the SQL output results and has a low Count and a long average Time is a heavy SQL.
This is a bottleneck that can be easily found from the slowlog.

The SQL statements that appear at the top of the output results, with a large Count, a short average Time, and a long cumulative execution time, are relatively heavy and occur frequently.
These are bottlenecks that are difficult to find without using mysqldump.

Next, we will explain how to parse SQL

How to tune SQL to a system operating with MySQL Part 2 ~SQL analysis~