How to tune SQL in a MySQL system: Find bottlenecks

This is Haraoka from Beyond.
Today's topic is 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'll look at how to find bottlenecks.

・Why SQL tuning?

About half (50%) of the reasons why a system suddenly slows down are due to the database. And
if there's a problem with the database, I'd say about 70% of those are due to SQL queries.

100% × 0.5 × 0.7 = 35%

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

*Based on past experience

・SQL tuning flow

Let's get started.
Generally speaking, SQL tuning follows this process:

① Identify resource-intensive SQL queries
↓
② Analyze the SQL queries
↓
③ Modify the SQL queries or add indexes

Identifying heavy SQL

First, identify the heavy SQL statements

・slowlog output

slowlogThis log outputs
which are the most time-consuming SQL queries.

To output Slowlog, write the following in my.cnf

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

`long_query_time`It's easiest to start with
This is because while `long_query_time` can be set in 0.1-second increments, setting it to 0.1 seconds can result in a large amount of output, so
it's best to start by eliminating 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 |

Simply using `set global` will cause the settings to revert after a reboot, so we'll add it to the configuration file.
*Note: The above method doesn't work for version 5.0, so a reboot is required after making the changes.

$ 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 value hereRows_examined(number of rows processed).
This indicates "how much data the database has read."
In other words, the higher this value, the more data has been read.

, by dividing by Rows_sent, we can find out how many lines were read to output one line.
Furthermore

Rows_examined: 9971 ÷ Rows_sent: 3 = 3323

So,to output one line, it reads 3323 lines of data,right?

Therefore, quickly review the logs and pick out the SQL statements with a high Rows_examined value.
SQL statements with a high Rows_examined value are often the bottleneck.

Running mysqldumpslow

If you want to analyze the statistics of the output slowlog and identify particularly heavy SQL queries,
mysqldumpslow`you can use the `
This commandSQL queries that are somewhat heavy and executed frequentlymakes it easier to find

・Extracting logs

While you can run mysqldumpslow directly on slowlog,
if the logs have accumulated over a long period of operation, it's easier to identify recent heavy SQL queries by segmenting the logs into appropriate rows.

$ 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` option 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
queries that rank highly in the output results and have a low count and a long average time are resource-intensive.
These are bottlenecks that can be easily identified using slowlog.

The top-ranking queries in the output, those with a high count, a short average time, and a long cumulative execution time, are likely to be somewhat resource-intensive and frequently executed SQL queries.
These bottlenecks are difficult to find without using mysqldump.

Next, we will explain how to parse SQL

How to tune SQL in a system running MySQL Part 2: SQL analysis