Apr 072020

ADM

How to show open database connections in MySQL

  • 07 April 2020
  • ADM

 

 

 

How to show open database connections in MySQL - images/logos/mysql.jpg

 

Recently I cam across to a problem where a Golang application was not closing properly the database connections, resulting in MySQL to refuse any new connection.

MySQL / MariaDB has two commands that came in handy to see what is happening in the database.

MySQL show status

The MySQL server maintains many status variables that provide information about its operation. You can view these variables and their values by running the show status command.

We are interested into the Connections status variable. The variable shows the number of connection attempts (successful or not) to the MySQL server. To get only the informations we need the like keyword can be used.

MariaDB [(none)]> show status like 'Conn%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Connection_errors_accept          | 0     |
| Connection_errors_internal        | 0     |
| Connection_errors_max_connections | 0     |
| Connection_errors_peer_address    | 0     |
| Connection_errors_select          | 0     |
| Connection_errors_tcpwrap         | 0     |
| Connections                       | 165   |
+-----------------------------------+-------+
7 rows in set (0.001 sec)

MariaDB [(none)]>

As we can see in the result there are 165 of connections attemps since the server started.

MySQL show processlist

To get more details about the current connections the command show processlist can be used.

MariaDB [(none)]> show full processlist;
+-----+-------------+-----------------+----------------+---------+------+--------------------------+-----------------------+----------+
| Id  | User        | Host            | db             | Command | Time | State                    | Info                  | Progress |
+-----+-------------+-----------------+----------------+---------+------+--------------------------+-----------------------+----------+
|   1 | system user |                 | NULL           | Daemon  | NULL | InnoDB purge worker      | NULL                  |    0.000 |
|   2 | system user |                 | NULL           | Daemon  | NULL | InnoDB purge worker      | NULL                  |    0.000 |
|   3 | system user |                 | NULL           | Daemon  | NULL | InnoDB purge coordinator | NULL                  |    0.000 |
|   4 | system user |                 | NULL           | Daemon  | NULL | InnoDB purge worker      | NULL                  |    0.000 |
|   5 | system user |                 | NULL           | Daemon  | NULL | InnoDB shutdown handler  | NULL                  |    0.000 |
| 156 | root        | localhost:49734 | notificationdb | Sleep   |  287 |                          | NULL                  |    0.000 |
| 157 | root        | localhost:49738 | notificationdb | Sleep   |  161 |                          | NULL                  |    0.000 |
| 161 | root        | localhost:51739 | admfactorydb   | Sleep   | 1425 |                          | NULL                  |    0.000 |
| 162 | root        | localhost:51740 | admfactorydb   | Sleep   | 1425 |                          | NULL                  |    0.000 |
| 163 | root        | localhost:51853 | NULL           | Query   |    0 | init                     | show full processlist |    0.000 |
+-----+-------------+-----------------+----------------+---------+------+--------------------------+-----------------------+----------+
10 rows in set (0.000 sec)

MariaDB [(none)]>

If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads.

As you can see I used the FULL keyword, otherwise only the first 100 characters of each statement are shown in the Info column.

 

 

 

References