How to show open database connections in MySQL
mysql
mariadb
show connections
database
mysql processes status
mysql show status
processlist
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.
