This is an old revision of the document!


In MySQL we can monitor the MysQL sessions and processes as follows:

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 ***************************

  • mysql_monitoring.1612888896.txt.gz
  • Last modified: 2021/02/09 16:41
  • by andonovj