Compressed таблицы в MySQL

Для чего нужны compressed-таблицы

Как ни странно, четкого ответа на этот вопрос в официальной MySQL документации нет, зато есть два основных положения по поводу компрессии таблиц:

1. Таблицы сжимаются до 40%-70% от исходного размера
2. Таблицы становятся Read-Only

Исходя из этого следует что сжатые таблицы (по сравнению с MyISAM таблицами) имеют приемущества в более быстром считывании данных с диска, что в целом уменьшает нагрузку на дисковую систему. Более того, можно предположить, что ядро MySQL тратит меньше времени на подготовку таблицы к запросу, т.к. в этом случае Read-Only не нужно проверять блокировки и выполнять сопутствующие операции - блокировать таблицу нет смысла и она всегда доступна для всех потоков, у которых есть права чтения этой таблицы.

На моих личных тестах рост производительности compressed-таблиц в сложных запросах составляет 5-15%. Согласитесь, не очень впечатляющий выигрыш за необходимость переводить таблицу в Read Only. Но, опять же из личного опыта, на одном из проектов оказалось намного выгоднее перенести его с выделенного сервера c HDD-дисками на виртуальный сервер на SSD-дисках. Проблема была в том что в проекте были очень большие, но почти никогда не обновляемые данные (кроме пары столбцов). Перенос обновляемых столбцов в отдельную таблицу и компрессия неизменяемых данных дала выигрыш в занимаемом проектом дисковом пространсве примерно в 40%, что для конфигурируемых хостингов очень неплохая скидка в итоговом ценнике. Благодаря этим манипуляциям удалось ускорить генерацию страниц с 500мс до 50мс (т.к. выборка значений из огромных таблиц создавала большую iops-нагрузку на систему), а цена аренды снизилась в 5 раз. В данном случае переезд на SSD решал проблему производительности, а компрессия самой "тяжелой" части проекта позволила дополнительно сократить ценник аренды на 30%.

За счет чего MySQL сжимает таблицы

myisampack использует несколько различных способов при компрессии таблицы.

Изначально перед сжатием утилита проверяет все данных по столбцам. Далее в мета-информацию по таблице записываются некоторые обшие данные, которые позволяют не записывать эти данные непосредственно в строчк таблицы. Например, если в вашей таблице есть столбец is_active и все строки столбцы имеют значение 1, то эта информация попадет лишь в мета-информацию о том что все строки имеют одинаковое значение равное 1. Из самих строк этот байт информации будет удален.

Если набор значений по таблице невелик, утилита может привести тип столбца к ENUM-типу. Например, если в Вашем столбце status типа varchar(16) на 4млн записей есть только значения active, inactive, wait, deleted, то myisampack преобразует его в enum и в compressed-таблице эти знчачения будут занимать 2 бита вместо 4-8 байт. Аналогично к меньшему типу приводятся и числовые столбцы, если диапазон используемых значений позволяет использовать более узкий тип для хранения этих данных.

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

Как создать compressed-таблицу

Переходим в директорию базы данных где находится нужная нам таблица

Запускаем утилиту myisampack на нужную таблицу

Упаковка занимает некоторое время. Утилита показывает прогресс выполнения.

После упаковки нужно пересчитать индекс, используем утилиту myisamchk

Ключ --sort-index нужен для того чтоб утилита отсортировала индекс, это ускорит работу с ним в будущем. --analyze заставляет утилиту проанализировать таблицу - это позволяет движку MySQL правильнее оптимизировать запросы с использованием сжатой таблицы в будущем.

Далее нужно выполнить flush tables

Теперь таблица готова к использованию. Посмотреть подробную информацию по компрессии можно утилитой myisamchk с ключами -dvv

Например, здесь мы видим что стобцы 5 и 6 были сжаты методом table-lockup. Это означает что набор значений в столбцах был небольшим и myisampack преобразовал его в enum.

Распаковка compressed-таблиц

Для возврата таблицы в обычное состояние нужно выполнить myisamchk с ключом -u или --unpack

Далее нужно снова выполнить flush tables и можно снова работать с таблицей.

В заключение

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

Если Вы ищете как еще можно ускорить работу базы данных - советую почитать как оптимизировать MySQL-индексы и как оптимизировать JOIN-запросы