The method of checking and tuning MySQL performance

I have been using the MySQL database software, it works more stable, efficient and very high. In the face of serious performance problems, there are generally several possibilities:

1, the index is not well established;

2, the SQL writing is too complex;

3, configuration error;

4, the machine is really not load;

1, the index is not built well

If you see the CPU consumed by MySQL is very large, you can use the MySQL client tool to check.

Execute on Linux

/usr/local/mysql/bin/mysql-hlocalhost-uroot-p

Enter the password, and if there is no password, you can go to the client interface without the-p parameter.

Look at the current operation

Show Full Processlist

Can run a few more times

This command sees the currently executing SQL statement, which tells you the SQL, database name, execution status, client IP, account number used, elapsed time, and so on.

In my cache back end, this most of the time is not see the display of any SQL statements, I think this is more normal. If you see a lot of SQL statements, then this MySQL will certainly have a performance problem

If there is a performance problem, you can analyze:

1. Is there a SQL statement stuck?

This is a lot of cases, if the database is using MyISAM, then there may be a written thread will lock the data table, if this statement does not end, the other statements will not run.

Look at the time in processlist to see if there is a long execution statement, pay attention to these statements.

2, a lot of the same SQL statement is executing

If this happens, it is possible that the SQL statement does not perform efficiently, as well as paying attention to these statements.

Then assemble the statements you suspect and check them with Desc (explain).

First look at a normal desc output:

Mysql> 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 | PRIMARY | 8 | Const | 1 | |

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

1 row in Set (0.00 sec)