Usr sbin mysqld жрет много памяти
Обновлено: 06.07.2024
MySQL - это одна из самых популярных реляционных систем управления базами данных, которая используется для обеспечения большинства веб-сайтов в интернете. От скорости записи и получения данных из таблиц зависит скорость работы сайта, в целом, так как, если на один запрос будет уходить больше секунды, то это будет тормозить работу php, а в следствии скоро накопиться столько запросов, что сервер не сможет их обработать.
В сегодняшней статье мы поговорим о том, как выполняется оптимизация производительности mysql. Какие программы для этого лучше использовать и как это работает.
Скорость работы MySQL
Оптимизация без аналитики бессмысленна. Перед тем как переходить к оптимизации давайте посмотрим как работает база данных сейчас, есть ли запросы, которые выполняются очень медленно. Все настройки вашего сервиса mysql находятся в файле /etc/my.cnf. Чтобы включить отображение медленных запросов добавьте такие строки в my.cnf, в секцию [mysqld]:
Здесь первая строка включает запись лога медленных запросов, вторая указывает, что минимальное время запроса для внесения его в этот лог - две секунды. Еще можно включить в лог запросы, которые не используют индексы:
Но это уже необязательно для проверки скорости и используется больше для отладки кода и правильности создания таблиц. Дальше перезапустите сервер баз данных и посмотрите лог:
systemctl restart mariadb
tail -f /var/log/mariadb/slow-queries.log
Мы можем видеть, что есть запросы, которые выполняются больше, чем 10 секунд. Это, например, запрос
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
Можно его выполнить отдельно, в консоли mysql:
Здесь тоже измеряется время, и мы видим результат - три секунды. Это очень много. И еще ничего, если такие запросы приходят редко, если ваш сайт постоянно под нагрузкой, то тремя секундами вы не отделаетесь, количество необработанных запросов будет расти, а скорость ответа увеличиваться до нескольких минут. Можно пойти двумя путями - оптимизировать код, убрать сложные запросы, или же нужна оптимизация mysql на сервере.
Оптимизация MySQL
Конфигурация MySQL достаточно сложная, но, к счастью, вам не нужно в нее сильно углубляться. Есть специальный скрипт под названием MySQLTunner, который анализирует работу MySQL и дает советы какие параметры нужно изменить и какие значения для них установить. Скрипт поддерживает большинство версий MariaDB, MySQL и Percona XtraDB. Нам понадобится загрузить три файла с помощью wget:
Первый из них - это сам скрипт, написанный на Perl, второй и третий - база данных простых паролей и уязвимостей. Они позволяют обнаружить проблемы с безопасностью. Дальше можно переходить к тестированию. Я использую сервер с настройками mysql по умолчанию, установленными панелью управления VestaCP.
Буквально за несколько минут скрипт выдаст полную статистику по работе MySQL. Количеству запросов, занимаемому объему памяти и эффективности работы буферов. Вы можете ознакомиться со всем этим, чтобы лучше понять в чем причина проблем. Проблемные места обозначены красными восклицательными знаками. Например, здесь мы видим, что размер буфера движка таблиц InnoDB (InnoDB buffer pool) намного меньше, чем должен быть для оптимальной работы:
Все параметры нужно добавлять в /etc/my.cnf. Еще раз замечу, что вы не копируете статью, а смотрите что вам выдала утилита. Начнем с query-cache.
query_cache_size=0
query_cache_type=0
query_cache_limit=1M
Скрипт рекомендует отключить кэш запросов. Query Cache - это кэш вызовов SELECT. Когда базе данных отправляется запрос, она выполняет его и сохраняет сам запрос и результат в этом кэше. И все бы ничего, но при использовании его вместе с InnoDB при любом изменении совпадающих данных кэш будет перестраиваться, что влечет за собой потерю производительности. И чем больше объем кэша, тем больше потери. Кроме того при обновлении кэша могут возникать блокировки запросов. Таким образом, если данные часто пишутся в базу данных - его надежнее отключить.
Оба параметра устанавливают размер памяти, которая используется для внутренних временных таблиц MySQL. Утилита рекомендует использовать объем больше 16 мегабайт, просто установите это ваше значение для обоих переменных, если у вас достаточно памяти, то можно выделить 32 или даже 64. Но важно чтобы оба значения совпадали, иначе будет использоваться минимальное.
Этот параметр отвечает за количество потоков, которые будут закэшированны. После того, как работа с подключением будет завершена, база данных не разорвет его, а закэширует, если количество кэшированных потоков не превышает ограничение. Утилита рекомендует больше четырех, например, 16.
Указывает, что не нужно пытаться определить доменное имя для подключений извне. Ускоряет работу, так как не тратится время на DNS запросы.
Этот параметр определяет размер буфера InnoDB в оперативной памяти, от этого размера очень сильно зависит скорость выполнения запросов. Значение зависит от размера ваших таблиц и количества данных в них. Если памяти недостаточно, запросы будут обрабатываться дольше. У меня используется стандартный объем 128, а нужно больше 652.
Размер файла лога innodb должен составлять 25% от размера буфера. В случае 800 мегабайт это будет 200М. Но тут есть одна проблема. Чтобы изменить размер лога нужно выполнить несколько действий. Поскольку мы изменили все нужные параметры перейдем к перезагрузке сервера. Для нашего лога нужно остановить сервис:
systemctl stop mariadb
Затем переместите файлы лога в /tmp:
mv /var/lib/mysql/ib_logfile[01] /tmp
И запустите сервис:
systemctl start mariadb
systemctl status mariadb
Тестирование результата
Готово оптимизация базы данных mysql завершена, теперь тестируем тот же запрос через клиент mysql:
> USE база_данных;
> SELECT option_name, option_value FROM wpfc_options WHERE autoload = 'yes';
Первый раз он выполняется долго, может даже дольше чем обычно, но все последующие разы буквально мгновенно. Результат с более 3 секунд до 0,15. А если брать статистику из slow-log, то от более 12. Если в выводе утилиты для вас были предложены и другие оптимизации, то их тоже стоит применить.
Выводы
Как видите, оптимизация mysql это достаточно просто благодаря такому скрипту, но, в то же время, такая операция может очень сильно помочь, особенно высоконагруженным проектам. Еще лучше ускорить работу может только оптимизация запросов mysql. Не забывайте время от времени проверять параметры, чтобы быть уверенным что все в порядке. Если у вас остались вопросы, спрашивайте в комментариях!
На завершение лекция про производительность MySQL от Percona:
чтобы оценить использование ОЗУ, я использую эту формулу:
эта формула производит около 5,3 ГБ расчетного распределения. Вместо этого Оперативная память, используемая MySQL, продолжает расти, и через пару дней активности она значительно превышает 9 ГБ (сильно записывается в swap).
что я упустил? Как может Я понимаю, кто ест оставшегося барана?
еще несколько кусочков информации.
Я запускаю MySQL CE 5.6.17 на RHEL6.3 64 бита. Мой сервер имеет 6 ГБ ОЗУ и 8 ГБ пространства подкачки. У меня постоянно около 150 живых подключений к MySQL, но я держу max_connections к более высоким значениям, чтобы приспособить пики и предвидеть некоторый трафик на этой машине. Я использую только InnoDB. Мое приложение использует объединенные подключения к БД. Производители открывают соединения и выпускают их в бассейн, когда они сделали. Я измерил, что у меня всегда есть несколько параллельных запросов, и объединение ускоряет создание немного соединений (так быстрее запросов). Пул (DBCP) автоматически настраивает разумное количество живых соединений, и обычно это около 100-150. Я уже пробовал:
- убийство соединений не освобождает ОЗУ
- флеш-таблицы не освобождают ОЗУ
после моего config:
это память, выделенная mysql после нескольких дней активности:
дополнительная информация на моем сервере:
как было предложено, я работал с и без секционирования на моем сервере (у меня есть 5 таблиц с 2000 разделов каждый), и кажется, что отключение секционирования помогает в некотором роде. Но все же у меня нет ясной причины, и я не могу устранить разделение вообще.
если я правильно читаю вывод, соответствующая утечка не найдена, и у меня есть огромное потребление памяти на соединение (500 МБ каждого соединения).
это правильно? Есть идеи, почему он так сильно растет?
словарь данных InnoDB может расти без границ, как вы открываете много таблиц, за innodb_additional_mem_pool_size, и он часто растет огромный, если у вас есть тысячи таблиц. Это не зависит от количества соединений.
Я видел, как другие люди сообщают, что MySQL 5.6 имеет много использования памяти, но мы не отследили его окончательно. Нужно было бы запустить mysqld под valgrind, чтобы отслеживать рост памяти.
MySQL 5.7 все еще находится под разработка, но они создают новые инструменты PERFORMANCE_SCHEMA для отслеживания использования памяти.
Если память, сообщаемая только словарем данных InnoDB, не учитывает рост памяти, я предлагаю прочитать следующие отчеты об ошибках, чтобы узнать, применяются ли они в вашем случае:
Но, с этими настройками использование памяти наоборот выросло до 20%.
Значения по умолчанию дают 18%.
Пробовал менять значения - удавалось сбить до 10%. Но, наверное и это много.
Mysqltuner.pl
innodb_buffer_pool_size = 1024M для Вашей базы слишком большой
Можно уменьшить до 256 Mb
p.s. все эти советы в статьях - это может сказаться как в лучшую так и в худшую сторону
Нет универсального решения, всё нужно тюнить под конкретные нагрузки/объёмы базы
Уменьшить буффер innodb, как вам выше написали. Уменьшить количество соединений и вообще 20% это норм.
vm.swappiness = 10 или меньше в sysctl, чтобы в swap не лезло.
Mobiaaa:innodb_buffer_pool_size = 1024M для Вашей базы слишком большой
Можно уменьшить до 256 Mb
p.s. все эти советы в статьях - это может сказаться как в лучшую так и в худшую сторону
Нет универсального решения, всё нужно тюнить под конкретные нагрузки/объёмы базы
Спасибо за совет!
Уменьшил это значение с 1 Гб. до 134 Мб.
Использование памяти mysql снизилось с 1530 до 1300 мб.
top показывает использование памяти 12,8% (было)
Вспомнил один случай из своей жизни.
Это было больше 10 лет назад.
Я купил компьютер и не умел даже драйвер с диска для диалап модема поставить.
Но, на самом компе поставил разные проги, которые показывают температуру проца, винта, всякие показатели питания и т.д. И вдруг, одна прога показала, что возможно проц перегревается, что нет нормальной вентиляции и т.д.
Я сразу за системник и на сервис. Пришел такой полный ламер и мастера на меня смотрят и говорят: ну, рассказывай, что там случилось?
Я: проц перегревается!
А они: а ты откуда знаешь?
Я уверено: прога показала.
Честные ребята попались, успокоили меня и выпроводили по быстрому, чтобы мозги не трах**.
А современные мастера уже бы пол компа пересобрали, чтобы проц не грелся ))) (бабки рулят).
Я вижу, что моему серверу не хватает памяти и происходит сбой даже с
48 ГБ ОЗУ. Например, я не мог продолжать работать на 24G. БД такого размера должна быть в состоянии работать на гораздо меньших объемах. Понятно, что мне не хватает чего-то принципиального.
Это был недосмотр использования файла на таблицу, и мне нужно это изменить (у меня 6000 таблиц, и большинство из них разбиты на разделы).
После недолгого бега (один час) mytop показывает это:
И бесплатно показывает это:
Топ показывает это:
Как это может быть? Это связанный файл для таблицы? Вся БД может поместиться в памяти. Что я делаю неправильно?
Ну, я решил проблему. Я ценю всю проницательность от тех, кто откликнулся. Решение очень странное, и я не могу объяснить, почему это решает проблему, но это так. Я добавил следующую строку в my.cnf:
Кроме того, вам может понадобиться добавить следующее:
Результаты сохраняются и загружаются из памяти, и, учитывая, что вы работаете со скоростью 283 в секунду, вероятно, в любой момент времени выдается много данных.
Я бы подумал, что вы хорошо справляетесь с работой, вытесняя из этого сервера много. Представьте, что таблицы - это одно, а затем схема, включающая 6000 таблиц, плюс тот факт, что вы выполняете 283 запроса в секунду для базы данных объемом 35 ГБ и что эти результаты хранятся в памяти во время их обслуживания. Остальные из нас могли бы также учиться у вас.
Относительно остановки и перезапуска MySQL
Вы могли бы попробовать innodb_flush_method=normal , который рекомендуется здесь и здесь, но я не могу обещать, что это сработает.
Пожалуйста, включите журнал ошибок MySQL в вашей обычной конфигурации. Когда MySQL дает сбой, защитите журнал ошибок перед перезапуском и добавьте последний журнал ошибок, доступный для вашего Вопроса, пожалуйста. Он должен иметь представление, почему MySQL не работает. Запуск «маленькой» конфигурации будет работать как собака, когда поддержит объем активности, о котором сообщает SHOW GLOBAL STATUS. Пожалуйста, вернитесь к своей обычной производственной конфигурации. Я просматриваю предоставленные вами данные и буду предлагать некоторые настройки в течение следующих 24 часов. Похоже, большинство действий со списком процессов связаны с репликацией. Это было бы правдой?
В вашем файле my.cnf (конфигурация MySQL) :
Добавить параметр в блок [mysqld]
Для MySQL 5.7.8 и более поздних версий вам нужно будет добавить дополнительные настройки, как показано ниже:
ПРИМЕЧАНИЕ. Это сократит использование памяти до более чем 50–60%. "show_compatibility_56" не является обязательным, в некоторых случаях он работает, лучше проверить его после добавления в файл конфигурации.
118 активных потоков могут быть разумными, но, кажется, вызывают экстремальное переключение контекста, пытаясь ответить на 118 вопросов одновременно.
Хотелось бы увидеть ваши ШОУ ГЛОБАЛЬНОЕ СТАТУС и ШОУ ГЛОБАЛЬНЫЕ ПЕРЕМЕННЫЕ, если бы вы могли опубликовать их.
A) max_allowed_packet_size в 1G, скорее всего, то, что вы имели в виду в своей конфигурации, учитывая, что 7.08.2017 ваши удаленные агенты отправляют пакеты 1G для обработки на этом оборудовании. Как управляются удаленные агенты с точки зрения планирования их отправки данных, чтобы предотвратить исчерпание всех 48G на этом хосте для этого единственного использования памяти? Состояние указывает на то, что bytes_received 6/6/2017 было 885 485 832 из max_used_connections из 86 в первые 1520 секунд безотказной работы.
B) innodb_io_capacity на 200, вероятно, является значительным препятствием для ваших возможных операций ввода-вывода в секунду, здесь мы используем 700. Утилита sqlio.exe использовалась для направления нас в этом направлении.
C) innodb_io_capacity_max , вероятно, также следует скорректировать.
D) thread_cache_size из 11, подумайте о переходе на 128.
E) thread_concurrency из 10, подумайте о переходе на 30.
F) Я понимаю, что длина process-list.txt в количестве идентификаторов сна, вероятно, вызвана использованием постоянных соединений. Соединение просто ожидает какой-то дополнительной активности от клиента в течение длительного периода времени. 8/8/2017
G) СОСТОЯНИЕ Com_begin обычно очень близко к Com_commit счету, не в вашем случае. 8/8/2017 Com_begin было 2 года, а Com_commit - 709 910 в течение 11 часов безотказной работы.
H) Вероятно, было бы полезно просмотреть всего 3 минуты общего журнала, если это возможно.
Держите меня в курсе ваших успехов.
Я бы проверил table_open_cache . У вас много таблиц, и это четко отражается в средних открытых файлах в секунду: около 48, когда нормальное значение находится в диапазоне от 1 до 5. Это подтверждается значениями Table_open_cache_misses и Table_open_cache_overflows , в идеале эти значения должны быть Cero. Это означало неудачные попытки использовать кеш и, как следствие, потерю памяти. Попробуйте увеличить его как минимум до 3000 и увидеть результаты.
Добрый день. Есть сервер с 16 гигами оперативки. Сервер обслуживает ряд доменов со сложно структурированной базой данных. В целях ускорения отклика страниц я по крону, раз в 15 минут, создаю для каждого домена (всего их на сервере 14) по 161 таблице типа Memory, в которые кладу подготовленные, отсортированные данные.
Сами таблицы вместе с индексами, согласно show table status, занимают в сумме 5,7 гигабайт (Data_length + Index_length) - что сразу после загрузки сервера приблизительно согласуется с памятью, которую, согласно top, забирает mysql - порядка 7 гигабайт. Однако в дальнейшем mysql начинает забирать под себя всё больше памяти, в течении нескольки часов доходит уже до 11-12 гигабайт. Затем наступает момент, когда сервер виснет и бутается. В такие моменты шелл ssh с топом умирает при следующих значениях (привожу за последние 2 ребута):
Разница в Swap: - это попытка ограничить доступную память, т.к. в первом случае сервер умирал от того, что данные перемещались в swap, и HDD просто захлёбывался в процессах, пытавшихся писать на него.
Т.е., как я понимаю, mysql выжирает всю память, и когда её остаётся слишком мало для апача+пхп (на сервере также есть ramfs
на 600Мб) - сервер падает от нагрузки.
Mysql на сервере использует только эта система.
Все "твёрдые" таблицы лежат в отдельной базе, имеют тип MyISAM и общий размер, со всеми индексами, чуть меньше 1Гб. А если отсюда вычесть размер таблиц+индексов, которые вообще не используются, и даже не открываются (остались от старой системы) - то реальный размер сокращается до 330Мб.
Все таблицы типа Memory лежат в другой базе. По крону, раз в 15 минут, производятся следующие действия: я прохожу по циклу все домены, для каждого домена создаю 161 новую таблицу с временным названием, потом переименовываю таблицы - как-бы меняю старые на новые, потом обрабатываю данные на основе накопившихся данных в shared memory и старых таблиц, после чего удаляю старые таблицы и перехожу к следующему домену.
Помогите плиз разобраться - откуда такие аппетиты MySQL, и как это вылечить?
Читайте также: