MySQL Optimization problem location

MySQL Optimization problem location

First pull down, a long time did not come to CSDN blog, recently learning Yan 18 MySQL optimization, and the teacher said up to the top of the air, today just have time to summarize this period of learning things

First on a flowchart (this picture is from the Yan 18 tutorial)

When there is a problem with a DB server, the first is not to see the code where there is a problem, whether the SQL statement is written, whether the structure of the table is reasonable, and so on, but need to see from a macro point of view where the problem

The first step to find out whether the server problem is the hardware bottleneckIf a server hardware itself is not good, can only withstand 100M io Read and write, if you do not want it to provide the IO to 200M, then even if you can not optimize it, then we need the benchmark to install Sysbench, which provides the CPU, Io, memory, MySQL and other performance testing,;
1.CPU Test sysbench--test=cpu--cpu-max-prime=2000000 run 2.io test

Sysbench--test=fileio--num-threads=16--file-total-size=3g--file-test-mode=rndrw preparesysbench--test=fileio-- Num-threads=16--file-total-size=3g--file-test-mode=rndrw runsysbench--test=fileio--num-threads=16-- file-total-size=3g--FILE-TEST-MODE=RNDRW Cleanup

3.OLTP Test
Sysbench--TEST=OLTP--mysql-table-engine=myisam--oltp-table-size=1000000--mysql-socket=/tmp/mysql.sock-- Mysql-user=test--mysql-host=localhost--mysql-password=test Prepare
After these tests, but also can know their own server capabilities, if found that the performance of the server is good, but still can not meet the needs of users, then it is only a software problem, you need to locate exactly which piece is the problem
The second step, to observe the MySQL in a certain period of time the connection state, processing state if the hardware problem is not big, then we need to observe the state of MySQL, generally this state is not womb can be done, are required to write a script in the background record MySQL in a period of pressure value record, such as a day, A week is a cycle; the status command to view MySQL is show status; This command returns hundreds of lines of stuff, and we only need to focus on 3 line 1. Queries, the query that has already occurred (can be subtracted from the number of queries in two time periods for the number of queries in a time period) 2. threads_connected, how many connections are currently connected to the MYSQL3. Threads_running, there are currently several threads running that are usually threads_connected >= threads_running, because even MySQL does not have to work, may block, Suspend the like to get results 1. We write a script to read these three numbers every second to append to the Mysql.status file 2. Simulate access with the AB tool,with 50 concurrent, send 20,000 requests (this page of each request will be multiple access to MySQL), so that the above script can get resultsab-c 50-n Http://59.69.128.203/JudgeOnline/nyistoj/index.php/Problem/indexLet's take a look at the contents of this mysql.status file. We use the first value of the previous line minus the first value of the next line to get the number of MySQL per second, almost 1000+, you can see that there are basically 50 connections, with an average of two threads in processing the request ; You can write a script again to do the processing so that the number of processing per second, more than 1000 a bit, seemingly not good feeling results analysis 1. The frequency of access to MySQL is very stable (for example), it is optimized from other parts of MySQL, such as table structure, SQL statement optimization, MySQL configuration, engine selection, index optimization and so on 2.mysql frequency of access is periodic changes (such as), then is optimized from the peak , such as whether the memcatch is a cyclical failure, then can be used in a random way to make the failure more uniform, or let him at night around 3 o'clock failure, this time the traffic is not big, to the second day Memcatch buffer is basically established; or from a business perspective, For example, 12306 of the vote, can be divided into a period of time in batches to put the ticket, so that the whole country to avoid the collective grab tickets brought by the Super peak value, but also in the peak time to open the slow query, processlist and other tools to analyze the specific SQL statements;
Three. View the status of the MySQL process if you need to know the overall situation of the MySQL process for processing SQL statements, then we need to use the show Processlist tool, which is mainly able to record the process of each SQL execution We write a script to crawl the status, and then look at our MySQL process as a whole what the time spent basically is doing; show processlist\g

the status situation here may be more, but we are mainly concerned about the following states:1. Create TMP table; Creates temporary tables, such as a new temporary table with a right connection2. Sending data; Send a number, such as limit 1, 1000; then this will send a lot of data and spend time, can limit a little bit3. Sorting for group; Sorting by group, this time optimization is generally based on the composite index4. Copying to TMP table on desk; the hard disk that is copying the table of memory, the main table is too large, such as a join to produce a large table can only put the hard disk, avoid join5. Locked; lock data, transactional aspects of optimization, can not use the transaction is not6. Converting HEAP to MyISAM; The result of the query is too large, is trying to save the result; optimization is to try to get a little more than the data, such as the reading of the news list a little bit, the reader seldom read hundreds of after;then we write a script to fetch these status:

then deal with the next mysql.process;

you can get the following results:

It can be seen that many times are spent in the copying to TMP table, sending data, Sort result number of times, you can roughly know that the business logic results in the need to take out more data, can change the business or buffer server in front of MySQL;
look at Copying to TMP table;first open the profiles;
turn on monitoring, open this switch can take a snapshot of every stage of SQL execution, so we can clearly find out the SQL execution process, the specific time spent in which phase, and then targeted optimization

Then execute the SQL and it will be logged.

then use show profiles to get the ID of the statement just now;

you can know that the ID of the statement is 27, it took 6 seconds to see the specific content of the ID 26:


now we know that this SQL takes time to copy to hard disk and sort, because we have three joins, and these joins are sorted by title at the same time, resulting in the inability to overwrite the index, which requires data to be returned to the hard disk so that a very large table can not be put into memory, Can only be placed on the hard drive;and then a targeted optimization on the line of this SQL;Summary:after the above steps, we have been able to locate our server gradually where the problem, is the server itself is not strong enough, or is a cyclical problem, or its own code or table structure is not good enough, or the business logic and other issues, we mainly focus on specific problem optimization, This is the next piece of content.