Этот пост я решил написать, прочитав огромное количество неправильных ответов на запрос из сабжа в интернете. По всему интернету разбросаны неграмотные или не полные ответы, в результате чего складывается впечатление о том, что смигрировать вашу базу данных на 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, потому что
Отсюда вывод — нужно отступать от классической теории реляционных баз данных, и выделять большие по размеру столбцы (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 базу можно дропать.
Вот, вроде бы, и всё.
Комментарии (35)
felicast
20.10.2015 09:15+2Какое-то двоякое ощущение от статьи: с одной стороны вроде умные вещи про непосредственно миграцию с MyISAM на InnoDB (хотя тут я не специалист). А с другой — странные php советы.
Во-первых, использование mysqli_query.
Во-вторых, for для повторения запросов. Мне кажется правильнее все-таки падать с ошибкой, а не долбить пока не получится. Ну или если и использовать такой подход, то далеко не везде.
В-третьих — не вижу никакого преимущества в использовании &$result. Только ухудшает читаемость кода.
danx
20.10.2015 09:17+1Normally, 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
danx
20.10.2015 09:48-3В-третьих — не вижу никакого преимущества в использовании &$result.
Тогда:
— Внутри функции my_mysqli_query создается локальная переменная $result.
— Ей присваивается значение, выбранное из базы (это могут быть сотни строк)
— этот объект возвращается в вызывающую функцию копирующим конструктором:
$Result = my_mysqli_query($link, $query);
— при этом происходит передача значения через стэк, а локальная переменная $result выхоит из cкоупа и разрушается (вызывается деструктор объекта).
Т.е. происходит лишняя операция создания объекта, копирования и разрушения объекта при каждом вызове my_mysqli_query().
Если конечно внутри PHP не реализованы смарт объекты с поддержкой референсов на их юсадж каунт, о чем я не знаю, поэтому полагаться на эту гипотетическую возможность не хочу. Поэтому сужу по опыту из языка С.
Кстати, если бы эти smart objects они были реализованы — то не существовало/не требовалось бы в PHP оператора передачи переменной по ссылке (&), это было бы лишним.
alekciy
20.10.2015 10:17-2Гуглиться за одну минуту: stackoverflow.com/questions/2715026/are-php5-objects-passed-by-reference
felicast
20.10.2015 10:47Ну во-первых, все объекты в php передаются по-ссылке (хотя в аналогии с С скорее в виде указателей).
Во-вторых даже если бы происходило копирование, то это явно не самое узкое место этой функции. Что-то мне подсказывает, что основное время уйдет на выполнение самого запроса.danx
20.10.2015 10:56Вопрос насчет того, как именно мне передать результат mysqli_query в мою вызывающую функцию, является наименее критичным и наименее важным, на самом деле. Я же не пишу этот код для собеседования в Гугл. Даже регистру букв даже были претензии)
Тот код был чисто для иллюстративных целей.
kaichou
20.10.2015 17:12+2Статья не про php.
Смысл кода понятен, любой пхп-шник за минуты адаптирует его под свой фреймворк/движок/стандарт за считанные минуты.
Спасибо за статью!
danx
20.10.2015 10:28Цель статьи была не в том, чтобы научить меня азам PHP. Но за ссылку спасибо.
KIVagant
20.10.2015 11:26Мне кажется, что вам стоило бы отредактировать статью, пригласив кого-нибудь в соавторы. Иначе комментировать будут не суть статьи, а способ реализации в php.
danx
20.10.2015 12:14Отредактировал… Хотя код с передачей аргумента по ссылке был эквивалентен по производительности. Но не суть.
Ставьте теперь плюсики)
svetasmirnova
20.10.2015 13:10Отсюда вывод — нужно отступать от классической теории реляцонных баз данных, и выделять большие по размеру столбцы (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%danx
20.10.2015 13:22То, чего вы добиваетесь, нужно делать установкой опции 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. Забыл сказать.
jrip
20.10.2015 13:12-2Долбить запросом, пока он не выполнится или не пройдет 10 (кстати почему именно 10?) попыток это пять.
Следующим шагом рекомендую аппаратный перезагрузчик сервака на основе анализа звука кулеров.danx
20.10.2015 13:17+1Читайте MySQL документацию, и не учите меня жить) Там написано, что именно нужно всего лишь повторить запрос. Повторный запрос попадет в другие условия на базе (дедлока уже не будет, конфликтующая транзакция уже либо выполнится, либо захватит локи), поэтому повторный запрос либо выполнится успешно сразу, либо немного посидит на локах.
10 — для перестраховки. Я же не зря логгирую это — и по логам, все дедлоки у меня резолвятся со второй попытки.svetasmirnova
20.10.2015 13:30Мне понятно зачем вы это делаете, но всё равно выглядит именно цикл странно. Было бы лучше написать, что транзакцию можно повторить и один из способов сделать это в цикле. Также объяснить почему именно столько повторений и что в другом случае их может быть, например
42
merlin-vrn
20.10.2015 13:34ага, убрать магическое число в дефайн
svetasmirnova
20.10.2015 13:41Лучше rand-ом инициализировать =)
merlin-vrn
20.10.2015 13:44Да зачем тут ранд-то? Число — впролне характеризует «надёжность» — чем больше итераций, тем выше шанс, что сервер всё-таки выполнит запрос.
Ранд тут уместен как задержка между итерациями цикла, чтобы не получилось, что два таких цикла друг друга забивают.
Кстати, в первом if ($result) можно не breakом завершать цикл, а сразу return $result. Зачем дорабатывать функцию зря? :)svetasmirnova
20.10.2015 13:51Шутка.
Просто это же зависит от приложения. Например, в одном случае мы можем повторять попытки до бесконечности, в другом до достижения какого-то timeout (или количества попыток как в статье). Соответственно возникает вопрос: почему 10, а не что-то ещё. В независимости в дефайне ли число или hard-coded.danx
20.10.2015 13:5710 — чтобы было, и чтобы было больше 2, но меньше бесконечности.
Реальное число retries = 2 (со второй попытки все происходит успешно).
Всё, разумеется, анализируется, и в случае появления 10-й попытки перезапустить транзакцию (в логах) будут применяться уже другие меры — к коду, к порядку написания запросов, и т.д.
foxkeys
20.10.2015 19:40Вообще, десятикратным автоповтором для любого дедлока вы можете сами себя заDOSить при определенной нагрузке.
Не буду утверждать, что это однозначно плохое решение, но я бы не рекомендовал для бездумного повторения. В крайнем случае — ставить не 10 а 2.
Вы же сами пишите, что всегда проходит со второй попытки, зачем десять-то делать?
И, да, в своих проектах я на дедлоках просто прерываю исполнение и откатываю всю транзакцию (радуя пользователя ошибкой, да).
Но, обязательно кидаю сообщение себе на почту и, получив такое сообщение, сразу начинаю расследование.
Любой дедлок — это ошибка в структуре данных и/или коде и должен устраняться.
stychos
20.10.2015 23:16Вот, и не лучше ли воспользоваться while'ом с break'ом по таймауту?
merlin-vrn
21.10.2015 08:44Здесь не нужен таймаут, он уже есть в БД, в логике разрешения деадлоков. Если таковой возник, БД сама по своему внутреннему таймеру выбьет обе транзакции с неудачей.
danx
21.10.2015 09:33-1Выбивается только более «легкая» транзакция, и сразу же, моментально, как только был обнаружен дедлок. Никакого таймаута там нет.
Такое чувство, что слово «deadlock» народ неправильно понимает — это не тот случай, когда две транзакции, как бараны, стоят и смотрят друг на друга часами.merlin-vrn
21.10.2015 10:14Есть подозрение, что вы и термин deadlock применяете не на месте. Потому, что это именно когда две транзакции как бараны не могут разойтись. Обеим нужны ресурсы А и Б, первая захватила А и ждёт, когда освободится Б, а вторая — захватила Б и ждёт, когда освободится А. И всё, стоят и ждут, своё держат, и никуда не продвинутся, пока кто-то не вмешается.
Я действительно погорячился, для решения проблемы действительно достаточно убить одну (любую) из них. Таймаут в свою очередь не так нужен для обнаружения дедлоков, которые можно находить анализом ситуации, как для лайвлоков, когда ситуация меняется, но не продвигается. Пример лайвлока — два человека не могут разойтись в корридоре, каждый пытается уступить дорогу другому и в результате они снова сталкиваются.
Как бы то ни было, мой основной посыл — что логика с таймаутом всё равно более уместна в коде разрешения блокировок в базе, чем в коде, который рестартует транзакции — остаётся в силе.danx
21.10.2015 11:41Ясно, статью вы читали по диагонали. Я же там специально про локи писал.
innodb_lock_wait_timeout=5
innodb_rollback_on_timeout=1
danx
20.10.2015 13:39Эмм… Там так и написано)
которая будет повторять откаченную транзакцию, например так:
affka
> траблшутинге перформанс ишшусов
че?!
garex
Лет хим спик фром хис харт ин инглиш.
kolu4iy
Реентерабельность тоже не совсем то, что подразумевает автор…
danx
да, меня поправили, правильный термин — идемпотентный. Обновил в посте.