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

Воспользуйтесь рекомендациями в этом разделе, чтобы сделать правильный выбор архитектуры и конфигурации. Когда вы готовы провести длительные тесты и запустить сжатые таблицы в производство, см. Раздел 17.9.1.4, «Мониторинг сжатия таблиц InnoDB во время выполнения», чтобы проверить эффективность этих решений в реальных условиях.

Когда использовать сжатие

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

Характеристики данных и сжатие

Ключевым фактором эффективности сжатия в уменьшении размера файлов данных является характер самих данных. Помните, что сжатие работает за счёт идентификации повторяющихся последовательностей байтов в блоке данных. Полностью случайные данные — худший случай. Типичные данные часто содержат повторяющиеся значения и поэтому сжимаются эффективно. Строковые данные часто сжимаются хорошо, независимо от того, определены ли они в столбцах типа CHAR, VARCHAR, TEXT или BLOB. С другой стороны, таблицы, содержащие в основном бинарные данные (целые числа или числа с плавающей точкой) или данные, которые уже сжаты (например, изображения JPEG или PNG), обычно не сжимаются эффективно, незначительно или вообще не сжимаются.

Вы решаете, включить ли сжатие для каждой таблицы InnoDB. Таблица и все её индексы используют один и тот же (сжатый) формат. Возможно, индекс (кластеризованный), который содержит данные для всех столбцов таблицы, сжимается более эффективно, чем вторичные индексы. В тех случаях, когда есть длинные строки, использование сжатия может привести к тому, что значения длинных столбцов будут храниться «вне страницы», как описано в формате строк DYNAMIC. Эти страницы переполнения могут сжиматься хорошо. С учётом этих соображений, для многих приложений некоторые таблицы сжимаются более эффективно, и вы можете обнаружить, что ваша рабочая нагрузка лучше всего работает только с подмножеством сжатых таблиц.

Чтобы определить, следует ли сжимать определённую таблицу, проведите эксперименты. Вы можете получить приблизительную оценку того, насколько эффективно данные могут быть сжаты, используя утилиту, реализующую сжатие LZ77 (например, gzip или WinZip) на копии таблицы без сжатия. Вы можете ожидать меньшее сжатие от сжатой таблицы MySQL, чем от инструментов для сжатия файлов, поскольку MySQL сжимает данные кусками на основе размера страницы, 16 КБ по умолчанию. Кроме пользовательских данных, формат страницы включает некоторые внутренние системные данные, которые не сжимаются. Утилиты сжатия файлов могут анализировать значительно большие куски данных и поэтому могут находить больше повторяющихся строк в большом файле, чем MySQL может найти на отдельной странице.

Другой способ проверить сжатие для конкретной таблицы — скопировать данные из вашей несжатой таблицы в аналогичную сжатую таблицу (с теми же индексами) в файловой табличной группе и посмотреть размер полученного файла .ibd. Например:

USE test;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL autocommit=0;

-- Create an uncompressed table with a million or two rows.
CREATE TABLE big_table AS SELECT * FROM information_schema.columns;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
COMMIT;
ALTER TABLE big_table ADD id int unsigned NOT NULL PRIMARY KEY auto_increment;

SHOW CREATE TABLE big_table\G

select count(id) from big_table;

-- Check how much space is needed for the uncompressed table.
\! ls -l data/test/big_table.ibd

CREATE TABLE key_block_size_4 LIKE big_table;
ALTER TABLE key_block_size_4 key_block_size=4 row_format=compressed;

INSERT INTO key_block_size_4 SELECT * FROM big_table;
commit;

-- Check how much space is needed for a compressed table
-- with particular compression settings.
\! ls -l data/test/key_block_size_4.ibd

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

-rw-rw----  1 cirrus  staff  310378496 Jan  9 13:44 data/test/big_table.ibd
-rw-rw----  1 cirrus  staff  83886080 Jan  9 15:10 data/test/key_block_size_4.ibd

Чтобы увидеть, эффективно ли сжатие для вашего конкретного случая:

Сжатие данных в базе данных по сравнению с сжатием в приложении

Решите, сжимать ли данные в приложении или в таблице; не используйте оба типа сжатия для одних и тех же данных. Когда вы сжимаете данные в приложении и сохраняете результаты в сжатой таблице, дополнительная экономия места крайне маловероятна, и двойное сжатие просто тратит циклы процессора.

Сжатие в базе данных

При включённом сжатии MySQL, сжатие таблиц происходит автоматически и применяется ко всем столбцам и значениям индексов. Столбцы по-прежнему могут быть проверены операторами, такими как LIKE, и операции сортировки могут по-прежнему использовать индексы даже при сжатых значениях индекса. Поскольку индексы часто составляют значительную часть общего размера базы данных, сжатие может привести к существенной экономии места, ввода-вывода или времени процессора. Операции сжатия и распаковки происходят на сервере базы данных, который, скорее всего, является мощной системой, масштабированной для обработки ожидаемой нагрузки.

Сжатие в приложении

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

Гибридный подход

Конечно, возможен комбинированный подход. Для некоторых приложений может быть уместно использовать некоторые сжатые таблицы и некоторые несжатые таблицы. Возможно, лучше предварительно сжать некоторые данные (и сохранить их в несжатых таблицах) и позволить MySQL сжать (некоторые из) других таблиц в приложении. Как всегда, предварительный дизайн и тестирование в реальных условиях ценны для принятия правильного решения.

Характеристики рабочей нагрузки и сжатие

В дополнение к выбору таблиц для сжатия (и размера страницы), рабочей нагрузкой является другой ключевой фактор производительности. Если приложение в основном выполняет чтение, а не обновление, меньше страниц нужно перестраивать и пересжимать после того, как страница индекса заполняется для страничного «журнала модификаций», который MySQL поддерживает для сжатых данных. Если обновления в основном изменяют неиндексированные столбцы или те, которые содержат BLOB или длинные строки, которые хранятся «вне страницы», накладные расходы на сжатие могут быть приемлемыми. Если единственные изменения в таблице — это INSERT, которые используют монотонно возрастающий первичный ключ, и мало вторичных индексов, нет необходимости перестраивать и пересжимать страницы индексов. Поскольку MySQL может «помечать удаление» и удалять строки на сжатых страницах «на месте» путём модификации несжатых данных, операции DELETE над таблицей относительно эффективны.

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

В основе работы сжатия лежит использование времени процессора для сжатия и распаковки данных. Таким образом, если ваша рабочая нагрузка ограничена ввода-вывода (I/O), а не процессором, вы можете обнаружить, что сжатие может улучшить общую производительность. При тестировании производительности вашего приложения с различными конфигурациями сжатия, тестируйте на платформе, аналогичной предполагаемой конфигурации производственной системы.

Характеристики конфигурации и сжатие

Чтение и запись данных базы данных на диск — самая медленная часть производительности системы. Сжатие пытается уменьшить ввод-вывод (I/O), используя время процессора для сжатия и распаковки данных, и наиболее эффективно, когда I/O является относительно редким ресурсом по сравнению с циклами процессора.

Это часто особенно актуально при работе в многопользовательской среде с быстрыми многоядерными процессорами. Когда страница сжатой таблицы находится в памяти, MySQL часто использует дополнительную память, обычно 16 КБ, для несжатой копии страницы. Алгоритм адаптивного LRU пытается сбалансировать использование памяти между сжатыми и несжатыми страницами, учитывая, работает ли рабочая нагрузка в режиме ограниченного ввода-вывода или процессора. Тем не менее, конфигурация с большей памятью, выделенной для буфера пула, как правило, работает лучше при использовании сжатых таблиц, чем конфигурация с ограниченным объёмом памяти.

Выбор размера страницы сжатия

Оптимальное значение размера страницы сжатия зависит от типа и распределения данных, содержащихся в таблице и ее индексах. Размер страницы сжатия всегда должен быть больше максимального размера записи, в противном случае операции могут завершиться неудачей, как указано в Сжатие страниц B-дерева.

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

Обычно размер страницы сжатия устанавливается в 8 КБ или 4 КБ. Учитывая, что максимальный размер строки для таблицы InnoDB составляет около 8 КБ, KEY_BLOCK_SIZE=8 обычно является безопасным выбором.