Оптимизация работы с БД

[djdlexx]

Кто нибудь сталкивался с наездами хостинга за чрезмерную нагрузку на БД?

В магазине ~ 10 000 товаров

Чрезмерно медленно работают 2 запроса

model/catalog/product

1) getProductsByCategoryId

2) getProductSpecials

Запросы и вправду монструозные, есть идеи, как их упростить?

[Sammy95]

Какая версия движка? Можете выложить бэкап своей БД?

[Fix305]

если 10000 записей в БД (причем выборка идет по индексированным полям) для хостера проблема, то я бы задумался "Нужен ли мне такой хостер?"

[Sammy95]

Fix305: как раз с индексами-то в движке и проблемы. У разработчиков ocStore тупо нет на руках примера большой базы, чтобы устранить эту проблему.

[Fix305]

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

чтоб создать большую базу достаточно написать всего 1 цикл

да и основные индексы нужные стоят для 2х предыдущих функции

[Sammy95]

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

Нужен, если собираешься всё сделать как положено - с нахождением медленных запросов, исследованием их через explain и подбором наиболее оптимального набора индексов.

чтоб создать большую базу достаточно написать всего 1 цикл

Пока никто не написал.

[afwollis]

чтоб создать большую базу достаточно написать всего 1 цикл

Пока никто не написал.

кто мешает про-print-ить запросы, выполняемые при создании товаров/категорий/производителей и загнать данные из выхлопов в цикл?

[Sammy95]

afwollis: никто. Создайте и выложите готовую базу. В чём проблема?

[djdlexx]

я бы задумался "Нужен ли мне такой хостер?"

нужет, потому что хостер 1Gb.ru, больше 5 лет с ними работаю - очень доволен. второй раз за всё это время сталкиваюсь с проблемой перегруза.

У разработчиков ocStore тупо нет на руках примера большой базы, чтобы устранить эту проблему.

если вам нужно, то могу отключить фильтрацию при импорте и база будет ещё больще, тысяч эдак на 60 )))

Какая версия движка?

OC Store 0.2.0

Можете выложить бэкап своей БД?

куда заслать?

P.S. Спасибо за желание помочь )))

[Yesvik]

нужет, потому что хостер 1Gb.ru, больше 5 лет с ними работаю - очень доволен. второй раз за всё это время сталкиваюсь с проблемой перегруза.

1Gb действительно не плохой хостинг, но хитрожопый и жадный.

[Yesvik]

Чрезмерно медленно работают 2 запроса

model/catalog/product

1) getProductsByCategoryId

2) getProductSpecials

Запросы и вправду монструозные, есть идеи, как их упростить?

Проблема не совсем в монструозности запросов. Скорее всего при выводе категории или спецпредложений у тебя много товаров на одной странице.

Выкладываю пост, в котором на примере getProductsByCategoryId описано что происходит и как с этим бороться.

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

Для примера рассмотрим вывод страницы категории.

В контроллере извлекается список товаров, а потом в цикле для каждого товара извлекаются:

• Рейтинг (+1 запрос)
• Цена с дисконтом (+1 запрос)
• Если Дисконт не задан извлекается Специальная цена (+1 запрос)
• Опции (+1 запрос если опций нет)

В итоге, если дисконт и опции не заданы, получаем 4 запроса на каждый товар, а при количестве 20 товаров на страницу - получаем 80 запросов.

Вот кусок кода который я описал (этот код встречается довольно часто)

$results = $this  ->model_catalog_product ->getProductsByCategoryId($category_id, $sort, $order, ($page - 1) * $this ->config ->get('config_catalog_limit'), $this ->config ->get('config_catalog_limit'));

foreach ($results as $result) {
	if ($result['image']) {
		$image = $result['image'];
	} else {
		$image = 'no_image.jpg';
	}

	if ($this ->config ->get('config_review')) {
		$rating = $this ->model_catalog_review ->getAverageRating($result['product_id']);
	} else {
		$rating = false;
	}

	$special = FALSE;

	$discount = $this ->model_catalog_product ->getProductDiscount($result['product_id']);

	if ($discount) {
		$price = $this ->currency ->format($this ->tax ->calculate($discount, $result['tax_class_id'], $this ->config ->get('config_tax')));
	} else {
		$price = $this ->currency ->format($this ->tax ->calculate($result['price'], $result['tax_class_id'], $this ->config ->get('config_tax')));

		$special = $this ->model_catalog_product ->getProductSpecial($result['product_id']);

		if ($special) {
			$special = $this ->currency ->format($this ->tax ->calculate($special, $result['tax_class_id'], $this ->config ->get('config_tax')));
		}
	}

	$options = $this ->model_catalog_product ->getProductOptions($result['product_id']);

Теперь о запросах

Вот отформатированный для удобства обсуждения SQL запрос извлекающий список товаров категории

SELECT *, 
	pd.name AS name, 
	p.image, 
	m.name AS manufacturer, 
	ss.name AS stock, 
	(SELECT AVG(r.rating) FROM oc_review r WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating 
FROM oc_product p 
LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) 
LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) 
LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) 
LEFT JOIN oc_stock_status ss ON (p.stock_status_id = ss.stock_status_id) 
LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) 
WHERE 
	p.status = '1' AND 
	p.date_available <= NOW() AND 
	pd.language_id = '1' AND 
	p2s.store_id = '0' AND 
	ss.language_id = '1' AND 
	p2c.category_id = '18'
ORDER BY p.sort_order ASC
LIMIT 0,20

Абсолютно не понятно зачем тут подзапрос извлекающий рейтинг если это значение нигде не используется, да и значение рейтинга извлекается без учета статуса поставленной оценки.

Как минимум стоит убрать этот подзапрос, но лучше его исправить и оставить, убрав извлечение рейтинга в цикле...

Кроме рейтинга добавить в этот запрос извлечение дисконтной и специальной цен, а так-же признака наличия опций.

У нас получится вот такой запрос

SELECT *, 
	pd.name AS name, 
	p.image, 
	m.name AS manufacturer, 
	ss.name AS stock, 
	(SELECT AVG(r.rating) FROM oc_review r WHERE r.status = '1' AND p.product_id = r.product_id GROUP BY r.product_id) AS rating, 
	COUNT(po.product_id) AS options,
	pdis.price AS discount, 
	ps.price AS special, 
        p.price,
        p.product_id
FROM oc_product p 
LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) 
LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) 
LEFT JOIN oc_manufacturer m ON (p.manufacturer_id = m.manufacturer_id) 
LEFT JOIN oc_stock_status ss ON (p.stock_status_id = ss.stock_status_id) 
LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) 
LEFT JOIN oc_product_option po ON (p.product_id = po.product_id) 
LEFT JOIN oc_product_discount pdis ON (p.product_id = pdis.product_id) AND pdis.customer_group_id = '" . (int)$customer_group_id . "' AND pdis.date_start < NOW() AND (pdis.date_end = '0000-00-00' OR pdis.date_end > NOW()) AND pdis.quantity <= p.minimum 
LEFT JOIN oc_product_special ps    ON (p.product_id =   ps.product_id) AND   ps.customer_group_id = '" . (int)$customer_group_id . "' AND   ps.date_start < NOW() AND (  ps.date_end = '0000-00-00' OR   ps.date_end > NOW()) 
WHERE 
	p.status = '1' AND 
	p.date_available <= NOW() AND 
	pd.language_id = '1' AND 
	p2s.store_id = '0' AND 
	ss.language_id = '1' AND 
	p2c.category_id = '18'
GROUP BY p.product_id
ORDER BY p.sort_order ASC
LIMIT 0,20

Но в этом запросе есть один ньюанс. Цены с дисконтом и специальные извлекаются без учета приоритетов.

Я не нахожу объяснения зачем там приоритеты и стоит ли за эти приоритеты платить такую цену?

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

Но даже если признать необходимость приоритетов, то цены извлекаемые этим запросом можно использовать как признаки на основании которых принимать решение... делать запрос на извлечение дисконтной или специальной цены.

Так же и с опциями - делаем запрос только в том случае если есть что извлекать.

Код который я приводил в начале, с предложенным запросом будет примерно таким

$results = $this ->model_catalog_product ->getProductsByCategoryId($category_id, $sort, $order, ($page - 1) * $this ->config ->get('config_catalog_limit'), $this ->config ->get('config_catalog_limit'));

foreach ($results as $result) {
	if ($result['image']) {
		$image = $result['image'];
	} else {
		$image = 'no_image.jpg';
	}

	if ($this ->config ->get('config_review')) {
		$rating = round($result['rating']);
	} else {
		$rating = false;
	}

	$discount = $result['discount'];

	if ($discount) {
		$price = $this ->currency ->format($this ->tax ->calculate($discount, $result['tax_class_id'], $this ->config ->get('config_tax')));

		$special = false;
	} else {
		$price = $this ->currency ->format($this ->tax ->calculate($result['price'], $result['tax_class_id'], $this ->config ->get('config_tax')));

		$special = $result['special'];

		if ($special) {
			$special = $this ->currency ->format($this ->tax ->calculate($special, $result['tax_class_id'], $this ->config ->get('config_tax')));
		}
	}

	if ($result['options']) {
		$options = $this ->model_catalog_product ->getProductOptions($result['product_id']);
	} else {
		$options = array();
	}

Если выводится по 20 товаров на странице, плюс 10-20 товаров в сайдбарах - то можно сэкономить 100-150 запросов.

Для простеньких магазинов живущих на виртуальных хостингах это существенный момент.

[Sammy95]

если вам нужно, то могу отключить фильтрацию при импорте и база будет ещё больще, тысяч эдак на 60 )))

Чем больше будет база, тем лучше.

OC Store 0.2.0

куда заслать?

Можно на какой-нибудь бесплатный файлообменник и ссылку сюда, любо ко мне в личку.

[djdlexx]

но хитрожопый и жадный.

все они хитрожопые и жадные )) но у гебешников тех поддержка очень хорошая, а вот на ней многие хостинги экономят ))

Есть один минус - хакеры досами на них повадились в последнее время нападать больно часто ))

у тебя много товаров на одной странице.

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

Я не нахожу объяснения зачем там приоритеты и стоит ли за эти приоритеты платить такую цену?

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

Чем больше будет база, тем лучше.

Кстати могу тот самый "цикл" генерирующий большую базу замутить на основе своего скрипта импорта, сейчас на 100 000 товаров базу организую ))

[Yesvik]

вот приоритеты мне как раз могут понадобиться

Зачем? Расскажи, а то у меня уже мозги кипят от потуг придумать зачем это надо.

[dmonco]

Что Вы решили по этой проблеме? Поделитесь.

У меня ситуация такая. Хостинг - 1gb.ua (тоже самое что и 1gb.ru)

Товаров в базе - всего 1000 шт.

Сайт иногда пропадает из-за перенагрузки процессора.

Лог файл смотрел. Толком особо ничего не понял. Но я не нашел, чтобы в это время сайт индексировали какие-то кроны.

Нужна помощь, так как далек в этом вопросе.

[Yesvik]

Если сайт отрубается - значит стоит ограничение по нагрузке на процессор. У этого ограничителя есть свои логи.

В панели управления хостингом должны появляться ахтунги с информацией о перегрузках...

[dmonco]

Логи есть. Например, допустимая нагрузка 5%. Если выше 5%, вырубается. Непонятно, почему нагрузка иногда прыгает выше 5%.

[UncleAndy]

По результатам оптимизации alexxxus своей большой базы были добавлены следующие индексы:

oc_product:

KEY `model` (`model`),

KEY `stock_status_id` (`stock_status_id`),

KEY `quantity` (`quantity`,`date_available`),

KEY `tax_class_id` (`tax_class_id`,`weight_class_id`,`length_class_id`),

KEY `sort_order` (`sort_order`)

oc_product_option_description:

KEY `product_id` (`product_id`)

oc_product_option_value:

KEY `product_option_id` (`product_option_id`),

KEY `product_id` (`product_id`)

oc_url_alias:

UNIQUE KEY `query` (`query`),

KEY `keyword` (`keyword`)

oc_zone:

KEY `country_id` (`country_id`)

[dmonco]

Народ! Ещё есть у кого-то реальные примеры оптимизации запросов к базе?

Тема очень интересная, а реальных примеров нет. Делитесь.