Поиск по сайту:

15 полезных советов по настройке и оптимизации производительности MySQL/MariaDB

MySQL — это мощная система управления реляционными базами данных с открытым исходным кодом или, сокращенно, СУБД. Он был выпущен в далеком 1995 году (20 лет). Он использует Язык структурированных запросов, который, вероятно, является наиболее популярным выбором для управления содержимым в базе данных. Последняя версия MySQL — 5.6.25, выпущенная 29 мая 2015 г..

Интересным фактом о MySQL является тот факт, что имя происходит от дочери Майкла Видениуса (создателя MySQL) My. Несмотря на, существует множество интересных фактов о MySQL, цель этой статьи — показать вам некоторые полезные приемы, которые помогут вам управлять своим сервером MySQL.

В апреле 2009 проект MySQL был куплен Oracle. В результате была создана ветвь сообщества MySQL под названием MariaDB. Основной причиной создания форка было желание сохранить проект свободным под лицензией General Public License.

Сегодня MySQL и MariaDB являются одними из наиболее (если не самыми) часто используемыми СУБД, используемыми для таких веб-приложений, как WordPress, Joomla, Magento и другие.

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

  1. Установка LAMP на RHEL/CentOS 7
  2. Установка LAMP в Fedora 22
  3. Настройка LAMP в Ubuntu 15.04
  4. Установка MariaDB на Debian 8
  5. Установите MariaDB в Gentoo Linux.
  6. Установите MariaDB в Arch Linux

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

Что вам нужно знать:

  1. Файл конфигурации MySQL/MariaDB находится в /etc/my.cnf. Каждый раз, когда вы изменяете этот файл, вам нужно будет перезапустить службу MySQL, чтобы новые изменения вступили в силу.
  2. При написании этой статьи в качестве шаблона использовался 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 в отдельном разделе.

Примечание. Эта настройка работает только с 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 будут доступны с нового диска.