MySQL: выбор запросов в 10 раз медленнее на виртуальной машине Azure по сравнению с локальной виртуальной машиной

Мы работаем над проектом по переносу базы данных MySQL с локального сервера Linux на виртуальную машину Windows в Azure (IaaS). (Есть определенная причина, по которой мы выбрали вариант IaaS вместо предложения Azure MySQL PaaS).

После миграции мы видим, что запросы к базе данных MySQL значительно медленнее (примерно в 10 раз) на новом сервере. Виртуальная машина настроена на 64 ЦП и 256 ГБ ОЗУ (у локальной ВМ было 48 ЦП и 256 ГБ ОЗУ).

Все таблицы в базе данных используют механизм InnoDB. Мы много читали о медленных запросах с таблицами InnoDB, и большая часть из них, похоже, указывает на размер innodb_buffer_pool_size, который я уже настроил на 185 ГБ (примерно 70% от общего объема ОЗУ). Мы также попытались внести ряд других изменений в мой.ini конфигурация как

key_buffer_size = 20 МБ
innodb_io_capacity = 2000
query_cache_size = 0
query_cache_type = 0
увеличение thread_cache_size
innodb_read_io_threads
innodb_write_io_threads

И т. д. Но ничего не помогает, с производительностью запросов.

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

• Может ли кто-нибудь предложить какие-либо другие варианты, которые мы могли бы попытаться улучшить производительность?

Еще пара моментов.

• Мы замечаем, что хотя некоторые запросы занимают 30+ минут для запуска (похоже, они работают на локальном сервере всего за 5 минут), загрузка ЦП на виртуальной машине остается очень низкой (менее 10%).Есть ли какая-то настройка, такая как innodb_buffer_pool_size`, чтобы выделить некоторое количество ЦП серверу MySQL?

Как я упоминал ранее, локальная виртуальная машина основана на Linux, а виртуальная машина Azure работает на Windows — может ли это быть проблемой? Я не могу найти каких-либо окончательных доказательств того, что MySQL в Windows вызовет такое серьезное снижение производительности.

Моя полная конфигурация my.ini приведена ниже:

# Другие значения настройки по умолчанию
# Файл конфигурации экземпляра сервера MySQL
# --------------------------------------------- ---------------------
# Генерируется Мастером настройки экземпляра сервера MySQL
#
#
# Инструкция по установке
# --------------------------------------------- ---------------------
#
# В Linux вы можете скопировать этот файл в /etc/my.cnf, чтобы установить глобальные параметры,
# mysql-data-dir/my.cnf для установки специфичных для сервера параметров
# (@localstatedir@ для этой установки) или в
# ~/.my.cnf для установки пользовательских параметров.
#
# В Windows вы должны хранить этот файл в каталоге установки 
# вашего сервера (например, C:\Program Files\MySQL\MySQL Server X.Y). К
# убедитесь, что сервер читает файл конфигурации, используйте параметр запуска 
# "--файл по умолчанию". 
#
# Чтобы запустить сервер из командной строки, выполните это в 
# оболочка командной строки, например.
# mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
#
# Чтобы установить сервер как службу Windows вручную, выполните это в 
# оболочка командной строки, например.
# mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
#
# А затем выполните это в оболочке командной строки, чтобы запустить сервер, например
# чистый старт MySQLXY
#
#
# Рекомендации по редактированию этого файла
# --------------------------------------------- ---------------------
#
# В этом файле вы можете использовать все длинные опции, которые поддерживает программа.
# Если вы хотите узнать, какие параметры поддерживает программа, запустите программу
# с опцией "--help".
#
# Более подробная информация об отдельных опциях также может быть
# найдено в руководстве.
#
# Советы по изменению настроек см.
# https://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
#
#
# КЛИЕНТСКИЙ РАЗДЕЛ
# --------------------------------------------- ---------------------
#
# Следующие параметры будут считываться клиентскими приложениями MySQL.
# Обратите внимание, что только клиентские приложения, поставляемые MySQL, гарантированно
# чтобы прочитать этот раздел. Если вы хотите, чтобы ваша собственная клиентская программа MySQL
# учитывайте эти значения, вам нужно указать это как опцию во время
# Инициализация клиентской библиотеки MySQL.
#
[клиент]

# труба=

# сокет=MYSQL

порт=3306

[mysql]
нет сигнала

# набор символов по умолчанию =

# СЕРВЕРНАЯ СЕКЦИЯ
# --------------------------------------------- ---------------------
#
# Следующие опции будут прочитаны сервером MySQL. Убедись в том, что
# вы правильно установили сервер (см. выше), поэтому он читает это 
# файл.
#
# тип_сервера=1
[mysqld]
plugin-load-add=validate_password.dll
подтвердить пароль = FORCE_PLUS_PERMANENT
# Следующие три параметра являются взаимоисключающими для SERVER_PORT ниже.
# пропуск сети
# включить именованный канал
# Общая память

# общая-память-базовое-имя = MYSQL

# Труба, которую будет использовать сервер MySQL
# сокет=MYSQL

# Порт TCP/IP, который сервер MySQL будет прослушивать
порт=3306

# Путь к каталогу установки. Все пути обычно разрешаются относительно этого.
#basedir="C:/Program Files/MySQL/MySQL Server 5.7/"

# Путь к корню базы данных
каталог данных = F:\MySQL\Данные

# Набор символов по умолчанию, который будет использоваться при создании новой схемы или таблицы.
# создан, но набор символов не определен
# сервер набора символов =

# Механизм хранения по умолчанию, который будет использоваться при создании новых таблиц, когда
механизм хранения по умолчанию = INNODB

# Установить режим SQL на строгий
sql-mode = "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION"

# Общая и медленная регистрация.
лог-выход = ФАЙЛ

общий журнал = 0

general_log_file="ZEUWPJIRA01.log"

журнал медленных запросов = 1

slow_query_log_file="ZEUWPJIRA01-slow.log"

long_query_time = 10

# Регистрация ошибок.
лог-ошибка = "ZEUWPJIRA01.err"

# ***** Группа, связанная с репликацией *****
# Задает базовое имя для двоичных файлов журнала. С бинарным журналированием
# включено, сервер регистрирует все операторы, которые изменяют данные в двоичном формате
# журнал, который используется для резервного копирования и репликации.
# лог-бин

# ***** Группа, связанная с репликацией *****
# Указывает идентификатор сервера. Для серверов, которые используются в топологии репликации,
# вы должны указать уникальный идентификатор сервера для каждого сервера репликации в
# диапазон от 1 до 2^32 ≈ 1. "Уникальный" означает, что все идентификаторы должны быть разными
# от каждого другого идентификатора, используемого любым другим источником репликации или репликой.
идентификатор сервера = 26
# ***** Группа, связанная с репликацией *****
# Имя хоста или IP-адрес реплики, которая будет передана источнику
# во время регистрации реплики. Это значение появляется в выводе SHOW SLAVE HOSTS.
# на исходном сервере. Оставьте значение неустановленным, если вы не хотите, чтобы реплика
# зарегистрируйтесь в источнике.
# отчет_хост=0.0

# ***** Группа, связанная с репликацией *****
# Определяет алгоритм, используемый для хеширования записей, извлеченных во время транзакции. если ты
# используется групповая репликация, эта переменная должна быть установлена ​​в XXHASH64, так как процесс
# извлечения записей из транзакции требуется для обнаружения конфликтов на всех
# Участники группы.
# transaction_write_set_extraction=0.0
нижний_case_table_names=1

# Безопасный файл priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Загрузки"

# Максимальное количество одновременных сеансов, которые сервер MySQL может
# позволять. Одно из этих подключений будет зарезервировано для пользователя с
# СУПЕР привилегии, позволяющие администратору войти в систему, даже если
# лимит подключений достигнут.
#max_connections=151
#[29062021]изменено Шридхараном, чтобы увеличить максимальное количество одновременных сеансов
max_connections=1000
#[29062021]изменено Шридхараном, чтобы увеличить максимальное количество ошибок подключения
max_connect_errors=500

# Количество открытых таблиц для всех потоков. Увеличение этого значения
# увеличивает количество файловых дескрипторов, которые требуются mysqld.
# Поэтому вы должны обязательно установить количество открытых файлов
# разрешено не менее 4096 в переменной "open-files-limit" в
# раздел [mysqld_safe]
#table_open_cache=2000
table_open_cache = 2048
table_definition_cache = 2048
myisam_sort_buffer_size = 8M
#скип-внешняя-блокировка
# Максимальный размер внутренних (в памяти) временных таблиц. Если таблица
# становится больше, чем это значение, оно автоматически преобразуется в дисковое
# таблица на основе Это ограничение относится к одной таблице. Может быть много
# их.
tmp_table_size=4G

# Сколько потоков мы должны хранить в кеше для повторного использования. Когда клиент
# отключается, потоки клиента помещаются в кеш, если их нет
# больше потоков, чем thread_cache_size из предыдущего. Это значительно снижает
# необходимое количество созданий потоков, если у вас много новых
# соединения. (Обычно это не дает заметных результатов
# улучшение, если у вас есть хорошая реализация потока.)
# thread_cache_size=10
thread_cache_size = 64
query_cache_size = 0
query_cache_type = 0

#*** Специальные параметры MyISAM
# Максимальный размер временного файла, который MySQL может использовать при
# воссоздание индекса (во время REPAIR, ALTER TABLE или LOAD DATA INFILE.
# Если размер файла будет больше этого, будет создан индекс
# через кеш ключей (что медленнее).
myisam_max_sort_file_size=100G

# Размер буфера, который выделяется при сортировке индексов MyISAM
# во время REPAIR TABLE или при создании индексов с помощью CREATE INDEX
# или ИЗМЕНИТЬ ТАБЛИЦУ.
myisam_sort_buffer_size=6G

# Размер буфера ключей, используемого для кэширования индексных блоков для таблиц MyISAM.
# Не устанавливайте его больше, чем 30% доступной памяти, так как часть памяти
# также требуется ОС для кэширования строк.Даже если вы не используете
# Таблицы MyISAM, вы все равно должны установить его на 8-64M, так как он также будет
# используется для внутренних временных дисковых таблиц.
key_buffer_size=20M

# Размер буфера, используемого для полного сканирования таблиц MyISAM.
# Выделяется на поток, если требуется полное сканирование.
read_buffer_size=64K

read_rnd_buffer_size=256K
tmp_table_size=64M
max_heap_table_size=64M

#*** Специальные опции INNODB ***
# innodb_data_home_dir=

# Используйте эту опцию, если у вас есть сервер MySQL с включенной поддержкой InnoDB
# но вы не планируете его использовать. Это сэкономит память и место на диске
# и ускорить некоторые вещи.
# пропустить-innodb

# Если установлено значение 1, InnoDB будет сбрасывать (fsync) журналы транзакций в
# диск при каждом коммите, что обеспечивает полное поведение ACID. Если ты
# готовы поступиться этой безопасностью, и у вас мало
# транзакций, вы можете установить это значение в 0 или 2, чтобы уменьшить дисковый ввод-вывод до
# логи. Значение 0 означает, что журнал записывается только в файл журнала и
# файл журнала сбрасывается на диск примерно раз в секунду. Значение 2
# означает, что журнал записывается в файл журнала при каждой фиксации, но журнал
# файл сбрасывается на диск примерно раз в секунду.
innodb_flush_log_at_trx_commit=2

# Размер буфера, который InnoDB использует для буферизации данных журнала. Как только
# он заполнен, InnoDB придется сбросить его на диск. Как это смыто
# в любом случае раз в секунду, нет смысла делать его очень большим
# (даже при длинных транзакциях).
innodb_log_buffer_size=200M

# InnoDB, в отличие от MyISAM, использует пул буферов для кэширования как индексов, так и
# Данные ряда. Чем больше вы установите это значение, тем меньше дискового ввода-вывода потребуется для
# доступ к данным в таблицах. На выделенном сервере базы данных вы можете установить это
# параметр до 80% объема физической памяти машины. Не устанавливайте его
# слишком большой, потому что конкуренция за физическую память может
# вызвать пейджинг в операционной системе. Обратите внимание, что в 32-битных системах вы
# может быть ограничено 2-3,5 ГБ пользовательской памяти на процесс, поэтому не
# установите слишком высокое значение.
#[29062021]Sridharan изменил размер пула буферов innodb с 16 до 24 ГБ
#[06072021]Sridharan изменил размер пула буферов innodb с 24 до 48 ГБ.
innodb_buffer_pool_size=185G

# Размер каждого файла журнала в группе журналов. Вы должны установить комбинированный размер
# файлов журналов примерно до 25%-100% от размера вашего буферного пула, чтобы избежать
# ненужная активность очистки буферного пула при перезаписи файла журнала. Однако,
# обратите внимание, что больший размер файла журнала увеличит время, необходимое для
# процесс восстановления.
innodb_log_file_size=4G

# Количество потоков, разрешенных внутри ядра InnoDB. Оптимальное значение
# сильно зависит от приложения, оборудования и ОС
# свойства планировщика. Слишком высокое значение может привести к перегрузке потока.
#innodb_thread_concurrency=17
innodb_thread_concurrency=32

# Размер приращения (в МБ) для увеличения размера автоматически расширяемого файла системного табличного пространства InnoDB, когда он заполняется.
innodb_autoextend_increment = 64

# Количество областей, на которые разделен буферный пул InnoDB.
# Для систем с пулами буферов размером в несколько гигабайт разделение пула буферов на отдельные экземпляры может улучшить параллелизм,
# уменьшая конкуренцию при чтении и записи кэшированных страниц разными потоками.
#innodb_buffer_pool_instances=8
#[29062021 Sridharan] количество экземпляров буферного пула увеличено с 8 до 12.
innodb_buffer_pool_instances=12

#[29062021 Sridharan] добавлены конфигурации io_capacity, read_io_threads и write_io_threads для ускорения запросов
innodb_read_io_threads = 32
innodb_write_io_threads = 32
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Определяет количество потоков, которые могут одновременно входить в InnoDB.
innodb_concurrency_tickets=5000

# Определяет, как долго в миллисекундах (мс) блок, вставленный в старый подсписок, должен оставаться там после первого доступа, прежде чем
# его можно переместить в новый подсписок.
innodb_old_blocks_time=1000

# Указывает максимальное количество файлов .ibd, которые MySQL может держать открытыми одновременно. Минимальное значение равно 10.
innodb_open_files=300

# Когда эта переменная включена, InnoDB обновляет статистику во время операторов метаданных.
innodb_stats_on_metadata=0

# Когда innodb_file_per_table включен (по умолчанию в 5.6.6 и выше), InnoDB сохраняет данные и индексы для каждой вновь созданной таблицы
# в отдельном файле .ibd, а не в системном табличном пространстве.
innodb_file_per_table=1

# Используйте следующий список значений: 0 для crc32, 1 для strict_crc32, 2 для innodb, 3 для strict_innodb, 4 для отсутствия, 5 для strict_none.
innodb_checksum_algorithm = 0

# Количество невыполненных запросов на соединение, которые может иметь MySQL.
# Эта опция полезна, когда основной поток MySQL получает много запросов на соединение за очень короткое время.
# Затем основному потоку требуется некоторое время (хотя и очень мало), чтобы проверить соединение и запустить новый поток.
# Значение back_log указывает, сколько запросов может быть сложено в стек за это короткое время, прежде чем MySQL на мгновение
# перестает отвечать на новые запросы.
# Вам нужно увеличить это, только если вы ожидаете большое количество подключений за короткий промежуток времени.
back_log=80

# Если установлено ненулевое значение, все таблицы закрываются каждые flush_time секунд для высвобождения ресурсов и
# синхронизировать несохраненные данные на диск.
# Эту опцию лучше всего использовать только в системах с минимальными ресурсами.
флеш_время = 0

# Минимальный размер буфера, который используется для сканирования простого индекса, сканирования индекса диапазона и соединений, которые не используют
# индексирует и, таким образом, выполняет полное сканирование таблицы.
join_buffer_size=256K

# Максимальный размер одного пакета или любой сгенерированной или промежуточной строки, или любого параметра, отправленного
# mysql_stmt_send_long_data() Функция C API.
max_allowed_packet=1024M

# Если несколько последовательных запросов на подключение от хоста прерываются без успешного подключения,
# сервер блокирует этот хост от выполнения дальнейших подключений.
max_connect_errors=100

# Изменяет количество файловых дескрипторов, доступных для mysqld.
# Вам следует попробовать увеличить значение этой опции, если mysqld выдает ошибку "Слишком много открытых файлов".
open_files_limit=4161

# Если вы видите много sort_merge_passes в секунду в выводе SHOW GLOBAL STATUS, вы можете рассмотреть возможность увеличения
# значение sort_buffer_size для ускорения операций ORDER BY или GROUP BY, которые нельзя улучшить с помощью оптимизации запросов
# или улучшенная индексация.
sort_buffer_size = 256 КБ

# Количество определений таблиц (из файлов .frm), которые можно сохранить в кэше определений.
# Если вы используете большое количество таблиц, вы можете создать кэш определений больших таблиц, чтобы ускорить открытие таблиц.
# Кэш определения таблицы занимает меньше места и не использует файловые дескрипторы, в отличие от обычного кэша таблиц.
# Минимальное значение и значение по умолчанию равно 400.
table_definition_cache=1400

# Укажите максимальный размер двоичного события журнала на основе строк в байтах.
# Строки группируются в события меньшего размера, если это возможно. Значение должно быть кратно 256.
binlog_row_event_max_size=8K

# Если значение этой переменной больше 0, реплика синхронизирует свой файл master.info с диском.
# (используя fdatasync()) после каждого события sync_master_info.
sync_master_info=10000

# Если значение этой переменной больше 0, сервер MySQL синхронизирует свой журнал ретрансляции с диском.
# (используя fdatasync()) после каждой записи sync_relay_log в журнал реле.
sync_relay_log=10000

# Если значение этой переменной больше 0, реплика синхронизирует свой файл relay-log.info с диском.
# (используя fdatasync()) после каждой транзакции sync_relay_log_info.
sync_relay_log_info=10000

# Загрузить плагины mysql при запуске."plugin_x ; plugin_y".
# plugin_load

# Порт TCP/IP, который будет прослушивать протокол MySQL Server X.
# свободный_mysqlx_port=33060
# relay-log=C:/ProgramData/MySQL/MySQL Server 5.7/Data/mysql-relay-bin.log
реле-журнал = F:\MySQL\Data\mysql-relay-bin.log

# log_bin=C:/ProgramData/MySQL/MySQL Server 5.7/Data/mysql-bin.log
log_bin=F:\MySQL\Data\mysql-bin.log

binlog_do_db=jira_datacenter
binlog_format = СМЕШАННЫЙ

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

... мы замечаем, что, хотя выполнение некоторых запросов занимает более 30 минут (кажется, что они выполняются на локальном сервере всего за 5 минут) ...

Даже 5 минут это много времени для запуска запроса.
Могут ли эти запросы быть настроенный, даже в локальном экземпляре?
Переносят ли они большие объемы данных по сети? Проще говоря, ваша виртуальная машина Azure находится на конце более длинного фрагмента электронной строки, чем ваш локальный экземпляр (и может подвергаться всем видам сетевой маршрутизации, управления трафиком и т. д.).

... загрузка ЦП на виртуальной машине остается очень низкой ...

Опять же, это звучит так, как будто много данных перемещается с очень небольшой обработкой на стороне сервера.
Являются ли диски занятый? Запросы, которые выполняют много обновлений или используют много временных наборов результатов, будут трэш диски, вообще не используя много процессора. (И наоборот, множество [в памяти] сканирование таблиц обычно проявляется высокой загрузкой процессора и низкой активностью диска).