15 полезных советов по настройке и оптимизации производительности MySQL/MariaDB
MySQL — это мощная открытая система управления реляционными базами данных (RDBMS). Она была выпущена в 1995 году (ей 20 лет). Она использует язык структурированных запросов (SQL), который, вероятно, является самым популярным выбором для управления содержимым в базе данных. Последняя версия MySQL — 5.6.25, и она была выпущена 29 мая 2015 года.
Интересный факт о MySQL заключается в том, что название происходит от имени дочери Михаэля Видениуса (создателя MySQL) Майи. Хотя существует множество интересных фактов о MySQL, эта статья предназначена для того, чтобы показать вам некоторые полезные практики, которые помогут управлять вашим сервером MySQL.
В апреле 2009 года проект MySQL был куплен компанией Oracle. В результате был создан форк сообщества MySQL под названием MariaDB. Основной причиной создания форка было сохранить проект бесплатным в рамках Общей публичной лицензии.
Сегодня MySQL и MariaDB являются одной из самых (если не самой) часто используемых РСУБД для веб-приложений, таких как WordPress, Joomla, Magento и другие.
Эта статья покажет вам несколько основных, но полезных советов о том, как оптимизировать и тонко настроить производительность MySQL/MariaDB. Пожалуйста, имейте в виду, что эта статья предполагает, что у вас уже установлены MySQL или MariaDB. Если вы все еще не знаете, как установить их на вашу систему, вы можете следовать нашим подробным руководствам здесь:
- Установка LAMP на RHEL/CentOS 7
- Установка LAMP на Fedora 22
- Настройка LAMP на Ubuntu 15.04
- Установка MariaDB на Debian 8
- Установите MariaDB на Gentoo Linux
- Установите MariaDB на Arch Linux
style="color: red;">Важно: Прежде чем начать - не принимайте эти рекомендации слепо. Каждая установка MySQL уникальна и требует дополнительного обдумывания перед внесением каких-либо изменений.
Вещи, которые вам нужно знать:
- Файл конфигурации MySQL/MariaDB находится в
/etc/my.cnf. Каждый раз после изменения этого файла вам нужно будет перезапустить службу MySQL, чтобы новые изменения вступили в силу. - Для написания этой статьи была использована версия MySQL 5.6 в качестве шаблона.
1. Включите InnoDB файл для каждой таблицы
Сначала важно объяснить, что InnoDB является движком хранения. MySQL и MariaDB используют InnoDB в качестве движка хранения по умолчанию. Ранее MySQL сохранял таблицы базы данных и индексы в системном пространстве таблиц. Этот подход был предназначен для серверов, основная цель которых — обработка баз данных, а их диск для хранения не используется для других целей.
InnoDB обеспечивает более гибкий подход, и информация каждой базы данных хранится в файле данных .ibd. Каждый файл .ibd представляет собой отдельное табличное пространство. Таким образом, операции с базой данных, такие как “TRUNCATE”, могут выполняться быстрее, а также вы можете вернуть неиспользуемое пространство при удалении или усечении таблицы базы данных.
Еще одним преимуществом этой конфигурации является то, что вы можете хранить некоторые таблицы базы данных на отдельном устройстве хранения. Это может значительно улучшить входные/выходные нагрузки на ваши диски.
Параметр innodb_file_per_table включен по умолчанию в MySQL 5.6 и выше. Вы можете увидеть это в файле /etc/my.cnf. Директива выглядит следующим образом:
innodb_file_per_table=1
2. Хранение данных базы данных MySQL на отдельном разделе
style="color: red;">Примечание: Эта настройка работает только с MySQL, но не с MariaDB.
Иногда операции чтения/записи ОС могут замедлять работу вашего сервера MySQL, особенно если они находятся на одном жестком диске. Вместо этого я бы порекомендовал использовать отдельный жесткий диск (желательно SSD) для службы MySQL.
Чтобы завершить, вам нужно будет подключить новый диск к вашему компьютеру/серверу. Для целей этой статьи я буду исходить из того, что диск будет находиться по адресу /dev/sdb.
Следующий шаг — подготовить новый диск:
# fdisk /dev/sdb
Теперь нажмите «n», чтобы создать новый раздел. Далее нажмите «p», чтобы сделать новый раздел основным. После этого установите номер раздела от 1-4. Затем вы выберете размер раздела. Нажмите Enter здесь. На следующем шаге вам нужно будет настроить размер раздела.
Если вы хотите использовать весь диск, нажмите Enter еще раз. В противном случае вы можете вручную установить размер нового раздела. Когда будете готовы, нажмите «w», чтобы сохранить изменения. Теперь нам нужно создать файловую систему для нашего нового раздела. Это можно легко сделать с помощью:
# mkfs.ext4 /dev/sdb1
Теперь мы смонтируем наш новый раздел в папку. Я назвал свою папку «ssd» и создал её в корневом каталоге:
# mkdir /ssd/
Мы готовы смонтировать новый раздел, который мы только что создали, в новой папке:
# mount /dev/sdb1 /ssd/
Вы можете выполнить подключение при запуске, добавив следующую строку в файл /etc/fstab.
/dev/sdb1 /ssd ext3 defaults 0 0
Теперь вы готовы перенести MySQL на новый диск. Сначала остановите службу MySQL с помощью:
# service mysqld stop
Я бы рекомендовал вам также остановить Apache/nginx, чтобы предотвратить любые попытки записи в базы данных:
# service httpd stop
# service nginx stop
Теперь скопируйте весь каталог MySQL на новый диск:
# cp /var/lib/mysql /ssd/ -Rp
Это может занять некоторое время в зависимости от расположения ваших баз данных MySQL. После завершения этого процесса переименуйте каталог MySQL:
# mv /var/lib/mysql /var/lib/mysql-backup
Далее мы создадим символическую ссылку.
# ln -s /ssd/mysql /var/lib/mysql
Теперь вы готовы начать вашу работу с MySQL и веб-сервисом:
# service mysqld start
# service httpd start
# service nginx start
На этом этапе ваши базы данных MySQL будут доступны с нового диска.