How to locate, eliminate and avoid MySQL database performance problems

When developing large-traffic access-level web application development projects, we have to constantly inspect and optimize various functions in the application to Prevent the application from being suspended at critical times. The authors will give some comments on how to locate, exclude, and avoid MySQL database performance issues. We hope to help our colleagues build stronger and more stable web applications.

(1) Use innodb database engine whenever possible during Table Design

When creating a table, explicitly specify the innodb database engine instead of the myisam engine. The myisam engine locks are table locks, read locks and write locks are mutually exclusive, and read/write operations are sequential, lock conflicts will seriously affect concurrency. innodb provides row-level locks to provide good concurrency performance. In our business scenarios, it will not cause deadlocks.

(2) Make good use of database Indexes

Properly create indexes for the fields used in the where condition in SQL statements. Although creating an index on a table affects the write efficiency to some extent, the index brings more benefits when the table data size is not large and the write pressure is not very high.

(3) Reasonable database/table sharding

The data should be sharded by database and table, and the application layer should dynamically select the database and table. Although the innodb Engine table of MySQL can store massive amounts of data in theory, it is reasonable to control data below million in our business scenarios. In pursuit of performance, it is best to control the table below million, reasonable index.

(4) Reasonable Use of Cache

Loading infrequently modified, limited data volume, and intensive query information into the cache can effectively reduce the pressure on the database. In general business scenarios, it is recommended to use open source memcache, which is simple and efficient.

(5) make good use of the explain command

After the project is developed, the explain command should be frequently used to check the SQL statement to see whether the index is used and whether the filesort problem exists (that is, the order by column is not set as an index ), and check whether the number of rows (rows) retrieved by the data is too large. Generally, rows <1000 is within the acceptable range. Rows in 1000 ~ In the case of intensive concurrent access, the performance may be poor, but if the access is not too frequent (less than one minute), and it is difficult to optimize it, acceptable, but note that when the rows is greater than 10 thousand, you should carefully consider the SQL design, optimize SQL, and optimize the database. Generally, frequent operation is not allowed (the frequency is less than 1 hour ). When the number of rows reaches, it cannot be used as a real-time running SQL statement. Except for data import, the time and frequency of data import must be well controlled.

The explain SQL statement should be a habitual action in daily development. Sometimes the explain result may be out of the expectation of deviation from the design. Therefore, it is strongly recommended that when designing the SQL statement, especially for a slightly complex SQL statement, the explain check must be performed in advance in the test environment or even the actual environment.

(6) Enable MySQL slow query log

In general, you should open the MySQL slow query log (in my. the log_slow_queries and long_query_time parameters are added to cnf. All SQL statements whose query duration exceeds long_query_time are recorded. After these statements are logged, The explain command is run one by one for analysis and optimization.

(7) Monitor the MySQL database Process List

Log on to the MySQL client and use show processlist to view the currently running SQL statements. If there are too many running statements and the running time is too long, the MySQL efficiency is faulty. If necessary, kill the corresponding process.

(8) monitor the system processes occupied by MySQL

Use top, vmstat, and other system commands to check the cpu, memory, and disk IO used by the MySQL process to further optimize the database.

Articles you may be interested in

• Case sensitivity of mysql Databases
• Mysql database cache function analysis, debugging, and performance summary
• How to view MySQL database character sets
• Rational use of MySQL database indexes to make the database run efficiently
• MySQL large data volume quick insert method and statement Performance Optimization
• How to know that the SQL statements executed in the database are slow
• Steps for synchronizing and backing up mysql Databases in windows
• How to modify the character set of a mysql database