mysql_monitoring

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		 Previous revision
mysql_monitoring [2021/02/09 16:12] – created andonovjmysql_monitoring [2021/02/09 16:44] (current) – andonovj
Line 1: Line 1:
 +=====Overview=====
 In MySQL we can monitor the MysQL sessions and processes as follows: In MySQL we can monitor the MysQL sessions and processes as follows:
  
  
 +=====General Monitoring with SQL=====
 +<Code:bash|General Monitoring>
 +mysql> SHOW ENGINE INNODB STATUS \G
 +*************************** 1. row ***************************
 +  Type: InnoDB
 +  Name:
 +Status:
 +=====================================
 +2021-02-09 16:43:46 0x7fb910070700 INNODB MONITOR OUTPUT
 +=====================================
 +Per second averages calculated from the last 48 seconds
 +-----------------
 +BACKGROUND THREAD
 +-----------------
 +srv_master_thread loops: 14 srv_active, 0 srv_shutdown, 7970 srv_idle
 +srv_master_thread log flush and writes: 7984
 +----------
 +SEMAPHORES
 +----------
 +OS WAIT ARRAY INFO: reservation count 13
 +OS WAIT ARRAY INFO: signal count 13
 +RW-shared spins 0, rounds 26, OS waits 13
 +RW-excl spins 0, rounds 0, OS waits 0
 +RW-sx spins 0, rounds 0, OS waits 0
 +Spin rounds per wait: 26.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
 +------------
 +TRANSACTIONS
 +------------
 +Trx id counter 4370
 +Purge done for trx's n:o < 4370 undo n:o < 0 state: running but idle
 +History list length 0
 +LIST OF TRANSACTIONS FOR EACH SESSION:
 +---TRANSACTION 421908100246240, not started
 +0 lock struct(s), heap size 1136, 0 row lock(s)
 +---TRANSACTION 421908100245328, not started
 +0 lock struct(s), heap size 1136, 0 row lock(s)
 +--------
 +FILE I/O
 +--------
 +I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
 +I/O thread 1 state: waiting for completed aio requests (log thread)
 +I/O thread 2 state: waiting for completed aio requests (read thread)
 +I/O thread 3 state: waiting for completed aio requests (read thread)
 +I/O thread 4 state: waiting for completed aio requests (read thread)
 +I/O thread 5 state: waiting for completed aio requests (read thread)
 +I/O thread 6 state: waiting for completed aio requests (write thread)
 +I/O thread 7 state: waiting for completed aio requests (write thread)
 +I/O thread 8 state: waiting for completed aio requests (write thread)
 +I/O thread 9 state: waiting for completed aio requests (write thread)
 +Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 + ibuf aio reads:, log i/o's:, sync i/o's:
 +Pending flushes (fsync) log: 0; buffer pool: 0
 +281 OS file reads, 147 OS file writes, 58 OS fsyncs
 +0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
 +-------------------------------------
 +INSERT BUFFER AND ADAPTIVE HASH INDEX
 +-------------------------------------
 +Ibuf: size 1, free list len 0, seg size 2, 0 merges
 +merged operations:
 + insert 0, delete mark 0, delete 0
 +discarded operations:
 + insert 0, delete mark 0, delete 0
 +Hash table size 34673, node heap has 0 buffer(s)
 +Hash table size 34673, node heap has 0 buffer(s)
 +Hash table size 34673, node heap has 0 buffer(s)
 +Hash table size 34673, node heap has 0 buffer(s)
 +Hash table size 34673, node heap has 0 buffer(s)
 +Hash table size 34673, node heap has 0 buffer(s)
 +Hash table size 34673, node heap has 0 buffer(s)
 +Hash table size 34673, node heap has 1 buffer(s)
 +0.00 hash searches/s, 0.00 non-hash searches/s
 +---
 +LOG
 +---
 +Log sequence number 2970191
 +Log flushed up to   2970191
 +Pages flushed up to 2970191
 +Last checkpoint at  2970182
 +0 pending log flushes, 0 pending chkp writes
 +43 log i/o's done, 0.00 log i/o's/second
 +----------------------
 +BUFFER POOL AND MEMORY
 +----------------------
 +Total large memory allocated 137428992
 +Dictionary memory allocated 176720
 +Buffer pool size   8191
 +Free buffers       7910
 +Database pages     280
 +Old database pages 0
 +Modified db pages  0
 +Pending reads      0
 +Pending writes: LRU 0, flush list 0, single page 0
 +Pages made young 0, not young 0
 +0.00 youngs/s, 0.00 non-youngs/s
 +Pages read 243, created 37, written 90
 +0.00 reads/s, 0.00 creates/s, 0.00 writes/s
 +No buffer pool page gets since the last printout
 +Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
 +LRU len: 280, unzip_LRU len: 0
 +I/O sum[0]:cur[0], unzip sum[0]:cur[0]
 +--------------
 +ROW OPERATIONS
 +--------------
 +0 queries inside InnoDB, 0 queries in queue
 +0 read views open inside InnoDB
 +Process ID=18207, Main thread ID=140432823506688, state: sleeping
 +Number of rows inserted 27, updated 4, deleted 0, read 480
 +0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
 +----------------------------
 +END OF INNODB MONITOR OUTPUT
 +============================
 +
 +1 row in set (0.01 sec)
 +
 +</Code>
 +
 +
 +=====Process Monitoring with SQL=====
 <Code:bash|Monitor connected threads> <Code:bash|Monitor connected threads>
 mysql> show status where variable_name = 'threads_connected'; mysql> show status where variable_name = 'threads_connected';
Line 74: Line 193:
 mysql> mysql>
  
 +
 +</Code>
 +
 +
 +We can also monitor pass each queries as follows:
 +
 +<Code:bash|List of old queries>
 +mysql> SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000000 SUM_TIMER_WAIT_SEC, MIN_TIMER_WAIT/1000000000000 MIN_TIMER_WAIT_SEC, AVG_TIMER_WAIT/1000000000000 AVG_TIMER_WAIT_SEC, MAX_TIMER_WAIT/1000000000000 MAX_TIMER_WAIT_SEC, SUM_LOCK_TIME/1000000000000 SUM_LOCK_TIME_SEC, FIRST_SEEN, LAST_SEEN FROM events_statements_summary_by_digest\G;
 +*************************** 1. row ***************************
 +       SCHEMA_NAME: NULL
 +            DIGEST: 271e1927e3681d4a6b138e6906f8e3de
 +       DIGEST_TEXT: SELECT @@`version_comment` LIMIT ?
 +        COUNT_STAR: 12
 +SUM_TIMER_WAIT_SEC: 0.0010
 +MIN_TIMER_WAIT_SEC: 0.0001
 +AVG_TIMER_WAIT_SEC: 0.0001
 +MAX_TIMER_WAIT_SEC: 0.0002
 + SUM_LOCK_TIME_SEC: 0.0000
 +        FIRST_SEEN: 2021-02-09 14:30:34
 +         LAST_SEEN: 2021-02-09 16:34:12
 +*************************** 2. row ***************************
 +       SCHEMA_NAME: NULL
 +            DIGEST: fc25eb00492af3872c5d7c7b41437547
 +       DIGEST_TEXT: SHOW MASTER STATUS
 +        COUNT_STAR: 4
 +SUM_TIMER_WAIT_SEC: 0.0002
 +MIN_TIMER_WAIT_SEC: 0.0000
 +AVG_TIMER_WAIT_SEC: 0.0001
 +MAX_TIMER_WAIT_SEC: 0.0001
 + SUM_LOCK_TIME_SEC: 0.0000
 +        FIRST_SEEN: 2021-02-09 14:30:34
 +         LAST_SEEN: 2021-02-09 15:46:57
 +*************************** 3. row ***************************
  
 </Code> </Code>
  • mysql_monitoring.1612887125.txt.gz
  • Last modified: 2021/02/09 16:12
  • by andonovj