Перейти к содержанию
djdlexx

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

Рекомендуемые сообщения

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

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

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

model/catalog/product

1) getProductsByCategoryId

2) getProductSpecials

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

Поделиться сообщением


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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

Поделиться сообщением


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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

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

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

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

OC Store 0.2.0

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

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

Чрезмерно медленно работают 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 запросов.

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

OC Store 0.2.0

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

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

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

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

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

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

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

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

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

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

По результатам оптимизации 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`)

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

Для публикации сообщений создайте учётную запись или авторизуйтесь

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

Создать учетную запись

Зарегистрируйте новую учётную запись в нашем сообществе. Это очень просто!

Регистрация нового пользователя

Войти

Уже есть аккаунт? Войти в систему.

Войти

  • Последние посетители   0 пользователей онлайн

    Ни одного зарегистрированного пользователя не просматривает данную страницу

×

Важная информация

На нашем сайте используются файлы cookie и происходит обработка некоторых персональных данных пользователей, чтобы улучшить пользовательский интерфейс. Чтобы узнать для чего и какие персональные данные мы обрабатываем перейдите по ссылке. Если Вы нажмете «Я даю согласие», это означает, что Вы понимаете и принимаете все условия, указанные в этом Уведомлении о Конфиденциальности.