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

    Давайте я отвлеку вас от котиков и расскажу, основываясь на своём опыте, какие подводные камни появляются при переходе с 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 базу можно дропать.

    Вот, вроде бы, и всё.

    Похожие публикации

    Средняя зарплата в IT

    110 000 ₽/мес.
    Средняя зарплата по всем IT-специализациям на основании 8 550 анкет, за 2-ое пол. 2020 года Узнать свою зарплату
    Реклама
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее

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

      +15
      > траблшутинге перформанс ишшусов
      че?!
        +4
        Лет хим спик фром хис харт ин инглиш.
          +1
          Реентерабельность тоже не совсем то, что подразумевает автор…
            0
            да, меня поправили, правильный термин — идемпотентный. Обновил в посте.
          +2
          Какое-то двоякое ощущение от статьи: с одной стороны вроде умные вещи про непосредственно миграцию с MyISAM на InnoDB (хотя тут я не специалист). А с другой — странные php советы.
          Во-первых, использование mysqli_query.
          Во-вторых, for для повторения запросов. Мне кажется правильнее все-таки падать с ошибкой, а не долбить пока не получится. Ну или если и использовать такой подход, то далеко не везде.
          В-третьих — не вижу никакого преимущества в использовании &$result. Только ухудшает читаемость кода.
            +1
            Ах ну да, еще и переменные то с маленькой буквы, то с большой.
              +1
              Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.

              dev.mysql.com/doc/refman/5.5/en/innodb-deadlocks.html
                –3
                В-третьих — не вижу никакого преимущества в использовании &$result.

                Тогда:
                — Внутри функции my_mysqli_query создается локальная переменная $result.
                — Ей присваивается значение, выбранное из базы (это могут быть сотни строк)
                — этот объект возвращается в вызывающую функцию копирующим конструктором:
                $Result = my_mysqli_query($link, $query);
                — при этом происходит передача значения через стэк, а локальная переменная $result выхоит из cкоупа и разрушается (вызывается деструктор объекта).

                Т.е. происходит лишняя операция создания объекта, копирования и разрушения объекта при каждом вызове my_mysqli_query().
                Если конечно внутри PHP не реализованы смарт объекты с поддержкой референсов на их юсадж каунт, о чем я не знаю, поэтому полагаться на эту гипотетическую возможность не хочу. Поэтому сужу по опыту из языка С.
                Кстати, если бы эти smart objects они были реализованы — то не существовало/не требовалось бы в PHP оператора передачи переменной по ссылке (&), это было бы лишним.
                  –2
                    0
                    Ну во-первых, все объекты в php передаются по-ссылке (хотя в аналогии с С скорее в виде указателей).
                    Во-вторых даже если бы происходило копирование, то это явно не самое узкое место этой функции. Что-то мне подсказывает, что основное время уйдет на выполнение самого запроса.
                      0
                      Вопрос насчет того, как именно мне передать результат mysqli_query в мою вызывающую функцию, является наименее критичным и наименее важным, на самом деле. Я же не пишу этот код для собеседования в Гугл. Даже регистру букв даже были претензии)
                      Тот код был чисто для иллюстративных целей.
                    +2
                    Статья не про php.
                    Смысл кода понятен, любой пхп-шник за минуты адаптирует его под свой фреймворк/движок/стандарт за считанные минуты.

                    Спасибо за статью!
                    0
                    Цель статьи была не в том, чтобы научить меня азам PHP. Но за ссылку спасибо.
                      0
                      Мне кажется, что вам стоило бы отредактировать статью, пригласив кого-нибудь в соавторы. Иначе комментировать будут не суть статьи, а способ реализации в php.
                        0
                        Отредактировал… Хотя код с передачей аргумента по ссылке был эквивалентен по производительности. Но не суть.
                        Ставьте теперь плюсики)
                      0
                      Отсюда вывод — нужно отступать от классической теории реляцонных баз данных, и выделять большие по размеру столбцы (MEDIUMTEXT например) в отдельные таблицы, если данные в них меняются реже, чем остальные атрибуты в этой сущности.


                      Ну вообще в выделении больших по размеру столбцов в отдельные таблицы я не вижу ничего плохого, окромя хорошего. Особенно если в «основной» таблице у вас много полей. Только к размеру row image в binary log это отношения не имеет. То, чего вы добиваетесь, нужно делать установкой опции binlog-row-image либо в minimal, либо в noblob.

                      innodb_log_file_size к «подводному камню №3» отношения тоже не имеет. Это не тоже самое, что binary log. И формат лога в данном случае называется ROW, а не MIXED. MIXED значит, что мы всё, что можно, пишем в STATEMENT, а что нельзя — в ROW.

                      # этот размер выставляем в 50-80% от размера всей оперативной памяти у сервера БД.
                      innodb_buffer_pool_size = 512M


                      Этот совет взят из мануала или шаблона конфигурации по умолчанию, но на данный момент ведутся разговоры, что он устарел в связи с резко возросшим количеством памяти на боевых серверах. Правильный совет: innodb_buffer_pool_size дожен вмещать все активные данные (грубо говоря суммарный размер таблиц, которыми вы пользуетесь). И вот если он больше размера доступной памяти, тогда пользоваться правилом 50-80%
                        0
                        То, чего вы добиваетесь, нужно делать установкой опции binlog-row-image
                        Introduced 5.6.2
                        In MySQL 5.5 and earlier, full row images are always used for both before images and after images.

                        а у меня база 5.5.44. Забыл сказать.
                          0
                          Хорошо бы всё равно добавить для читателей статьи.
                            0
                            Добавлено.
                        –2
                        Долбить запросом, пока он не выполнится или не пройдет 10 (кстати почему именно 10?) попыток это пять.
                        Следующим шагом рекомендую аппаратный перезагрузчик сервака на основе анализа звука кулеров.
                          +1
                          Читайте MySQL документацию, и не учите меня жить) Там написано, что именно нужно всего лишь повторить запрос. Повторный запрос попадет в другие условия на базе (дедлока уже не будет, конфликтующая транзакция уже либо выполнится, либо захватит локи), поэтому повторный запрос либо выполнится успешно сразу, либо немного посидит на локах.
                          10 — для перестраховки. Я же не зря логгирую это — и по логам, все дедлоки у меня резолвятся со второй попытки.
                            0
                            Мне понятно зачем вы это делаете, но всё равно выглядит именно цикл странно. Было бы лучше написать, что транзакцию можно повторить и один из способов сделать это в цикле. Также объяснить почему именно столько повторений и что в другом случае их может быть, например 42
                              0
                              ага, убрать магическое число в дефайн
                                0
                                Лучше rand-ом инициализировать =)
                                  0
                                  Да зачем тут ранд-то? Число — впролне характеризует «надёжность» — чем больше итераций, тем выше шанс, что сервер всё-таки выполнит запрос.
                                  Ранд тут уместен как задержка между итерациями цикла, чтобы не получилось, что два таких цикла друг друга забивают.

                                  Кстати, в первом if ($result) можно не breakом завершать цикл, а сразу return $result. Зачем дорабатывать функцию зря? :)
                                    +1
                                    Теория одного return из функции. Старая школа.
                                      0
                                      Шутка.

                                      Просто это же зависит от приложения. Например, в одном случае мы можем повторять попытки до бесконечности, в другом до достижения какого-то timeout (или количества попыток как в статье). Соответственно возникает вопрос: почему 10, а не что-то ещё. В независимости в дефайне ли число или hard-coded.
                                        0
                                        10 — чтобы было, и чтобы было больше 2, но меньше бесконечности.
                                        Реальное число retries = 2 (со второй попытки все происходит успешно).

                                        Всё, разумеется, анализируется, и в случае появления 10-й попытки перезапустить транзакцию (в логах) будут применяться уже другие меры — к коду, к порядку написания запросов, и т.д.
                                          0
                                          Вообще, десятикратным автоповтором для любого дедлока вы можете сами себя заDOSить при определенной нагрузке.
                                          Не буду утверждать, что это однозначно плохое решение, но я бы не рекомендовал для бездумного повторения. В крайнем случае — ставить не 10 а 2.
                                          Вы же сами пишите, что всегда проходит со второй попытки, зачем десять-то делать?

                                          И, да, в своих проектах я на дедлоках просто прерываю исполнение и откатываю всю транзакцию (радуя пользователя ошибкой, да).
                                          Но, обязательно кидаю сообщение себе на почту и, получив такое сообщение, сразу начинаю расследование.
                                          Любой дедлок — это ошибка в структуре данных и/или коде и должен устраняться.
                                        • НЛО прилетело и опубликовало эту надпись здесь
                                            0
                                            Здесь не нужен таймаут, он уже есть в БД, в логике разрешения деадлоков. Если таковой возник, БД сама по своему внутреннему таймеру выбьет обе транзакции с неудачей.
                                              –1
                                              Выбивается только более «легкая» транзакция, и сразу же, моментально, как только был обнаружен дедлок. Никакого таймаута там нет.
                                              Такое чувство, что слово «deadlock» народ неправильно понимает — это не тот случай, когда две транзакции, как бараны, стоят и смотрят друг на друга часами.
                                                0
                                                Есть подозрение, что вы и термин deadlock применяете не на месте. Потому, что это именно когда две транзакции как бараны не могут разойтись. Обеим нужны ресурсы А и Б, первая захватила А и ждёт, когда освободится Б, а вторая — захватила Б и ждёт, когда освободится А. И всё, стоят и ждут, своё держат, и никуда не продвинутся, пока кто-то не вмешается.

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

                                                Как бы то ни было, мой основной посыл — что логика с таймаутом всё равно более уместна в коде разрешения блокировок в базе, чем в коде, который рестартует транзакции — остаётся в силе.
                                                  0
                                                  Ясно, статью вы читали по диагонали. Я же там специально про локи писал.

                                                  innodb_lock_wait_timeout=5
                                                  innodb_rollback_on_timeout=1
                                    0
                                    Эмм… Там так и написано)
                                    которая будет повторять откаченную транзакцию, например так:

                              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                              Самое читаемое