Вчера, в MySQL 8.4, была выпущена самая первая LTS-версия MySQL.
Наконец-то было удалено множество устаревших версий, и несколько значений по умолчанию переменных InnoDB были изменены, чтобы соответствовать текущим рабочим нагрузкам и спецификациям оборудования.
Изменены значения по умолчанию для 20 переменных InnoDB!
Давайте взглянем на эти переменные и объясним причину такой модификации:
innodb_buffer_pool_in_core_file
| Предыдущее значение: | ON |
| Новое значение (8.4 LTS): | OFF, если поддерживается MADV_DONTDUMP еще ON |
MADV_DONTDUMP — это макрос, поддерживаемый в Linux 3.4 и более поздних версиях (присутствует файл заголовка “sys/mman.h” и содержит символ MADV_DONTDUMP, расширение, отличное от POSIX для madvise()), это не поддерживается на компьютерах с Windows или в большинстве систем macOS.
Подводя итог, это означает, что по умолчанию в системах Linux содержимое буферного пула не сбрасывается в основной файл.
innodb_buffer_pool_instances
| Предыдущее значение: | 8 (или 1, если BP < 1 ГБ) |
| Новое значение (8.4 LTS): | Если BP <= 1 ГБ: 1 Если BP > 1 ГБ: тогда минимальное значение в диапазоне 1-64 между: a. (innodb_buffer_pool_size / innodb_buffer_pool_chunk_size) / 2 b. 1/4 доступных логических процессоров |
Старое значение 8 могло быть слишком большим в некоторых системах. Руководство содержит отличные примеры расчета размера BP, см. Настройка размера пула буферов InnoDB.
innodb_change_buffering
| Предыдущее значение: | All |
| Новое значение (8.4 LTS): | No |
Буферизация изменений — это метод, который был полезен для предпочтения последовательного ввода-вывода за счет задержки операций записи во вторичные индексы. На самом современном оборудовании случайный ввод-вывод больше не является проблемой.
innodb_dedicated_server
| Предыдущее значение: | OFF |
| Новое значение (8.4 LTS): | ON |
Начиная с 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
| Предыдущее значение: | ON |
| Новое значение (8.4 LTS): | OFF |
AHI (InnoDB Adaptive Hash Index) долгое время был причиной некоторых проблем с производительностью. Каждый опытный администратор базы данных всегда советует просто отключить его, почти как старый кэш запросов. Мы удивлены, что не было AHI-тюнера, подобного Query Cache Tuner от Domas Mituzas 😉
AHI может обеспечить некоторое преимущество при запросах на чтение (SELECT), когда ни одна из данных не изменена и полностью кэшируется в буферном пуле. Как только происходят операции записи, или повышается нагрузка на систему, или если все данные, необходимые для чтения, не могут быть кэшированы, адаптивный хэш-индекс становится серьезным узким местом.
Чтобы обеспечить более предсказуемое время отклика, рекомендуется отключить его.
innodb_doublewrite_files
| Предыдущее значение: | innodb_buffer_pool_instances * 2 |
| Новое значение (8.4 LTS): | 2 |
Ранее значение по умолчанию вычислялось в соответствии с количеством буферных пулов, для упрощения теперь значение по умолчанию равно 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_write_io_threads (по умолчанию 4) |
| Новое значение (8.4 LTS): | 128 |
В результате нашего тестирования и из соображений производительности мы поняли, что иметь большее значение по умолчанию было лучше, поскольку мы часто рекомендовали увеличить его.
innodb_flush_method
| Предыдущее значение: | fsync |
| Новое значение (8.4 LTS): | O_DIRECT (или fsync) |
При поддержке O_DIRECT всегда было предпочтительным значением, и мы рекомендовали использовать его для обхода кэша файловой системы для удаления изменений InnoDB на диск (для файлов данных и файлов журналов).
Если O_DIRECT не поддерживается, мы используем старый метод fsync. Это для Unix, в Windows значение по умолчанию — небуферизованный.
innodb_io_capacity
| Предыдущее значение: | 200 |
| Новое значение (8.4 LTS): | 10000 |
Для последних систем (RAIDs, SSD, …) пропускная способность ввода-вывода по умолчанию была слишком низкой. Поскольку переменная определяет количество операций ввода-вывода, доступных для фоновых операций InnoDB, слишком низкое значение ограничивало производительность.
innodb_io_capacity_max
| Предыдущее значение: | 2 * innodb_io_capacity (минимум 2000) |
| Новое значение (8.4 LTS): | 2 * innodb_io_capacity |
Если InnoDB необходимо выполнить более агрессивную очистку, эта переменная определяет максимальное количество операций ввода-вывода, которые InnoDB может использовать для выполнения фоновых операций. Новое значение по умолчанию проще, поскольку оно просто вдвое превышает innodb_io_capacity.
innodb_log_buffer_size
| Предыдущее значение: | 16 МБ |
| Новое значение (8.4 LTS): | 64 МБ |
Мы увеличили значение по умолчанию, потому что большой буфер журнала позволяет выполнять большие транзакции, не требуя записи журнала на диск перед фиксацией транзакций.
innodb_numa_interleave
| Предыдущее значение: | OFF |
| Новое значение (8.4 LTS): | ON |
Когда система поддерживает 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
| Предыдущее значение: | 4 |
| Новое значение (8.4 LTS): | innodb_buffer_pool_instances |
Новое значение по умолчанию заключается в использовании такого количества потоков для удаления грязных страниц из экземпляров буферного пула, сколько существует экземпляров буферного пула.
innodb_parallel_read_threads
| Предыдущее значение: | 4 |
| Новое значение (8.4 LTS): | логические процессоры / 8 (мин. 4) |
По соображениям производительности в системах с большим количеством логических процессоров количество потоков, используемых для параллельного чтения кластеризованных индексов, автоматически увеличивается.
innodb_purge_threads
| Предыдущее значение: | 4 |
| Новое значение (8.4 LTS): | 1 если логические процессоры <= 16 , то еще 4 |
Эта переменная каким-то образом также автоматически настраивается для систем с большим количеством (> = 16) vCPU. Но мы также поняли, что наличие 4 потоков очистки может быть проблематичным в некоторых небольших системах. Для такой системы мы уменьшили значение по умолчанию до 1.
innodb_read_io_threads
| Предыдущее значение: | 4 |
| Новое значение (8.4 LTS): | логические процессоры / 2 (мин. 4) |
Эта переменная также увеличивается автоматически, если в системе более 8 vCPU.
innodb_use_fdatasync
| Предыдущее значение: | OFF |
| Новое значение (8.4 LTS): | ON |
В системах, поддерживающих его, вызов fdatasync() не сбрасывает изменения в метаданных файла, если это не требуется. Это обеспечивает повышение производительности.
temptable_max_ram
| Предыдущее значение: | 1 ГБ |
| Новое значение (8.4 LTS): | 3% от общего объема памяти (в диапазоне 1-4 ГБ) |
Значение по умолчанию теперь автоматически увеличивается, если системе требуется большой объем памяти. Но ограничение по умолчанию составляет 4 ГБ. Таким образом, для систем с объемом памяти более 132 ГБ по умолчанию значение temptable_max_ram будет установлено равным 4 ГБ.
temptable_max_mmap
| Предыдущее значение: | 1 ГБ |
| Новое значение (8.4 LTS): | 0 (отключено) |
Новое значение по умолчанию отключает выделение памяти из временных файлов, отображаемых в памяти (файлы в tmpdir не создаются).
temptable_use_mmap
| Предыдущее значение: | ON |
| Новое значение (8.4 LTS): | OFF |
Когда temptable_use_mmap отключен (новое значение по умолчанию), механизм временного хранения использует внутренние временные таблицы InnoDB на диске вместо выделения места для внутренних временных таблиц в памяти в виде временных файлов с отображением в память в tmpdir, когда объем механизма временного хранения с возможностью изменения превышает предел, определенный переменной temptable_max_ram.
Заключение
С этой совершенно новой версией MySQL, самой первой LTS, у нас появилась возможность изменить значения по умолчанию некоторых переменных InnoDB, чтобы привести их в большее соответствие с реальностью производственных серверов.
Некоторые из них теперь автоматически настраиваются, чтобы лучше соответствовать системе, на которой запущен MySQL.
Наслаждайтесь MySQL и новыми настройками по умолчанию!