mysql, java, and MySQLTimeoutException

Question

I've moved the mysql server on different machine in LAN, 16 core, 8GB RAM, and the same dreaaded result. During a load test, all is going well until around 300 seconds. There are 15 persistent connections from DB's client to DB, and after this period I can see it going flat:

Suddenly all queries sent to it start to time out:

mysql> show processlist;
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
| Id  | User        | Host               | db       | Command | Time | State | Info             |
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
| 238 | tigase_user | 58.64.157.39:35595 | NULL     | Query   |    0 | NULL  | show processlist | 
| 244 | tigase_user | 58.64.157.25:22624 | tigasedb | Sleep   |    0 |       | NULL             | 
| 245 | tigase_user | 58.64.157.25:22625 | tigasedb | Sleep   |    0 |       | NULL             | 
| 246 | tigase_user | 58.64.157.25:22626 | tigasedb | Sleep   |    0 |       | NULL             | 
| 247 | tigase_user | 58.64.157.25:22627 | tigasedb | Sleep   |    0 |       | NULL             | 
| 248 | tigase_user | 58.64.157.25:22628 | tigasedb | Sleep   |    0 |       | NULL             | 
| 249 | tigase_user | 58.64.157.25:22629 | tigasedb | Sleep   |    0 |       | NULL             | 
| 250 | tigase_user | 58.64.157.25:22630 | tigasedb | Sleep   |    0 |       | NULL             | 
| 251 | tigase_user | 58.64.157.25:22631 | tigasedb | Sleep   |    0 |       | NULL             | 
| 252 | tigase_user | 58.64.157.25:22632 | tigasedb | Sleep   |    0 |       | NULL             | 
| 253 | tigase_user | 58.64.157.25:22633 | tigasedb | Sleep   |    0 |       | NULL             | 
| 254 | tigase_user | 58.64.157.25:22634 | tigasedb | Sleep   |  619 |       | NULL             | 
| 255 | tigase_user | 58.64.157.25:22635 | tigasedb | Sleep   |  619 |       | NULL             | 
| 256 | tigase_user | 58.64.157.25:22636 | tigasedb | Sleep   |  620 |       | NULL             | 
| 257 | tigase_user | 58.64.157.25:22637 | tigasedb | Sleep   |  619 |       | NULL             | 
| 258 | tigase_user | 58.64.157.25:22638 | tigasedb | Sleep   |  620 |       | NULL             | 
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
16 rows in set (0.00 sec)


[...] after 25 seconds [...]

mysql> show processlist;
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
| Id  | User        | Host               | db       | Command | Time | State | Info             |
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
| 238 | tigase_user | 58.64.157.39:35595 | NULL     | Query   |    0 | NULL  | show processlist | 
| 244 | tigase_user | 58.64.157.25:22624 | tigasedb | Sleep   |   25 |       | NULL             | 
| 245 | tigase_user | 58.64.157.25:22625 | tigasedb | Sleep   |   25 |       | NULL             | 
| 246 | tigase_user | 58.64.157.25:22626 | tigasedb | Sleep   |   25 |       | NULL             | 
| 247 | tigase_user | 58.64.157.25:22627 | tigasedb | Sleep   |   25 |       | NULL             | 
| 248 | tigase_user | 58.64.157.25:22628 | tigasedb | Sleep   |   25 |       | NULL             | 
| 249 | tigase_user | 58.64.157.25:22629 | tigasedb | Sleep   |   25 |       | NULL             | 
| 250 | tigase_user | 58.64.157.25:22630 | tigasedb | Sleep   |   25 |       | NULL             | 
| 251 | tigase_user | 58.64.157.25:22631 | tigasedb | Sleep   |   25 |       | NULL             | 
| 252 | tigase_user | 58.64.157.25:22632 | tigasedb | Sleep   |   25 |       | NULL             | 
| 253 | tigase_user | 58.64.157.25:22633 | tigasedb | Sleep   |   25 |       | NULL             | 
| 254 | tigase_user | 58.64.157.25:22634 | tigasedb | Sleep   |  644 |       | NULL             | 
| 255 | tigase_user | 58.64.157.25:22635 | tigasedb | Sleep   |  644 |       | NULL             | 
| 256 | tigase_user | 58.64.157.25:22636 | tigasedb | Sleep   |  645 |       | NULL             | 
| 257 | tigase_user | 58.64.157.25:22637 | tigasedb | Sleep   |  644 |       | NULL             | 
| 258 | tigase_user | 58.64.157.25:22638 | tigasedb | Sleep   |  645 |       | NULL             | 
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
16 rows in set (0.00 sec)

mysql> show processlist;
+-----+-------------+--------------------+----------+---------+------+---------------+------------------------------------------------------------------------------------------------------+
| Id  | User        | Host               | db       | Command | Time | State         | Info                                                                                                 |
+-----+-------------+--------------------+----------+---------+------+---------------+------------------------------------------------------------------------------------------------------+
| 238 | tigase_user | 58.64.157.39:35595 | NULL     | Query   |    0 | NULL          | show processlist                                                                                     | 
|

     244 | tigase_user | 58.64.157.25:22624 | tigasedb | Query   |    0 | freeing items | select nid as nid3, node as node3 from tig_nodes, (select nid as nid2, node as node2 from tig_nodes, | 
    | 245 | tigase_user | 58.64.157.25:22625 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 246 | tigase_user | 58.64.157.25:22626 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 247 | tigase_user | 58.64.157.25:22627 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 248 | tigase_user | 58.64.157.25:22628 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 249 | tigase_user | 58.64.157.25:22629 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 250 | tigase_user | 58.64.157.25:22630 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 251 | tigase_user | 58.64.157.25:22631 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 252 | tigase_user | 58.64.157.25:22632 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 253 | tigase_user | 58.64.157.25:22633 | tigasedb | Sleep   |    0 |               | NULL                                                                                                 | 
    | 254 | tigase_user | 58.64.157.25:22634 | tigasedb | Sleep   |  645 |               | NULL                                                                                                 | 
    | 255 | tigase_user | 58.64.157.25:22635 | tigasedb | Sleep   |  645 |               | NULL                                                                                                 | 
    | 256 | tigase_user | 58.64.157.25:22636 | tigasedb | Sleep   |  646 |               | NULL                                                                                                 | 
    | 257 | tigase_user | 58.64.157.25:22637 | tigasedb | Sleep   |  645 |               | NULL                                                                                                 | 
    | 258 | tigase_user | 58.64.157.25:22638 | tigasedb | Sleep   |  646 |               | NULL                                                                                                 | 
    +-----+-------------+--------------------+----------+---------+------+---------------+------------------------------------------------------------------------------------------------------+
    16 rows in set (0.01 sec)

This correlates with what I see in my logs at mysql's client:

Caused by: tigase.db.TigaseDBException: Error getting user data for: multi-user-chat/rooms/room3_+391737@muc.xmpp.xgate.com.hk/creation-date; method took 25410 ms, thread: 399

however, the load on mysql machine is almost 0 during all this time (as reported by 'top')

After some more time the connections start to disappear (although DB's client has not died):

    mysql> show processlist;
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
| Id  | User        | Host               | db       | Command | Time | State | Info             |
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
| 238 | tigase_user | 58.64.157.39:35595 | NULL     | Query   |    0 | NULL  | show processlist | 
| 247 | tigase_user | 58.64.157.25:22627 | tigasedb | Sleep   |   14 |       | NULL             | 
| 249 | tigase_user | 58.64.157.25:22629 | tigasedb | Sleep   |   14 |       | NULL             | 
| 250 | tigase_user | 58.64.157.25:22630 | tigasedb | Sleep   |   14 |       | NULL             | 
| 252 | tigase_user | 58.64.157.25:22632 | tigasedb | Sleep   |   14 |       | NULL             | 
| 254 | tigase_user | 58.64.157.25:22634 | tigasedb | Sleep   | 1609 |       | NULL             | 
| 255 | tigase_user | 58.64.157.25:22635 | tigasedb | Sleep   | 1609 |       | NULL             | 
| 256 | tigase_user | 58.64.157.25:22636 | tigasedb | Sleep   | 1610 |       | NULL             | 
| 257 | tigase_user | 58.64.157.25:22637 | tigasedb | Sleep   | 1609 |       | NULL             | 
| 258 | tigase_user | 58.64.157.25:22638 | tigasedb | Sleep   | 1610 |       | NULL             | 
+-----+-------------+--------------------+----------+---------+------+-------+------------------+
10 rows in set (0.00 sec)

The 10 problematic timeout-prone connections have beome 4 as seen above, and those would eventually vanish too, only the last 5 would remain (which are used differently btw)

EDIT: after even some more time, 3 out of those remaining 4 have survived and another 7 new connections have appeared.

EDIT2: after even more hours - 0connections to DB with DB's client still around. wth is going on I wonder...

And as per Daniel's remarks:

mysql> show variables like "max_connections";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    439
Current database: *** NONE ***

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   | 
+-----------------+-------+
1 row in set (0.31 sec)

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

EDIT3:

mysql> show variables like 'max_allowed_packet'
    -> ;
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 | 
+--------------------+----------+
1 row in set (0.01 sec)

Where should Ibe looking further ? Thanks!

what is unusual is the `ERROR 2006 (HY000): MySQL server has gone away` you are getting. could you please post the value of `max_allowed_packet` using `show variables like 'max_allowed_packet'` — Daniel t., Mar 09 '13 at 04:31

score 1 · Answer 1 · answered Mar 14 '17 at 10:35

1. First edit my.cnf (the MySQL configuration file).

Ubuntu 16.04

 sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Debian

 sudo vi /etc/mysql/my.cnf

Centos

 sudo vi /etc/my.cnf

2. Locate the timeout configuration and adjust it to fit your server.

[mysqld]
wait_timeout = 31536000
interactive_timeout = 31536000

3. Save the changes and exit the editor.

4. Restart MySQL to apply the changes as follows:

sudo /etc/init.d/mysql restart

score 0 · Answer 2 · answered Mar 09 '13 at 02:37

0

A couple of comments -

1. what is the max_connections value in your mysql configuration file? Normally it is set to 100 by default. Check if you have the proper value set -

    mysql> show variables like "max_connections";

2. what is the wait_timeout value for your mysql setup? It appears it is set above 1600, you might need to lower it, to the point where it doesn't negatively affect your application -

    mysql> SHOW VARIABLES LIKE 'wait_timeout';

Then in your mysql configuration file, add the value, say wait_timeout=180, under mysqld section.

3. Disable persistent connections - it is generally not recommended to use persistent connections from your client application when connecting to mysql.

If none of this helps, try running mysqltuner - mysql tunning script, and apply the relevant recommendations.

answered Mar 09 '13 at 02:37

Daniel t.

9,061
1
32
36

Hi, thanks for the answer. Perhaps I was a bit unclear. This really does not look like a MySQL problem to me, but rather a client side problem. So what I was asking for really was about proper ways to establish where my client is blowing up. As for the wait_time value, I'd be thinking the more the better. I definitely don't want my connections slain on MySQL's whims. Same for 3. PS - see EDIT2 and below – kellogs Mar 09 '13 at 04:23
It this is a client issue, I am not sure if I will be of any help. The only thing I can say is make sure you select the DB when you make a connection, and try to increase the `max_allowed_packet` to 32M or 64M to see if that helps with the `MySQL server has gone away` error. – Daniel t. Mar 09 '13 at 21:22

mysql, java, and MySQLTimeoutException

2 Answers2