Mysql Performance check and optimization method bitsCN.com
I have been using the mysql database software, which is stable and highly efficient. There are several possible causes for serious performance problems:
1. The index is not created;
2. SQL statements are too complex;
3. configuration error;
4. the machine cannot be loaded;
1. index not created
If you see that mysql consumes a lot of cpu, you can use the mysql client tool to check.
Run
/Usr/local/mysql/bin/mysql-hlocalhost-uroot-p
Enter the password. if there is no password, you can enter the client interface without the-p parameter.
Check the current running status
Show full processlist
It can be run several times more
This command shows the SQL statement being executed. it informs the executed SQL statement, database name, execution status, client ip address, account used, and running time.
In my cache backend, no SQL statements are displayed most of the time. I think this is normal. If you see a lot of SQL statements, this mysql will certainly have performance problems
If there is a performance problem, you can analyze it:
1. is there any SQL statement stuck?
This is often the case. if myisam is used in the database, a write thread may lock the data table. if this statement does not end, other statements cannot be run.
Check the time item in processlist to see if the statements have been executed for a long time. pay attention to these statements.
2. a large number of identical SQL statements are being executed
In this case, the execution efficiency of the SQL statement may be low. pay attention to these statements as well.
Then you can set all the statements you suspect and use desc (explain) to check these statements.
First, let's look at a normal desc output:
Mysql & gt; desc select * from imgs where imgid = 1651768337;
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ------- +
| 1 | SIMPLE | imgs | const | PRIMARY | 8 | const | 1 |
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ------- +
1 row in set (0.00 sec)
Note key, rows, and Extra. the results returned by this statement indicate that the SQL statement uses the PRIMARY key index for query. the number of result sets is one and Extra is not displayed, it proves that sorting or other operations are not used. From this result, we can infer that mysql will query the imgid = 1651768337 record from the index, and then retrieve all fields from the real table. this is a very simple operation.
Author: "blog of Lingnan Liushao"
BitsCN.com