The problem of Mysql optimization has been explained first. I haven't been writing a blog on csdn for a long time. Recently, I have been studying mysql optimization at Yan 18, and this teacher is still talking about it, today, I am just free to summarize what I learned during this period. I will first go through a flowchart (this figure is taken from the Mysql optimization tutorial of Yan 18.
I 've been writing a blog on csdn for a long time. Recently, I 've been learning mysql optimization at Yan 18, and this teacher is still talking about it, today, I just have time to summarize what I learned during this period.
First, go to a flowchart (this figure is taken from the tutorial of Yan 18)
When there is a problem with a db server, first of all, it is not to look at the code where there is a problem, think about whether the SQL statement is written, whether the table structure is reasonable or not; but from a macro perspective, we need to look at where there are problems.
The first step is to find out whether the server problem is caused by a hardware bottleneck. if a server hardware itself is not good, it can only handle 200 M of io reads and writes. if you want it to provide up to M of io, so even if you want to optimize it, we need to first install sysbench in the benchmark test. It provides performance tests such as cpu, Io, memory, and mysql. 1. cpu test sysbench -- test = cpu -- cpu-max-prime = 2000000 run2.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 passing these tests, you will not be able to know the capabilities of your server. if you find that the server has good performance but still cannot meet your needs, it will only be a software problem, you need to locate the problem.
Step 2: Observe the connection status of mysql in a certain period of time. if the processing status is not big, we need to observe the status of mysql. Generally, this status cannot be fixed at half past one, all of them need to write a script to record the pressure value record of mysql in a certain cycle in the background, for example, one day, one week is a cycle; the command to view the mysql status is show status; this command returns several hundred lines of things, but we only need to pay attention to three lines 1. queries, the current query has occurred (you can use the number of Queries in two time periods to subtract the number of Queries in the time range) 2. threads_connected: how many connections are currently connected to mysql3. Threads_running. several threads are currently running, which is usually Threads_connected> = Threads_running. it may be blocked because mysql does not have to work when connected, pending. we write a script to read these three numbers every second and append them to mysql. status File 2. simulate access with the AB tool, send 20000 requests with 50 concurrent requests (each request on this page will visit mysql multiple times ), in this way, the above script will get the result AB-c 50-n 2000 http://59.69.128.203/JudgeOnline/nyistoj/index.php/Problem/index Let's check this mysql. in the status file, we use the first value of the previous row minus the first value of the next row to obtain the number of mysql accesses per second, which is about 1000 +, it can also be seen that there are basically 50 connections, with two threads processing requests on average; you can write a script again for processing to get the number of processes per second, A little more than 1000, it seems that the results are not good. 1. the frequency of accessing mysql is very stable (for example), so we can optimize it from other aspects of mysql, such as the table structure, SQL statement optimization, mysql configuration, and engine selection, index optimization 2. the access frequency of mysql changes cyclically (for example), and is optimized from the peak. for example, if memcatch is periodically invalid, the random mode can be used to make the failure more even, or let him expire at around 3 o'clock in the evening. the access traffic is not large at this time, and the memcatch buffer is also basically set up in the next day; or it is optimized from the business perspective, such as 12306 of the votes, you can release tickets in batches in different provinces and time periods, which avoids the ultra-high peak value caused by collective ticketing across the country. you can also enable slow queries during peak hours, analysis of specific SQL statements using tools such as processlist; 3. view the status of the mysql process. if you need to know the overall situation of the mysql process for processing SQL statements, you need to use the show processlist tool, this tool is mainly used to record every SQL execution process. we write a script to capture the status, and then take a look at what the mysql process is basically doing; show processlist \ G the Status may be many, but we mainly focus on the following statuses: 1. create tmp table; Create a temporary table. for example, a temporary table is created after right join. sending Data; send Data, such as limit 1, 1000; then it will send a large amount of Data and spend time, you can limit a little less 3. sortIng for Group; the Group is being sorted. in this case, the optimization is generally based on composite index 4. copying to tmp table on desk; the hard disk is being copied to the memory table, mainly because the table is too large. for example, if you join the table, a large table can only be stored on the hard disk. avoid join. locked; data locking, transaction optimization, no need to use transactions 6. converting HEAP to MyISAM; the query result is too large and you are trying to store the result on the hard disk. the optimization is to try to read less data at a time, such as reading the news list at a time, the reader rarely reads several hundred entries at a time; then we write a script to capture these statuses:
Then process mysql. process;
The following result is displayed:
It can be seen that many times the Copying to tmp table, Sending data, and Sort result are spent many times. you can roughly know that the business logic leads to a large amount of data to be retrieved, it can change the business or buffer server to block the mysql front;
Check Copying to tmp table; first open profiles;
When monitoring is enabled, you can take snapshots of each stage of SQL Execution. in this way, you can clearly find out the SQL execution process and the specific stage in which it takes time, further targeted optimization
Then the SQL statement will be recorded,
Use show profiles to obtain the statement id;
The statement id is 27, which takes more than 6 seconds to view the specific content of id 26:
Now we know that this SQL statement takes time to copy data to the hard disk and sort data. because we have three joins, and these joins are sorted by titles at the same time, indexes cannot be overwritten, therefore, the data in the hard disk needs to be returned, which leads to a very large table and cannot be put into the memory. Instead, the data can only be placed on the hard disk. then, this SQL statement can be implemented through targeted optimization; conclusion: After the above steps, we can gradually locate the problem where our server is located, either because the server itself is not strong enough or is periodically faulty, or your own code or table structure is not good enough, or business logic and other issues. We will mainly optimize the specific problems later. this is the content of the next article.