База данных MySQL InnoDB 'зависает' на выборах

Пожалуйста, внимательно посмотрите на список процессов и на «показать статус innodb движка». Что вы видите ???

Все идентификаторы процессов 1,2,4,5,6,13 пытаются запустить COMMIT.

Кто все задерживает ??? Процесс с идентификатором 40 выполняет запрос к большой_таблице.

Процесс с идентификатором 40 работает в течение 33 секунд. Идентификаторы процессов 1,2,4,5,6,13 выполняются менее 33 секунд. Идентификатор процесса 40 что-то обрабатывает. В чем задержка ???

Прежде всего, запрос сталкивается с кластерным индексом large_table через MVCC .

Внутри идентификаторов процессов 1,2,4,5,6,13 находятся строки с данными MVCC, защищающими изоляцию транзакций. Идентификатор процесса 40 содержит запрос, который просматривает строки данных. Если есть индекс в поле hotspot_id, этот ключ + ключ к фактической строке из кластерного индекса должен выполнять внутреннюю блокировку. (Примечание. По замыслу, все неуникальные индексы в InnoDB содержат как ваш ключ (столбец, который вы хотели индексировать), так и ключ кластеризованного индекса). Этот уникальный сценарий, по сути, заключается в том, что «Неудержимая сила встречает неподвижный объект».

По сути, COMMIT должны ждать, пока можно будет безопасно применить изменения к large_table. Ваша ситуация не уникальна, не единичное, не редкое явление.

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

• Приведут ли эти два запроса к взаимоблокировке при последовательном выполнении?
• Проблемы с расшифровкой взаимоблокировки в журнале состояния innodb.
• Причины иногда медленных запросов?

В дополнение к этим ответам, I ответил на вопрос другого человека о тупиках в InnoDB в отношении SELECT .

Надеюсь, мои прошлые сообщения по этой теме помогут прояснить, что с вами происходит.

ОБНОВЛЕНИЕ 2011-08-25 08:10 EDT

] Вот запрос от процесса с идентификатором 40

SELECT * FROM `large_table`
WHERE (`large_table`.`hotspot_id` = 3000064)
ORDER BY discovered_at LIMIT 799000, 1000;

Два наблюдения:

• Вы выполняете «SELECT *», вам нужно получить каждый столбец? Если вам нужны только определенные столбцы, вы должны пометить их, потому что временная таблица из 1000 строк может быть больше, чем вам действительно нужно.

• Предложения WHERE и ORDER BY обычно выдают проблемы с производительностью или делают дизайн таблицы блестящим. Вам необходимо создать механизм, который ускорит сбор ключей перед сбором данных.

В свете этих двух наблюдений необходимо внести два основных изменения:

ОСНОВНОЕ ИЗМЕНЕНИЕ №1: Реорганизовать запрос

] Перепроектируйте запрос так, чтобы

1. ключи собирались из индекса
2. только 1000 или они собирались
3. и присоединялись к основной таблице

Вот новый запрос, который выполняет эти три вещи

SELECT large_table.* FROM
large_table INNER JOIN
(
    SELECT hotspot_id,discovered_at
    FROM large_table
    WHERE hotspot_id = 3000064
    ORDER BY discovered_at
    LIMIT 799000,1000
) large_table_keys
USING (hotspot_id,discovered_at);

подзапрос large_table_keys собирает 1000 необходимых вам ключей. Затем результат подзапроса ВНУТРЕННЕЕ СОЕДИНЕНИЕ с large_table. Пока что извлекаются ключи, а не целые строки. Осталось прочитать 799 000 строк. Есть лучший способ получить эти ключи, который приводит нас к ...

ОСНОВНОЕ ИЗМЕНЕНИЕ № 2: Создание индексов, поддерживающих рефакторинг запроса

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

ALTER TABLE large_table ADD INDEX hotspot_discovered_ndx (hotspot_id,discovered_at);

Почему именно этот индекс? Посмотрите на предложение WHERE. Hotspot_id - статическое значение. Это заставляет все hotspot_ids формировать последовательный список в индексе. Теперь посмотрите на предложение ORDER BY. Столбец Discover_at, вероятно, является полем DATETIME или TIMESTAMP.

Естественный порядок, который он представляет в индексе, следующий:

• Индекс содержит список hostpot_ids
• Каждый hotspot_id имеет упорядоченный список полей Discover_at

Создание этого индекса также исключает внутреннюю сортировку временных таблиц.

Пожалуйста, внесите эти два основных изменения, и вы увидите разницу во времени выполнения.

Попробуйте !!!

ОБНОВЛЕНИЕ 2011-08 -25 08:15 EDT

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

ALTER TABLE large_table ADD INDEX hotspot_discovered_ndx (hotspot_id,discovered_at);

Почему именно этот индекс? Посмотрите на предложение WHERE. Hotspot_id - статическое значение. Это заставляет все hotspot_ids формировать последовательный список в индексе. Теперь посмотрите на предложение ORDER BY. Столбец Discover_at, вероятно, является полем DATETIME или TIMESTAMP.

Естественный порядок, который он представляет в индексе, следующий:

• Индекс содержит список hostpot_ids
• Каждый hotspot_id имеет упорядоченный список полей Discover_at

Создание этого индекса также исключает внутреннюю сортировку временных таблиц.

Пожалуйста, внесите эти два основных изменения, и вы увидите разницу во времени выполнения.

Попробуйте !!!

ОБНОВЛЕНИЕ 2011-08 -25 08:15 EDT

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

ALTER TABLE large_table ADD INDEX hotspot_discovered_ndx (hotspot_id,discovered_at);

Почему именно этот индекс? Посмотрите на предложение WHERE. Hotspot_id - статическое значение. Это заставляет все hotspot_ids формировать последовательный список в индексе. Теперь посмотрите на предложение ORDER BY. Столбец Discover_at, вероятно, является полем DATETIME или TIMESTAMP.

Естественный порядок, который он представляет в индексе, следующий:

• Индекс содержит список hostpot_ids
• Каждый hotspot_id имеет упорядоченный список полей Discover_at

Создание этого индекса также исключает внутреннюю сортировку временных таблиц.

Пожалуйста, внесите эти два основных изменения, и вы увидите разницу во времени выполнения.

Попробуйте !!!

ОБНОВЛЕНИЕ 2011-08 -25 08:15 EDT

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

Это заставляет все hotspot_ids формировать последовательный список в индексе. Теперь посмотрите на предложение ORDER BY. Столбец Discover_at, вероятно, является полем DATETIME или TIMESTAMP.

Естественный порядок, который он представляет в индексе, следующий:

• Индекс содержит список hostpot_ids
• Каждый hotspot_id имеет упорядоченный список полей Discover_at

Создание этого индекса также исключает внутреннюю сортировку временных таблиц.

Пожалуйста, внесите эти два основных изменения, и вы увидите разницу во времени выполнения.

Попробуйте !!!

ОБНОВЛЕНИЕ 2011-08 -25 08:15 EDT

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

Это заставляет все hotspot_ids формировать последовательный список в индексе. Теперь посмотрите на предложение ORDER BY. Столбец Discover_at, вероятно, является полем DATETIME или TIMESTAMP.

Естественный порядок, который он представляет в индексе, следующий:

• Индекс содержит список hostpot_ids
• Каждый hotspot_id имеет упорядоченный список полей Discover_at

Создание этого индекса также исключает внутреннюю сортировку временных таблиц.

Пожалуйста, внесите эти два основных изменения, и вы увидите разницу во времени выполнения.

Попробуйте !!!

ОБНОВЛЕНИЕ 2011-08 -25 08:15 EDT

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

• Индекс содержит список hostpot_ids
• Каждый hotspot_id имеет упорядоченный список полей Discover_at

Создание этого индекса также исключает внутреннюю сортировку временных таблиц.

Пожалуйста, внесите эти два основных изменения, и вы будете увидеть разницу во времени работы.

Попробуйте !!!

ОБНОВЛЕНИЕ 2011-08-25 08:15 EDT

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

• Индекс содержит список hostpot_ids
• Каждый hotspot_id имеет упорядоченный список полей Discover_at

Создание этого индекса также исключает внутреннюю сортировку временных таблиц.

Пожалуйста, внесите эти два основных изменения, и вы будете увидеть разницу во времени работы.

Попробуйте !!!

ОБНОВЛЕНИЕ 2011-08-25 08:15 EDT

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