расставляй правильно приоритеты и не отвлекайся на мелочи

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.

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

Примеры:

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

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

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

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

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

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

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

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

  • Поддержка ACID транзакций. Встроенная отказоустойчивость данных, равная надежности 99.999%. Блокировка уровня строк (сравните с полной блокировкой всей таблицы в MyISAM) означает обеспечение быстрой записи конкурирующих операций.
  • Выполнение «SELECT Count(*) FROM table» без индексов выполняется в InnoDB очень медленно и требует сканирования всей таблицы. (Для MyISAM эта операция ничего не стоит, потому что он хранит внешние записи счетчиков для каждой таблицы).
    Если Вам часто необходима операция «SELECT COUNT(*)» на таблицах InnoDB, создайте MySQL триггер на вставку/удаление, который будет увеличивать/уменьшать счетчик, когда данные добавляются или удаляются из таблицы.
  • Резервирование (бакапирование)
    Простое архивирование всех файлов таблиц для InnoDB невозможно.
    MySQLDump резервирует InnoDB очень медленно. (Если Вы настаиваете на таком резервировании, включите флаг: —opt —compress)
    Быстрое жизнеспособное резервирование, которое так-же может быть использовано как новая «ведомая» (slave) машина, это InnoDB Hot Backup.
  • Восстановление
    В InnoDB встроена поддержка восстановления, которая работает в 99% случаев автоматически. Никогда не трогайте .frm или .ibd файлы в надежде «помочь» восстановлению базы данных. Если встроенное восстановление не сработало, переключайтесь на «ведомый» сервер и восстанавливайте основной из архивов.
  • LOAD DATA INFILE в InnoDB работает очень медленно. Для операций LOAD DATA присмотритесь к использованию MyISAM таблиц.
  • InnoDB меньше, чем MyISAM, прощает выполнение запросов построенных не на индексах. InnoDB отправит Вас в «школу», что-бы быть уверенным, что каждый запрос или обновление будет запущено на индексах. Выполните непроиндексированный запрос и Вы поплатитесь за это временем исполнения.
  • Никогда не изменяйте my.cnf InnoDB лог файл, когда запущен сервер баз данных. Вы разрушите последовательный лог-номер (log sequence number) оставшись без возможность восстановления.
  • Для увеличения производительности 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 комментариев на статью:
  1. Яков:

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

  2. bridennis:

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

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

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

  4. омск:

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

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

  5. bridennis:

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

  6. Nick:

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

  7. webroot:

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

  8. Александр:

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

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

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

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