Мониторинг работы баз данных MySQL/MariaDB с использованием Percona на сервере под управлением Ubuntu

На этом занятии вы узнаете, как установить PMM (Percona Monitoring and Management) на Ubuntu, чтобы отслеживать производительность баз данных MySQL и MariaDB.

Функционал Percona Monitoring and Management (PMM)

Percona — это компания, которая предлагает решения для баз данных с открытым исходным кодом. Основные функции PMM:

• Программа является бесплатной и с открытым исходным кодом. PMM функционирует по модели клиент-сервер, и программное обеспечение как клиента, так и сервера устанавливается на ваше оборудование.
• Поддерживает базы данных MySQL/MariaDB, PostgreSQL, MongoDB и ProxySQL, что предоставляет возможность отслеживать все ваши системы управления базами данных с открытым исходным кодом в едином интерфейсе.
• Поддерживает хранилища InnoDB, XtraDB и MyRocks для MySQL/MariaDB.
• Поддерживает хранилища данных WiredTiger, MMAPv1, InMemory и RocksDB для MongoDB.
• Анализ запросов и отслеживание показателей.
• Аудит на предмет выявления распространенных уязвимостей в системах управления базами данных.
• Легко распознает неожиданные запросы к базе данных с целью улучшения безопасности информации.
• Поддерживает кластер Percona XtraDB.

Для применения PMM требуется установить серверный компонент на главный сервер, после чего необходимо установить клиентский компонент на те хосты MySQL/MariaDB, которые вы хотите отслеживать. Клиент будет передавать информацию о производительности на сервер. Если у вас имеется только один хост MySQL/MariaDB, вы можете разместить оба компонента на этом хосте.

Установите Docker на сервере с Ubuntu 22.04 или 20.04.

Серверная часть PMM доступна в формате образа Docker, поэтому необходимо установить Docker.

Docker доступен в репозитории программного обеспечения для Ubuntu. Тем не менее, для установки самой свежей версии рекомендуется использовать APT-репозиторий от Docker. Чтобы добавить репозиторий Docker на ваш сервер Ubuntu, выполните следующую команду.

echo "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release - cs) stable" | sudo tee /etc/apt/sources. list. d/docker. list

Затем выполните следующую команду для добавления ключа GPG Docker в вашу систему Ubuntu, чтобы APT мог удостоверяться в подлинности пакетов при их установке.

curl - fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -

Так как данный репозиторий применяет соединение HTTPS, что я настоятельно рекомендую для всех программных репозиториев, необходимо установить пакеты apt-transport-https и ca-certificates.

sudo apt install apt-transport-https ca-certificates

В завершение обновите индекс пакетов на вашей системе Ubuntu и установите docker-ce (Community Edition Docker).

sudo apt update sudo apt install docker-ce

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

systemctl status docker

systemctl status docker

.» width=»851″ height=»264″ />

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

sudo systemctl start docker

Активируйте автозапуск при старте операционной системы.

sudo systemctl enable docker

Убедитесь, что у вас установлена актуальная версия Docker.

docker - v

Docker version 20.10.18, build b40c2f6

Инсталляция сервера PMM.

Создайте Docker-том данных для сервера PMM.

sudo docker create - v /srv --name pmm-data percona/pmm-server:latest /bin/true

Запустите Docker-контейнер с сервером PMM.

sudo docker run - d - p 8000:80 - p 8443:443 --volumes-from pmm-data --name pmm-server --restart always percona/pmm-server:latest

Покажите перечень активных контейнеров Docker.

sudo docker ps

Необходимо убедиться, что контейнер pmm-server работает.

CONTAINER ID IMAGE COMMAND CREATED STATUS c9199659fdd9 percona/pmm-server:2 "/opt/entrypoint. sh" 9 seconds ago Up 8 seconds (healthy)

Теперь доступ к веб-интерфейсу PMM можно получить по адресу https://your-server-ip:8443. Поскольку по умолчанию используется самоподписанный TLS-сертификат, в браузере потребуется добавить исключение безопасности.

• В браузере Firefox выберите «Дополнительно», затем нажмите «Принять риск и продолжить».
• В браузере Google Chrome выберите «Дополнительно» и нажмите «Перейти на your-server-ip (небезопасный доступ)».

Стандартные имя пользователя и пароль: admin : admin. После первого входа необходимо задать новый пароль. Обратите внимание, что пароль не должен содержать символ /, иначе клиент PMM не сможет подключиться к серверу PMM.

Если брандмауэр UFW активирован, необходимо открыть TCP-порт 8443 для доступа к панели управления PMM.

sudo ufw allow 8443/tcp

В панели управления по умолчанию присутствует хост, которым является контейнер Docker pmm-server.

Установите PMM Client на сервере с MySQL/MariaDB.

У клиентов PMM есть собственный репозиторий пакетов Deb для систем на базе Debian/Ubuntu.

Скачайте репозиторий PMM.

wget https://repo. percona.com/apt/percona-release_latest.$(lsb_release - sc)_all. deb

Установите программный пакет.

sudo apt install ./percona-release_latest*.deb

Репозиторий Percona теперь добавлен. Пожалуйста, обновите локальный индекс пакетов и установите клиент PMM.

sudo apt update sudo apt install pmm2-client

Агент PMM начнет работу автоматически, и это можно проверить с помощью:

systemctl status pmm-agent

Если он активен, вы можете инициировать его с помощью команды:

sudo systemctl enable pmm-agent --now

После этого подключите клиента к серверу, используя следующую команду.

sudo pmm-admin config --server-insecure-tls --server-url=https://admin:admin_assword@your-server-ip:8443

Проверка состояния локального агента pmm-agent. pmm-agent запущен. Регистрация pmm-agent на сервере PMM. Зарегистрирован. Конфигурационный файл /usr/local/percona/pmm2/config/pmm-agent. yaml обновлен. Перезагрузка конфигурации pmm-agent. Конфигурация перезагружена. Проверка состояния локального агента pmm-agent. pmm-agent запущен.

Перезагрузите панель управления сервером PMM. Хост MySQL/MariaDB будет расположен в нижней части страницы.

Если вы хотите настроить агента PMM для подключения к другому серверу PMM, просто повторите команду, указанную выше. Не забудьте при этом изменить IP-адрес и пароль.

Теперь необходимо сконфигурировать клиент PMM для отслеживания базы данных MySQL/MariaDB. Подключитесь к консоли MySQL/MariaDB.

sudo mysql - u root

mysql - u root - p

Создайте учетную запись PMM для осуществления мониторинга.

CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'secret_password' WITH MAX_USER_CONNECTIONS 10;

GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm'@'localhost';

Удалите данные из таблицы привилегий.

FLUSH PRIVILEGES;

EXIT;

Используйте следующую команду для интеграции службы MySQL/MariaDB в PMM.

sudo pmm-admin add mysql --username=pmm --password=secret_password --query-source=perfschema

Служба MySQL добавлена. ID службы: /service_id/8597086d-828e-47aa-a74f-9c86918af208 Название службы: linux16.ru-mysql Сбор статистики по таблицам отключен (лимит — 1000, фактическое количество таблиц — 2600).

Наблюдение за базой данных MySQL/MariaDB

Если вы заметили медленную работу вашей базы данных MySQL/MariaDB, начните с анализа показателя QPS (запросы в секунду). Эта метрика позволяет оценить нагрузку на сервер базы данных. На следующем скриншоте видно, что один из моих серверов MariaDB испытывает значительную нагрузку, достигающую примерно 700 запросов в секунду.

После этого вам потребуется воспользоваться Query Analytics (QAN) для анализа указанных SQL-запросов.

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

Какое оборудование лучше всего подходит для MySQL/MariaDB?

• Если у вас объемная база данных и требуется расширить приложение, убедитесь, что у вас как минимум 8 ядер процессора.
• Применяйте NVMe SSD.
• Применяйте RAID для повышения эффективности операций ввода-вывода.
• Больше оперативной памяти означает, что в ней может храниться больше данных из базы, что снижает необходимость обращения к диску.
• Использование swap-пространства не рекомендуется, так как это значительно снизит производительность базы данных. Тем не менее, Linux прибегает к swap по ряду причин. Чтобы узнать, как настроить и отключить swap-пространство, вы можете ознакомиться со следующей статьей.

Создание структуры базы данных

После того как оборудование выбрано, следующим этапом для достижения высокой производительности MySQL/MariaDB становится проектирование базы данных, что входит в обязанности разработчика. Необходимо учесть следующие аспекты:

• Разработайте структуру базы данных с акцентом на максимальную скорость извлечения данных.
• Выбор подходящих типов данных: Подбирайте оптимальные типы данных для эффективного хранения на жестком диске.

Если вы работаете с популярным веб-приложением, таким как WordPress/WooCommerce или Magento, структура базы данных уже разработана.

Легкая оптимизация работы MySQL/MariaDB.

Конфигурация InnoDB в моем файле /etc/mysql/mariadb.conf.d/50-server. cnf выглядит следующим образом. Это базовая настройка для повышения производительности.

innodb_buffer_pool_size = 2048M innodb_buffer_pool_dump_at_shutdown = ON innodb_buffer_pool_load_at_startup = ON innodb_log_file_size = 512M innodb_log_buffer_size = 8M #Улучшение производительности дискового ввода-вывода innodb_file_per_table = 1 innodb_open_files = 400 innodb_io_capacity = 400 innodb_flush_method = O_DIRECT innodb_read_io_threads = 64 innodb_write_io_threads = 64 innodb_buffer_pool_instances = 3

• Размер буферного пула InnoDB рекомендуется устанавливать на уровне не менее половины объема оперативной памяти или 70% от свободной памяти. Важно учитывать, что использование swap нежелательно. Буферный пул InnoDB представляет собой временное хранилище данных в памяти для MySQL/MariaDB, поэтому при наличии достаточного объема ОЗУ этот параметр следует увеличить. Оптимальный вариант — хранить все данные в оперативной памяти, чтобы минимизировать обращения к диску.
• Размер журнала InnoDB рекомендуется установить на уровне 25% от объема буферного пула.
• InnoDB поддерживает использование прямого ввода-вывода на платформах Linux и FreeBSD.
• Установите максимальное число потоков для операций чтения и записи ввода-вывода на уровне 64.
• Настройте MariaDB для использования трех экземпляров буферного пула InnoDB. Число экземпляров следует устанавливать в зависимости от количества процессорных ядер, умноженного на число потоков на каждое ядро в вашей системе.

После внесения изменений перезагрузите MariaDB.

sudo systemctl restart mariadb

Применение mysqltuner

mysqltuner — это утилита для оптимизации производительности MySQL и MariaDB. Вы можете установить её через репозиторий Ubuntu.

sudo apt install mysqltuner

Конечно, вот перефразированный текст:

Затем просто выполняйте это.

sudo mysqltuner

Он выполнит анализ вашего сервера баз данных и предоставит советы по оптимизации. Например, mysqltuner выявил, что объем моих данных составляет 23,5 ГБ, и порекомендовал увеличить innodb_buffer_pool_size до этого значения.

Применение кэша Redis

Redis используется для кэширования запросов MySQL. (Замечание: кэш запросов был удалён в MySQL версии 8.) Он сохраняет результаты запросов в оперативной памяти, что может заметно повысить производительность системы.

Установите сервер Redis и расширение для PHP.

sudo apt install redis-server php-redis

Если вы работаете с PHP версии 7.4, вам потребуется установить пакет php7.4-redis.

sudo apt install php7.4-redis

Если вы работаете с PHP8.1, необходимо установить php8.1-redis.

sudo apt install php8.1-redis

После установки Redis должен запуститься самостоятельно. Убедитесь в его статусе:

systemctl status redis

* redis-server.service - Расширенное хранилище ключ-значение Загрузка: загружен (/lib/systemd/system/redis-server.service; активирован; предустановленный поставщик: активирован) Статус:в действии (функционирует) с Сб 2022-07-30 10:54:02 SAST; 1 месяц 11 дней назад Документация: http://redis. io/documentation, man:redis-server(1) Главный PID: 1176 (redis-server) Задачи: 4 (лимит: 38329) Память: 3.0M CGroup: /system. slice/redis-server.service `-1176 /usr/bin/redis-server 127.0.0.1:6379

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

sudo systemctl enable --now redis-server

Пожалуйста, проверьте текущее состояние интеграции Redis с PHP.

php --ri redis

redis Поддержка Redis => включено Версия Redis => 5.3.7 Версия Redis Sentinel => 0.1 Доступные сериализаторы => php, json, igbinary Доступные методы сжатия => lzf, zstd, lz4

Мы замечаем, что расширение активировано. Теперь, когда задний фон настроен, давайте настроим интерфейс WordPress. Необходимо установить и настроить плагин Redis Object Cache для WordPress.

Если на вашем сайте установлены плагины W3 Total Cache, WP Super Cache или другие кэширующие плагины для WordPress, сначала удалите их. После этого установите плагин Redis Object Cache от Тилля Крусса и активируйте его.

Затем перейдите в Настройки >Redis. Активируйте кэширование объектов.

Вы должны убедиться, что связь налажена.

Вы также можете ввести следующую команду на вашем сервере Ubuntu.

redis-cli monitor

Вы будете в состоянии следить за процессом работы Redis Cache в реальном времени, что свидетельствует о корректной работе кэша Redis на вашем сайте WordPress.

monitor

в Redis CLI» width=»725″ height=»343″ />

Если вы получили данное сообщение об ошибке, это указывает на то, что сервер Redis запрашивает пароль для получения доступа.

NOAUTH Authentication required

Поскольку Redis функционирует исключительно на локальном хосте, я не вижу необходимости в использовании пароля, поэтому можно отключить аутентификацию. (Если вы применяете брандмауэр UFW для ограничения публичного доступа к порту 6379, это также делает аутентификацию по паролю излишней.)

sudo nano /etc/redis/redis.conf

Найдите строку requirepass и добавьте к ней комментарий.

#requirepass secret_password

Сохраните файл и закройте его. Затем перезапустите Redis.

sudo systemctl restart redis-server

В версии WordPress 6.1 реализован кэш WP-Query, что существенно повышает эффективность работы с базой данных по умолчанию.

Завершение

Надеюсь, что эта статья была полезна для настройки PMM для отслеживания производительности MySQL/MariaDB. Если вас интересует мониторинг работы Nginx и PHP-FPM, ознакомьтесь с следующей статьей:

• Контроль эффективности LEMP-стека с использованием Nginx Amplify на Ubuntu 20.04/18.04.

Вы можете рассмотреть возможность настройки кеша Nginx FastCGI, чтобы сократить время отклика сервера.

• Конфигурация кеша FastCGI в Nginx для снижения времени отклика сервера WordPress.