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

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

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

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

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

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

Вы выбираете, включить ли сжатие для каждой таблицы InnoDB. Таблица и все ее индексы используют один и тот же (сжатый) формат. Возможно, что индекс (кластеризованный), содержащий данные всех столбцов таблицы, сжимается более эффективно, чем вторичные индексы. В тех случаях, когда строки длинные, использование сжатия может привести к тому, что длинные значения столбцов будут храниться “вне страницы”, как обсуждается в DYNAMIC Row Format. Эти страницы переполнения могут сжиматься эффективно. Учитывая эти соображения, для многих приложений некоторые таблицы сжимаются более эффективно, и вы можете обнаружить, что ваша рабочая нагрузка лучше всего работает только со сжатыми подмножествами таблиц.

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

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

USE test;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
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 обычно является безопасным выбором.