Detailed explanation of the example code of mysql innodb monitoring (system layer, database layer)

This article mainly introduces the relevant information of mysql innodb monitoring (system layer, database layer). Friends in need can refer to

mysql innodb monitoring ( System layer, database layer)

Regarding MySQL monitoring, mysql provides data collection commands, such as the show status command or the GLOBAL_STATUS object that reads the database informat_schema; you can also use some ready-made monitoring tools. For queries, innotop, mysqlreport, mtop, mytop, and orzdba developed by Taobao perl language are currently used more frequently.

As far as monitoring indicators are concerned, there are system level and database level.

1. The system level includes the system load, cpu, whether the memory has swap, disk IO, and network.

        vmstat -w 1

        iostat -x -k 1

        sar -n DEV 1

2, mysql databaselayer

 show global status where Variable_name in('xxxx')或者show global status like 'xxx%'

The data collected is generally the total quantity that has been collected so far. If you want to calculate The number per second can be calculated like this, taking TPS as an example.

#         1) Sql statement execution times QPS------(Com_select, Com_insert, Com_update, Com_delete)

      2) Transaction TPS----(Com_commit, Com_rollback)       3) Row status (number of rows modified and deleted per second)-----(Innodb_rows_inserted, Innodb_rows_updated, Innodb_rows_deleted, Innodb_rows_read)

    4) Page status (total pages in the buffer pool) Number, free, dirty, number of flushed pages per second) ---- (Innodb_buffer_pool_pages_flushed020cd970d3807098e7fdf610f4d533fe, Innodb_buffer_pool_pages_data9dc1572a5d8828c35f50c88ce3a0adcc, Innodb_buffer_pool_pages_free7c5ca5edc792c41c499b09d6083c71f9, Innodb_buffer_pool_pages_dirtyb2a24afdb73e45a597da6448d05a49e2 ;)

      5) Query cache Hit (query cache hit rate)-----(inno_buffer_pool_read_requestsc8750b8bfeb67e22802f2b7ee2eade1a, Innodb_buffer_pool_reads51433ece947ae26e4e75bdf0e3482603)

      6 )Data status (number of data reads and writes per second in the memory buffer, read and write size per second M)----(Innodb_data_reads5f71637006da290c32eee11dcf49f271, Innodb_data_writes8f0a1939e7d7de40cdfa3cee96fd6010, Innodb_data_read5e7bece0378d8ff30fb05a62786eac22, Innodb_data_writtenefebe4625486843960227a813faafd35)

        7) Log (log_fsync number of times the log is synced to disk per second, log size written per second M) ---- -(Innodb_os_log_fsyncs77c12249492be1367a8d4b95bda2eabe, Innodb_os_log_written8e09c314b7d731f32ab754dedcaedfab)

        8) Thread status (number of threads created per second) ----- - (Threads_running5f5269d5f72611e2ff83328c3635f42e, Threads_connected48b86b206084fa65939eafe97176b5f0, Threads_created727f9c9cc92a025afc8d6aeed6d08225, Threads_cached5c483ab6e8b23ccbc0fd226459ee7785)

                                                                                                                                                           . The amount of data received or sent from the client M---(Bytes_received, Bytes_sent)

The above is the detailed content of Detailed explanation of the example code of mysql innodb monitoring (system layer, database layer). For more information, please follow other related articles on the PHP Chinese website!