MySQL: Archive Storage Engine

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

Из своего опыта работы с достаточно большими массивами данных, хранимых в современных реляционных СУБД (по большей части в MySQL) могу сказать что не менее 50% данных в любой системе – это данные которые представляют собой только “историческую” ценность.

Чаще всего по ним просчитаны-пересчитына все возможные “срезы”, построены все отчеты и вероятность их использования в дальнейшем с каждым днем все больше приближается к нулю.
Также часто бывает, что просто удалить подобные данные по разным причинам нельзя.

Будучи чуть более молодым и менее опытным, чем сейчас я делал так: разделял данные на 2 части (старые и новые), со старых делал дапм, архивировал и писал на CD, после чего смело удалял их из MySQL.
Думаю не стоит обьяснять во что выливалась внезапная необходимость поднять старый массив данных для какого-то супер-нового отчета. :)

Вот тут очень кстати вспомнить за что я люблю MySQL – это его plugable storage engines, которые существуют под любую специфичную задачу.

Потому предлагаю кратко пробежаться по особенностям ARCHIVE storage engine, созданного для хранения “архивных” данных максимально эффективно:

• данные в archive-таблицах хранятся в сжатом библиотекой zlib виде
• не поддерживает обновление и удаление даннных (UPDATE/REPLACE/DELETE) – только добавление (INSERT)
• не поддерживает индексы
• использует row-based локи
• поддерживает strict sql_mode

С появлением ARCHIVE  Storage Engine описанный выше use-case существенно преобразился: теперь старые данные не дампятся, а переносятся в “архивную” таблицу – полную копию существующей.
В результате их хранение за долгий период не является очень накладным, а удобство использования и мгновенный доступ посредством SQL сохраняется.

В качестве эксперимента приведу результаты сегодняшнего “архивирования” описанным образом одной небольшой таблицы:
(по хорошему надо было взять таблицу побольше, но под рукой такой не оказалось)
После разделения данных на “архивные” и “актуальные” и оптимизации таблицы “актулаьных” данных получил такое:

+--------------------+--------+---------------+------------+
| table_name         | engine | total_size_mb | table_rows |
+--------------------+--------+---------------+------------+
| arch_downloads_log | MyISAM |         63.68 |      45801 |
| wap_downloads_log  | MyISAM |         18.67 |      13429 |
+--------------------+--------+---------------+------------+

Итого 69тыс строк данных занимают почти 82Mb дискового пространства.
После удаления индексов из arch_downloads_log и конвертирования в тип ARCHIVE получил:

+--------------------+---------+---------------+------------+
| table_name         | engine  | total_size_mb | table_rows |
+--------------------+---------+---------------+------------+
| arch_downloads_log | ARCHIVE |          1.49 |      45801 |
| wap_downloads_log  | MyISAM  |         18.67 |      13429 |
+--------------------+---------+---------------+------------+

Итого, таблица размером в 64Mb была сжата в 1.5Mb – тоесть в 42 раза.
(хм… как интересно совпало с 42%, о которых писалось вначале заметки)

Выводы? – Тут уж думайте сами, решайте сами надо ли и поможет ли оно вам.

Ссылки:

• Документация об ARCHIVE storage engine от MySQL AB
• Обзор возможностей ARCHIVE storage engine от Robin Schumacher

P.S. Перечитал написанное  – какой-то банальщиной попахивает. Ну и ладно.

P.S.S. Ах, да! Те кто сейчас хочет подискутировать на тему “MySQL не предназначен для хранения больших обьемов данных” – предлагаю даже не начинать, ибо ответ будет единственно-правильный: “Просто вы не умеете его готовить”.