У меня есть таблица InnoDB в базе данных Mysql 5.1, в которой около 27 миллионов строк. В этой таблице есть три неиндексированных столбца mediumint unsigned, которые я хочу периодически глобально сбрасывать на «0». Например:
update myTable set countA = 0;
Этот очень простой запрос на обновление сталкивается с проблемой блокировки InnoDB на уровне строк. После блокировки слишком большого количества строк запрос на обновление завершается ошибкой с хорошо задокументированной ошибкой:
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
Проблема в том, что в такой большой таблице количество блокировок отдельных строк превысило пространство, выделенное для хранения блокировок.
Я нашел несколько советов, как справиться с этой проблемой:
Заблокируйте всю таблицу, чтобы отключите блокировку строк
Это показалось мне лучшим и самым чистым решением, и у меня нет проблем с тем, что эта конкретная таблица блокируется на несколько минут во время этих нечастых операций. Проблема в том, что данное решение на самом деле не сработало для меня. Может быть, это то, что используется для работы со старыми версиями Mysql?
Увеличить размер буфера блокировки
Увеличив значение переменной Mysql innodb_buffer_pool_size , мы можем создать больше места для блокировок строк. Мне крайне неудобно это решение, потому что, даже если я могу выделить достаточно места, я настраиваю себя на неудачу по мере роста моих таблиц. Кроме того, это похоже на плохую настройку, которая требует создания гигабайтов, возможно, ненужных блокировок.
Индексировать затронутые столбцы ( см. комментарии)
Если мы выполняем массовое обновление одного столбца, который поддерживается соответствующим индексом, то InnoDB может избежать блокировки всех строк. Используя индекс, он может заблокировать только затронутые строки. На самом деле я попробовал это, но обнаружил, что управление этими тремя индексами значительно замедляет мои добавочные обновления. Поскольку у меня будут десятки миллионов запросов на обновление, настраивающих эти три счетчика для каждого случая необходимости сброса счетчиков, я не хочу жертвовать эффективностью добавочных обновлений.
Пакетное обновление столбца
В исходном документе это описывается как обходной путь, но я обнаружил, что до определенного момента он был очень эффективным:
update myTable set countA = 0 where countA != 0 limit 500000;
Делая это несколько раз, пока количество затронутых строк не станет меньше указанного limit, все строки будут обновлены. Это решение не помогло мне в особенно больших таблицах, так как количество строк, которые можно было бы обновить за одну итерацию, резко падает, поскольку Mysql должен искать соответствующие строки дальше. К тому времени, когда 1000 обновленных строк стало слишком много для одного выполнения, мне все еще нужно было обновить миллионы ненулевых значений.
Итак, какие возможности у меня остались?
- Прекратите использовать InnoDB: это потребует другой реорганизации моих текущих процессов, но я бы это рассмотрел.
- Переместите столбцы счетчика из основной таблицы: если у меня есть таблица CountA, я могу сбросить счетчики, используя
delete from CountA, и я могу получить счетчики с помощью внутреннего соединения с основной таблицей. Это замедлило бы мои обновления отдельных счетчиков, поскольку мне пришлось бы получать идентификатор из основной таблицы перед условным обновлением или вставкой строки в таблицу CountA. Не супер, но что-то, что я бы рассмотрел. - Что-то еще, что является одновременно чистым решением и тем, от которого можно ожидать достаточного роста с моими таблицами?
Обновление: с помощью принятого ответа у меня теперь есть реализация пакетной обработки, которая выполняет работу примерно за пять минут. Хотя я бы предпочел, чтобы пакетная обработка не была необходимой, пока не будет найдено более прямое решение. На случай, если это поможет следующему человеку наткнуться на этот вопрос, вот мой связанный код Java JDBC. (Рекомендуется также прочитать сообщение в блоге, связанное с принятым ответом.)
int batchsize = 10_000;
PreparedStatement pstmt = connection.prepareStatement
("UPDATE tableName SET countA = 0, countB = 0, countC = 0 "
+ "WHERE id BETWEEN ? AND ?");
for (int left = 0; left < maxId; left += batchsize) {
pstmt.setInt(1, left + 1);
pstmt.setInt(2, left + batchsize);
pstmt.executeUpdate();
}
pstmt.close();
