Overview
In MySQL we can monitor the MysQL sessions and processes as follows:
General Monitoring with SQL
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)
Process Monitoring with SQL
Monitor connected threads
mysql> show status where variable_name = 'threads_connected'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 1 | +-------------------+-------+ 1 row in set (0.00 sec) mysql>
Monitor sessions
mysql> show processlist; +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ | 6 | system user | | NULL | Connect | 5950 | Waiting for master to send event | NULL | | 7 | system user | | NULL | Connect | 3384 | Slave has read all relay log; waiting for more updates | NULL | | 8 | root | localhost | repDB | Query | 0 | starting | show processlist | +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ 3 rows in set (0.00 sec) mysql>
Using a query
mysql> select id,
user,
host,
db,
command,
time,
state,
info
from information_schema.processlist\G;
*************************** 1. row ***************************
id: 6
user: system user
host:
db: NULL
command: Connect
time: 6049
state: Waiting for master to send event
info: NULL
*************************** 2. row ***************************
id: 8
user: root
host: localhost
db: repDB
command: Query
time: 0
state: executing
info: select id, user, host, db, command, time, state, info from information_schema.processlist
*************************** 3. row ***************************
id: 7
user: system user
host:
db: NULL
command: Connect
time: 3483
state: Slave has read all relay log; waiting for more updates
info: NULL
3 rows in set (0.00 sec)
ERROR:
No query specified
mysql>
We can also monitor pass each queries as follows:
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 ***************************