Shulou(Shulou.com)06/03 Report--

This article mainly introduces the status of obtaining the overall performance of MySQL, hoping to supplement and update some knowledge, if you have any other questions you need to know, you can continue to follow my updated article in the industry information.

Obtain the overall performance status of the CVM

First of all, to optimize the performance of a database server, you need to know where the server's current major performance problems are. Sql server is similar at this point. Sql server will first analyze the waiting type of the current server.

We can use the show [session | global] status command to get the information we want. By default, all the statistical parameter values of the current connection are displayed, and we can directly query the session_ status table in the information_schema database.

Show status;# or use use information_schema;select * from SESSION_STATUS

My current mysql version is 5.6.21, with a total of 341 lines of parameters queried.

Here is an article that analyzes in detail what each parameter value represents: http://blog.sina.com.cn/s/blog_68baf43d0100vu2x.html

SQL operation count

Next, we mainly analyze the com_ parameters, com_ parameters and the operations performed by various SQL on the database.

Show status like 'com_%';# or use use information_schema;select * from SESSION_STATUS WHERE variable_name like' com_%'

The total count of various SQL operations is 142. different versions have different results. Next, let's test it. The number of operations of the current connection of the alter table in the table is 0. Now I'll modify the table to see the results.

ALTER TABLE test ADD Name CHAR (10) NOT NULL;show status like 'com_%'

You can see that the alter_table count has increased by 1.

There are several important parameters in the com_ count, and other parameters are often used as references.

Com_delete: the number of times the delete operation was performed.

Com_select: the number of times the select operation was performed.

Com_insert: the number of times the insert operation is performed, and the batch insert operation is counted only once no matter how many times it is cycled inside.

Com_update: the number of times the update operation was performed.

Com_commit: the number of times the transaction commit was performed.

Com_rollback: the number of times the transaction rollback was performed.

The above count includes all storage engines, and several parameters are specific to the innodb storage engine, recording the number of rows per operation of read,inserted,updated,deleted.

Show status like 'innodb_rows%';# or SQL statements that use use information_schema;select * from SESSION_STATUS WHERE variable_name like' innodb_rows%'; to locate inefficiently

1. You can locate it through the slow query log, which can only query statements that have been executed. If you cannot query the current problem, this method will be described in detail in the mysql log in a later article.

Since I set the time for slow query to 0.01s, anything exceeding this value will be recorded. The screenshot above is a record of SQL operation in the slow query log, which records when the operation was performed and the user information of the operation. It took 0.19s to execute, 0.001 seconds to lock, 0 rows returned and 1 row queried.

two。 Use the show processlist command to query the current thread of progress, which is often used to analyze the state of the current server.

There are the last four fields to understand in the above figure, of which

Command: records a status of the current query, sleep, query, connect.

Time: duration, in seconds, which is often used for analysis operations.

State: the status of the current statement. This status value is very important. There are many values. You can learn about it. The figure above is waiting for the table to be unlocked.

Info: record operation statement

3. With the help of third party monitoring tools

After reading the above about the status of obtaining the overall performance of MySQL, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.