Доброго всем дня.

Мой работодатель собирается переносить существующую систему (интернет магазин) на новую инфраструктуру (load balancer + несколько узлов с веб и mysql серверами). В целом все уже работает на новом месте, но при открытии определенных продуктов возникает странная задержка при выполнении одного запроса. Вот лог запроса:
[2020-06-09 12:14:36] Query:
SELECT IFNULL(pvtb.price, IFNULL(pv.price, p2b.products_price)), IFNULL(pvtb.special_price, pv.special_price), pv.status
FROM products p
JOIN products_to_brands p2b ON p2b.products_id = p.products_id AND p2b.brands_id = '3'
LEFT JOIN products_variants pv ON p.products_id = pv.products_id AND pv.hash = '47991{1}607{2}95{3}58'
LEFT JOIN products_variants_to_brands pvtb ON pvtb.variant_id = pv.id AND pvtb.brand_id = '3'
WHERE p.products_id = '47991'

Executed for 53.521 seconds

При выполнении того же запроса через phpmyadmin все ок:
Showing rows 0 - 0 (1 total, Query took 0.0059 seconds.)

Т.е. проблема возникает только когда этот запрос вызывается из php кода (вместе с кучей других). Кеширование запросов включено, но проблемы не решает - при обновлении страницы зависание происходит на этом же запросе. Причем время зависания практически постоянно - 53-54 секунды.
План выполнения запроса ничего плохого не показывает:
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra 1SIMPLEpconstPRIMARYPRIMARY4const1Using index1SIMPLEp2bconstPRIMARY,products_idPRIMARY8const,const1 1SIMPLEpvconsthash,products_idhash767const1 1SIMPLEpvtbconstPRIMARY,variant_id,brand_idPRIMARY8const,const1

На моем локальном компе тот же продукт(запрос) открывается без задержки. Поэтому просто нет идей, что может быть причиной. Сисадмин той системы говорит, что никаких пиковых нагрузок при обновлении страницы не возникает, загрузка процессора 0.3% на всех процессах. Может, кто сталкивался с подобным или может подсказать, хоть в каком направлении копать?

Версии ПО на новом сервере:
Server version: 10.4.13-MariaDB-1:10.4.13
Database client version: libmysql - mysqlnd 5.0.12-dev
PHP version: 7.2.31

Что еще заметил по логу медленных запросов - задержка возникает даже не на определенном запросе, а на определенном месте php кода. Вот этот кусок кода:



Так вот в лог медленных запросов иногда попадает то первый, то второй запрос. Оба при выполнении напрямую выполняются моментально. Т.е. проблема даже не в самом запросе, а в том, что mysql в какой-то примерно один и тот же момент времени начинает "втыкать" около 53-54 секунд. Может, есть какое-то ограничение на кол-во запросов в одном коннекте и при его превышении mysql начинает задерживать их выполнение?

Alex_Ustinov,

Да, индексы все есть. А как "моментально" я имею в виду время < 0.01сек. И если бы проблема была в запросе, то задержка была бы всегда именно на одном этом запросе. А тут ситуация такова, что одна и та же задержка "прыгает" то на один, то на другой запрос. Т.е. впечатление такое, что задержка происходит на определенном номере запроса в коннекте. А "прыжок" между запросами может быть связан с каким-нибудь дополнительным запросом при генерации страницы (например, переключение языка, обновление кеша и т.п.).

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

Alex_Ustinov,

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

miksoft,

query_cache_size = 64M

запрос без кеша:
SELECT SQL_NO_CACHE IFNULL(pvtb.price, IFNULL(pv.price, p2b.products_price)), IFNULL(pvtb.special_price, pv.special_price), pv.status FROM products p JOIN products_to_brands p2b ON p2b.products_id = p.products_id AND p2b.brands_id = '3' LEFT JOIN products_variants pv ON p.products_id = pv.products_id AND pv.hash = '47991{1}607{2}95{3}58' LEFT JOIN products_variants_to_brands pvtb ON pvtb.variant_id = pv.id AND pvtb.brand_id = '3' WHERE p.products_id = '47991'

Query took 0.0017 seconds

Сервер пока что еще только готовится к запуску, т.е. большой статистики использования пока нет, но кроме этой странной задержки вроде других проблем не замечено.

Все, вопрос решился.

Сисадмин сообщил, что это была проблема буфера в load balancer сервера. Т.е. как я и предполагал, с самими запросами это не было связано.