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

This article mainly introduces the relevant information about 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 reading the GLOBAL_STATUS object of the database informat_schema; you can also use some ready-made monitoring tools for query. The ones currently used are relatively There are many innotop, mysqlreport, mtop, mytop, and orzdba developed by Taobao perl language.

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 database layer

 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. If you want to calculate the number per second, you can calculate it like this, taking TPS as an example. ：

        1) Number of Sql statement executions QPS------ (Com_select, Com_insert, Com_update, Com_delete)

        2) Transaction TPS---- (Com_commit, Com_rollback )

  3) Row status (increase the number of modified and deleted rows per second)-----(Innodb_rows_inserted, Innodb_rows_updated, Innodb_rows_deleted, Innodb_rows_read)

4) Page status (in the buffer pool The total number of pages, free, dirty, and the number of pages flushed per second) ---- (Innodb_buffer_pool_pages_flushed1c71fded39245bc804c18b3a2d404c96, Innodb_buffer_pool_pages_datac3aa7d11aa0f3cf624eb5233b965af98, Innodb_buffer_pool_pages_free5d20c098981550af9d9ef30f7ac455cb, Innodb_buffer_pool_pages_dirtyb113d0a6a11d6c4dc6ca054443af1fc1)

              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 M per second) ---- (Innodb_data_reads5f71637006da290c32eee11dcf49f271, Innodb_data_writes8f0a1939e7d7de40cdfa3cee96fd6010, Innodb_data_read< ;The amount of data that has been read so far>, Innodb_data_written5301984fec493be77fb80f438c541210)

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

        8) Thread status (number of threads created per second) -- ----(Threads_runninga453703e9c41089ca371d8cd08103dbc, Threads_connected817954efabbc5e6271359afabe88812e, Threads_created0d965377b6fc20253ce91e72376c2809, Threads_cached765d5c3da2df7ee90be2f90fd68a240c)

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

Thank you for reading, I hope it can help everyone, thank you for your support of this site!

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