MariaDB: настройка и мониторинг с помощью Zabbix

От правильной настройки и надежной работы сервера СУБД зависит работоспособность и быстродействие интернет-магазинов, работающих на сервисе SAAS. То же самое относится к любым сайтам, если им нужна база данных. 

Очень часто в качестве СУБД используется MySQL или MariaDB. 

Из нашей статьи вы узнаете, как установить сервер MariaDB в ОС Debian 11, как оптимизировать его параметры сразу после установки и как контролировать работу MariaDB вручную и с помощью Zabbix. 

Cразу после установки конфигурация сервера MySQL или MariaDB обычно сильно отличается от оптимальной и требует обязательной настройки. В процессе работы могут появляться новые базы данных, новые таблицы и запросы. При этом может потребоваться дополнительная настройка параметров работы СУБД. 

Отличия MariaDB от MySQL рассмотрены здесь. А в этой статье рассказано о совместимости.  В плане настройки конфигурации и мониторинга для MySQL и MariaDB используются схожие процедуры.

Установка сервера MariaDB в ОС Debian 11

Если на сервере есть панель управления, такая как ISPmanager или Hestia Control Panel, то сервер MariaDB  или MySQL у вас, скорее всего, уже установлен. Обычно MariaDB устанавливается вместе с панелью, если это задано при установке. Некоторые панели, например, ISPmanager, позволяют добавить MySQL или MariaDB уже после установки панели.

Если панель не используется, установите MariaDB вручную следующими командами:

# apt update
# apt upgrade
# apt install mariadb-server

После установки сервиса проверьте, что он запустился и находится в состоянии enabled, то есть будет запущен автоматически после перезагрузки ОС:

~# systemctl status mariadb
● mariadb.service - MariaDB 10.5.15 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Mon 2022-08-29 09:44:00 MSK; 16s ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 814172 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 814173 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 814175 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WS>
    Process: 814241 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 814243 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 814222 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 19 (limit: 4676)
     Memory: 68.9M
        CPU: 746ms
     CGroup: /system.slice/mariadb.service
             └─814222 /usr/sbin/mariadbd

Проверка конфигурации MariaDB

Для оценочной проверки параметров MariaDB или MySQL можно использовать утилиту MySQLTuner, опубликованную на Github.

После того как вы добавили на сервер базы данных пользователей и сервер проработал хотя бы сутки, загрузите утилиту MySQLTuner и запустите ее следующим образом:

# wget http://mysqltuner.pl/ -O mysqltuner.pl
# perl mysqltuner.pl

Если вы запускаете эту утилиту в Debian 11 от пользователя root, то вам не нужно указывать пароль root сервера MariaDB. 

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

[!!] InnoDB is enabled but isn't being used
…
[!!] There is no basic password file list!
…
[--] InnoDB is enabled.
[!!] No tables are Innodb
…
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 96.0M * 1 / 128.0M should be equal to 25%

В конце отчета вы найдете раздел рекомендаций:

-------- Recommendations ------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    skip-name-resolve=1
    performance_schema=ON
    innodb_log_file_size should be (=32M) if possible, so InnoDB total log files size equals 25% of buffer pool size.

Здесь указаны изменения, которые нужно внести в файл конфигурации MariaDB. Обратите внимание, что если сервер СУБД проработал менее 24 часов, то рекомендации могут быть неточными.

Настройка конфигурации MariaDB

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

Для повышения производительности в первую очередь необходимо правильно задать размер буферов. При этом нужно сделать так, чтобы общий объем памяти, потребляемый MariaDB, не превысил разумных значений. Иначе памяти может не хватить, и сервис MariaDB завершит свою работу аварийно, либо вообще не сможет стартовать. 

Ни в коем случае не изменяйте значения параметров, не разобравшись предварительно, на что они влияют. Даже если вы получили такие рекомендации от утилиты MySQLTuner.

Конфигурация сервиса MariaDB находится в файле /etc/mysql/mariadb.conf.d/50-server.cnf. Перед изменениями сделайте копию файла конфигурации, например, в своем рабочем каталоге. Тогда, если с измененной конфигурацией сервер не запустится, вы всегда сможете восстановить старый вариант. 

После редактирования файла конфигурации перезапустите MariaDB и убедитесь, что сервис запустился:

# systemctl restart mariadb
# systemctl -l status mariadb

Если сервис mariadb не запустился, посмотрите журнал ошибок и найдите там возможную причину.

Чтобы понять, где находится файл журнала ошибок, откройте файл /etc/mysql/mariadb.conf.d/50-server.cnf и проверьте параметр log_error:

#log_error = /var/log/mysql/error.log

В новых версиях MariaDB этот параметр закрыт символом комментария. Это означает, что для журнала используется сервис journald.

В этом случае содержимое журнала MariaDB можно посмотреть так:

# journalctl -u mariadb

Хорошую статью по использованию journalctl можно найти здесь.

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

Отключите обратный поиск по DNS 

Для того чтобы сервис не тратил время на поиск адреса IP клиента в DNS, добавьте в файл конфигурации следующую строку:

skip-name-resolve=1

Это позволит увеличить производительность при большом количестве запросов со стороны клиентов с разных адресов IP.

Настройте key_buffer_size

Если в ваших базах данных используются таблицы типа MyISAM, нужно настроить размер буфера для индексных блоков. Этот размер задается параметром key_buffer_size и по умолчанию составляет всего 128 Мбайт.

Оптимальный размер этого буфера позволяет исключить обращения к диску для чтения блоков индекса.

Чтобы узнать текущий размер буфера, введите в консольном приглашении MariaDB такую команду:

MariaDB [(none)]> show variables like 'key_buffer_size';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| key_buffer_size | 134217728 |
+-----------------+-----------+

В документации MariaDB рекомендуется установить размер key_buffer_size равной примерно четверти объема памяти, доступной на сервере: https://mariadb.com/kb/en/optimizing-key_buffer_size/.

Для более точной установки размера буфера нужно сравнить значения переменных key_read_requests и key_reads. Первая из них содержит общее количество запросов на чтение индекса, а вторая — количество запросов, для выполнения которых пришлось читать данные с диска.

Значение переменных можно посмотреть так:

MariaDB [(none)]> SHOW STATUS LIKE "key%";
+------------------------+-------------+
| Variable_name          | Value       |
+------------------------+-------------+
| Key_blocks_not_flushed | 0           |
| Key_blocks_unused      | 1469932     |
| Key_blocks_used        | 250318      |
| Key_blocks_warm        | 55690       |
| Key_read_requests      | 24417275945 |
| Key_reads              | 13131669    |
| Key_write_requests     | 38609729    |
| Key_writes             | 17155447    |
+------------------------+-------------+
8 rows in set (0.001 sec)

Чем меньше отношение значения key_reads к значению key_read_requests, тем лучше. Отношение 1:100 еще приемлемо, а вот отношение 1:10 уже очень плохое — нужна оптимизация размера key_buffer_size.

При выделении памяти учтите, что на сервере работают и другие сервисы, помимо СУБД, которым также требуется память.

Если вы проверяете настройки при помощи утилиты mysqltuner.pl, то при недостатке свободной памяти на сервере для реализации текущих настроек MariaDB вы получите такое предупреждение:

*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***

В этом случае нужно или добавить памяти на сервер, или выполнить дополнительную оптимизацию параметров, влияющих на потребление памяти.

Например, если вы не используете таблицы MyISAM, то для key_buffer_size можно установить минимальное значение 64 Кбайт или использовать значение по умолчанию.

Настройте innodb_buffer_pool_size

Если ваши базы данных содержат таблицы InnoDB, нужно установить буферный пул для кэширования и индексирования данных. Этот размер задается при помощи параметра innodb_buffer_pool_size.

По умолчанию для MariaDB размер буферного пула составляет всего 128 Мбайт, поэтому его нужно увеличить, например:

innodb_buffer_pool_size = 3G

Используйте здесь от четверти до половины общего объема памяти, установленной на сервере, но с учетом требований к памяти других сервисов.

Также установите размер файла журнала, равный четверти от размера innodb_buffer_pool_size:

innodb_log_file_size = 384M

Еще нужно установить значение параметра innodb_buffer_pool_instances, равное количеству гигабайт памяти , выделенных для буферного пула:

innodb_buffer_pool_instances = 3

Чтобы определить необходимый размер буферного пула innodb_buffer_pool_size, сравните количество запросов из буферного пула Innodb_buffer_pool_read_requests с количеством операций чтения с диска Innodb_buffer_pool_reads:

MariaDB [(none)]> SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_read_requests | 9560  |
+----------------------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 497   |
+--------------------------+-------+
1 row in set (0.00 sec)

В идеале количество чтений Innodb_buffer_pool_reads должно составлять не более 1% от общего количества запросов Innodb_buffer_pool_read_requests.

Также убедитесь, что в параметре Innodb_buffer_pool_wait_free находится нулевое значение. 

MariaDB [(none)]> SHOW STATUS LIKE 'Innodb_buffer_pool_wait_free';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Innodb_buffer_pool_wait_free | 0     |
+------------------------------+-------+

В противном случае размер буферного пула нужно увеличить.

Не увлекайтесь увеличением размеров буферов

Очень внимательно стоит отнестись к изменению размеров следующий буферов:

• read_buffer_size

• read_rnd_buffer_size

• join_buffer_size

Параметры read_buffer_size и read_rnd_buffer_size задают размеры буферов чтения и размер буфера случайного чтения, соответственно.

Параметр join_buffer_size задает размер буфера для операций объединения таблиц без использования индексов.

Следует учитывать, что буферы, задаваемые этими параметрами, создаются для каждого соединения с MariaDB. Максимальное количество соединений задается так:

max_connections = 300

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

Калькулятор для вычисления необходимого объема памяти

Для приблизительной оценки влияния настроек MySQL и MariaDB на потребление памяти можно использовать калькулятор MySQL Calculator (рис. 1).

Задавая значения настроек, вы можете наблюдать в поле Totals изменение объема памяти, необходимого для работы СУБД.

Настройте максимальное количество открытых файлов

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

По умолчанию MariaDB может открыть 16384 файла. Чтобы увеличить это количество, например, вдвое, отредактируйте файл /etc/systemd/system/mariadb.service.d/nofile.conf, добавив в него строки:

[Service]
LimitNOFILE=32768

Далее перезапустите сервис и проверьте результат:

# systemctl restart mariadb
# mysql -u root
MariaDB [(none)]> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 32768 |
+------------------+-------+
1 row in set (0.001 sec)

Полезные статьи про оптимизацию

В интернете есть немало статей, посвященных оптимизации параметров MySQL и MariaDB, вот, например, несколько ссылок, которые могут быть вам полезны:

• https://russianblogs.com/article/42371100465/

• https://g-soft.info/articles/2717/nastroyka-i-optimizatsiya-mysql-i-mariadb/

• https://mariadb.com/kb/en/myisam-system-variables/

• https://netpoint-dc.com/blog/mysql-chastie-oshibki-nastroiki/

И, конечно, читайте документацию MariaDB.

Учтите, что нет универсальной инструкции или конфигурации, подходящей для любого случая. Настройка параметров MariaDB (как и любой другой СУБД) должна выполняться индивидуально для каждой конкретной ситуации.

Мониторинг с помощью MySQL by Zabbix agent 2

Если на сервере, где установлена СУБД MySQL или MariaDB, есть Zabbix agent 2, то вы сможете очень просто организовать мониторинг СУБД. Используйте для этого плагин MySQL by Zabbix agent, описанный здесь.

Откройте страницу Host в Web-интерфейсе Zabbix для узла, на котором нужно контролировать работу MariaDB. Затем добавьте к хосту шаблон MySQL by Zabbix agent 2.

Чтобы этот шаблон заработал, нужно создать пользователя, с правами которого будет выполняться мониторинг, и настроить соответствующим образом макросы шаблона.

Добавление пользователя zbx_monitor

Откройте консоль MariaDB:

# mysql -u root

Так как утилита mysql запущена от имени root в Debian, пароль root для подключения к MariaDB указывать не нужно.

Создайте пользователя zbx_monitor и укажите необходимые права доступа:

MariaDB [(none)]> CREATE USER 'zbx_monitor'@localhost IDENTIFIED BY '********';
MariaDB [(none)]> GRANT REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zbx_monitor'@localhost;

Здесь вместо символов ‘********’ укажите пароль.

Настройка макросов

Создав пользователя, добавьте для хоста, где работает сервис MariaDB, три макроса шаблона MySQL by Zabbix agent 2. 

В макросе {$MYSQL.DSN} задайте адрес и порт для подключения к MariaDB как tcp://localhost:3306, а в макросах {$MYSQL.USER} и {$MYSQL.PASSWORD} задайте, соответственно, имя пользователя zbx_monitor и его пароль (рис. 2).

Такие настройки нужно сделать для всех хостов с контролируемым сервисом MariaDB, при этом имеет смысл использовать на разных хостах для пользователя zbx_monitor разные пароли. 

На рис. 3 приведены все макросы шаблона MySQL by Zabbix agent 2.

Многие из этих макросов используются в условиях триггеров. Вы можете их изменять, если требуется настроить какие-либо из условий. 

Метрики шаблона MySQL by Zabbix agent 2

В шаблоне MySQL by Zabbix agent 2 вы найдете очень большое количество метрик, с помощью которых можно оценить состояние СУБД, узнать размеры баз данных и получить другую важную информацию. Небольшая часть метрик (часть одной из трех страниц) представлена на рис. 4.

Для каждой метрики строится график, с помощью которого можно отслеживать изменения соответствующей метрики. Например, на рис. 5 показано количество команд SELECT, выполненных сервером за одну секунду.

Триггеры шаблона MySQL by Zabbix agent 2

На рис. 6 показаны триггеры, определенные в шаблоне MySQL by Zabbix agent 2. 

Обратите внимание, что в условиях срабатывания используются макросы, упомянутые выше.

Самый большой уровень серьезности High назначен триггеру MySQL: Service is down. Он срабатывает, когда сервис СУБД не работает, и нужно срочно разбираться, в чем проблема.

Средний уровень серьезности Average у триггеров MySQL: Refused connections и MySQL: Server has aborted connections. Если сработали эти триггеры, сервис не успевает обрабатывать все соединения от клиентов.

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

Триггеры с низким уровнем серьезности Information предупредят об изменении версии и перезапуске сервиса СУБД, а также о проблемах с получением данных от агента.

Мониторинг состояния MariaDB с помощью Zabbix поможет выявить ряд проблем, связанных с работоспособностью и производительностью сервиса СУБД. Однако для более детального анализа ситуации и для оптимизации параметров работы потребуется анализ метрик и текущих значений параметров работы MariaDB.

Автор: Александр Фролов

НЛО прилетело и оставило здесь промокод для читателей нашего блога:

— 15% на все тарифы VDS (кроме тарифа Прогрев) — HABRFIRSTVDS.