MySQL: Archive Storage Engine

Рубрика: Development, MySQL | 13 March 2009, 20:56 | Vadim Voituk

Все очевидно, что любая современная информацонная система занимается хранением и анализом любого рода данных.  Аналитики утверждают что в среднем, обьем этих данных для среднестатистичекой компании растет на 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%, о которых писалось вначале заметки)

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

Ссылки:

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

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

Комментариев: 10

10 Responses to “MySQL: Archive Storage Engine”

Комментарии:

  1. maratische

    интересно, 100 лет не видел эту базу данных.

    а таблицы логов и прочего аудита можно сразу в архивную таблицу пихать, insert там быстро происходит?

  2. Vadim Voituk

    Собственно “складировать” логи будет даже куда эффективнее чем на MyISAM потому как в ARCHIVE используется построчная блокировка.

  3. Farcaller

    Познавательно, thx.

    Я не бот, но по делу сказать нечего, а вещь интересная :)

  4. crypto5

    А как скорость последующих сапросов по архиву?

  5. Vadim Voituk

    Учитывая, что индексов нет, а данные на диске расположены в порядке их добавления в таблицу – то не так быстро как хотелось.
    Зато быстро работает full-scan таблицы :)

  6. Vadim Voituk

    Я не бот, но по делу сказать нечего, а вещь интересная :)

    Повеселил :)

  7. Misterio

    У меня тут вопрос не совсем в тему.
    Есть в базе несколько таблиц с логами, в частности game_log, fight_log, chat_log и т.д.
    все на innoDB с индексами.
    Логи через 14 дней уже не нужны, поэтому они автоматически удаляются утилиткой, висящей в фоне, через простой DELETE:
    DELETE FROM game_log WHERE date<’2009-01-01′

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

    Есть идеи по этому поводу?

  8. Андрей

    после удаления делай
    OPTIMIZE TABLE game_log

  9. BigBug

    Непоможет optimize. Дело в том что innodb data файл может только расти, и вот раз в месяц он дорастает до размера когда всё тупит :) И только drop+пересоздание способно решить эту проблему.

    Для таких данных у нас заведено создавать еженедельные/ежедневные таблицы, ну а старые просто архивируются.

  10. Vadim Voituk

    @BigBug
    OPTIMIZE на InnoDB как раз и делает recreate+analyze.

Leave a Reply