Оптимизация mysql-запроса в Prestashop

mysql

Разместив сайт на хостинге timeweb, столкнулся с проблемой перерасхода лимита разрешенного времени mysql-запросов.

У клиента лимит на его тарифном плане составляет 1000cp в сутки (это какие-то их внутренние единицы расхода процессорного времени).

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

И т.к. до этого никаких претензий с их стороны не было, решил проигнорировать требования, проанализировав логи запросов целиком к сайту. Судя по логам apache, шел парсинг сайта с нескольких IP-адресов — перебирались все страницы подряд, без запросов картинок. А картинки в Prestashop обрабатываются именно сервером apache, даже при наличии nginx в качестве front-сервера.

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

На тот момент у заказчика был куплен аккаунт у другого хостера — netangels.ru. Совместно с заказчиком приняли решение переехать на этот аккаунт.

Помимо того, что у timeweb ведется подсчет нагрузки на сервер mysql, у них еще и стоит ограничение на нагрузку аккаунта. Из-за этого не получилось создать полный архив сайта. Пришлось паковать и перетаскивать кусочками, по несколько папок за один раз.

В итоге сайт запустился на новом для меня хостинге. До этого ни разу с ними не работал. Поэтому и не знал, как поведет себя на новом месте. По ощущениям, сервер работает несколько медленне, чем сервер timeweb. Время на формирование и отдачу страниц увеличилось примерно процентов на 20. Но пока это не критично. Формирование самых больших категорий сайта сейчас происходит за максимум 5-6 сек. Но чтобы такого добиться, пришлось немного «попотеть мозгу».

Итак, по-порядку.

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

Служба поддержки хостинга порадовала оперативными ответами по существу вопросов, дополнительной подстройкой сервера под мои просьбы (хотя на сервере находится больше 700 клиентских аккаунтов), вывела мне лог «длинных» запросов, которые выполняются дольше 1 минуты.

На самом деле, страница с самым большим количеством товаров http://trubio.ru/1458-muzhchiny, формировалась около 3 минут, и, часто, блокировала работу всего сайта. В это время другим пользователям не удавалось загрузить никакие другие страницы. Возможно и сайты других клиентов в это же время не отрабатывали нормально. Не было возможности проверить, т.к. на сервере они недоступны для просмотра, а искать сайты на том же IP, чтобы просто посмотреть, было лень. ))

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

Скачал скрипт и базу к себе на локалку целиком, чтоб искать причину уже на своей рабочей машинке. Так вот, установленный apache+mysql под виндой, работает раз в 20 медленнее, чем под линуксом в сети. 🙂 Та же страница, которая формируется на сервере около 3 минут, у меня на локалке не смогла сформироваться за 30. Что еще раз подтвердило мое отрицательное мнение об использовании этой операционной системы в вебе. Хотя… может быть я не умею готовить.

Проанализировав лог «долгих» запросов, нашел запрос, который на локалке формировался больше 3 минут и потом отваливался по таймауту. Специально сделал такое ограничение, чтоб не перезапускать апач, в случае более длительной работы.

Порывшись в коде и поискав всеми возможными способами запрос, нашел модуль, ответственный за данный трабл — blocklayered. Этот модуль как раз и формирует страницу с отображаемыми товарами на сайте, обрабатывает все задаваемые параметры фильтра и пр.

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

Настала очередь найти его тонкое место. Т.к. товаров всего чуть больше 400 и, исходя из своего опыта, запросы с выборкой в 400 строк должны были обрабатываться очень быстро. Сначала была мысль, что возможно какая-то проблема с индексами. Просмотрел все используемые в запросе таблицы на их наличие и корректность. Все было на своих местах.

Значит, проблема должна быть в некорректно написанном самом запросе. Начал поочередно отключать отбираемые поля, конструкции join, чтоб найти то, что и заставляло запрос так долго обрабатываться. В итоге определил, что перед последними выборками MAX(product_attribute_shop.id_product_attribute) и limit 0,21, в результате оказывается около 15 млн. записей. Да-да. Большое количество значений атрибутов приводит к бешенному росту требуемых ресурсов. Это не только требует большого процессорного времени, но и существенного расхода оперативной памяти.

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

После доработки запрос стал чуть длинее, немного сложнее в понимании, но результат мне понравился. 🙂

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

SELECT
	p.*,
	product_shop.*,
	product_shop.id_category_default,
	pl.*,
	MAX(image_shop.`id_image`) id_image,
	il.legend,
	m.name manufacturer_name,
	MAX(product_attribute_shop.id_product_attribute) id_product_attribute,
	DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(), INTERVAL '.(int)$nb_day_new_product.' DAY)) > 0 AS new,
	stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity

FROM '._DB_PREFIX_.'category_product cp
LEFT JOIN '._DB_PREFIX_.'category c ON (c.id_category = cp.id_category)
LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
'.Shop::addSqlAssociation('product', 'p').'
LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (p.`id_product` = pa.`id_product`)
'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1').'
'.Product::sqlStock('p', 'product_attribute_shop', false, Context::getContext()->shop).'
LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (pl.id_product = p.id_product'.Shop::addSqlRestrictionOnLang('pl').' AND pl.id_lang = '.(int)$cookie->id_lang.')
LEFT JOIN `'._DB_PREFIX_.'image` i  ON (i.`id_product` = p.`id_product`)'.
Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$cookie->id_lang.')
LEFT JOIN '._DB_PREFIX_.'manufacturer m ON (m.id_manufacturer = p.id_manufacturer)


WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog")
AND '.(Configuration::get('PS_LAYERED_FULL_TREE') ? 'c.nleft >= '.(int)$parent->nleft.' AND c.nright <= '.(int)$parent->nright : 'c.id_category = '.(int)$id_parent).'
AND c.active = 1
AND p.id_product IN ('.implode(',', $product_id_list).')

GROUP BY product_shop.id_product
ORDER BY '.Tools::getProductsOrder('by', Tools::getValue('orderby'), true).' '.Tools::getProductsOrder('way', Tools::getValue('orderway')).' 
LIMIT '.(((int)$this->page - 1) * $n.','.$n));

А теперь то, что получилось после переработки:

SELECT
	p.*,
	pl.*,
	MAX(image_shop.`id_image`) id_image,
	il.legend,
	m.name manufacturer_name,
	MAX(product_attribute_shop.id_product_attribute) id_product_attribute,
	stock.out_of_stock, 
	IFNULL(stock.quantity, 0) as quantity
	
FROM (
	select 
		p2.*,
		product_shop.id_shop,
		DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(), INTERVAL '.(int)$nb_day_new_product.' DAY)) > 0 AS new,
		cp.position
		
	from ps_category_product cp
	LEFT JOIN ps_category c ON (c.id_category = cp.id_category)
	LEFT JOIN `ps_product` p2 ON p2.`id_product` = cp.`id_product`
	'.Shop::addSqlAssociation('product', 'p2').'
	where
		p2.id_product IN ('.implode(',', $product_id_list).')
		and product_shop.`active` = 1
		and product_shop.`visibility` IN ("both", "catalog")
		and '.(Configuration::get('PS_LAYERED_FULL_TREE') ? 'c.nleft >= '.(int)$parent->nleft.' AND c.nright <= '.(int)$parent->nright : 'c.id_category = '.(int)$id_parent).'
		AND c.active = 1
	GROUP BY p2.id_product
	ORDER BY '.Tools::getProductsOrder('by', Tools::getValue('orderby'), true).' '.Tools::getProductsOrder('way', Tools::getValue('orderway')).' 
	LIMIT '.(((int)$this->page - 1) * $n.','.$n) .'
) p
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) 
'.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1').'
'.Product::sqlStock('p', 'product_attribute_shop', false, Context::getContext()->shop).'
LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product'.Shop::addSqlRestrictionOnLang('pl').'  AND pl.id_lang = '.(int)$cookie->id_lang.')
LEFT JOIN `ps_image` i  ON (i.`id_product` = p.`id_product`) 
'.Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
LEFT JOIN `ps_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$cookie->id_lang.')
LEFT JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)

GROUP BY p.id_product
ORDER BY '.str_replace('cp.', 'p.', Tools::getProductsOrder('by', Tools::getValue('orderby'), true)).' '.Tools::getProductsOrder('way', Tools::getValue('orderway'))

т.е. добавление всего одного вложенного запроса, в котором сокращается выборка до нужных мне итоговых 21 позиции товара, отображаемых на странице, и дальнейшее добавление в результат остальных параметров выборки, сократило время обработки такого запроса с времени около 3 мин до всего лишь 0.06 сек.

После этого сайт «полетел».

Так что если кому-то еще пригодится, буду рад. Если же сами боитесь править скрипты, запросы и не знаете к кому обратиться, обращайтесь ко мне. Помогу с этой проблемой.

Теперь нужно подождать следующих суток, чтоб посмотреть появляются ли еще какие-то запросы в логе. И, если появляются, так же оптимизировать и их.

PS. Разочаровался я в последнее время в timeweb. Раньше всем рекомендовал этого хостера, как образец для остальных. А теперь, в свете последних событий, поменял свое мнение и больше такого делать не буду. На данный момент они утратили свои преимущества. Жалко, конечно.

Добавить комментарий

или войти с помощью: 

Ваш e-mail не будет опубликован. Обязательные поля помечены *