MySQL 8.4 LTS – новые готовые к работе значения по умолчанию для InnoDB

Вчера, в MySQL 8.4, была выпущена самая первая LTS-версия MySQL.

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

Изменены значения по умолчанию для 20 переменных InnoDB!

Давайте взглянем на эти переменные и объясним причину такой модификации:

innodb_buffer_pool_in_core_file

MADV_DONTDUMP – это макрос, поддерживаемый в Linux 3.4 и более поздних версиях (присутствует файл заголовка “sys/mman.h” и содержит символ MADV_DONTDUMP, расширение, отличное от POSIX для madvise()), это не поддерживается на компьютерах с Windows или в большинстве систем macOS.

Подводя итог, это означает, что по умолчанию в системах Linux содержимое буферного пула не сбрасывается в основной файл.

innodb_buffer_pool_instances

Старое значение 8 могло быть слишком большим в некоторых системах. Руководство содержит отличные примеры расчета размера BP, см. Настройка размера пула буферов InnoDB.

innodb_change_buffering

Буферизация изменений – это метод, который был полезен для предпочтения последовательного ввода-вывода за счет задержки операций записи во вторичные индексы. На самом современном оборудовании случайный ввод-вывод больше не является проблемой.

innodb_dedicated_server

Начиная с MySQL 8.0, мы рекомендуем включить эту переменную и не изменять вручную настройки InnoDB, за которые отвечает эта переменная, когда MySQL работает на выделенном сервере, где для базы данных доступны все ресурсы.

Начиная с MySQL 8.4, innodb_dedicated_server настраивает следующие переменные:

• innodb_buffer_pool_size
  • 128 МБ – это то, что на сервере меньше 1 ГБ памяти.
  • обнаружена память сервера * 0,5, если на сервере от 1 ГБ до 4 ГБ памяти.
  • обнаружена память сервера * 0.75, если на сервере было более 4 ГБ памяти.
• innodb_redo_log_capacity: (количество доступных логических процессоров / 2) ГБ, максимум 16 ГБ.

Метод innodb_flush_method не настраивается автоматически при включении innodb_dedicated_server.

innodb_adaptive_hash_index

AHI (InnoDB Adaptive Hash Index) долгое время был причиной некоторых проблем с производительностью. Каждый опытный администратор базы данных всегда советует просто отключить его, почти как старый кэш запросов. Мы удивлены, что не было AHI-тюнера, подобного Query Cache Tuner от Domas Mituzas 😉

AHI может обеспечить некоторое преимущество при запросах на чтение (SELECT), когда ни одна из данных не изменена и полностью кэшируется в буферном пуле. Как только происходят операции записи, или повышается нагрузка на систему, или если все данные, необходимые для чтения, не могут быть кэшированы, адаптивный хэш-индекс становится серьезным узким местом.

Чтобы обеспечить более предсказуемое время отклика, рекомендуется отключить его.

innodb_doublewrite_files

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

В документации указано, что это значение определяет количество файлов двойной записи для каждого пула буферов. Но у меня сложилось впечатление, что оно глобальное независимо от количества экземпляров пула буферов.

Из журнала ошибок MySQL:

2024-05-01T05:43:03.226604Z 1 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 2.000000G, instances = 2, chunk size =128.000000M 
[...]
2024-05-01T05:43:03.288068Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite
2024-05-01T05:43:03.295917Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite
2024-05-01T05:43:03.317319Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.bdblwr' for doublewrite
2024-05-01T05:43:03.317398Z 1 [Note] [MY-013566] [InnoDB] Double write buffer files: 2
2024-05-01T05:43:03.317410Z 1 [Note] [MY-013565] [InnoDB] Double write buffer pages per instance: 128
2024-05-01T05:43:03.317423Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite
2024-05-01T05:43:03.317436Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite

Мы видим, что у нас есть 2 экземпляра пула буферов, но по-прежнему только 2 файла буфера двойной записи. Я ожидал бы 4 согласно документации. Третий файл, #ib_16384_0.bdblwr, создан для использования, когда innodb_doublewrite имеет значение “DETECT_ONLY“.

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

innodb_doublewrite_pages

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

innodb_flush_method

При поддержке O_DIRECT всегда было предпочтительным значением, и мы рекомендовали использовать его для обхода кэша файловой системы для удаления изменений InnoDB на диск (для файлов данных и файлов журналов).

Если O_DIRECT не поддерживается, мы используем старый метод fsync. Это для Unix, в Windows значение по умолчанию – небуферизованный.

innodb_io_capacity

Для последних систем (RAIDs, SSD, …) пропускная способность ввода-вывода по умолчанию была слишком низкой. Поскольку переменная определяет количество операций ввода-вывода, доступных для фоновых операций InnoDB, слишком низкое значение ограничивало производительность.

innodb_io_capacity_max

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

innodb_log_buffer_size

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

innodb_numa_interleave

Когда система поддерживает NUMA, новое значение по умолчанию устанавливает политику памяти NUMA в значение MPOL_INTERLEAVE для mysqld во время выделения пула буферов InnoDB. Эта операция случайным образом распределяет память между всеми узлами numa, обеспечивая лучшее распределение между этими узлами.

Конечно, вы выиграете от этого, только если в вашей системе несколько узлов NUMA.

Вот как проверить количество узлов:

$ numactl --hardware
	available: 2 nodes (0-1)
	node 0 size: 16160 MB
	node 0 free: 103 MB
	node 1 size: 16130 MB
	node 1 free: 83 MB
	node distances:
	node   0   1 
	  0:  10  20 
	  1:  20  10

В приведенном выше примере мы можем видеть, что центральный процессор состоит из двух узлов.

Вы также можете использовать lstopo для отображения архитектуры и ядер NUMA. Это еще один пример.:

innodb_page_cleaners

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

innodb_parallel_read_threads

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

innodb_purge_threads

Эта переменная каким-то образом также автоматически настраивается для систем с большим количеством (> = 16) vCPU. Но мы также поняли, что наличие 4 потоков очистки может быть проблематичным в некоторых небольших системах. Для такой системы мы уменьшили значение по умолчанию до 1.

innodb_read_io_threads

Эта переменная также увеличивается автоматически, если в системе более 8 vCPU.

innodb_use_fdatasync

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

temptable_max_ram

Значение по умолчанию теперь автоматически увеличивается, если системе требуется большой объем памяти. Но ограничение по умолчанию составляет 4 ГБ. Таким образом, для систем с объемом памяти более 132 ГБ по умолчанию значение temptable_max_ram будет установлено равным 4 ГБ.

temptable_max_mmap

Новое значение по умолчанию отключает выделение памяти из временных файлов, отображаемых в памяти (файлы в tmpdir не создаются).

temptable_use_mmap

Когда temptable_use_mmap отключен (новое значение по умолчанию), механизм временного хранения использует внутренние временные таблицы InnoDB на диске вместо выделения места для внутренних временных таблиц в памяти в виде временных файлов с отображением в память в tmpdir, когда объем механизма временного хранения с возможностью изменения превышает предел, определенный переменной temptable_max_ram.

Заключение

С этой совершенно новой версией MySQL, самой первой LTS, у нас появилась возможность изменить значения по умолчанию некоторых переменных InnoDB, чтобы привести их в большее соответствие с реальностью производственных серверов.

Некоторые из них теперь автоматически настраиваются, чтобы лучше соответствовать системе, на которой запущен MySQL.

Наслаждайтесь MySQL и новыми настройками по умолчанию!

(1 оценок, среднее: 5,00 из 5)

Загрузка...