|
|
Задавайте вопросы, мы ответим
Вы не зашли.
У меня магазин на Opencart 1.5.4.1
На локальном сервере нашел медленный запрос:
# Time: 131013 18:59:57
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 11.390625 Lock_time: 0.000000 Rows_sent: 10 Rows_examined: 4413
use magazin;
SET timestamp=1381683597;
SELECT p.product_id, (SELECT AVG(rating) AS total FROM review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p2c.category_id = '20' GROUP BY p.product_id ORDER BY p.quantity DESC, LCASE(pd.name) DESC LIMIT 0,10;
Я не специалист в этом вообще.
Кто может помочь в этом.
Сайт действующий, но периодически падает - 503 ошибка.
Времени читать документацию нет.
Буду рад любой толковой помощи.
Если предоставил мало информации, то могу чего-нбудь добавить по запросу.
Неактивен
Покажите результат
explain ваш запрос;
и структуру всех входящих в него таблиц, т.е.
show create table product;
и т.д.
Неактивен
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY p2s index PRIMARY PRIMARY 8 NULL 1323 Using where; Using index; Using temporary; Using filesort
1 PRIMARY pd eq_ref PRIMARY PRIMARY 8 cdroom_mail_ru_.p2s.product_id,const 1 Using where
1 PRIMARY p eq_ref product_id product_id 4 cdroom_mail_ru_.p2s.product_id 1 Using where
1 PRIMARY p2c eq_ref PRIMARY PRIMARY 8 cdroom_mail_ru_.p.product_id,const 1 Using where; Using index
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Table Create Table
product CREATE TABLE `product` (
`product_id` int(11) NO...
Можно было фоткой выложить, но я не понял как это здесь сделать.
Неактивен
Вторая часть более полная
product CREATE TABLE `product` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`model` varchar(64) NOT NULL,
`sku` varchar(64) NOT NULL,
`upc` varchar(12) NOT NULL,
`ean` varchar(14) NOT NULL,
`jan` varchar(13) NOT NULL,
`isbn` varchar(13) NOT NULL,
`mpn` varchar(64) NOT NULL,
`location` varchar(128) NOT NULL,
`quantity` int(4) NOT NULL DEFAULT '0',
`stock_status_id` int(11) NOT NULL,
`image` varchar(255) DEFAULT NULL,
`manufacturer_id` int(11) NOT NULL,
`shipping` tinyint(1) NOT NULL DEFAULT '1',
`price` decimal(15,4) NOT NULL DEFAULT '0.0000',
`points` int(8) NOT NULL DEFAULT '0',
`tax_class_id` int(11) NOT NULL,
`date_available` date NOT NULL,
`weight` decimal(15,8) NOT NULL DEFAULT '0.00000000',
`weight_class_id` int(11) NOT NULL DEFAULT '0',
`length` decimal(15,8) NOT NULL DEFAULT '0.00000000',
`width` decimal(15,8) NOT NULL DEFAULT '0.00000000',
`height` decimal(15,8) NOT NULL DEFAULT '0.00000000',
`length_class_id` int(11) NOT NULL DEFAULT '0',
`subtract` tinyint(1) NOT NULL DEFAULT '1',
`minimum` int(11) NOT NULL DEFAULT '1',
`sort_order` int(11) NOT NULL DEFAULT '0',
`status` tinyint(1) NOT NULL DEFAULT '0',
`date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`viewed` int(5) NOT NULL DEFAULT '0',
UNIQUE KEY `product_id` (`product_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1401 DEFAULT CHARSET=utf8
Неактивен
А ещё покажите так же структуру остальных таблиц (фоткой лучше не надо).
product_description
product_to_store
product_to_category
review
Неактивен
Table Create Table
product_description CREATE TABLE `product_description` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`language_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`description` text NOT NULL,
`meta_description` varchar(255) NOT NULL,
`meta_keyword` varchar(255) NOT NULL,
`seo_title` varchar(255) NOT NULL,
`seo_h1` varchar(255) NOT NULL,
`tag` text NOT NULL,
PRIMARY KEY (`product_id`,`language_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1401 DEFAULT CHARSET=utf8
Table Create Table
product_to_store CREATE TABLE `product_to_store` (
`product_id` int(11) NOT NULL,
`store_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`product_id`,`store_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
Table Create Table
product_to_category CREATE TABLE `product_to_category` (
`product_id` int(11) NOT NULL,
`category_id` int(11) NOT NULL,
`main_category` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`product_id`,`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
Table Create Table
review CREATE TABLE `review` (
`review_id` int(11) NOT NULL AUTO_INCREMENT,
`product_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`author` varchar(64) NOT NULL DEFAULT '',
`text` text NOT NULL,
`rating` int(1) NOT NULL,
`status` tinyint(1) NOT NULL DEFAULT '0',
`date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`review_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
Неактивен
А этот запрос всегда выполняется долго или это разовое попадание в лог медленных?
Из того что можно предложить по самому запросу:
1. заменить все left join на join
2. разбить запрос на 2
сначала найти 10 нужных product_id (т.е. ваш запрос без подзапроса, который считает рейтинг). Затем отдельно посчитать рейтинг для 10 нужных product_id
Неактивен
Честно говоря я даже не знаю куда и как прописывать эти запросы, чтоб они постоянно там обрабатывались.
По поводу всегда или нет не знаю, но думаю, что и из-за него тоже, т.к. страница может загрузиться за 10 сек, а может и за 20-25, это то что я первое пока нашел.
На странице 15 фоток товара, кнопка купить,быстрый показ, цена.
Еще через firebug смотрел загрузку страницы продуктов.
Вот 2 медленные:
Это я так понял переход на страницу товаров категории в начале, длится 8-9 секунд (Get index...).
Заголовки ответапоказать исходный код
Cache-Control no-store, no-cache, must-revalidate, post-check=0, pre-check=0
Connection Keep-Alive
Content-Type text/html; charset=windows-1251
Date Tue, 15 Oct 2013 14:15:26 GMT
Expires Thu, 19 Nov 1981 08:52:00 GMT
Keep-Alive timeout=5, max=94
Pragma no-cache
Server Apache/2.2.22 (Win32) mod_ssl/2.2.22 OpenSSL/1.0.1c PHP/5.3.13
Transfer-Encoding chunked
X-Powered-By PHP/5.3.13
Заголовки запросапоказать исходный код
Accept text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Accept-Encoding gzip, deflate
Accept-Language ru-RU,ru;q=0.8,en-US;q=0.5,en;q=0.3
Connection keep-alive
Cookie vc=4; language=ru; currency=RUB; __gads=ID=9571e3a080b8ab8c:T=1381831191:S=ALNI_MaIUTuBf8fyRHxwfS2COJtbkGXRNA; PHPSESSID=4obecn67vupplj7e5k3mk1gap5
Host localhost
Referer http://localhost/magazin/
User-Agent Mozilla/5.0 (Windows NT 5.1; rv:24.0) Gecko/20100101 Firefox/24.0
И в конце закрывается 9 секунд:
Заголовки ответапоказать исходный код
Cache-Control no-store, no-cache, must-revalidate, post-check=0, pre-check=0
Connection Keep-Alive
Content-Type text/html; charset=windows-1251
Date Tue, 15 Oct 2013 14:15:40 GMT
Expires Thu, 19 Nov 1981 08:52:00 GMT
Keep-Alive timeout=5, max=90
Pragma no-cache
Server Apache/2.2.22 (Win32) mod_ssl/2.2.22 OpenSSL/1.0.1c PHP/5.3.13
Transfer-Encoding chunked
X-Powered-By PHP/5.3.13
Заголовки запросапоказать исходный код
Accept image/png,image/*;q=0.8,*/*;q=0.5
Accept-Encoding gzip, deflate
Accept-Language ru-RU,ru;q=0.8,en-US;q=0.5,en;q=0.3
Connection keep-alive
Cookie vc=4; language=ru; currency=RUB; __gads=ID=9571e3a080b8ab8c:T=1381831191:S=ALNI_MaIUTuBf8fyRHxwfS2COJtbkGXRNA; PHPSESSID=4obecn67vupplj7e5k3mk1gap5
Host localhost
Referer http://localhost/magazin/index.php?route=product/category&path=20
User-Agent Mozilla/5.0 (Windows NT 5.1; rv:24.0) Gecko/20100101 Firefox/24.0
Неактивен
Есть еще один медленный запрос:
# Time: 131013 19:42:22
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 6.625000 Lock_time: 0.000000 Rows_sent: 111 Rows_examined: 111
SET timestamp=1381686142;
SHOW TABLE STATUS FROM `magazin`;
Неактивен
Если вы в клиенте mysql выполните ваш медленный запрос (SELECT p.product_id, (SELECT AVG(rating) ...) сколько он будет выполняться?
И какая у вас версия сервера MySQL, что показывает команда select version();
Неактивен
0.03-0.045с , но бывает и 0.8-2 с (%10).
5.5.25
Неактивен
Т.е. у вас не сам запрос медленный, а медленный из-за внешних причин. Нужно искать узкие места, см., например, ботанический определитель узких мест.
И ещё покажите show global variables; и show global status;
Неактивен
2 медленных запроса я убрал в CDN - jquery.js
Стало немного лучше.
Мой хостер посоветовал мне изменить на это.
Единственное, я не знаю как и куда это записывать, чтобы обрабатывалось постоянно.
Могли бы Вы мне пошагово объяснить как это сделать, и чтоб старый запрос не выполнялся?
SELECT
p.product_id,
AVG(rating) AS total
FROM product p
LEFT JOIN product_description pd ON (p.product_id = pd.product_id)
LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id)
LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id)
LEFT JOIN review r1 ON (p.product_id = r1.product_id AND r1.status = '1')
WHERE
pd.language_id = '1' AND
p.status = '1' AND
p.date_available <= NOW() AND
p2s.store_id = '0' AND
p2c.category_id = '20'
GROUP BY p.product_id, r1.rating
ORDER BY p.quantity DESC, LCASE(pd.name) DESC LIMIT 0,10;
Он будет работать быстрее предыдущего запроса с вложенным SELECT.
Кроме того, мы бы посоветовали вынести значение NOW() в какую-либо переменную, объявленную до выполнения запроса. В этом случае вызов функции будет сделан один раз, а не каждый раз при сравнении строк (при проверке критериев в конструкции WHERE).
Также, в общем случае следует избегать выполнения сортировки на уровне базы данных - лучше выполнять ее на уровне вызывающего кода. Поэтому строку:
ORDER BY p.quantity DESC, LCASE(pd.name) DESC LIMIT 0,10;
лучше из запроса убрать, а соответствующую выборку делать на уровне бизнес-логики. В случае, если результирующее количество данных велико, то тогда сортировку нужно делать на уровне БД, при этом необходимо настраивать использование индексов, чтобы выборка происходила быстрее.
Скорость выполнения указанных рекомендаций следует проверять на реальных данных, замеряя каждый раз полученные результаты, поскольку каждый сервер баз данных по-разному оптимизирует схему выполнения запросов.
Неактивен
VictorK написал:
Единственное, я не знаю как и куда это записывать, чтобы обрабатывалось постоянно.
Могли бы Вы мне пошагово объяснить как это сделать, и чтоб старый запрос не выполнялся?
Изменения нужно вносить в скрипты, которые формируют этот запрос.
VictorK написал:
Он будет работать быстрее предыдущего запроса с вложенным SELECT.
Не факт.
VictorK написал:
Кроме того, мы бы посоветовали вынести значение NOW() в какую-либо переменную, объявленную до выполнения запроса. В этом случае вызов функции будет сделан один раз, а не каждый раз при сравнении строк (при проверке критериев в конструкции WHERE).
Можно, но это уже нюансы.
VictorK написал:
Также, в общем случае следует избегать выполнения сортировки на уровне базы данных - лучше выполнять ее на уровне вызывающего кода.
Сомнительное утверждение.
VictorK написал:
Поэтому строку:
ORDER BY p.quantity DESC, LCASE(pd.name) DESC LIMIT 0,10;
лучше из запроса убрать, а соответствующую выборку делать на уровне бизнес-логики. В случае, если результирующее количество данных велико, то тогда сортировку нужно делать на уровне БД, при этом необходимо настраивать использование индексов, чтобы выборка происходила быстрее.
У вас как раз случай - результирующее количество данных велико.
VictorK написал:
Скорость выполнения указанных рекомендаций следует проверять на реальных данных, замеряя каждый раз полученные результаты, поскольку каждый сервер баз данных по-разному оптимизирует схему выполнения запросов.
Верное замечание.
Улучшение конкретного запроса дело хорошее, но узкое место, судя по приведенным вами данным о времени выполнения запроса вручную, не в нем.
Что же касается исправления самого запроса, то рекомендации из моего 7го поста остаются в силе.
P.S. А что у вас за хостинг?
Неактивен
Изменения нужно вносить в скрипты, которые формируют этот запрос.
А вот это можно подробней?
Хостинг белорусский - HB.BY, у нас по закону зарубежный нельзя.
У меня в принципе быстро грузится страница, основное время занимает "первый байт" -от 2 сек и до 8-10 сек.
И в конце Get "мой сайт" тоже пару секунд.
Я так понял чтоб уменьшить кол-во 503 ошибки необходимо уменьшить кол-во запросов к серверу, а не трогать запросы.
У меня виртуальный хостинг, нагрузка не велика, о обращений к первой странице 114-117, тестил спомощью http://www.webpagetest.org.
Неактивен
VictorK написал:
Изменения нужно вносить в скрипты, которые формируют этот запрос.
А вот это можно подробней?
А как? Подробней только сделать.
VictorK написал:
Хостинг белорусский - HB.BY, у нас по закону зарубежный нельзя.
Сурово у вас 
VictorK написал:
Я так понял чтоб уменьшить кол-во 503 ошибки необходимо уменьшить кол-во запросов к серверу, а не трогать запросы.
Нет. http://www.jino.ru/support/faq/problems … #error-503
Давайте определимся о чем идет речь. Я правильно понимаю,что:
1) у вас на хостинге запущен slow-query-log
2) в нем медленный запрос (11 сек), который вы процитировали в первом посте (в логе этот запрос встречается часто или нет)
3) если вы выполняете этот запрос в ручную, то он выполняется быстро (выполняете естественно на сервере)
Неактивен
