Блог

MySQL 5.1, Ubuntu 10.10 64bit, виртуальная машина Linode.

Все таблицы являются InnoDB.

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

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

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

Все это работает стабильно около 6 месяцев. Внезапно, 3 дня назад, время выполнения еженощного скрипта увеличилось в среднем с 40 секунд до 11 минут.

Общие пропорции сохраненных данных существенно не изменились.

Я исследовал все, что мог, и часть скрипта, которая внезапно работает медленнее, — это последняя инструкция update, которая записывает новые отображаемые значения. Он выполняется один раз для каждой строки, учитывая идентификатор строки (INT(11)) и новое отображаемое значение (также INT).

 update `table` set `display_value` = ? where `id` = ?
  

Забавно то, что очистка всех предыдущих значений выполняется как:

 update `table` set `display_value` = null
  

И этот оператор по-прежнему выполняется с той же скоростью, что и всегда.

display_value Поле не проиндексировано. id это первичный ключ. В table есть еще 4 внешних ключа, которые не изменяются ни в какой момент во время выполнения.

И последняя кривая ошибка: если я сброшу эту схему на тестовую виртуальную машину и выполню тот же скрипт, он запустится за 40 секунд, а не за 11 минут. Я не пытался перестроить схему на рабочей машине, поскольку это просто не долгосрочное решение, и я хочу понять, что здесь происходит.

Что-то не так с моими индексами? Они получают проблемы после тысяч обновлений в одних и тех же строках?

Обновить

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

 mysqlcheck -A -o -u <user> -p
  

Есть вероятность, что UPDATE оператор не будет использовать индекс в id , однако это очень маловероятно (если вообще возможно) для запроса, подобного вашему.

Есть ли вероятность, что ваша таблица заблокирована длительным параллельным запросом / DML ? Какой движок использует таблица?

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

 CREATE TEMPORARY TABLE tmp_display_values (id INT NOT NULL PRIMARY KEY, new_display_value INT);

INSERT
INTO    tmp_display_values
VALUES
(?, ?),
(?, ?),
…;

UPDATE  `table` dv
JOIN    tmp_display_values t
ON      dv.id = t.id
SET     dv.new_display_value = t.new_display_value;