Оптимизация производительности MySQL в виртуальных машинах Azure Linux

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

Сведения об оптимизации виртуальных машин Linux с помощью модели Resource Manager см. здесь.

Использование RAID в виртуальной машине Azure

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

С помощью RAID можно существенно увеличить пропускную способность ввода-вывода диска и улучшить время ответа операций ввода-вывода в Azure. Наши лабораторные тесты показали, что при удвоении количества дисков RAID (с 2 до 4, с 4 до 8 и т. д.) удваивается пропускная способность ввода-вывода дисков, а время ответа операций ввода-вывода уменьшается в среднем в два раза. Дополнительные сведения см. в приложении А.

Помимо дисковых операций ввода-вывода производительность MySQL увеличивается при увеличении уровня RAID. Дополнительные сведения см. в приложении Б.

Кроме того, вы можете обратить внимание на размер блоков. В целом чем больше размер блока, тем ниже нагрузка, особенно для объемных операций записи. Тем не менее, если размер блока слишком большой, это может привести к дополнительной нагрузке, и вы не сможете воспользоваться преимуществами RAID. Текущий размер блоков по умолчанию — 512 КБ. Он является оптимальным для большинства рабочих сред. Дополнительные сведения см. в приложении В.

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

В этой статье предполагается, что вы уже создали виртуальную машину Linux, а также установили и настроили MySQL. Дополнительную информацию о начале работы см. в статье «Как установить MySQL в Azure».

Настройка RAID в Azure

Ниже объясняется, как создать RAID в Azure с помощью классического портала Azure. RAID также можно настроить с помощью сценариев Windows PowerShell. В этом примере мы настроим RAID 0 с 4 дисками.

Шаг 1. Добавление диска данных в виртуальную машину

На странице «Виртуальные машины» классического портала Azure щелкните виртуальную машину, в которую требуется добавить диск данных. В этом примере виртуальная машина — mysqlnode1.

На странице виртуальной машины щелкните Панель мониторинга.

На панели задач щелкните Подключить.

Затем щелкните Присоединить пустой диск.

Для дисков данных для параметра Настройки кэша узла необходимо задать значение Нет.

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

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

sudo grep SCSI /var/log/dmesg

Шаг 2. Создание RAID с дополнительными дисками

Чтобы просмотреть подробные шаги по настройке RAID, перейдите к этой статье:

Настройка программного RAID-массива в Linux

Чтобы установить файловую систему XFS в ОС Debian, Ubuntu или Linux Mint, используйте следующую команду:

apt-get -y install xfsprogs  

Чтобы установить файловую систему XFS в ОС Fedora, CentOS или RHEL, используйте следующую команду:

yum -y install xfsprogs  xfsdump

Шаг 3. Настройка нового пути к хранилищу

Используйте следующую команду:

root@mysqlnode1:~# mkdir -p /RAID0/mysql

Шаг 4. Копирование исходных данных в новый путь к хранилищу

Используйте следующую команду:

root@mysqlnode1:~# cp -rp /var/lib/mysql/* /RAID0/mysql/

Шаг 5. Изменение разрешений таким образом, чтобы MySQL могла получить доступ (на чтение и запись) к диску данных

Используйте следующую команду:

root@mysqlnode1:~# chown -R mysql.mysql /RAID0/mysql && chmod -R 755 /RAID0/mysql

Настройка алгоритма планирования дисковых операций ввода-вывода

В Linux реализовано четыре типа алгоритмов планирования операций ввода-вывода:

• алгоритм NOOP (без операции);
• алгоритм крайнего срока (крайний срок);
• алгоритм организации полностью равноправных очередей (CFQ);
• алгоритм бюджетного периода (прогнозирование).

Вы можете выбрать различные планировщики операций ввода-вывода с различными сценариями для оптимизации производительности. В среде с полностью произвольным доступом не важно какой алгоритм используется для повышения производительности: CFQ или алгоритм крайнего срока. Обычно при использовании последнего рекомендуется задать среду базы данных MySQL для стабильности. Если выполняется множество последовательных операций ввода-вывода, метод CFQ может снизить производительность дисковых операций ввода-вывода.

Для SSD и другого оборудования лучше использовать алгоритм NOOP или алгоритм крайнего срока, чем планировщик по умолчанию, чтобы повысить производительность.

В ядре 2.5 алгоритм планирования операций ввода-вывода по умолчанию — алгоритм крайнего срока. Начиная с ядра 2.6.18, CFQ стал алгоритмом планирования операций ввода-вывода по умолчанию. Этот параметр можно указать при загрузке ядра или динамически изменить его при работе системы.

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

Для семейства дистрибутивов Debian:

Шаг 1. Просмотр текущего планировщика операций ввода-вывода

Используйте следующую команду:

root@mysqlnode1:~# cat /sys/block/sda/queue/scheduler

Вы увидите следующий результат, который указывает тип текущего планировщика.

noop [deadline] cfq

Шаг 2. Изменение текущего устройства (/dev/sda) алгоритма планирования операций ввода-вывода

Используйте следующие команды:

azureuser@mysqlnode1:~$ sudo su -
root@mysqlnode1:~# echo "noop" >/sys/block/sda/queue/scheduler
root@mysqlnode1:~# sed -i 's/GRUB_CMDLINE_LINUX=""/GRUB_CMDLINE_LINUX_DEFAULT="quiet splash elevator=noop"/g' /etc/default/grub
root@mysqlnode1:~# update-grub

Вы увидите следующий результат, означающий, что файл grub.cfg успешно перестроен и планировщик по умолчанию обновлен до алгоритма NOOP.

Generating grub configuration file ...
Found linux image: /boot/vmlinuz-3.13.0-34-generic
Found initrd image: /boot/initrd.img-3.13.0-34-generic
Found linux image: /boot/vmlinuz-3.13.0-32-generic
Found initrd image: /boot/initrd.img-3.13.0-32-generic
Found memtest86+ image: /memtest86+.elf
Found memtest86+ image: /memtest86+.bin
done

Для семейства дистрибутивов Redhat вам необходимо выполнить только следующую команду:

echo 'echo noop >/sys/block/sda/queue/scheduler' >> /etc/rc.local

Настройка параметров операций системного файла

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

Для этого следует изменить файл конфигурации файловой системы /etc/fstab и добавить параметр noatime .

Например, измените файл vim /etc/fstab, добавив параметр noatime, как показано ниже.

# CLOUD_IMG: This file was created/modified by the Cloud Image build process
UUID=3cc98c06-d649-432d-81df-6dcd2a584d41       /        ext4   defaults,discard        0 0
#Add the “noatime” option below to disable atime logging
UUID="431b1e78-8226-43ec-9460-514a9adf060e"     /RAID0   xfs   defaults,nobootwait, noatime 0 0
/dev/sdb1       /mnt    auto    defaults,nobootwait,comment=cloudconfig 0       2

Затем подключите файловую систему заново с помощью следующей команды:

mount -o remount /RAID0

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

Пример «до»:

Пример «после»:

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

MySQL — база данных с высокой степенью параллелизма. Количество параллельных дескрипторов по умолчанию для Linux — 1024. Однако их не всегда достаточно. Выполните следующие шаги, чтобы увеличить максимальное количество параллельных дескрипторов системы для поддержки высокой степени параллелизма MySQL.

Шаг 1. Изменение файла limits.conf

Добавьте следующие четыре строки в файл /etc/security/limits.conf, чтобы увеличить максимально допустимое количество параллельных дескрипторов. Обратите внимание, что максимальное количество, поддерживаемое системой, — 65536.

* soft nofile 65536
* hard nofile 65536
* soft nproc 65536
* hard nproc 65536

Шаг 2. Обновление операционной системы для новых ограничений

Выполните следующие команды:

ulimit -SHn 65536
ulimit -SHu 65536

Шаг 3. Обеспечение обновления ограничений при загрузке

Вставьте следующие команды, выполняемые при запуске, в файл /etc/rc.local, чтобы они выполнялись при каждой загрузке.

echo “ulimit -SHn 65536” >>/etc/rc.local
echo “ulimit -SHu 65536” >>/etc/rc.local

Оптимизация базы данных MySQL

Вы можете использовать стратегию настройки производительности на локальной машине для настройки MySQL в Azure.

Основные правила оптимизации операций ввода-вывода:

• увеличьте размер кэша;
• уменьшите время ответа при выполнении операций ввода-вывода.

Чтобы оптимизировать параметры сервера MySQL, можно обновить файл my.cnf, который является файлом конфигурации по умолчанию для серверных и клиентских компьютеров.

Следующие элементы конфигурации являются основными факторами, влияющими на производительность MySQL.

• innodb_buffer_pool_size. Буферный пул содержит буферизованные данные и индекс. Обычно для этого параметра задается значение, равное 70 % объема физической памяти.
• innodb_log_file_size. Это размер журнала повторяемых операций. Журналы повторяемых операций используются, чтобы обеспечить высокую скорость, надежность и возможность восстановления операций записи после сбоя. Для этого параметра задается значение 512 МБ, что обеспечит достаточно места для операций записи журнала.
• max_connections. Иногда приложения не закрывают подключения должным образом. Если установить большее значение, у сервера будет больше времени для очистки неактивных подключений. Максимальное количество подключений — 10 000, но рекомендуемое максимальное количество — 5000.
• Innodb_file_per_table. Этот параметр позволяет включить или отключить возможность InnoDB сохранять таблицы в отдельных файлах. Если включить параметр, то это обеспечит эффективное применение нескольких дополнительных операций администрирования. С точки зрения производительности это позволит ускорить передачу табличного пространства и оптимизировать производительность управления мусором. Поэтому рекомендуемое значение для этого параметра — ON.
  Начиная с версии MySQL 5.6, значение по умолчанию — ON. Таким образом, не требуется ничего делать. Для других версий, более ранних, чем 5.6, значение по умолчанию — OFF. Для этого параметра требуется установить значение ON. И это необходимо сделать перед загрузкой данных, так как этот параметр влияет только на новые таблицы.
• innodb_flush_log_at_trx_commit. Значение по умолчанию — 1 со значением для области от 0 до 2. Значение по умолчанию — наиболее подходящий вариант для автономной базы данных MySQL. При установке значения 2 достигается наибольший уровень целостности данных. Это значение подходит для главного узла в кластере MySQL. При установке значения 0 допускается потеря данных, которая может повлиять на надежность и в некоторых случаях увеличить производительность. Это значение подходит для подчиненного узла в кластере MySQL.
• Innodb_log_buffer_size. Буфер журнала позволяет выполнять транзакции без необходимости записи журнала на диск до их фиксации. Однако при наличии большого двоичного объекта или текстового поля объем кэша будет очень быстро заполняться и дисковые операции ввода-вывода будут инициироваться очень часто. Лучше увеличить размер буфера, если для переменной состояния Innodb_log_waits установлено значение, отличное от 0.
• query_cache_size. Лучше всего отключить его с самого начала. Установите для параметра Query_cache_size значение 0 (теперь это значение по умолчанию в MySQL 5.6) и используйте другие методы для ускорения запросов.

Сравнение уровней производительности после оптимизации приведено в приложении Г .

Включение журнала медленных запросов MySQL для анализа узкого места производительности

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

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

Шаг 1. Изменение файла my.cnf путем добавления следующих строк в конец

long_query_time = 2
slow_query_log = 1
slow_query_log_file = /RAID0/mysql/mysql-slow.log

Шаг 2. Перезапуск сервера MySQL

service  mysql  restart

Шаг 3. Проверка работы параметра с помощью команды show

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

Приложение

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

Приложение А.
Производительность диска (количество операций ввода-вывода в секунду) с разными уровнями RAID

Команды тестирования:

fio -filename=/path/test -iodepth=64 -ioengine=libaio -direct=1 -rw=randwrite -bs=4k -size=5G -numjobs=64 -runtime=30 -group_reporting -name=test-randwrite

AZURE.NOTE. В рабочей нагрузке этого теста используется 64 потока в попытке достичь верхнее предельное значение RAID.

Приложение Б.
Сравнение производительности (пропускной способности) MySQL с разными уровнями RAID
(Файловая система XFS)

Команды тестирования:

mysqlslap -p0ps.123 --concurrency=2 --iterations=1 --number-int-cols=10 --number-char-cols=10 -a --auto-generate-sql-guid-primary --number-of-queries=10000 --auto-generate-sql-load-type=write –engine=innodb

Сравнение производительности (OLTP) MySQL с разными уровнями RAID

Команды тестирования:

time sysbench --test=oltp --db-driver=mysql --mysql-user=root --mysql-password=0ps.123  --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-socket=/var/run/mysqld/mysqld.sock --mysql-db=test --oltp-table-size=1000000 prepare

Приложение В.
Сравнение производительности диска (количества операций ввода-вывода в секунду) для блоков разного размера
(Файловая система XFS)

Команды тестирования:

fio -filename=/path/test -iodepth=64 -ioengine=libaio -direct=1 -rw=randwrite -bs=4k -size=30G -numjobs=64 -runtime=30 -group_reporting -name=test-randwrite
fio -filename=/path/test -iodepth=64 -ioengine=libaio -direct=1 -rw=randwrite -bs=4k -size=1G -numjobs=64 -runtime=30 -group_reporting -name=test-randwrite  

Обратите внимание, что для этого теста использовался файл размером 30 ГБ и 1 ГБ соответственно в файловой системе XFS с RAID 0 (4 диска).

Приложение Г.
Сравнение производительности (пропускной способности) MySQL до и после оптимизации
(Файловая система XFS)

Команды тестирования:

mysqlslap -p0ps.123 --concurrency=2 --iterations=1 --number-int-cols=10 --number-char-cols=10 -a --auto-generate-sql-guid-primary --number-of-queries=10000 --auto-generate-sql-load-type=write –engine=innodb,misam

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

Параметры	значение по умолчанию	Оптимизация
innodb_buffer_pool_size	None	7G
innodb_log_file_size	5M	512M
max_connections	100	5000
innodb_file_per_table	0	1
innodb_flush_log_at_trx_commit	1	2
innodb_log_buffer_size	8M	128M
query_cache_size	16M	0

Дополнительную информацию о параметрах конфигурации для оптимизации см. в официальных указаниях MySQL.

http://dev.mysql.com/doc/refman/5.6/en/innodb-configuration.html

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_method

Тестовая среда

Оборудование	Сведения
ЦП	AMD Opteron(tm), процессор 4171 HE, 4 ядра
Память	14 ГБ
Диск	10 ГБ на диск
ОС	Ubuntu 14.04.1 LTS

Источник