MyISAM против Innodb MySQL Engine для WordPress что лучше и конвертация таблиц базы данных

На облачных сайтах, выделенном сервере обычно используется Innodb. Innodb предлагает лучшую производительность на лучшем оборудовании. Это вариант создания таблиц баз данных для вашего сайта, причём очень популярный, имеющий ряд своих преймуществ. Первый и самый важный момент заключается в том, что я не гуру базы данных. MySQL настолько специализирован, и когда речь идет о MySQL Engine, честно говоря, мне довольно сложно это объяснить. Поскольку вопрос был задан читателем сайта, я пытаюсь ответить.

MyISAM против Innodb MySQL Engine для WordPress

Поскольку нам нужно определенное оборудование, чтобы предлагать MySQL Engine для WordPress, на практике это типичная война MyISAM против Innodb.

WordPress предназначен для работы только на базе данных MySQL. По этой причине оптимизация MySQL является очень важной проблемой, если вы хотите очень быстро запускать WordPress на используемом оборудовании. Но это может быть не так просто, как вы думаете, учитывая, что MySQL может предложить вам множество механизмов хранения.

Начиная с MySQL 5.0, в MySQL есть 10 (да, десять) механизмов хранения. До выпуска MySQL 5.5 MyISAM был механизмом хранения по умолчанию, и когда вы создаете новую таблицу без указания механизма, таблица будет использовать механизм MyISAM. После обновления до MySQL 5.5, механизм по умолчанию теперь InnoDB. Самое замечательное в MySQL – то, что вы можете использовать разные механизмы хранения для каждой таблицы.

MyISAM

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

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

Из-за этого MyISAM в среднем хорош для большинства таблиц WordPress. MyISAM хорошо работает “из коробки” и не требует слишком большой оптимизации. Даже с оптимизацией вы не сможете значительно повысить производительность по сравнению с настройками по умолчанию.

MyISAM не очень надежен в случае аппаратного сбоя, остановки процесса или какого-либо другого сбоя. Это почти всегда вызывает повреждение данных в зависимости от последнего запуска операций.

InnoDB

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

Но разработка таблиц с ограничениями внешнего ключа не всегда проста, она не поддерживает полнотекстовое индексирование и требует больше ресурсов (памяти), чем MyISAM. Также вам нужно потратить некоторое время на оптимизацию этого движка.

В зависимости от уровня оптимизации и используемого оборудования, InnoDB может быть настроен на очень быструю работу, некоторые даже в 20 раз быстрее, чем настройки по умолчанию.

InnoDB не имеет постоянного размера для таблицы. Даже когда вы получаете размеры таблиц, вы должны знать, что возвращаемые значения являются только оценочными. Это происходит из-за того, что InnoDB обрабатывает данные, и поэтому ему требуется больше места для данных, чем MyISAM.

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

Какой вид таблиц выбрать?

Ну, это не легко ответить. Поскольку InnoDB теперь является механизмом по умолчанию (MySQL 5.5), при установке нового WordPress на сервер, на котором установлена ​​последняя версия MySQL, вы, скорее всего, получите все таблицы InnoDB.

Если у вас мощный сервер с большим объемом памяти, вы не заметите никаких замедлений (если InnoDB настроен правильно), и для многих операций вы можете заметить повышение скорости.

Если у вас есть все таблицы подсистем MyISAM в базе данных, вы можете рассмотреть возможность переключения некоторых из них на подсистему InnoDB. Здесь нет правильного ответа, и все зависит от того, что вам нужно от вашей базы данных. По умолчанию WordPress не использует полнотекстовую индексацию, поэтому он вполне может использовать InnoDB.

Если вам нужно больше надежности данных, InnoDB это путь в лучшее для сайта. Поскольку WordPress не использует внешние ключи для табличных отношений, выбор движка также не важен, оба будут работать хорошо.

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

Лучше всего протестировать оба механизма для разных таблиц в течение определенного периода (опять же, учтите ресурсы вашего сервера) и найти баланс, устанавливающий некоторые таблицы с InnoDB, а некоторые с MyISAM. Кроме того, не забудьте проверить настройки MySQL для обоих типов движков и проконсультироваться с системным администратором, чтобы убедиться, что оба настроены на лучшую производительность (это очень важно для InnoDB).

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

Я думаю, что WordPress в следующей крупной ревизии (возможно, для 3.4 или 3.5) должен рассмотреть вопрос о настройке движков для каждой таблицы на основе основной роли таблицы. Это потребовало бы тщательного тестирования, но стоило бы иметь лучший выбор двигателей с самого начала.

Лучшая производительность и стабильность

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

После того как я переключил формат всех таблиц базы данных на InnoDB, моя общая загрузка ЦП на сервере снизилась, сайты стали значительно быстрее, и ни одного сбоя еще не было. Я все еще в поиске, но сейчас я принял, что InnoDB – лучший формат для баз данных WordPress.

Имейте в виду, что WordPress не решает, какой формат будут использовать базы данных. По умолчанию ваш сервер определяет формат таблицы базы данных.

Как конвертировать WP таблицы из MyISAM в InnoDB

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

Шаг 1 – Узнайте, какая у вас версия MySQL

Это важно. Если вы используете MySQL версии 5.5 или выше, вам не придется беспокоиться о втором шаге, связанном с полнотекстовыми индексами .

До MySQL 5.5 InnoDB не поддерживал полнотекстовые индексы, поэтому все, что у вас есть в ваших таблицах, не будет работать, если вы преобразуете их. Это не значит, что не нужно конвертировать, но это означает, что сначала удалите эти полнотекстовые индексы.

Шаг 2 – Найти и удалить полнотекстовые индексы

Зайдите в MySQL или выполните следующее в PHPMyAdmin для каждой таблицы, которая использует MyISAM – я использую wp_posts в качестве примера:

SHOW INDEX FROM wp_posts;

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

Если существует полнотекстовый индекс, удалите его, введя следующую команду:

DROP INDEX index_name ON wp_posts

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

Шаг 3 – преобразовать механизм хранения таблицы

ALTER TABLE wp_posts ENGINE = InnoDB;

Это будет завершено, и вы успешно завершите изменение для этой таблицы. Теперь, чтобы сделать другие старые таблицы тоже!

Повторить на всех таблицах

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

После того, как вы получите их все, вы сможете настроить свой файл My.cnf и получить эту настройку для таблиц InnoDB, чтобы добиться успеха.

Наличие таблиц как в InnoDB, так и в MyISAM, что часто случается с сайтами WP, которые существуют уже давно, означает, что вы должны поддерживать оба в файлах my.cnf.

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

Дополнительно, если вы чувствуете себя достаточно смелым, вы можете выполнить следующие действия, чтобы обновить все свои базы данных:

Настройка механизма хранения по умолчанию

Установите механизм хранения по умолчанию на InnoDB, добавив  default_storage_engine = InnoDB в раздел [mysqld] файла конфигурации системы, расположенный по адресу: /etc/my.cnf. Перезапуск службы MySQL необходим серверу для обнаружения изменений в файле.

~ $ cat /etc/my.cnf
[mysqld]
log-error=/var/lib/mysql/mysql.err
innodb_file_per_table=1
default-storage-engine=innodb
innodb_buffer_pool_size=128M

Преобразование всех таблиц между MyISAM и InnoDB

К сожалению, MySQL по своей природе не имеет возможности конвертировать таблицы, оставляя каждую таблицу для индивидуального изменения. Мы составили простой план обслуживания для этого процесса. Сценарий, который вы можете запустить на необходимом сервере через оболочку доступа (SSH), преобразует все таблицы между механизмами хранения.

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

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

Резервное копирование всех баз данных в файл

Приведенная ниже команда создает резервную копию одного файла для всех баз данных с именем all-database-backup.sqld и может быть удалена после успешного завершения конвертации и отсутствия явных проблем.

mysqldump –all-database> all-database-backup.sql

Записать существующие движки таблиц в файл

Запустите следующий скрипт, чтобы записать существующие механизмы таблиц в файл с именем table-engine-backup.sql . Затем вы можете «импортировать» или «запустить» этот файл позже, чтобы преобразовать их в исходные движки, если это необходимо.

mysql
-Bse ‘SELECT CONCAT(“ALTER TABLE “,table_schema,”.”,table_name,”
ENGINE=”,Engine,”;”) FROM information_schema.tables WHERE table_schema
NOT IN(“mysql”,”information_schema”,”performance_schema”);’ | tee
table-engine-backup.sql

Если по какой-либо причине вам нужно вернуть таблицы движков обратно, выполните:

mysql <table-engine-backup.sql

Преобразование таблиц MyISAM в InnoDB

Приведенная ниже команда будет выполняться даже в случае сбоя таблицы и даст вам знать, какие таблицы не удалось преобразовать. Вывод сохраняется в файл с именем convert-to-innodb.log для последующего просмотра .

mysql
-Bse ‘SELECT CONCAT(“ALTER TABLE “,table_schema,”.”,table_name,”
ENGINE=InnoDB;”) FROM information_schema.tables WHERE table_schema NOT
IN (“mysql”,”information_schema”,”performance_schema”) AND Engine =
“MyISAM”;’ | while read -r i; do echo $i; mysql -e “$i”; done | tee
convert-to-innodb.log

Преобразование всех таблиц InnoDB в MyISAM

Эта команда будет выполняться даже в случае сбоя таблицы и даст вам знать, какие таблицы не удалось преобразовать. Вывод также сохраняется в файл с именем convert-to-myisam.log для последующего просмотра.

mysql
-Bse ‘SELECT CONCAT(“ALTER TABLE “,table_schema,”.”,table_name,”
ENGINE=MyISAM;”) FROM information_schema.tables WHERE table_schema NOT
IN (“mysql”,”information_schema”,”performance_schema”) AND Engine =
“InnoDB”;’ | while read -r i; do echo $i; mysql -e “$i”; done | tee
convert-to-myisam.log

Преобразовать одну таблицу в MyISAM:

mysql -Bse ‘ALTER TABLE database_name.table_name ENGINE = MyISAM;’

Вот и все. Пожалуйста, имейте в виду, что вы будете нести ответственность за соблюдение этих правил, и мы не несем ответственности за возникшие проблемы.

Tags: innodbmysqlбазы данных