mysql，java和MySQLTimeoutException

我已经移动在不同的机器在局域网，16核心，8GB内存的MySQL服务器和相同的结果。在负载testing期间，一切进展顺利，直到大约300秒。从数据库的客户端到数据库有15个持续的连接，在这段时间之后，我可以看到它持平：

突然间，所有发送给它的查询都开始超时：

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)

这与我在mysql客户端的日志中看到的相关：

 Caused by: tigase.db.TigaseDBException: Error getting user data for: multi-user-chat/rooms/[email protected]/creation-date; method took 25410 ms, thread: 399

然而，在这个时候，mysql机器上的负载几乎为0（正如'top'所报告的那样）

经过一段时间的连接开始消失（尽pipe数据库的客户端还没有死）：

  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)

如上所示，10个有问题的超时倾向连接有4个，最终会消失，只有最后5个会保持不变（这是用不同的btw）

编辑：甚至更多的时间后，剩下的4人中有3人幸存下来，另有7个新的连接出现。

编辑2：更多的小时后 – 与DB的客户端仍然在0 DB连接。我正在怀疑…

按照但以理的话说：

 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)

我应该在哪里看更进一步？谢谢！

1.首先编辑my.cnf（MySQLconfiguration文件）。

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.find超时configuration并进行调整以适合您的服务器。

的[mysqld]
 wait_timeout = 31536000
 interactive_timeout = 31536000

3.保存更改并退出编辑器。

4.重新启动MySQL以应用更改如下：

 sudo /etc/init.d/mysql重新启动

几个意见 –

1.什么是你的mysqlconfiguration文件中的max_connections值？通常默认设置为100。检查你是否有适当的价值集 –

  mysql> show variables like "max_connections";

2.什么是你的mysql设置的wait_timeout值？它似乎设置在1600以上，您可能需要降低它，以不会对您的应用程序产生负面影响 –

  mysql> SHOW VARIABLES LIKE 'wait_timeout';

然后在你的mysqlconfiguration文件中，在mysqld部分下添加值，比如wait_timeout=180 。

3.禁用持久连接 – 通常不build议在连接到mysql时使用来自客户端应用程序的持久连接。

如果这些都没有帮助，请尝试运行mysqltuner – mysql tunning脚本，并应用相关的build议。