Summary of MySQL performance monitoring methods

Summary of MySQL performance monitoring methods

--- First, the concept that logical reads in Oracle correspond to physical reads is to count and classify the number of data accesses using the data cache technology.
--- Physical read, which actually has I/O. Logical read reads data from the cache area. You can check the hit rate of the cache area, but only the hit rate of a specific object in the cache area.
--- Therefore, data cache technology can be used in a similar way. However, this only measures the database performance from the IO perspective. That is, it is not completely reliable.
--- Second, MySQL provides some methods to test the IO usage.
--- MySQL Method 1: distinguish between "index read" and "random read ". In this case, data is scanned from a single table instead of from the cache area.
--- Therefore, the following methods can be used to assist in judgment:
Mysql> show status like 'handler _ read % ';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| Handler_read_first | 0 |
| Handler_read_key | 2 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+ ----------------------- + ------- +
7 rows in set (0.00 sec)
--- MySQL Method 2: Check the overall server status through the status variable
--- Learn the execution frequency of various SQL statements through show status and application features
--- You can use show status to provide server status information. For example, the following parameters count both MyISAM and Innodb Storage engines:
1. com_select: count the number of select operations executed. Only 1 is accumulated for one query;
2. com_insert: the number of insert operations. insert operations in batches are accumulated only once;
3. com_update: the number of update operations;
4. com_delete: the number of delete operations;
--- The following parameters are used to count the Innodb Storage engine, and the accumulative algorithms are slightly different:
1. Innodb_rows_read: the number of rows returned by the query, not only the select operation, but also the delete and update operations on the tuples;
2. Innodb_rows_inserted: number of rows inserted by the insert operation;
3. Innodb_rows_updated: number of rows updated by the update operation;
4. Innodb_rows_deleted: number of rows deleted by the delete operation;
Using the show status command to view the parameter values, you can easily understand whether the current database application mainly performs insert update or query operations, and the number of update operations is counted, is the count of the number of executions, whether submitted successfully or rolled back, it will accumulate.
For transactional applications, you can use com_commit and com_rollback to learn about transaction commit and rollback. For databases with frequent rollback operations, it may mean that there is a problem with application writing.
--- MySQL method 3: Learn the overall server status/IO status through PFS (Performance Schema)
1. The MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level.
2. The Performance Schema monitors server events. an "event" is anything the server does that takes time and has been instrumented so that timing information can be collected. in general, an event cocould be a function call, a wait for the operating system, a stage of an SQL statement execution such as parsing or sorting, or an entire statement or group of statements. currently, event collection provides access to information about synchronization CALS (such as for mutexes) file and table I/O, table locks, and so forth for the server and for several storage engines.
3. This function is very powerful. Please be aware of it. For more internal content, we will not mention it. We can refer to the official performance-schema.htm file. It must be the mainstream of MySQL monitoring.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: