This is an old revision of the document!
Overview
In MySQL we can monitor the MysQL sessions and processes as follows:
Using 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 ***************************