Mysql Performance check and optimization method _ MySQL

Source: Internet
Author: User
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

Contact Us

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.