Все подряд О чем здесь

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-таблицу

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

cd /var/lib/mysql/databasename

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

myisampack tablename


пример работы myisampack

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

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

myisamchk -rq --sort-index --analyze tablename


пример работы myisamchk -rq

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

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

mysqladmin -uroot -p flush-tables

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

myisamchk -dvv tablename


пример работы myisamchk -dvv

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

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

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

myisamchk -u tablename

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

В заключение

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

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