Ошибка подключения к базе данных. Как решать проблемы с падением Mysql или mariadb?

Содержание

Одна из распостраненных задач, которые доводится решать в системном администрировании — это обеспечение стабильной работы сервера баз данных. Чаще всего в этой роли выступает Mysql.  Есть и другие, postgresql тоже отличный сервер,  хотя он используется обычно для не коробочных проектов, для индивидуальных разработок. Например,  для приложений на python часто применяется. Но большинство популярных коробочных CMS, таких как WordPress, DLE, Joomla, работают в связке с базой данных Mysql. Поэтому я хочу рассмотреть решение проблем Mysql, возникающих у многих вебмастеров.

К примеру, частая проблема — это «Ошибка подключения к базе данных». Так например ругается wordpress, когда не может установить связь с БД. Чаще всего это случается когда Mysql просто выключается. Такое может происходить например при нехватке ресурсов, допустим на серверах минимальных тарифов и конфигураций, даже под небольшой нагрузкой.

Выглядит это примерно следующим образом.

Ошибка mysql: «Fatal error: cannot allocate memory…»

В логах mysql звучит оно чаще всего именно так.  Обычно что-то вроде

InnoDB: Fatal error: cannot allocate memory for the buffer pool

При этом всём, сервер не будет показывать явную нехватку памяти. Именно это и заводит обычно разбирающихся с проблемой в тупик. Чаще всего это бывает, когда на сервере 1-2 гб памяти.

Происходит при этом интересная вещь. Mysql аккуратно убивается ядром системы. Для того, чтобы системе хватило памяти на саму себя и другие процессы.  Обычно это конечно неоправданно, ибо на сервере нет других столь же ресурсоёмких процессов, как mysql или mariadb.  Не всегда причина в этом, но это наиболее частая, типичная картина при регулярных падениях базы данных сайта.

Убедиться в том можно посмотрев в системный лог /var/log/messages либо /var/log/syslog — в зависимости от семейства OS.  Там будут сообщения о том, что сработал OOMKiller и выключил mysql.

Как это происходит:

Чаще всего, с mysql за потребление памяти конкурирует Apache.  Скажем, на сервере 1 гб памяти. Из них, скажем, в норме mysql  использует 200, апач 100 и ядро OS и  вся остальная система 100.  В сумме 400 мегабайт занято, всё хорошо, система работает стабильно. Тут начинается час пик, на сайт идёт трафик.  Апач начинает есть 200 мб памяти, mysql 500, а ОС все так же 100.  В сумме — 800. А у нас на сервере 1 gb, всё хорошо, проблем быть не должно.  Но по-умолчанию политика 50%.  Ядро видит, что mysql взяло 500, не дожидается что пока оно съест ещё больше, и убивает его. А иначе вероятна ситуация, что процесс съест память, и система умрёт сама. Для защиты от этого и предназначен сей механизм.

Отсюда и возникают проблемы с базой данных. Обычно вебмастера «лечат» это обычной перезагрузкой сервера. Что и логично — всё перезагрузили, память переспределилась, потребление меньше — система работает. Но буквально через считанные минуты под нагрузкой всё может повториться.  Те кто продвинутей, понимают, что дёргать весь сервер смысла нет. Можно перезапускать процессы.

Начинающие сисадмины могут «лечить» это костылем куда нибудь в cron, типа перезапуск mysql каждые 10 минут. В принципе вариант. Но довольно опасный. Ибо можно, и часто так и бывает, убить данные в базах таким способом. У вас через какое-то время просто перестанет корректно подниматься база, посыпятся таблицы, и т.д.

Что же тут делать чтобы избежать падений?

Логичный вариант — добавить оперативной памяти на сервер.  Но многие скажут — да как же, у нас и так всего 800 из одного гб используется, какой смысл добавлять ещё?  И будут правы.

Можно не добавлять. Особенно если сайты у вас не такие уж и посещаемые, вы уверены, что нагрузка там не такая уж большая и вашего 1 gb должно хватать.  И действительно, это лечится. Многие уже догадались, что нужно лишь изменить политику распределения памяти в ядре.

Дело в том, что в ядре Linux есть параметры, отвечающие за выделение памяти процессам.  По-умолчанию задана политика, когда на процесс не может быть выделено более 50% доступной машине памяти. И есть там такая штука, которая прибивает процессы, требующие большего объема памяти, чем указано в этой политике. Называется она OOMKiller.  OOM = Out Of Memory —  классическая аббревиатура, обозначающая нехватку памяти.   А в логах ещё обычно пишут «cannot allocate memory».

Итак, за это отвечают два параметра:

vm.overcommit_ratio = 50
vm.overcommit_memory = 0

Именно такие значения они имеют по-умолчанию. Нам же нужно их изменить следующим образом:

vm.overcommit_ratio = 90
vm.overcommit_memory = 2

Я не буду вдаваться в подробности и объяснения по второму параметру.  Кому нужна эта информация может ознакомиться здесь.

Переопределять эти параметры нужно в файле /etc/sysctl.conf.  Но прежде, нужно сделать ещё одну важную вещь, без которой вы можете сильно навредить системе. Перед тем как назначить такие значения ядру, необходимо убедиться в том, что на вашем сервере есть swap. То есть файл или раздел подкачки. И если нет, то нужно его создать. Он необходим для распределения памяти согласно заданным параметрам ядра.

Как проверить наличие swap и создать его

Для этого нужно лишь взглянуть на менеджер процессов в вашей системе. Обычно это top:

На скрине отмечена информация о свопе. А точнее, о его отсутствии.

Но я сам люблю и всем рекомендую пользоваться htop (поставьте его командой yum install -y htop )

В нем это гораздо наглядней и понятней:

Как видите, нижняя строка — Swp — 0. Это значит, что в системе свопа нет. Скорей всего ваш случай. Только у вас строка Mem над ней будет выглядеть иначе, поскольку у вас скорей всего будет меньше памяти.  А если говорить конкретно об этом сервере, откуда я сделал скрин — то для него отсутствие свопа не проблема, ибо на нём памяти более чем достаточно и её нехватки не предвидится ( Кстати, такой мощный сервер с 24 гб памяти и 6 ядрами, 600 GB SSD стоит всего 15 евро в месяц )

Если же своп у вас уже есть, то выглядеть это будет примерно вот так:

Создаем свопфайл

Вообще, на большинстве серверов обычно своп создается по умолчанию, при установке системы. Под него выделяется отдельный раздел диска. Но поскольку вебмастера чаще имеют дело с VPS, то если он не был создан хостером при создании VPS, возможности создать его отдельным разделом уже нет. Или это довольно сложно и не нужно. Удобно и достаточно создать swap-файл.

Делается это следующим образом:

dd if=/dev/zero of=/swapfile bs=1M count=1024

Эта команда создаст файл по адресу /swapfile объемом в 1 гб . Если же вы хотите создать подкачку большего объема, соответственно вам нужно в параметре count указать большее значение — например 2048 для создания свопа в 2 gb.

По завершении команда выдаст отчет о том что сколько-то данных было записано и с какой скоростью.

Теперь нужно этот файл инициализировать и  подключить в качестве свопа.

chmod 0600 /swapfile

mkswap /swapfile

Теперь нужно прописать его в  таблицу файловых систем, чтобы после перезагрузки сервера он подключался автоматически.

Для этого добавим строку в файл /etc/fstab такого содержания:

/swapfile swap swap defaults 0 0

В приниципе после перезагрузки у нас своп теперь появится. Но чтобы подключить его в первый раз сразу и без перезагрузки мы можем дать такую команду:

swapon -a

Эта команда перечитает файл /etc/fstab и подключит наш новый своп. Теперь можем снова смотреть в htop, и увидим что он появился.

Меняем параметры ядра Linux, политику распределения памяти

Для этого открываем файл /etc/sysctl.conf и дописываем строки

vm.overcommit_ratio = 90
vm.overcommit_memory = 2

Если вы этого ещё не сделали.

Если же сделали ранее, то ваши параметры ещё не применились, поскольку нам нужно было создать своп сначала, чтобы не повесить систему.  Дело в том, что здесь как и с файлом fstab — настройки будут подхватываться только при перезагрузке. Но дабы опять таки без оной обойтись, мы можем просто сказать:

sysctl -p

Эта команда перечитает файл sysctl.conf и принудительно задаст наши параметры из него, о чём и отрапортует после выполнения.

На этом всё.  Теперь ни mysql, ни любой другой процесс не будет убиваться ядром при большем потреблении памяти.  Своп мы создали в качестве страховки, он как бы дополняет и продолжает основную память системы, является используемым резервом.

Оптимизация параметров mysql

Однако не всегда всего вышеописанного может быть достаточно для стабильной работы mysql. Часто есть необходимость также изменить параметры самого mysql. Ибо в его конфигурации также есть много параметров, отвечающих за использование памяти. Это всевозможные кэши, пулы и буферы.

Но это история для другой статьи. Впрочем, в подробностях уже писал об оптимизации параметров mysql в своем большом руководстве по оптимизации серверов.

Ну а мне за сим остаётся только пожелать вашим серверам, базам данных и сайтам стабильной и быстрой работы.  А также без лишней скромности напомнить, что в случае проблем с mysql или mariadb, (или даже postgresql чего доброго) вы всегда можете обращаться по контактам  к автору сего опуса.

Источник

Комментарии (13):

seoonly.ru09.10.2018 17:20

Спасибо, бро

vpsadm10.10.2018 08:27

Обращайся

Артем10.10.2018 22:51

Как в тему) Только недавно возился с этой проблемой. В итоге подключил своп на 1Гб и база при нагрузках падать перестала. Осталась одна проблема — при нагрузочном тестировании (30-50 одновременных человек) подрастает время ответа сервера до 2-7 сек (в норме до 500мс). Манипуляции с /etc/sysctl.conf могут в этом помочь или они только именно от падения базы?
P.S. В /etc/sysctl.conf у меня кстати вообще пусто, только комменты какие-то =)

Mie10.10.2018 22:51

Качественная статья, спасибо. А книжку можешь какую-нибудь посоветовать по LAMP, а то одно старье везде лежит?

vpsadm10.10.2018 22:51

Книжек вообще никаких не могу посоветовать, а тем более по LAMP. Да и что там читать в тех книжках. Какая конкретно инфа интересует? как конфигурить его или что?

vpsadm10.10.2018 22:51

Нет, эти параметры не помогут ускорить время ответа. В /etc/sysctl.conf по-умолчанию пусто, всё верно.

А что может помочь — расписано в мануале по оптимизации, на который есть ссылка в конце статьи. Если при нагрузке время ответа увеличивается — то самый логичный вариант переключаться на связку nginx+php-fpm вместо apache. Она держит нагрузку в 3-5 раз лучше. Либо можно ещё кэшировать страницы на уровне nginx. Тоже об этом всём расписано в мануале.

Mie10.10.2018 22:51

Да конфиг апача с nginx, повышение производительности, кэширование, безопасность, сравнение апача c php-fpm и т.п.

Артем10.10.2018 22:51

Да, спасибо. Я уже начал ее читать и очень пожалел, что не увидел раньше) Добавил в закладки — буду делать) Кстати, по поводу кэширования nginx маленький вопрос в тему, не нашел в той статье: в ISP Manager 5 включается кэширование одной галкой в настройках домена — это совсем не то, что нужно?)

вова13.10.2018 13:56

я еще испоьзую memlock
запрет на перенос процесса mysqld в свап

vpsadm13.10.2018 13:56

Спасибо, рад, что помогает)
Что касается галки кэширование в ISPmanager — это тоже нужно, но не то. Это expires, браузерное кэширование статики. То есть изображения, js, css оседают в кэше браузера клиента и на какой-срок — вот что это. Это то самое кэширование, которое просит включить Google Pagespeed Insights в своих рекомендациях. Суть его в том, чтобы при повторных обращениях браузер не перекачивал статические ресурсы, а брал их из кэша, с диска компьютера, чтобы меньше нагружать сеть.

В мануале же описано более продвинутое решение — серверное кэширование. Если говорить о нем и панельках, то оно реализовано (хоть и не лучшим образом) в панели VestaCP. Там эта самое кэширование включается в настройках сайта шаблоном вебсервера «caching» вместо «default». И хотя работает хоть и не столь эффективно, насколько это возможно, но всё же работает.

vpsadm13.10.2018 13:56

Это странно. Ибо при такой проблеме своп обычно отсутствует в принципе, а при его наличии и минимальном объеме памяти это только ухудшит ситуацию. Я тут наоборот создаю своп для решения проблемы) Проще тогда уж отключить своп, да и всё.
Но вообще не слышал про memlock, любопытно, поизучаю, спасибо.

Артем13.10.2018 13:56

Спасибо! Не знал, думал этой галкой включается именно кэш apache-nginx, а не nginx-браузер. Буду делать, может даже не забуду и отпишусь о результатах)

vpsadm13.10.2018 13:56

Та не за что. А результаты там обычно практически незаметны. Ибо сегодня каналы широкие и быстрые, поэтому особо нет проблемы передать лишние пару мегабайт статики при запросах к сайтам. А ещё браузеры чрезмерно «умные», есть подозрение, что они многое кэшируют независимо от того установлены эти заголовки на сайте или нет.

Войдите или зарегистрируйтесь чтобы оставить комментарий