A summary of some methods of MySQL monitoring performance

---First, the concept of logical read Correspondence physics reading in Oracle is to classify the number of data accesses by using the technology of data buffer.
---Physical reading, real IO, logical reading, reading from the buffer, you can examine the hit rate of a buffer, but only the hit rate of a specific object in the buffer.
---Therefore, the use of data buffer technology, can have a similar way. However, this only measures the performance of the database from an IO perspective. That is not completely reliable.
---Second, MySQL provides some ways to investigate the use of IO.
---mysql mode 1: Distinguish between "index read" or "Random read". This situation, not from the point of view of the buffer, is from the point of view of the single-table scan data.
---Therefore, the following methods can be used to assist in judging:
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 Mode 2: Understanding the overall status of the server through state variables
---Understand the frequency of SQL execution with show status and application features
---Through show status, you can provide server state information, such as the following parameters for MyISAM and InnoDB storage Engine count:
1, Com_select: Perform the Select operation Count, once the query only accumulates 1;
2, Com_insert: The number of times to perform insert operation, for the insert operation of BULK INSERT, only one time;
3, Com_update: The number of times to perform the update operation;
4. Com_delete: The number of times the delete operation was performed;
---The following parameters are counted against the InnoDB storage engine, and the accumulated algorithm is slightly different:
1, Innodb_rows_read: The number of rows returned by the query, not only the select operation, delete and update will also trigger the read operation of the tuple;
2. Innodb_rows_inserted: The number of rows inserted to perform the insert operation;
3. Innodb_rows_updated: The number of rows updated to perform update operations;
4. Innodb_rows_deleted: The number of rows deleted by performing the delete operation;
by using the show status command to view the parameter values, it is easy to see whether the current database application is primarily an insert update or a query operation, and for the count of update operations, count the number of executions, whether a successful commit or a rollback will accumulate.
for transactional applications, Com_commit and Com_rollback can be used to understand transaction commits and rollbacks, and for databases with very frequent rollback operations, it may mean that there is an application writing problem.
---mysql mode 3: Understanding Server Overall 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" was anything the server does that takes time and have been instrumented so, timing information can be collect Ed. In general, an event could is a function call, a wait for the operating system, a stage of a SQL statement execution such As parsing or sorting, or an entire statement or group of statements. Currently, event collection provides access to information on synchronization calls (such as for mutexes) file and Tabl e I/O, table locks, and so forth for the server and for several storage engines.
3, this function is very powerful, please pay attention to master. Content We do not specify, you can refer to the official manual of the performance-schema.htm file. He must be the mainstream of MySQL surveillance.

This article is from the Linux OPS blog, so be sure to keep this source http://2853725.blog.51cto.com/2843725/1546283

A summary of some methods of MySQL monitoring performance