Ужасное снижение производительности MySQL после обновления до Ubuntu 16.04 MySQL 5.7.12
У меня есть сервер разработки, который я недавно обновил с 14.04 до 16.04.
У меня есть база данных 'алгебра', в которой хранятся данные для моего сайта algebra.com. Это веб-сайт с вопросами и ответами, в котором есть вопросы и ответы с отношением от 1 до N.
После обновления производительность точно таких же запросов к базе данных резко ухудшилась.
Например, запрос, объединяющий вопросы и ответы по идентификатору вопроса, занимал менее половины секунды. После обновления это займет минуту.
Поскольку это сервер разработки, я могу сравнить его с рабочим сервером, на котором по-прежнему работает Ubuntu 14.04 и где тот же запрос занимает всего 0,38 секунды.
Вот планы запроса
mysql> explain SELECT
-> questions.id, questions.email, questions.topic, questions.question,
-> questions.date,
-> questions.deleted,
-> questions.is_spam,
-> questions.solved,
->
-> questions.tb_id,
-> questions.tb_isbn,
-> questions.tb_title,
-> questions.tb_edition,
-> questions.tb_chapter,
-> questions.tb_problem,
->
-> solutions.id, solutions.author author, solutions.date, solutions.answer
-> FROM questions, solutions
-> WHERE
-> questions.solved = 1
-> AND questions.id = solutions.question
-> AND questions.deleted != 1
-> AND questions.is_spam != 1
-> ORDER BY solutions.date DESC
-> LIMIT 50;
На "Хорошем Сервере":
+----+-------------+-----------+--------+-----------------------+---------+---------+----------------------------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+--------+-----------------------+---------+---------+----------------------------+--------+----------------+
| 1 | SIMPLE | solutions | ALL | solutions_by_question | NULL | NULL | NULL | 650770 | Using filesort |
| 1 | SIMPLE | questions | eq_ref | PRIMARY | PRIMARY | 4 | algebra.solutions.question | 1 | Using where |
+----+-------------+-----------+--------+-----------------------+---------+---------+----------------------------+--------+----------------+
2 rows in set (0.00 sec)
На "Плохом сервере":
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+----------------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+----------------------+--------+----------+----------------------------------------------+
| 1 | SIMPLE | questions | NULL | ALL | PRIMARY | NULL | NULL | NULL | 482186 | 8.10 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | solutions | NULL | ref | solutions_by_question | solutions_by_question | 4 | algebra.questions.id | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+----------------------+--------+----------+----------------------------------------------+
Содержимое базы данных более или менее одинаково, база данных разработки является резервной копией сервера с прошлой ночи.
Любые идеи, где я могу начать это понижение понимания производительности дикой погони?
Спасибо!
2 ответа
Если вы обновились до сервера Ubuntu 16.04 и используете 5.7.12, вы столкнулись - по крайней мере частично - с ошибкой, потребляющей ОЗУ, и с некоторыми динамическими оптимизациями / настройками по умолчанию, которые основаны на доступной ОЗУ сервера и установить меньше, чем в идеале. Это было проблематично для многих людей, но особенно для небольших серверов / VPS с низким объемом оперативной памяти.
Поиск laracasts.com для утечки памяти MySQL 5.7
https://www.reddit.com/r/mysql/comments/4gnj93/mysql_5712_ubuntu_1604_ridiculous_memory/
Есть и другие проблемы, некоторые из которых 5.7.13 исправлены, конечно...
http://mysqlentomologist.blogspot.com/2016/06/fun-with-bugs-43-bugs-fixed-in-mysql.html
Были также сделаны некоторые различные оптимизации / изменения, которые будут иметь влияние, в зависимости от того, используете ли вы InnoDB или MyISAM. Например, недавно установленный VPS с 2 ГБ ОЗУ, который потребляет около 320 МБ ОЗУ, если вы перезапустите MySQL, будет медленно расти до потребления 1 ГБ в режиме ожидания, в режиме обслуживания... с нулевым трафиком или запросами в дБ (что была установка OpenCart, которая использует MyISAM... что я бы не хотел, чтобы кто-то пытался двигаться вперед... но это был случай "поторопись и давайте сделаем это и будем использовать это и...."), И поэтому этому конкретному экземпляру требуется больше $ и времени, чтобы вернуться и справиться с той же проблемой низкой производительности MySQL из-за зависимости от MyISAM в приложении, утечки памяти и некоторых плохих настроек по умолчанию, которые команда Oracle выбросила в дикую природу.
Конечно, вы хотите, вероятно, оптимизировать свои запросы и объединения для новых обновлений. Но в то же время вы, вероятно, будете тратить огромные усилия и ничего не делать из-за утечки основной памяти, как, например, проблема с ситом. Если вы собираетесь пройти оптимизацию, вы можете также сделать это с другим сервером MySQL, отличным от того, который вызывает у вас проблемы.
Ваши варианты, учитывая, насколько медленно MySQL исправляет ошибки:
1) езда и ожидание обновлений репо, которые это исправят
2) удалить текущую версию и вернуться к предыдущей (но почему?)
3) заменить его на MariaDB или Percona
Если вы запускаете новый сервер Ubuntu 16.04, я бы изменил репозитории перед подключением любых пользовательских агентов удаленного администрирования или установкой панелей управления сервером, чтобы вы были на треке MariaDB / Percona. Или отслеживание официальных репозиториев MySQL, а не репозиториев Ubuntu, чтобы быстрее получать исправления.
Безопасное и незамедлительное решение (безусловно, разумнее, чем продление использования более ранних версий с ошибками и основной точкой прерывания совместимости в потоке релизов) - это переключение на MariaDB или Percona. Или, если вы используете приложение, которое может использовать PostgreSQL, а также MySQL, переключитесь на Postgre - если это что-то непрактичное.
Я не буду тратить свое время на оптимизацию базы данных, пока не обновлюсь до 5.7.13 и не проверил результаты или не переключился на MariaDB или Percona. Оптимизация / устранение неполадок 5.7.12 - это просто черная дыра, высасывающая ваше время и ресурсы.
В частности, это ошибка Избыточная память, используемая в памяти /innodb/os0file, начиная с 5.7.8.
Он отслеживается в Ubuntu как чрезмерное потребление оперативной памяти демона mysqld в Ubuntu 16.04, а также обсуждается Reddit.