MySQL

• 查詢同時連線數上限

  mysql> SHOW VARIABLES where Variable_name like 'max_connect%';
  +--------------------+-------+
  | Variable_name      | Value |
  +--------------------+-------+
  | max_connect_errors | 100   |
  | max_connections    | 151   |
  +--------------------+-------+
  2 rows in set (0.00 sec)
  

• 查詢目前的同時連線數

  mysql> show status where `variable_name` = 'Threads_connected';
  +-------------------+-------+
  | Variable_name     | Value |
  +-------------------+-------+
  | Threads_connected | 151   |
  +-------------------+-------+
  1 row in set (0.00 sec)
  

• 查詢目前連線的 process

  mysql> show processlist;
  +----+-------+-----------+-------+---------+------+-------+------------------+
  | Id | User  | Host      | db    | Command | Time | State | Info             |
  +----+-------+-----------+-------+---------+------+-------+------------------+
  |  1 | root  | localhost | NULL  | Query   |    0 | init  | show processlist |
  |  5 | cacti | localhost | cacti | Sleep   |  292 |       | NULL             |
  |  6 | cacti | localhost | cacti | Sleep   |  311 |       | NULL             |
  |  7 | cacti | localhost | cacti | Sleep   |    7 |       | NULL             |
  |  8 | cacti | localhost | cacti | Sleep   |    7 |       | NULL             |
  | 10 | cacti | localhost | cacti | Sleep   |  306 |       | NULL             |
  | 11 | cacti | localhost | cacti | Sleep   |  306 |       | NULL             |
  | 12 | cacti | localhost | cacti | Sleep   |  306 |       | NULL             |
  | 33 | cacti | localhost | cacti | Sleep   |    0 |       | NULL             |
  | 34 | cacti | localhost | cacti | Sleep   |    1 |       | NULL             |
  | 35 | cacti | localhost | cacti | Sleep   |   37 |       | NULL             |
  | 36 | cacti | localhost | cacti | Sleep   |    4 |       | NULL             |
  | 37 | cacti | localhost | cacti | Sleep   |    7 |       | NULL             |
  | 38 | cacti | localhost | cacti | Sleep   |    7 |       | NULL             |
  | 39 | cacti | localhost | cacti | Sleep   |    7 |       | NULL             |
  | 40 | cacti | localhost | cacti | Sleep   |    7 |       | NULL             |
  | 41 | cacti | localhost | cacti | Sleep   |    7 |       | NULL             |
  +----+-------+-----------+-------+---------+------+-------+------------------+
  17 rows in set (0.00 sec)
  

• 查詢目前的連線等待時間
  • [參考] ​https://stackoverflow.com/questions/23399111/safely-keeping-mysql-connections-alive
  • 預設值是 28800 seconds

    mysql> SHOW VARIABLES where Variable_name like 'wait%';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | wait_timeout  | 28800 |
    +---------------+-------+
    1 row in set (0.00 sec)
    

  • 可以 runtime 修改

    mysql> set global wait_timeout=60;
    mysql> set session wait_timeout=60;
    mysql> SHOW VARIABLES where Variable_name like 'wait%';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | wait_timeout  | 60    |
    +---------------+-------+
    1 row in set (0.00 sec)