Добавлено: Чт Ноя 22, 2007 5:56 pm Заголовок сообщения: cool_shop: нагрузка на Mysql
Привет
Есть несколько магазинов на дедике. Как только трафик начинает идти более-менее нормальный сразу жутко начинает тормозить мускуль. Саппорт ответил, что нагрузка связана с цитата "подозрение что виной тому трехстраничные запросы со вложенными джойнами" и предоставили запросы, которые тормозят БД.
Кто-то сталкивался? Как лечить? _________________ Make Money Online Blog
# Query_time: 12 Lock_time: 0 Rows_sent: 12 Rows_examined: 60381
use db1;
select p.prdID as prdID,in_stock,quantity,url_name,
price,p.price_type as price_type,spec_price,spec_time1,spec_time2,
pn.price_type as price_type_new,
cp.catID as catID,p.priority as priority,
attributes,
IF(pn.name!='',pn.name,p.name) as name,
# IF(pn.comment!='',pn.comment,p.comment) as comment,
IF(u1.uplID,u1.uplID,0) as uplID1,
IF(u2.uplID,u2.uplID,0) as uplID2,
# IF(u3.uplID,u3.uplID,0) as uplID3,
IF(u1.img_not_loaded,u1.name,CONCAT(u1.path,'/',u1.name)) as fna
me1,
IF(u2.img_not_loaded,u2.name,CONCAT(u2.path,'/',u2.name)) as fna
me2,
# IF(u3.img_not_loaded,u3.name,CONCAT(u3.path,'/',u3.name)) as fna
me3,
u1.width as width1,u1.height as height1,
u2.width as width2,u2.height as height2,
# u3.width as width3,u3.height as height3,
u1.img_not_loaded as img_not_loaded1,
u2.img_not_loaded as img_not_loaded2
# u3.img_not_loaded as img_not_loaded3
from (sc_product as p,sc_category_prod as cp)
left join sc_product_newval as pn on pn.prdID=p.prdID
left join uploads1 as u1 on uplID1=u1.uplID
left join uploads1 as u2 on uplID2=u2.uplID
# left join uploads1 as u3 on uplID3=u3.uplID
where p.active and cp.catID in (174,164,168,176,177,178,179,180,181,155,
153,154,156,157,158,182,187,175,152,162,166,167,169,170,171,172,173,160,161,163,
165,184,185,186,886,887,888,159,183) and
time_available<=1195668837 and cp.prdID=p.prdID
group by p.prdID
order by p.num_choosed desc,p.price_type_new DESC,priority,is_new DESC,rand() limit 0,12;
# Query_time: 21 Lock_time: 0 Rows_sent: 12 Rows_examined: 60352
use db2;
select p.prdID as prdID,in_stock,quantity,url_name,
price,p.price_type as price_type,spec_price,spec_time1,spec_time2,
pn.price_type as price_type_new,
cp.catID as catID,p.priority as priority,
attributes,
IF(pn.name!='',pn.name,p.name) as name,
# IF(pn.comment!='',pn.comment,p.comment) as comment,
IF(u1.uplID,u1.uplID,0) as uplID1,
IF(u2.uplID,u2.uplID,0) as uplID2,
# IF(u3.uplID,u3.uplID,0) as uplID3,
IF(u1.img_not_loaded,u1.name,CONCAT(u1.path,'/',u1.name)) as fname1,
IF(u2.img_not_loaded,u2.name,CONCAT(u2.path,'/',u2.name)) as fname2,
# IF(u3.img_not_loaded,u3.name,CONCAT(u3.path,'/',u3.name)) as fname3,
u1.width as width1,u1.height as height1,
u2.width as width2,u2.height as height2,
# u3.width as width3,u3.height as height3,
u1.img_not_loaded as img_not_loaded1,
u2.img_not_loaded as img_not_loaded2
# u3.img_not_loaded as img_not_loaded3
from (sc_product as p,sc_category_prod as cp)
left join sc_product_newval as pn on pn.prdID=p.prdID
left join uploads1 as u1 on uplID1=u1.uplID
left join uploads1 as u2 on uplID2=u2.uplID
# left join uploads1 as u3 on uplID3=u3.uplID
where p.active and cp.catID in (174,164,168,176,177,178,179,180,181,155,
153,154,156,157,158,182,187,175,152,162,166,167,169,170,171,172,173,160,161,163,
165,184,185,186,886,887,888,159,183) and
time_available<=1195668894 and cp.prdID=p.prdID
group by p.prdID
order by p.num_choosed desc,p.price_type_new DESC,priority,is_new DESC,rand() limit 0,12;
# Query_time: 12 Lock_time: 0 Rows_sent: 10 Rows_examined: 78083
use db3;
select p.prdID as prdID,p.priority as priority,url_name,
IF(pn.name!='',pn.name,p.name) as name,cp.catID as catID,
IF(attributes!='',1,0) as attributed,
p.price_type as price_type,price,spec_price,spec_time1,spec_time2,
pn.price_type as price_type_new
from (sc_product as p,sc_category_prod as cp)
left join sc_product_newval as pn on pn.prdID=p.prdID
where p.active and p.in_stock and cp.catID in (199,302,531,286,532,559,
297,492,287,490,560,489,488,491,487,289,290,291,294,292,298,300,486,192,520,206,
207,205,200,208,219,511,214,203,204,202,213,220,507,209,211,210,217,212,215,193,
349,362,363,364,347,360,340,333,354,359,331,341,345,334,348,332,342,226,329,330,
195,257,542,260,522,529,543,535,2636,244,245,254,256,306,1306,242,240,259,248,249
,250,251,253,262,303,304,305,497,510,238,307,194,528,541,232,233,236,493,231,237
,234,512,198,524,277,548,276,275,509,521,536,279,197,225,246,247,273,274,272,196
,269,498,267,271,495,266,218,313,239,309,316,540,319,318,317,263,264,265,326,365
,366,367,368,369,370,371,503,314,320,321,322,323,325,327,328,494,501,502,514,582
,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,578,580,581,583,312
,586,324,539,315,499,505,968,513,516) and
time_available<=1195668919 and
cp.prdID=p.prdID group by p.prdID order by priority,rand() limit 10;
# Query_time: 11 Lock_time: 0 Rows_sent: 10 Rows_examined: 31707
use db2;
select p.prdID as prdID,p.priority as priority,url_name,
IF(pn.name!='',pn.name,p.name) as name,cp.catID as catID,
IF(attributes!='',1,0) as attributed,
p.price_type as price_type,price,spec_price,spec_time1,spec_time2,
pn.price_type as price_type_new
from (sc_product as p,sc_category_prod as cp)
left join sc_product_newval as pn on pn.prdID=p.prdID
where p.active and p.in_stock and cp.catID in (174,164,168,176,177,178,
179,180,181,155,153,154,156,157,158,182,187,175,152,162,166,167,169,170,171,172,
173,160,161,163,165,184,185,186,886,887,888,159,183) and
time_available<=1195668919 and
cp.prdID=p.prdID group by p.prdID order by priority,rand() limit 10;
# Query_time: 11 Lock_time: 0 Rows_sent: 10 Rows_examined: 18262
use db4;
select p.prdID as prdID,p.priority as priority,url_name,
IF(pn.name!='',pn.name,p.name) as name,cp.catID as catID,
IF(attributes!='',1,0) as attributed,
p.price_type as price_type,price,spec_price,spec_time1,spec_time2,
pn.price_type as price_type_new
from (sc_product as p,sc_category_prod as cp)
left join sc_product_newval as pn on pn.prdID=p.prdID
where p.active and p.in_stock and cp.catID in (381,383,2,8,9,10,11,12,13,
14,16,406,460,555,584,587,882,967,384,17,18,19,20,21,31,22,23,24,27,32,5,98,6,35,
25,382,30,3,7,26,4,28,29,33,376,380,405,556,515) and
time_available<=1195668936 and
cp.prdID=p.prdID group by p.prdID order by priority,rand() limit 10;
# Query_time: 13 Lock_time: 0 Rows_sent: 10 Rows_examined: 31736
use db1;
select p.prdID as prdID,p.priority as priority,url_name,
IF(pn.name!='',pn.name,p.name) as name,cp.catID as catID,
IF(attributes!='',1,0) as attributed,
p.price_type as price_type,price,spec_price,spec_time1,spec_time2,
pn.price_type as price_type_new
from (sc_product as p,sc_category_prod as cp)
left join sc_product_newval as pn on pn.prdID=p.prdID
where p.active and p.in_stock and cp.catID in (174,164,168,176,177,178,
179,180,181,155,153,154,156,157,158,182,187,175,152,162,166,167,169,170,171,172,
173,160,161,163,165,184,185,186,886,887,888,159,183) and
time_available<=1195669122 and
cp.prdID=p.prdID group by p.prdID order by priority,rand() limit 10;
use db4;
select p.prdID as prdID,in_stock,quantity,url_name,
price,p.price_type as price_type,spec_price,spec_time1,spec_time2,
pn.price_type as price_type_new,
cp.catID as catID,p.priority as priority,
attributes,
IF(pn.name!='',pn.name,p.name) as name,
# IF(pn.comment!='',pn.comment,p.comment) as comment,
IF(u1.uplID,u1.uplID,0) as uplID1,
IF(u2.uplID,u2.uplID,0) as uplID2,
# IF(u3.uplID,u3.uplID,0) as uplID3,
IF(u1.img_not_loaded,u1.name,CONCAT(u1.path,'/',u1.name)) as fname1,
IF(u2.img_not_loaded,u2.name,CONCAT(u2.path,'/',u2.name)) as fname2,
# IF(u3.img_not_loaded,u3.name,CONCAT(u3.path,'/',u3.name)) as fname3,
u1.width as width1,u1.height as height1,
u2.width as width2,u2.height as height2,
# u3.width as width3,u3.height as height3,
u1.img_not_loaded as img_not_loaded1,
u2.img_not_loaded as img_not_loaded2
# u3.img_not_loaded as img_not_loaded3
from (sc_product as p,sc_category_prod as cp)
left join sc_product_newval as pn on pn.prdID=p.prdID
left join uploads1 as u1 on uplID1=u1.uplID
left join uploads1 as u2 on uplID2=u2.uplID
# left join uploads1 as u3 on uplID3=u3.uplID
where p.active and cp.catID in (381,383,2,8,9,10,11,12,13,14,16,406,460,
555,584,587,882,967,384,17,18,19,20,21,31,22,23,24,27,32,5,98,6,35,25,382,30,3,7
,26,4,28,29,33,376,380,405,556,515) and
time_available<=1195669122 and cp.prdID=p.prdID
group by p.prdID
order by p.num_choosed desc,p.price_type_new DESC,priority,is_new DESC,rand() limit 315,15;
# Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 28671
use db1;
select count(DISTINCT p.prdID)
from (sc_product as p, sc_category_prod as cp)
left join sc_product_newval as pn on p.prdID=pn.prdID
where p.active and cp.catID in (174,164,168,176,177,178,
179,180,181,155,153,154,156,157,158,182,187,175,152,162,166,167,169,170,171,172,
173,160,161,163,165,184,185,186,886,887,888,159,183) and
time_available<=1195669129 and cp.prdID=p.prdID and ((IF(pn.name!='',pn.
name,p.name) rlike '[[:<:]]18[[:alnum:]]*[[:>:]]' and IF(pn.name!='',pn.name,p.n
ame) rlike '[[:<:]]ga[[:alnum:]]*[[:>:]]' and IF(pn.name!='',pn.name,p.name) rli
ke '[[:<:]]barbell[[:alnum:]]*[[:>:]]'));
Добавлено: Вс Ноя 25, 2007 9:24 am Заголовок сообщения:
MySQL 4.1.18
Насчет остального я не очень разбираюсь в этом... Если бы ты подсказал, что именно надо узнать (что ты подразумевал под "т.п.") то я бы все у саппорта выяснил. _________________ Make Money Online Blog
Я уже давно хочу сделать шоп на статике, вот только впереди маячет новый движок, так что х.з., то ли надо ждать, то ли надо делать _________________ Дешевые домены за Webmoney
Никто не сталкивался с такой проблемой? Т.е. выходит дело непосредственно в моем хостинге, скрипт не при чем?
Может кто-то подскажет, что можно сделать, чтобы решить проблему?
Саппорт советует оформить запросы по другому, но я если честно боюсь лезть туда. Может как-то со стороны сервера что-то подкрутить-настроить?
а вариант взять другой хостинг не рассматривается? ощущение такое, что либо у вас не дедик вовсе, либо софт на нем настроен "нестандартно" _________________ Svift.org | Не все ссылки одинаково полезны...
Нужны правильные ссылки? Приглашения в PromoText.ru
Привет, Всем
cкажите а зачем сортировать по функции, тем более такой славной как RAND?
кусок из запроса:
order by priority,rand()
вот смотрите:
- сначал происходит поиск элементов на выдачу, связывание таблиц, по кулючам, оптимизация по индексам и т.д.
- потом их сортировка, по полям, для каждого элемента происходит вычисление ф-ции rand, его установка, потом сортировка всей выборки, все это дело проходит в памяти. Если указанное время в сек. то можно утверждать точно, сервак ушел в своп.
-потом paging - Limit I, K
Вы не можете начинать темы Вы не можете отвечать на сообщения Вы не можете редактировать свои сообщения Вы не можете удалять свои сообщения Вы не можете голосовать в опросах
FAQ
Поиск
Пользователи
Группы
Реклама
Регистрация
Профиль
Войти и проверить личные сообщения
Вход
