Назад | Перейти на главную страницу

Настройка сжатия MySQL INNODB KEY_BLOCK_SIZE

Недавно я начал использовать формат таблицы Barracuda InnoDB / MySQL, который допускает сжатие.

Я сжал одну из своих таблиц, запустив:

alter table pricing row_format=compressed, key_block_size=8;

После запуска я просмотрел статистику сжатия (я очистил их прямо перед ALTER TABLE):

mysql> select * from INFORMATION_SCHEMA.INNODB_CMP;
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| page_size | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
|      1024 |            0 |               0 |             0 |              0 |               0 |
|      2048 |            0 |               0 |             0 |              0 |               0 |
|      4096 |            0 |               0 |             0 |              0 |               0 |
|      8192 |      7029231 |         6352315 |          1437 |         339708 |              41 |
|     16384 |            0 |               0 |             0 |              0 |               0 |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
5 rows in set (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.INNODB_CMPMEM;
+-----------+------------+------------+----------------+-----------------+
| page_size | pages_used | pages_free | relocation_ops | relocation_time |
+-----------+------------+------------+----------------+-----------------+
|       128 |      11214 |          0 |        8434571 |               2 |
|       256 |          0 |         37 |              0 |               0 |
|       512 |          0 |         34 |              0 |               0 |
|      1024 |          0 |          2 |              0 |               0 |
|      2048 |          0 |        141 |              0 |               0 |
|      4096 |          0 |        298 |          96657 |               0 |
|      8192 |      15133 |          0 |        4121178 |               5 |
|     16384 |          0 |          0 |              0 |               0 |
+-----------+------------+------------+----------------+-----------------+
8 rows in set (0.00 sec)

Если разделить compress_ops_ok на compress_ops, получится 6352315/7029231 = 90,4%. Насколько я понимаю, в основном 90,4% страниц были сжаты с 16 КБ до 8 КБ, а остальные не смогли сжаться в 2 раза.

Я читал, что эти страницы, которые не сжимают, снижают производительность, но более 90%, которые успешно сжаты, должны немного улучшить производительность (за счет снижения количества операций ввода-вывода). Есть ли эмпирическое правило, какой процент страниц следует сжать, чтобы это считалось нормальным? Другим вариантом, вероятно, было бы просто отключить сжатие.

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

Даже после запуска сжатия вы все равно можете не получить требуемую производительность. Зачем ?

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

Обратите внимание, как это происходит в Документация MySQL

Сжатие и буферный пул InnoDB

В сжатой таблице InnoDB каждая сжатая страница (1 КБ, 2 КБ, 4 КБ или 8 КБ) соответствует несжатой странице размером 16 КБ. Чтобы получить доступ к данным на странице, InnoDB считывает сжатую страницу с диска, если она еще не находится в пуле буферов, затем распаковывает страницу в исходную форму размером 16 Кбайт. В этом разделе описывается, как InnoDB управляет пулом буферов по отношению к страницам сжатых таблиц.

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

InnoDB отслеживает, какие страницы оставить в памяти, а какие удалить, используя список наименее недавно использованных (LRU), так что «горячие» или часто используемые данные имеют тенденцию оставаться в памяти. Когда осуществляется доступ к сжатым таблицам, InnoDB использует адаптивный алгоритм LRU для достижения соответствующего баланса сжатых и несжатых страниц в памяти. Этот адаптивный алгоритм чувствителен к тому, работает ли система с привязкой к вводу-выводу или к ЦП. Цель состоит в том, чтобы не тратить слишком много времени на распаковку страниц, когда ЦП занят, и избежать лишних операций ввода-вывода, когда ЦП имеет свободные циклы, которые можно использовать для распаковки сжатых страниц (которые могут уже быть в памяти). Когда система привязана к вводу-выводу, алгоритм предпочитает удалять несжатую копию страницы, а не обе копии, чтобы освободить место для других страниц диска, чтобы они стали резидентными в памяти. Когда система привязана к процессору, InnoDB предпочитает удалять как сжатую, так и несжатую страницу, чтобы для «горячих» страниц можно было использовать больше памяти и уменьшить необходимость распаковывать данные в памяти только в сжатом виде.

Если это дублирование содержимого данных происходит в пуле буферов, вам необходимо увеличить innodb_buffer_pool_size небольшим линейным коэффициентом новой степени сжатия. Вот как:

СЦЕНАРИЙ

  • У вас есть сервер БД с пулом буферов 8 ГБ
  • Вы запустили сжатие с помощью key_block_size=8
    • 8 является 50.00% из 16
    • 50.00% из 8G является 4G
    • поднять innodb_buffer_pool_size к 12G (8G + 4G)
  • Вы запустили сжатие с помощью key_block_size=4
    • 4 является 25.00% из 16
    • 25.00% из 8G является 2G
    • поднять innodb_buffer_pool_size к 10G (8G + 2G)
  • Вы запустили сжатие с помощью key_block_size=2
    • 2 является 12.50% из 16
    • 12.50% из 8G является 1G
    • поднять innodb_buffer_pool_size к 9G (8G + 1G)
  • Вы запустили сжатие с помощью key_block_size=1
    • 1 является 06.25% из 16
    • 06.25% из 8G является 0.5G (512M)
    • поднять innodb_buffer_pool_size к 8704M (8G (8192M) + 512M)

МОРАЛЬ ИСТОРИИ : Буферному пулу InnoDB просто нужна дополнительная передышка при работе со сжатыми данными и индексными страницами.

Эти данные были собраны из ALTER TABLE - оператора, который вы не часто используете и который перезаписывает всю таблицу. Важна ваша повседневная рабочая нагрузка, все ВСТАВКИ и ОБНОВЛЕНИЯ, которые ваше приложение выполняет в производственной среде. Согласно руководству по MySQL:

«Вы можете отключить сжатие для таблиц, из-за которых количество« сбоев сжатия »в вашем приложении превышает 1% или 2% от общего числа. (Такой коэффициент сбоев может быть приемлемым во время временной операции, такой как загрузка данных ). "