![mysql、java 和 MySQLTimeoutException](https://linux22.com/image/799856/mysql%E3%80%81java%20%E5%92%8C%20MySQLTimeoutException.png)
我已经将 mysql 服务器移到 LAN 中的不同机器上,16 核,8GB RAM,结果同样令人恐惧。在负载测试期间,一切进展顺利,直到大约 300 秒。从 DB 的客户端到 DB 有 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”所报告的)
过了一段时间,连接开始消失(尽管 DB 的客户端还没有死):
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 个(顺便说一下,它们的用法不同)
编辑:再过一段时间,剩下的 4 个连接中有 3 个存活了下来,并且又出现了 7 个新的连接。
编辑2:经过更多小时——0 与 DB 的连接,但 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)
编辑3:
mysql> show variables like 'max_allowed_packet'
-> ;
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.01 sec)
我应该进一步研究什么?谢谢!
答案1
1.首先编辑my.cnf(MySQL配置文件)。
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. 找到超时配置并进行调整以适合您的服务器。
[mysqld] 等待超时 = 31536000 interactive_timeout = 31536000
3. 保存更改并退出编辑器。
4. 重新启动 MySQL 以应用更改,如下所示:
sudo /etc/init.d/mysql 重启
答案2
一些评论 -
1.
您的 mysql 配置文件中的值是什么max_connections
?通常默认设置为 100。检查您是否设置了正确的值 -
mysql> show variables like "max_connections";
2.
您的 mysql 设置值是多少wait_timeout
?它似乎设置在 1600 以上,您可能需要将其降低到不会对您的应用程序产生负面影响的程度 -
mysql> SHOW VARIABLES LIKE 'wait_timeout';
wait_timeout=180
然后在你的 mysql 配置文件中,在部分下添加值mysqld
。
3.
禁用持久连接 - 通常不建议在连接到 mysql 时使用来自客户端应用程序的持久连接。
如果这些都不起作用,请尝试运行mysqltuner
-mysql 调优脚本,并采纳相关建议。