Преимущества размеров страниц MySQL 8 InnoDB 32 КБ и 64 КБ для жесткого диска
Документация по размеру страницы MySQL говорит:
For releases up to and including MySQL 5.5, the size of each InnoDB page is fixed at 16 kilobytes. This value represents a balance: large enough to hold the data for most rows, yet small enough to minimize the performance overhead of transferring unneeded data to memory. Other values are not tested or supported.
Starting in MySQL 5.6, the page size for an InnoDB instance can be either 4KB, 8KB, or 16KB, controlled by the innodb_page_size configuration option. As of MySQL 5.7.6, InnoDB also supports 32KB and64KB page sizes. For 32KB and 64KB page sizes, ROW_FORMAT=COMPRESSED is not supported and the maximum record size is 16KB.
а потом
Smaller page sizes can help performance with storage devices that use small block sizes, particularly for SSD devices in disk-bound workloads, such as for OLTP applications. As individual rows are updated, less data is copied into memory, written to disk, reorganized, locked, and so on.
Как насчет использования большего (32 КБ или 64 КБ) размера страницы, чем 16 КБ по умолчанию? В каком случае вы должны это сделать и что вы получаете в качестве преимущества?
Я буду настраивать новый экземпляр MySQL с традиционным жестким диском, и мне было интересно, может ли изменение 16 КБ по умолчанию повлиять на производительность и эффективность использования хранилища.
До сих пор я нашел недостаток только в использовании размера страницы 32 КБ и 64 КБ:
For 32KB and 64KB page sizes, ROW_FORMAT=COMPRESSED is not supported and the maximum record size is 16KB.
@Билл Карвин просто имеет это, и ему нужно придерживаться этого. Слишком много данных, превышающих бюджет, если учитывать SSD.
Что ж, вам действительно следует провести нагрузочное тестирование ваших данных с трафиком запросов, чтобы быть уверенным. Никто не может догадаться, какая будет разница в производительности. Может и не будет существенной разницы. Вероятно, определение правильных индексов для поддержки ваших запросов принесет гораздо больше пользы, чем вы когда-либо могли бы получить, выбрав другой размер страницы.
@BillKarwin Правда, это в конце реальный ответ на вопрос, но мне просто любопытно, почему они поставили вариант 32 КБ и 64 КБ, и я думаю, что нашел 2 причины: (...) Например, максимальный размер страницы InnoDB 64 КБ с размером блока файловой системы 4 КБ может улучшить сжатие... из-за «Например, если innodb_page_size = 16K и блок файловой системы размер составляет 4 КБ, данные страницы должны быть сжаты до размера меньше или равного 12 КБ, чтобы можно было пробивать отверстия». Второе: развертывание MySQL на сайте filesys. это уже установлено 32 или 64 КБ, поэтому оба совпадают.
Ответы 2
Некоторые люди достигают максимальный размер строки около 8000 байт. Переход на страницы размером 32 КБ удваивает этот предел. Однако переключение на 64 КБ не выходит за пределы 16 КБ.
Поскольку блоки InnoDB имеют тенденцию быть разбросанный вокруг диска, наличие блоков большего размера немного сэкономит движение руки на жестком диске. Я ожидаю, что это будет только однозначное процентное улучшение. И она будет варьироваться в зависимости от вида деятельности. Только что загруженная таблица может не показать никаких улучшений; таблица с большим количеством оттока может показать некоторые.
Если ваш набор данных соответствует буфер_пул, то операций ввода-вывода не так много, поэтому размер блока не имеет большого значения.
Стоимость движение руки несколько минимизируется за счет драйверов или контроллеров, оптимизирующих порядок дисковых операций. RAID с кешем делает особенно хорошую работу и может сделать запись практически мгновенной. «Пробивка отверстий», вероятно, увеличивает частоту движений рук. Классический компромисс: «скорость против пространства».
Если ваш набор данных слишком большой, чтобы поместиться в ОЗУ, и если вы выполняете много «точечных запросов», то размер блока меньше будет лучше. Но если вы много сканируете таблицы (или индексы), то больший размер блока имеет небольшое преимущество.
Имейте в виду, что все данные должны использовать один и тот же размер блока.
Из тысяч вопросов на форуме, которые я видел, я не думаю, что какой-либо из них изменил размер блока. Вы окажетесь в неизведанных водах.
Также обратите внимание, что, хотя ROW_FORMAT=COMPRESSED экономит место на диске, он потребляет часть оперативной памяти — это связано с тем, что некоторые (все?) блоки хранятся в оперативной памяти как в сжатом, так и в несжатом виде.
Цифры, которые я видел для этого row_format, составляют всего 50%. Любой приличный алгоритм сжатия сжимает практически любой текст примерно на 2/3. Итак, если у меня возникает соблазн использовать сжатие, я сжимаю сжимаемые столбцы (например, TEXT, но не jpg) и делаю это в клиент, тем самым разгружая ЦП с сервера. Я считаю (без каких-либо веских доказательств), что это лучший путь для сжатия ваших данных. Кроме того, я почти никогда не использую BIGINT.
(Все, что я говорю здесь, это теоретический, основанное либо на очень ограниченной документации MySQL, либо на принципах жестких дисков.)
У меня есть Практическое правило для оптимизации. «Если предварительные расчеты показывают улучшение менее чем на 10%, тогда двигайтесь дальше — ищите что-то еще для оптимизации».
Итак, я говорю: «Давайте».
Конечно, из каждого правила есть исключения, но узнать, применимо ли исключение к данному приложению, можно с помощью для загрузки-тестирования этого приложения..
Больший размер блока БД уменьшает высоту дерева индексов = меньше операций ввода-вывода
Все в InnoDB является индексом. Так что польза должна быть заметна. Тем более для HDD.
Я только что перенес базу данных размером 150 ГБ с Percona 5.1 на 5.7 с помощью дампа/загрузки, изменив размер блока БД на 64 КБ. На сервере 128 ГБ ОЗУ, поэтому операций чтения почти нет, но я все же ожидаю прироста производительности при резервном копировании, меньшем количестве операций записи на диск, более быстром завершении работы/запуске.
Единственное важное соображение относительно большего размера блока БД — наличие достаточного объема оперативной памяти и предоставление InnoDB такого объема оперативной памяти для кэширования. Размер блока в 4 раза больше означает, что кеш будет содержать 1/4 количества блоков БД, что в некоторых крайних случаях может привести к большему количеству операций чтения из-за очистки кеша.
Если вас так заботит производительность, то почему вы используете жесткий диск?