我已经将 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 调优脚本，并采纳相关建议。