Show pageOld revisionsBacklinksODT exportBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. =====Overview===== 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> mysql> show status where variable_name = 'threads_connected'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 1 | +-------------------+-------+ 1 row in set (0.00 sec) mysql> </Code> <Code:bash|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> </Code> <Code:bash|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> </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> mysql_monitoring.txt Last modified: 2021/02/09 16:44by andonovj