Differences

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

--- mysql_monitoring [2021/02/09 16:41] – andonovj
+++ mysql_monitoring [2021/02/09 16:44] (current) – andonovj
@@ Line 3: / Line 3: @@
-=====Using SQL=====
+=====General Monitoring with SQL=====
+<Code:bash|General Monitoring>
+mysql> SHOW ENGINE INNODB STATUS \G
+*************************** 1. row ***************************
+  Type: InnoDB
+  Name:
+Status:
+=====================================
+-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
+lock struct(s), heap size 1136, 0 row lock(s)
+---TRANSACTION 421908100245328, not started
+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
+OS file reads, 147 OS file writes, 58 OS fsyncs
+.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)
+.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
+pending log flushes, 0 pending chkp writes
+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
+.00 youngs/s, 0.00 non-youngs/s
+Pages read 243, created 37, written 90
+.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
+--------------
+queries inside InnoDB, 0 queries in queue
+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
+.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
+----------------------------
+END OF INNODB MONITOR OUTPUT
+============================
+row in set (0.01 sec)
+</Code>
+=====Process Monitoring with SQL=====
 <Code:bash|Monitor connected threads>
 mysql> show status where variable_name = 'threads_connected';