MySQL InnoDB monitoring (system layer, database layer)

MySQL InnoDB monitoring (system layer, database layer)

Regarding MySQL monitoring, MySQL provides commands for data collection, 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 querying, the more commonly used ones are innotop, mysqlreport, mtop, mytop, and orzdba developed by Taobao Perl language.

In terms of monitoring indicators, there are system-level and database-level ones.

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') or show global status like 'xxx%'

The collected data is generally the total number of data currently collected. If you want to calculate the number per second, you can calculate it like this, taking TPS as an example

TPS = current Comm_commit - previously collected Comm_commit / time interval

The following are the main monitoring indicators:

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 added, modified, and deleted per second)-----(Innodb_rows_inserted, Innodb_rows_updated, Innodb_rows_deleted, Innodb_rows_read)

4) Page status (total number of pages in the buffer pool, free, dirty, number of pages flushed per second) ----(Innodb_buffer_pool_pages_flushed <number of page flushes>, Innodb_buffer_pool_pages_data <total amount>, Innodb_buffer_pool_pages_free <number of free pages>, Innodb_buffer_pool_pages_dirty <number of dirty pages>)

5) Query cache hit rate-----(inno_buffer_pool_read_requests <total number of logical reads>, Innodb_buffer_pool_reads <total number of physical reads>)

6) Data status (number of reads and writes per second in the memory buffer, read and write size M per second)----(Innodb_data_reads <total number of data reads>, Innodb_data_writes <total number of data writes>, Innodb_data_read <amount of data read so far>, Innodb_data_written <amount of data written so far>)

7) Log (log_fsync the number of times the log is synced to disk per second, the size of the log written per second M)-----(Innodb_os_log_fsyncs <total number of times the log file is written>, Innodb_os_log_written <number of bytes written to the log file>)

8) Thread status (number of threads created per second)------(Threads_running<number of threads in active state>, Threads_connected<number of currently open connections>, Threads_created<number of newly created threads>, Threads_cached<thread cache value>)

9) 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 you, thank you for your support of this site!