20 октября 2015

Правильная миграция с MyISAM на InnoDB

MySQL
Давайте я отвлеку вас от котиков и расскажу, основываясь на своём опыте, какие подводные камни появляются при переходе с MyISAM на InnoDB, и как их избежать. Код приложения будет на PHP.

Этот пост я решил написать, прочитав огромное количество неправильных ответов на запрос из сабжа в интернете. По всему интернету разбросаны неграмотные или не полные ответы, в результате чего складывается впечатление о том, что смигрировать вашу базу данных на InnoDB — это очень просто. Нет, это не просто! Итак, начнем!

Зачем переходить на InnoDB


С этим вопросом, я думаю, всем всё ясно. Объяснять не буду — преимуществам InnoDB посвящены куча статей в интернете. Если ты читаешь эти строки, то значит ты осознанно пришел к этой мысли о переводе своего хозяйства на InnoDB, и ты, хабраюзер, гуглишь) Надеюсь, эта статья — то, что тебе надо.

Подготовительный этап

1. Из банального — это обеспечить необходимое количество свободного места на диске, где у нас развернута база. InnoDB занимает примерно в 1,5 раза больше места, чем MyISAM.

2. Очень важный момент — он вам пригодится в будущем при траблшутинге перформанс ишшусов в базе. Нужно прокомментировать каждый SQL запрос в вашем приложении с использованием уникального идентификатора, например, порядкового номера. Если у вас сотни или тысячи SQL запросов, то как вы жили до сих пор без этого?

SELECT /*017*/ client_id, money, lastname FROM clients WHERE money > 100;


Если так сделать, то запросы вида SHOW PROCESSLIST, а также дампы запросов в slow лог файлы будут содержать подсказку для вас — номер SQL запроса, и потом вы мгновенно сможете найти этот запрос в коде и оптимизировать его.

3. Прописываем в конфиг-файле my.cnf:
[mysqld]
innodb_file_per_table=1

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

4. Настройка размера кэшей для InnoDB — в том же my.cnf файле:
# (уменьшаем это значение, оно для MyISAM и данный вид буфера нам больше не нужен)
key_buffer_size         = 8M 
# этот размер выставляем в 50-80% от размера всей оперативной памяти у сервера БД.
innodb_buffer_pool_size = 512M 

5. Настройка способа работы базы с транзакциями
transaction-isolation = READ-COMMITTED
innodb_lock_wait_timeout=5
innodb_rollback_on_timeout=1
binlog-format   = MIXED
innodb_log_file_size = 200M

Я на своем приложении выставил уровень изоляции транзакций READ-COMMITTED, вместо выставляющегося по умолчанию REPEATABLE-READ, поскольку в противном случае в базе было бы чрезмерное количество дедлоков. Я для себя решил, что мое приложение может прочитать не самые свежие данные, ценой более быстрой работы, вместо абсолютно актуальных данных, но отягощенных множеством блокировок. Впрочем, для mission-критикал транзакции в коде можно повысить её уровень изоляции — этот эффект будет действовать только на одну транзакцию:
mysqli_query($link, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");

Следующий параметр — таймаут, который я специально снизил с 50 до 5 секунд, чтобы он не подвешивал клиентские сессии на очень долго при наличии блокировок.

innodb_rollback_on_timeout очень важен относительно того, как именно ваш код обрабатывает ошибки. С этим моментом я не встречал ясности, поэтому расскажу.

— если этого флага нет, то InnoDB, при наступлении таймаута (Error code 1205) будет откатывать только один этот затаймаутившийся стейтмент в вашей транзакции. То есть, вам нужно будет повторить только его, а не всю транзакцию с начала. Для меня этот вариант показался сложнее в реализации.

— если флаг выставлен, то откатывается вся транзакция, точно так же, как это делается при выявлении дедлока (Error code 1213). Я выбрал именно этот вариант, потому что это позволяет сделать код обработки ошибок унифицированным, т.е. повторять транзакцию с первого стейтмента, с начала, при получении любой из этих двух ошибок.

innodb_log_file_size придется увеличить из-за подводного камня №3 (ниже), поскольку этот лог должен быть достаточным для хранения как минимум нескольких записей, а при наличии записей типа MEDIUMTEXT их размер может превысить несколько мб, поэтому дефолтное значение в 5мб крайне мало. После изменения этого параметра базу нужно остановить, старые файлы ib_logfile0 и ib_logfile1 нужно удалить, и только потом поднимать базу.

Чего бояться в InnoDB

Собственно, в InnoDB нужно внимательно смотреть только за этими двумя кодами ошибок: 1205 (таймаут) и 1213 (дедлок), которых не было в MyISAM. При настройке сервера, приведенной выше, он будет сам откатывать ваши транзакции в обоих случаях. Вам надо будет их повторить сначала. При этом ваш прикладной код может состоять как из отдельных стейтментов — транзакций (при autocommit=1), так и из транзакций, состоящих из нескольких SQL стейтментов — в этом случае транзакция начинается с
mysqli_query($link, "START TRANSACTION");
и завершается
mysqli_commit($link);

(Про mysqli_begin_transaction() знаю, но он только для MySQL >= 5.6, а не везде такие новые MySQL сервера).

Если у вас какой-то вызов mysqli_query() не обернут в for($i=0;$i<5;$i++) {}, то считайте, что ваш код под угрозой. Нужно переписать весь код, заменив все вызовы функции mysqli_query() на вызов вашей функции my_mysqli_query(), которая будет повторять откаченную транзакцию, например так:

function my_mysqli_query($link, $query)
{
    $result = FALSE;
    $error_code = 0;
    $msc = microtime(true);

    for($attempts = 1; $attempts <= 10; $attempts++)
    {
      $result = mysqli_query($link, $query);
      $msctime = microtime(true) - $msc;
      $msctime = round($msctime, 2);
      
      if($result) {
        if(($attempts > 1) || ($msctime > 2)) {
          tologfile("[$msctime sec.] SUCCESS (from attempt #$attempts)\n$query\n\n");
        }
        break;
      }

      $error_code = mysqli_errno($link);
      if(($error_code != 1205) && ($error_code != 1213)) {
        tologfile("[$msctime sec.] IGNORING Code: $error_code\n$query\n\n");
        break;
      }

      tologfile("[$msctime sec.] FOR RETRY; Code: $error_code (attempt $attempts)\n$query\n\n");
   }

   if(!$result) {
      tologfile("[$msctime sec.] FAILED after $attempts attempts; Code: $error_code\n$query\n\n");
   }

   return $result;
}

Этот код и повторяет откаченные из-за таймаутов или дедлоков одно-стейтментные транзакции, и также логгирует странности, что мне позволило выловить довольно редкие баги. Также обратите внимание, что код фактически является аналогом конфигурационной опции log_slow_queries, только сделан своими силами и более гибок. Например, я логгирую запросы с длительностью более 2 секунд.

Подводный камень №1

Видел распространенное заблуждение насчет того, как люди обрабатывают ошибки:
for ($attempts = 0; $attempts < 5; $attempts++) {
    $result = mysqli_query($link, $Query);
    if($result) {
      # транзакция успешна, выходим из цикла, иначе - повтор
      break;
    }
}
mysqli_commit($link);

Вроде бы всё правильно… Но только на первый взгляд. На самом деле, этот код смешной. Например, при синтаксической ошибке в SQL запросе (Error code 1064), или если в столбец не уберутся все данные (Data truncated, Error code 1265) — этот код будет 5 раз повторять очевидно избыточные вещи.
Поэтому вывод — уходим на следующую итерацию цикла только если код ошибки равен 1205 или 1213. В остальных случаях нужно логгировать ошибочный запрос, и разбираться с ним потом. Тут-то вам и пригодятся комменты в теле SQL запроса с его порядковым номером.

Подводный камень №2

Тут всё просто, просто нужно это помнить: код, который вы будете повторять при возникновении ошибок 1205 и 1213, должен быть идемпотентным. То есть, результаты работы этого кода один раз и несколько раз не должны отличаться. Например, если внутри цикла for вас есть перекладывания результатов запроса в массив:
array_push($clients_array, $Row['client_id']);

То в начале этого цикла for должен быть код очистки массива:
$clients_array = array();

иначе при повторе транзакции у вас будет уже в два раза больший массив результатов.

Подводный камень №3

А этот подводный камень — просто ахтунг. Помните, что я, руководствуясь благими намерениями, выставил уровень изоляции транзакций в READ-COMMITTED? Так вот, в этом случае, и если у вас включена репликация, то бинарные логи сервера будут расти как на дрожжах! При данном уровне изоляции транзакций MySQL уже не верит данным, которые вы модифицируете с помощью SQL запросов, поэтому в бинарные логи, и, соответственно, на слейвы передаются логи не в формате STATEMENT, как раньше, а в формате MIXED (binlog-format =MIXED в конфиг-файле, иначе не взлетит!), то есть в данном случае — целиком вся строка, в которой изменен хотя бы один столбец, кидается в лог.

Теперь представим, что у нас есть таблица в базе, в которой хранится какой-то большой MEDIUMTEXT, например какие-то логи хранятся в базе, наряду с другими столбцами:
CREATE TABLE `processing_logs` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `isfinished` int(11) NOT NULL,
 `text` mediumtext NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

И в коде мы делаем
mysqli_query($link, "UPDATE /*041*/ processing_logs set isfinished=1 where id=102");

В этом случае в mysql-bin.00001 лог будет добавлена вся строка, вместе с неизменившимся text, потому что гладиолус READ-COMMITTED, что приведет к тому, что 100-мегабайтный бинарный лог переполнится буквально через несколько минут работы на продакшен нагрузке.

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

Note: это справедливо для MySQL 5.5. В более новой версии базы есть опция binlog-row-image, которую можно выставить в minimal — это должно решить проблему с сильным ростом бинарных логов при каждом апдейте типа показанного выше. Но я не тестировал.

Собственно переход на InnoDB

Переходить на InnoDB мы будем путем создания новой базы данных, в которую будем копировать все таблицы старой БД. Вот этот кусок кода, кстати, делает правильный дамп базы без её остановки, и одновременно узнает master status, что нужно для запуска репликации на слейве.
select now()\G
FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only=ON;
show master status\G

\! mysqldump --add-drop-database -u root -pmypassword myclientsdb > /root/myclientsdb.sql

SET GLOBAL read_only=OFF;
select now()\G
exit

В нашем случае нужен просто SQL дамп, который мы будем загонять в новую InnoDB базу. Чтобы импорт в InnoDB прошел без проблем с превышением размера max_allowed_packet, надо выполнить эти две команды в mysql:
set global net_buffer_length=1000000; 
set global max_allowed_packet=1000000000;

Далее создаем новую базу и пользователя в ней:
create database newclientsdb;
use newclientsdb;
create user 'newclientsdb'@'localhost' identified by 'passworddb';
grant delete,insert,update,select on newclientsdb.* to 'newclientsdb'@'localhost';

И загоняем всю старую базу в новую. Люблю такую конвейеризацию, где конвертация движка базы делается на лету:
cat myclientsdb.sql | sed 's/ENGINE\=MyISAM/ENGINE\=InnoDB/g' | mysql -u root -pmypassword newclientsdb


Меняем username/password и имя базы данных в строке коннекта к БД на новую базу:
$link = mysqli_connect ($Host, $User, $Password, $DBName);

Тестируем, и если все ОК, то старую MyISAM базу можно дропать.

Вот, вроде бы, и всё.
Теги:MyISAMInnoDBMySQL
Хабы: MySQL
+7
25,1k 179
Комментарии 35
Похожие публикации
Fullstack-разработчик на JavaScript
27 ноября 202083 200 ₽SkillFactory
SMM-менеджер
27 ноября 202069 900 ₽Нетология
Интернет-маркетолог
27 ноября 202074 900 ₽Нетология
Дизайнер интерфейсов
27 ноября 202076 000 ₽GeekBrains
Основы HTML и CSS
30 ноября 2020БесплатноНетология
▇▅▄▅▅▄ ▇▄▅