Я переместил сервер mysql на другую машину в локальной сети, с 16 ядрами, 8 ГБ ОЗУ и получил тот же ужасный результат. Во время нагрузочного теста все идет хорошо примерно до 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/room3_+391737@muc.xmpp.xgate.com.hk/creation-date; method took 25410 ms, thread: 399
однако нагрузка на машину mysql все это время почти равна нулю (как сообщает 'top')
Еще через некоторое время соединения начинают пропадать (хотя клиент БД не умер):
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 (которые, кстати, используются по-разному)
РЕДАКТИРОВАТЬ: еще через некоторое время 3 из оставшихся 4 выжили и появилось еще 7 новых подключений.
EDIT2: еще больше часов - 0 подключений к БД с клиентом БД все еще существует. Что происходит, интересно ...
И согласно замечаниям Даниила:
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. Сначала отредактируйте 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] wait_timeout = 31536000 interactive_timeout = 31536000
3. Сохраните изменения и выйдите из редактора.
4. Перезапустите MySQL, чтобы изменения вступили в силу следующим образом:
sudo /etc/init.d/mysql restart
Пара комментариев -
1. что max_connections значение в вашем файле конфигурации mysql? Обычно по умолчанию установлено значение 100. Проверьте, правильно ли установлено значение -
mysql> show variables like "max_connections";
2. что wait_timeout значение для вашей настройки mysql? Похоже, что он установлен выше 1600, вам может потребоваться снизить его до точки, где это не повлияет отрицательно на ваше приложение -
mysql> SHOW VARIABLES LIKE 'wait_timeout';
Затем в вашем файле конфигурации mysql добавьте значение, скажем wait_timeout=180, под mysqld раздел.
3. Отключите постоянные подключения - обычно не рекомендуется использовать постоянные подключения из вашего клиентского приложения при подключении к mysql.
Если ничего из этого не помогает, попробуйте запустить mysqltuner - сценарий настройки mysqlи примените соответствующие рекомендации.