Повышение производительности базы данных MySQL (MariaDB) на Linux VPS

В этой статье мы расскажем, как повысить производительность базы данных на VPS Linux. Под производительностью базы данных понимается то, насколько эффективно система баз данных может обрабатывать запросы, извлекать данные и выполнять транзакции. Все это — важные компоненты веб-сайта, работающего на сервере Linux. Вы можете повысить производительность базы данных, оптимизировав запросы, применив индексацию, используя кэширование и настроив базу данных. Кроме того, можно настроить конфигурацию веб-сервера, использовать CDN, обновить ресурсы сервера и пакеты сервера. Производительность базы данных иногда может влиять на содержимое веб-сайта и код веб-приложения.

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

Что такое система управления базами данных?

Система управления базами данных активно упорядочивает связанные данные. Система управления базами данных (СУБД) управляет этими данными для конкретных приложений. Это структура упорядоченных данных с связями между элементами. Она включает в себя текст, числа, изображения и многие другие форматы. Системы управления базами данных активно классифицируются по трём основным категориям. Система управления реляционными базами данных (СУБД), система управления базами данных документов (СУБДД) и система управления колоночными базами данных. В следующем абзаце мы расскажем о важности оптимизации системы управления базами данных.

Что такое оптимизация базы данных и почему она важна?

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

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

Как оптимизировать производительность базы данных на Linux VPS?

Есть несколько способов повысить производительность базы данных на Linux VPS. Многие серверные приложения не оптимизированы для достижения максимальной производительности по умолчанию. Есть ключевые моменты, которые необходимо оптимизировать. Обновление программного обеспечения на VPS и оптимизация производительности базы данных путем удаления неиспользуемых индексов — одни из самых распространенных задач. Также полезно использовать решения для кэширования баз данных, такие как Memcached или Varnish, внедрять CDN и оптимизировать код веб-приложений.

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

1. Обновите программное обеспечение на VPS

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

Обновление MySQL: обновление службы баз данных MySQL значительно повысит производительность — до 40 %. Это касается таких функций, как улучшенная индексация, оптимизация запросов, повышенная параллельная обработка и многое другое. На момент написания этой статьи последней версией MySQL была MySQL 8.4, которая представляет собой стабильную и надёжную версию с долгосрочной поддержкой.

Обновление PHP: обновление версии PHP может существенно повлиять на производительность MySQL несколькими способами, в том числе за счёт более быстрого выполнения скриптов, снижения нагрузки на процессор и более эффективного использования оперативной памяти. Кроме того, будут улучшены библиотеки MySQL, такие как MySQLi и PDO, что приведёт к оптимизации пула соединений и поддержке новых функций MySQL.

Обновление веб-сервера: обновление веб-сервера (Apache, Nginx или LiteSpeed) может косвенно повлиять на производительность MySQL, особенно на то, как веб-приложение взаимодействует с ней. Это позволяет эффективно обрабатывать большее количество одновременных запросов, сокращать время ожидания, минимизировать очереди запросов и время ожидания в MySQL, а также даёт другие преимущества.

Перед обновлением MySQL до более новой версии необходимо создать резервную копию файлов MySQL или, что ещё лучше, всего VPS. После обновления понизить версию MySQL будет невозможно. Если веб-приложение не работает с более новой версией, мы не сможем вернуться к предыдущей версии и потеряем файлы. Поэтому перед любыми действиями необходимо создать резервную копию.ВАЖНОTooltip content

2. Индексирование базы данных

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

Существует четыре различных типа индексов: B-дерево, растровый, хэш-индекс и индекс Gist:

• Индексы B-дерева — наиболее распространённый тип индексов, используемый для эффективного поиска, запросов диапазона и сортировки.
• Растровые индексы используются для столбцов с небольшим количеством уникальных значений.
• Хэш-индексы используются для поиска по равенству.
• Gist-индексы используются для обработки сложных типов данных и пространственных запросов.

При индексировании система использует больше памяти на вашем VPS, поэтому тщательно выбирайте, что индексировать. Во время индексирования база данных замедляет запросы на запись, поскольку активно обновляет индекс вместе с данными. Планируйте операции индексирования на периоды минимальной активности сайта или её отсутствия, чтобы обеспечить согласованность данных.

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

3. Использование подходящего механизма хранения

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

Механизмы хранения транзакций бывают следующими:

• InnoDB — это механизм хранения данных по умолчанию для MySQL. Самые современные приложения, такие как сайты электронной коммерции, блоги и приложения с частыми обновлениями, например WordPress, Magento, Drupal и т. д., используют механизм хранения данных InnoDB. Механизм хранения данных InnoDB поддерживает аварийное восстановление, блокировку на уровне строк, внешние ключи и транзакции.
• XtraDB — это улучшенная версия InnoDB, используемая в MariaDB, с аналогичными функциями транзакций и оптимизацией производительности.
• NDB — это кластерная система хранения данных, предназначенная для приложений с высокой доступностью, которым требуется надёжная репликация данных и аварийное переключение.
• К нетранзакционным механизмам хранения относятся следующие:
• MyISAM — это механизм хранения данных без транзакций, который используется для операций с интенсивным чтением, таких как полнотекстовый поиск, но с ограничениями в отношении целостности данных и параллелизма. MyISAM был механизмом хранения данных по умолчанию в версиях MySQL до 5.5

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

4. Внедрите кэширование запросов

Кэш запросов MySQL — это функция, которая сохраняет результаты запросов SELECT в памяти. Это значительно повышает производительность, поскольку система обрабатывает идентичные запросы напрямую из кэша, что позволяет избежать их повторного выполнения. Активное использование кэширования запросов резко повышает производительность базы данных, особенно при выполнении идентичных запросов сотни раз за короткий промежуток времени.

При выполнении запроса MySQL активно проверяет, есть ли этот запрос в кэше. Если MySQL находит совпадение, она возвращает результаты клиенту, не обращаясь к базе данных. Это называется попаданием в кэш. Если MySQL не находит совпадения, она помечает запрос как промахнувшийся, но всё равно выполняет его в базе данных и сохраняет в кэше для дальнейшего использования. Когда действия UPDATE, INSERT или DELETE изменяют таблицу, MySQL активно аннулирует кэш запросов, чтобы убедиться, что кэшированные результаты отражают последние данные.

Вот рекомендуемые инструменты для кэширования базы данных MySQL:

• Memcached — это высокопроизводительная система кэширования в оперативной памяти, которая используется для ускорения работы веб-сайтов с интенсивным использованием баз данных.
• Varnish cache также повышает производительность динамических и загруженных веб-сайтов.

5. Оптимизация MySQL и настройка конфигурации MySQL

В файле конфигурации MySQL (/etc/my.cnf) можно изменить несколько параметров, чтобы оптимизировать работу службы баз данных. В этом разделе мы расскажем о них подробнее.

query_cache_size — это значение конфигурации MySQL, которое указывает объём памяти, выделенный для кэша запросов. В кэше запросов хранятся результаты запросов, которые выполняются слишком часто. Для MySQL 5.7 или MariaDB его значение составляет 128–256 МБ. В последней версии MySQL 8.0 кэш запросов был полностью удалён, поскольку было установлено, что он неэффективен в средах с высокой степенью параллелизма и частым изменением данных.

max_connections — это параметр конфигурации, который определяет максимальное количество одновременных подключений, разрешённых сервером базы данных в любой момент времени. Значение max_connections можно рассчитать по следующей формуле:

max_connections = (Available RAM - Global Buffers) / Thread Buffers.  

# Пример:
# Доступная оперативная память — это оперативная память на вашем сервере.
# Глобальные буферы — это память, выделенная для глобальных буферов, таких как буфер ключей, кэш запросов и т. д.
# Потоковые буферы — это память, выделенная для каждого соединения, например sort_buffer_size, read_buffer_size и т. д.

Innodb_buffer_pool_size — это параметр конфигурации, который определяет объём памяти, выделяемый буферному пулу механизма хранения InnoDB. Обычно он составляет 70–80 % доступной оперативной памяти. Для сервера с 32 ГБ оперативной памяти значение innodb_buffer_pool_size будет равно примерно 26 ГБ.

innodb_redo_log_capacity определяет общий размер журнала повторов InnoDB. Журнал повторов — это важнейший компонент механизма хранения данных InnoDB, который действует как журнал транзакций и записывает все изменения, внесённые в базу данных.

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

Innodb_buffer_pool_instances — это параметр конфигурации MySQL, который определяет, на сколько экземпляров разделен буферный пул InnoDB. Этот параметр актуален, если innodb_buffer_pool_size превышает 1 ГБ. Это означает, что для реализации параметра innodb_buffer_pool_instances нам нужен сервер с оперативной памятью не менее 2 ГБ.

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

6. Обновите VPS

Важно отметить, что обновление ресурсов VPS и сервера не обязательно гарантирует постоянное повышение производительности базы данных. Обновлять VPS необходимо только в том случае, если после оптимизации сервера ресурсов не хватает. Если использование оперативной памяти и процессора превышает 80 % от лимита сервера, требуется обновление сервера. Если вы выбираете хостинг, важно найти хостинговую компанию, которая использует процессоры последних поколений, диски NVMe и оперативную память DDRX последнего поколения.

Вот и всё. Вы узнали о некоторых базовых способах повышения производительности базы данных на Linux VPS.

Если вам понравился этот пост о повышении производительности баз данных на Linux VPS, поделитесь им с друзьями или оставьте комментарий ниже.

Редактор: AndreyEx