Где искать причину такого поведения сервера баз данных?

Question

[Алексей Лауд]

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

Как можно повлиять на эту ситуацию и/или где искать причину? Если нужны какие-то конкретные данные, напишу.

UPD: проверил, не всегда это касается индексов. Иногда просиходит даже при изменении данных, не находящихся в индексе.
UPD2: запросы останавливаются на стадии updating.
UPD3: нагрузка на процессор в обычном состоянии 20-40%, во время залипания - 100%. Память всегда использована процентов на 50-60 и практически никогда не меняется.

Answer 1

[Fortop]

Первое что нужно проверить это тип таблицы.

MyISAM или InnoDB.
Второе наличие транзакции в запросах.

Поведение характерно для таблиц первого типа. У них "table level lock" для изменений в транзакции.
Т.е. блокируется ВСЯ таблица

Comments

[Алексей Лауд]

Все таблицы InnoDB.
По поводу транзацкий: не знаю. Как проверить?

[Fortop]

Алексей Казаков, во время такого "зависания"

посмотреть show processlist или show full processlist
обратить внимание на условия запросов UPDATE.
Не одни ли и те же записи обновляются

И состояние запроса, который "держит" все остальные. Там есть дополнительная информация.

Проверить факт выполнения запроса в транзакции можно по-моему только по коду

Проверить наличие триггеров или хранимых процедур используемых/вызываемых при апдейте.

Определиться с типом апдейта. Т.е. это обновление ли? Или вставка новых данных в таблицу?

[Алексей Лауд]

В списке процессов у них состояние "updating".

Записи всегда разные, но пока ловил только три типа запросов:

1. UPDATE games SET last_message_time=NOW() WHERE id= 123123 (нет индекса, нет триггеров).
2. INSERT IGNORE INTO user_ips (uid, ip) VALUES (123, 'ip.ip.ip.ip') (индексы по ip и по обоим полям, триггеров нет)
3. Такой же, как и 2, только не user_ips, а evercookie.

[Fortop]

Объем базы? Количество таких запросов в секунду?
Объем памяти на сервере?
Сколько выделено памяти под индекс?
https://stackoverflow.com/questions/11748629/how-t...

[Алексей Лауд]

Fortop, скорее всего, дело было как раз в объеме индексов.
Они занимали 5Гб, а innodb_buffer_pool_size был 4. Сделал 8, пока не повторялось.

[Fortop]

Алексей Казаков, ок.