How to Locate, Troubleshoot and Avoid MySQL Performance Failure
Source: Internet
Author: User
Keywordstroubleshoot mysql locate mysql failure avoid mysql failure
This article introduces how to locate, troubleshoot and avoid MySQL performance failure.
The first is how to check the efficiency of SQL:
1. Make good use of explain:
After designing the SQL, you should use the explain command to check the SQL to see whether the index is used and whether there is a filesort, and focus on checking whether the number of rows retrieved is too large. Generally speaking:
Rows<1000, is within the acceptable range.
Rows between 1000~1w, may cause performance problems during intensive access, but if it is not too frequent access (frequency less than once every 1 minute), and it is difficult to optimize again, it is acceptable, but you need to pay attention to observation.
When rows are greater than 10,000, you should carefully consider the design of SQL, optimize SQL, optimize db, generally do not allow frequent operation (frequency is less than once an hour).
Rows reach 10w level, they can not be used as real-time SQL. Except for the case of the guide data, the guide data must control the time and frequency.
The explain SQL statement should be a customary action in daily development. Sometimes the result of the explanation may be out of design expectations, so it is strongly recommended that when designing SQL, especially a slightly more complex SQL, you must even test the environment. Explain in advance in the actual environment**
2. MySQL slow query log
Generally, you should open the MySQL slow query log (add two parameters log_slow_queries and long_query_time in my.cnf), it will record all SQL statements whose query duration exceeds long_query_time, log these statements, and then analyze and optimize them one by one. .
3. Monitor the current process
Log in to MySQL and use show processlist to view the running SQL statements. If there are too many running statements and the running time is too long, it means that there is a problem with MySQL efficiency. When necessary, you can kill the corresponding process.
4. System commands
Use system commands such as top/vmstat to check the CPU, memory, and disk IO used by the MySQL process.
There are many articles on MySQL optimization. Here are only a few of the more commonly used methods in daily work.
◆When building a table, explicitly specify to use the innodb database engine instead of myisam. The lock of the myisam engine is a table lock, the read lock and the write lock are mutually exclusive, and the read and write operations are serial. Lock conflicts will seriously affect concurrency. Innodb provides row-level locking, which can provide better concurrent performance, and in our business scenario, it will not cause a deadlock.
◆ Make good use of the index, and establish a reasonable index for the fields used in the SQL statement where condition. Although indexing the table will affect the writing efficiency to a certain extent, when the table data size is not large and the writing pressure is not particularly high, the benefits brought by the index are more.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
