Как ускорить выполнение запроса?

Question

[Тимур Громов]

Приветствую, подскажите кто знает.

Есть таблица myisam на 3000 записей (400 кб), периодически возникает необходимость обновить записи в таблице.
Обновляются пачками по макс 100 записей через

INSERT INTO `table` (id, field_varchar, field_int) VALUES (...)
ON DUPLICATE KEY UPDATE field_varchar=VALUES(field_varchar), field_int=VALUES(field_int)

Заметил, что если с интервалом 0.5-1 сек отправляются запросы, то примерно каждый третий запрос выполняется 3-5 сек, что очень долго, при этом остальные запросы доли секунды.

Знаю вариант, как реализовать через memcache, но все же хочется разобраться почему так происходит и как ускорить выполнение.

Answer 1

[Aleksey Ratnikov]

Тормозит, потому что MyISAM делает table lock для каждого такого запроса. Используйте InnoDB.

Comments

[Immortal_pony]

И как механизм блокирования таблицы влияет на скорость выполнения запросов?

[Aleksey Ratnikov]

А как, по вашему, он внесет изменения в таблицу, если она вся залочена вместо только тех рядов, которые нужно изменить(InnoDB)?

[Тимур Громов]

Тоже думал, что блокировка, менял движек для этой таблицы на innodb - тормозит также.

[Immortal_pony]

Aleksey, вопрос не в том, блокирована таблица или нет. Вопрос в том, почему вставка происходит так медленно. Еще рас спрашиваю, каким образом блокировка таблицы влияет на скорость вставки?

[Тимур Громов]

Вы хотите сказать, что блокировка не влияет на скорость вставки?) Я думал блокировка на все операции с записями таблицей влияет, запрос ждет пока снимется блокировка и только потом выполняется.

[Aleksey Ratnikov]

Напишите в одной сессии LOCK TABLES table_name WRITE; А в другой сессии попробуйте в эту таблицу сделать INSERT, и все станет понятно.

[Immortal_pony]

Тимур Громов, да, блокировка никак не влияет на скорость вставки. Блокировка влияет на возможность вставки, что в данном случае приводит нас к следующему вопросу:

запрос ждет пока снимется блокировка и только потом выполняется.

А предыдущий запрос чего ждет?

Aleksey, скорость вставки не изменится. Просто перед вставкой будет задержка.

[Aleksey Ratnikov]

> скорость вставки не изменится.
Совершенно верно.
Но общее время исполнения запроса будет включать в себя время ожидания снятия лока. В любом случае, автор выше отметил, что смена движка ему не помогла.

[Тимур Громов]

Immortal_pony: Под понятием "скорость" имел ввиду скорость выполнения функции в скрипте. Т.е. запрос может и выполнится за доли секунды, но если есть блокировка, запрос будет ждать ее снятия, а вместе с ним и скрипт будет ждать выполнения функции с этим запросом.
Значит все дело в блокировке.

Тогда почему при innodb тоже бывают тормоза?!
Возможно, что из-за того что при массовом update через insert запрос натыкается на одну или несколько заблокированных строк и ждет снятия блокировки, а скрипт соответственно тоже ждет пока функция завершит работу.

Теперь понял, что вы имели ввиду под slow log для этих запросов. У меня в скрипте свой локальный слоулог еще, который считает время выполнения функции, обращающейся к mysql.

[Immortal_pony]

Тимур Громов, еще раз повторюсь: если есть блокировка, значит существует запрос, который выполняется слишком долго и при том он не заблокирован. Именно поэтому смена движка ничем не помогает - эти запросы никуда не уходят. Вам необходимо определить причину медленного выполнения "изначального" запроса.
Чтоб оный запрос выявить, я предлагаю использовать встроенный механизм MySQL для выявления медленных запросов - slow_query_log. Включайте его и смотрте, какие запросы медленно выполняются. Потом тестируйте эти запорсы отдельно.
Также имеет смысл каким-либо образом убедиться, что к затруднениям приводят не какие-либо серверные проблемы.

[Immortal_pony]

Aleksey, чтобы решить проблему, надо понять ее причину. А "залочки" - лишь следствие. И потому ваш совет о смене движка не просто бесполезен в данном случае, но вреден - он лишь помешает в деле понимания причины.

[Aleksey Ratnikov]

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

[Aleksey Ratnikov]

Immortal_pony, Тимур Громов: Конкретно в данном случае не особо много вариантов: проблем с индексом тут быть не может, потому что UPDATE делает index seek для поиска дубликатов, и если это не проблема с локами таблицы нетранзакционным движком, то остаются только проблемы с медленными дисковыми операциями, из-за которых index seek может работает медленно.

Answer 2

[Immortal_pony]

Включайте slow query log и смотрите, какие конкретно запросы вызывают проблемы.

Comments

[Тимур Громов]

slow query включен всегда, в том то и дело, что запросы на обновление идентичные, а тормозит только каждый 3-5 последовательный запрос.
Может быть, что после первого запроса mysql начинает перестраивать индекс и поэтому следующий запрос тормозит, пока индекс обновляется?

[Immortal_pony]

slow query включен всегда, в том то и дело, что запросы на обновление идентичные, а тормозит только каждый 3-5 последовательный запрос.

Эти запросы видны в логе? Если склонировать таблицу и попробовать выполнить запрос для клона, то проблема сохранится?
В сервере не может быть проблемы? Попробуйте включить какой-нибудь dmesg и проверить состояние системы в моменты затруднений.

Answer 3

[Макс]

"Use INSERT DELAYED when you do not need to know when your data is written. This reduces the overall insertion impact because many rows can be written with a single disk write." может так попробовать?

Comments

[Aleksey Ratnikov]

Это Deprecated возможность, опасно ее использовать. К тому же, с INSERT .. ON DUPLICATE KEY UPDATE она не работает: The DELAYED option is ignored when you use ON DUPLICATE KEY UPDATE.( https://dev.mysql.com/doc/refman/5.6/en/insert-on-...

Answer 4

[lega]

Смотрите какая у вас нагрузка на IO, возможно затык в этом (особенно если это сервер от Hetzner).