MariaDB: Why do user sessions sometimes freeze

Question

Sometimes, the sessions I initiate with mariadb -h local freeze.

Here's my latest example:

MariaDB [(none)]> SELECT u

I'm certain that I did not hit any extra keys.

Then I did connect once more and could enter my query:

MariaDB [(none)]> SELECT user, host FROM mysql.user;
+-------------------+-----------+
| User              | Host      |
+-------------------+-----------+
| mariadb.sys       | localhost |
| mysql             | localhost |
| root              | localhost |
| user_of_mediawiki | localhost |
+-------------------+-----------+
4 rows in set (0.001 sec)

MariaDB [(none)]> 

I did notice the status of session 35:

MariaDB [(none)]> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+----------+
| Id | User | Host      | db   | Command | Time | State    | Info             | Progress |
+----+------+-----------+------+---------+------+----------+------------------+----------+
| 35 | root | localhost | NULL | Sleep   |  388 |          | NULL             |    0.000 |
| 37 | root | localhost | NULL | Query   |    0 | starting | show processlist |    0.000 |
+----+------+-----------+------+---------+------+----------+------------------+----------+
2 rows in set (0.000 sec)

MariaDB [(none)]> 

After I have terminated the working session:

root@# /var/www# ss -p | grep mariadb
u_str ESTAB 0      0           /run/mysqld/mysqld.sock 42965              * 43515  users:(("mariadbd",pid=711,fd=99))                                                                                                                                                                   
u_str ESTAB 0      0                                 * 43515              * 42965  users:(("mariadb",pid=16694,fd=3))                                                                                                                                                                   
u_str ESTAB 0      0                                 * 17863              * 17864  users:(("mariadbd",pid=711,fd=2),("mariadbd",pid=711,fd=1))                                                                                                                                          
root# 

I have not toggled the terminal to read-only mode.

Answer 1

It is really hard to tell what is happening. The ‘Sleep’ in Command column means only that the connection is up and running but nothing has happened for a while. The connection is responsive, it does not “sleep” literally. Once you start running queries, you will see its status changing.

Most likely there are other problems with your system, outside of what you have shown here. Unfortunately, it is quite hard to debug problems like that without full access to the system. General best practices would be to:

1. Check the output of top command - keep an eye on CPU and memory usage. is mysql or mysqld process using extensive CPU?

2. Check the system logs to see if there is anything worth investigating

3. Check the disk I/O to see if something is happening

4. Check the network (unless it is a local connection)

5. If you use socket to connect to MySQL, try to use TCP connection

6. If you use TCP to connect, try to use socket. See if it makes any difference