Shulou(Shulou.com)05/31 Report--

This article mainly explains "mysql performance check and tuning methods," interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "mysql performance check and tuning method"!

I have been using mysql this software, it works more stable, efficient. When encountering serious performance problems, there are usually several possibilities:

1. The index is not built;

2. SQL writing is too complicated;

3. Configuration error;

4, the machine is really not loaded;

1. The index is not built

If you see that the CPU consumption is large, you can check it with mysql's client tool.

Executed under Linux

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

Enter the password. If there is no password, you can enter the client interface without using the-p parameter.

Look at the current situation.

show full processlist

You can run it a few more times.

This command can see the sql statement currently being executed, it will tell the sql executed, database name, execution status, client ip from, account used, runtime and other information

In my cache backend, most of the time I don't see any sql statements displayed, which I think is normal. If you see a lot of SQL statements, then this MySQL must have performance problems.

If a performance problem occurs, you can analyze it:

1. Is there a SQL statement stuck?

This is a relatively common situation, if the database is myisam, then there may be a write thread will lock the data table, if this statement does not end, then other statements can not run.

Look at the time entry in the processlist to see if there are any statements that take a long time to execute. Pay attention to these statements.

A lot of the same SQL statements are being executed.

If this occurs, it is possible that the sql statement is executed inefficiently, so watch out for these statements as well.

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

Let's 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)

Note that key, rows and Extra are three items. The result returned by this statement indicates that the sql will use PRIMARY primary key index to query. The number of result sets is 1. Extra is not displayed, which proves that sorting or other operations are not used. From this result, it can be inferred that mysql will query the record imgid=1651768337 from the index, and then extract all the fields from the real table, which is a very simple operation.

At this point, I believe everyone has a deeper understanding of "mysql performance check and tuning methods," may wish to actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.