То, что не может быть измерено, не может быть улучшено

MySQL движки хранения данных

Вольный перевод: MySQL Storage Engines
Автор: Mike Peters

Одна из великолепных возможностей MySQL, отличная от бесплатности, широкой поддержки и быстроты, заключается в выборе различных движков хранения данных (storage engines) для различных таблиц.

MySQL предлагает 7 движков хранения данных, включая "example" - который позволяет Вам реализовать собственную библиотеку хранения.

Что-же такого великолепного в обладании всеми этими вариантами?

Каждый движок хранения данных полностью различен и спроектирован для конкретных нужд приложений.

Не зацикливаясь на одном движке (как Oracle), Вы тем самым можете оптимизировать и выбирать лучший инструмент для своей работы.

Совет: Хорошо спроектированное приложение, активно использующее MySQL, должно использовать различные движки хранения данных для различных таблиц. Если Вы все еще завязли на MyISAM таблицах, может теперь стоит, что-то пересмотреть.

Обзор движков хранения данных MySQL

MyISAM: Движок по умолчанию. Не поддерживает транзакций, средняя надежность хранения данных. Превосходная производительность чтения данных для интенсивных приложений. Большинство веб сервисов и хранилищ данных активно используют MyISAM.

HEAP: Все в памяти. Очень быстрый поиск данных, однако все они хранятся только в памяти и будут потеряны при остановке сервера. Великолепно подходит для временных таблиц.

Archive: Используется для хранения больших объемов данных без индексов, занимая меньший размер.

Merge: Коллекция MyISAM таблиц логически объединенных вместе для единого представления.

InnoDB: Транзакционный тип движка, применяемый при интенсивных операциях записи, спасибо возможности блокировки уровня строк таблицы. Великолепная восстанавливаемость и высокая надежность хранения данных. Движок InnoDB был приобретен Oracle в 2005 году.

NDB: Кластерный движок - данные автоматически разделяются и реплицируются по различным машинам, именуемым - дата узлы. Применяется для приложений, которые требуют высокой производительности с наивысшей степенью доступности. NDB хорошо работает на системах требующих высокой отдачи на операциях чтения. Для "тяжелых" приложений требующих активной записи в конкурирующей среде рассмотрите вариант с InnoDB.

Что-бы лучше понять уникальные характеристики каждого движка хранения данных, посмотрите на эту "магическую" диаграмму:

Характеристики движка MySQL

Примеры:

Ниже приведены несколько примеров использования наиболее подходящих движков хранения для различных задач:

  • Поисковый движок - NDB кластер
  • Логирование веб статистики - Обычные файлы для логирования с оффлайновым обработчиком и записью всей статистики в InnoDB таблицы
  • Финансовые транзакции - InnoDB
  • Сессионные данные - MyISAM или NDB кластер
  • Локальные расчеты - HEAP
  • Словари - MyISAM

Важные замечания по MyISAM таблицам:

  1. Таблицы могут быть повреждены. Ежедневно архивируйте Ваши данные или установите еще один MySQL сервер для выполнения репликаций.
  2. Включите авто-восстановление (auto-repair) в настройках Вашего сервера (my.cnf):
    myisam-recover=backup,force
    или рассмотрите возможность выполнения ежедневной автоматической проверки таблиц баз данных.
  3. Очень быстрое чтение данных (через SELECT)
  4. Конкурирующие записи полностью блокируют таблицы. Переключите все, что возможно, на оффлайн обработку записей сериями, что-бы не загружать движок сервера баз данных. (Оффлайн обработка - золотое правило, применимое для всех типов таблиц)

Важные замечания по HEAP/Memory таблицам:

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

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

Важные замечания по InnoDB таблицам:

  1. Поддержка ACID транзакций. Встроенная отказоустойчивость данных, равная надежности 99.999%. Блокировка уровня строк (сравните с полной блокировкой всей таблицы в MyISAM) означает обеспечение быстрой записи конкурирующих операций.
  2. Выполнение "SELECT Count(*) FROM table" без индексов выполняется в InnoDB очень медленно и требует сканирования всей таблицы. (Для MyISAM эта операция ничего не стоит, потому что он хранит внешние записи счетчиков для каждой таблицы).
    Если Вам часто необходима операция "SELECT COUNT(*)" на таблицах InnoDB, создайте MySQL триггер на вставку/удаление, который будет увеличивать/уменьшать счетчик, когда данные добавляются или удаляются из таблицы.
  3. Резервирование (бакапирование)
    Простое архивирование всех файлов таблиц для InnoDB невозможно.
    MySQLDump резервирует InnoDB очень медленно. (Если Вы настаиваете на таком резервировании, включите флаг: --opt --compress)
    Быстрое жизнеспособное резервирование, которое так-же может быть использовано как новая "ведомая" (slave) машина, это InnoDB Hot Backup.
  4. Восстановление
    В InnoDB встроена поддержка восстановления, которая работает в 99% случаев автоматически. Никогда не трогайте .frm или .ibd файлы в надежде "помочь" восстановлению базы данных. Если встроенное восстановление не сработало, переключайтесь на "ведомый" сервер и восстанавливайте основной из архивов.
  5. LOAD DATA INFILE в InnoDB работает очень медленно. Для операций LOAD DATA присмотритесь к использованию MyISAM таблиц.
  6. InnoDB меньше, чем MyISAM, прощает выполнение запросов построенных не на индексах. InnoDB отправит Вас в "школу", что-бы быть уверенным, что каждый запрос или обновление будет запущено на индексах. Выполните непроиндексированный запрос и Вы поплатитесь за это временем исполнения.
  7. Никогда не изменяйте my.cnf InnoDB лог файл, когда запущен сервер баз данных. Вы разрушите последовательный лог-номер (log sequence number) оставшись без возможность восстановления.
  8. Для увеличения производительности InnoDB, присмотритесь к использованию следующих настроек (my.cnf):
    
        innodb_open_files = 500
        innodb_file_per_table
        innodb_buffer_pool_size = 250M
        innodb_flush_log_at_trx_commit = 2
        innodb_thread_concurrency =8
        innodb_lock_wait_timeout = 500
        interactive_timeout = 20
        back_log = 75
        table_cache = 300
        thread_cache = 32
        thread_concurrency = 8
        wait_timeout = 30
        connect_timeout = 10
    

Расширяемость

Каждое успешное веб приложение, в конце концов, перерастает возможности сервера баз данных размещающегося на одной машине. В этом случае, обычно, Вы имеете две возможности - Репликации или Кластер NDB. Выбор зависит от требований Вашего приложения.

Для активно-читающей (read-heavy) среды, используйте NDB кластер или установите репликации для n MyISAM ведомых read-only машин.

Для активно-пишущей (write-heavy) среды, InnoDB с активно/пассивными репликациями будет лучшим типовым решением. Но Вы можете поэкспериментировать с NDB кластером. NDB кластер обычно медленнее чем InnoDB в операциях с активной записью, но он предлагает наивысший уровень доступности.

Полезные срипты

  • Конвертирование таблиц в InnoDB
  • MySQLDump одной базы в другую (разработано для случая, когда Вы не можете использовать InnoDB Hot backup и у Вас есть множество таблиц/баз для импорта. Скрипт автоматически пересоздает, переоткрывает соединение и импортирует одну таблицу за раз)
  • Добавление триггеров к таблицам для быстрого count(*) в InnoDB
    Вы можете установить только один delete/insert триггер на каждую таблицу, поэтому, если Вы уже используете триггеры, модифицируйте код.
10 комментариев на статью:
  • Яков:

    Очень полезно и своевременно (как раз хотел почитать нечто такое) — санкс 8-)

  • admin:

    Стараюсь переводить хороший материал :)

  • Денис Радченко:

    Спасибо за материал. Как раз задумываюсь перевести один проект на InnoDB для отказоустойчивости, но не хватает опыта работы с индексами.

  • омск:

    а собственно , где про кластеризацию то?
    ну на картинке вижу, есть слово..и все?

    как на ноде прописать в конфиги путь к хранилищу подскажите плиз

  • admin:

    Про это надо у гуру спрашивать (к примеру на хабре), я к таковым себя не отношу.

  • Nick:

    Вот прямо сейчас пытаемся, но никак не получается восстановить InnoDB. Обещанного автоматизма нет! Служба MySQL вообще не запускается по причине ошибки InnoDB плагина. Переключение Default engine на MyISAM не помогает. С cервером вообще ничего жестокого не происходило, база почти не юзается, т.е. не под нагрузкой! У меня раньше таблицы были исключительно MyISAM, сервак(писюк) был до смешного древний и убогий и электричество вырубали 10 раз на дню, ибо на стройплощадке дело было… И ничего! База проработала без падений 3 года непрерывно, пока я про нее не забыл. А тут этот хренов InnoDB, который обязан быть устойчивым к сбоям, на ровном месте подыхает и не восстанавливается…

  • webroot:

    У Oracle не один движек. Есть движок типа OLTP, есть движек для Data Warehouse, есть OLAP движок.

  • Александр:

    Действительно видно, что человек знает, что пишет… Спасибо за путеводитель

  • Типы таблиц MySQL | error_log blog:

    […] Вольный перевод статьи MySQL Storage Engines (by Mike Peters). Перевод взят отсюда. […]

Ответить
Обязательные поля помечены *