SmartCJ Support Forum

Админ хостинга который кучу лет с вашим smartcj работает 2 суток ковырял что тока не делал не нашел проблему
ftt совсем не грузит систему может 1% от всего

пробовали менять mysql 8 на mariadb тогда вообще все колом встает

какой конкретно процесс грузит проц?
если это запрос - запрос к какому конкретно урлу?
какая-то конкретика есть кроме "зуб даю это смарт!" ?

вот что он там 30 секунд селектит не понятно
https://prnt.sc/w7ncl4

вот slow-query log
JOIN rot_gal2tag on rot_gal2tag.gallery_id = gs.gallery_id
JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id ^M
WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.tested_thumb = 'no' and gi.sponsor_id NOT IN ('3') and gs.group_id = 0 AND rot_gal2tag.tag_id IN ('471','157','24','1200','26','301','4','894') and rot_gal2tag.site_id = '1' AND gs.gallery_id NOT IN ('39129','39089','39401','30751','30759','43824','38463','30948','38644','47948','38586','39033','39391','121106','38758','38727','38583','106313','46000','45605','106873','105772','193264','117680','247990','48602') group by gs.gallery_id, gs.thumb_id ORDER BY gs.total_ctr DESC LIMIT 0, 180# queryitems;
# Time: 2020-12-22T08:26:20.780821Z
# User@Host: scj[scj] @ localhost [] Id: 34926810
# Query_time: 59.518918 Lock_time: 0.000909 Rows_sent: 0 Rows_examined: 0
SET timestamp=1608625521;
SELECT gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gs.activation_date, gi.added_date, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats1 as gs FORCE INDEX (group_id) STRAIGHT_JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id
STRAIGHT_JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id

JOIN rot_gal2tag on rot_gal2tag.gallery_id = gs.gallery_id
JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id ^M
WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.best_thumb = 'yes' and gi.sponsor_id NOT IN ('3') and gs.group_id IN (1) AND rot_gal2tag.tag_id IN ('989','201','27','4') and rot_gal2tag.site_id = '1' group by gs.gallery_id, gs.thumb_id ORDER BY tested_thumb DESC, gs.total_ctr DESC LIMIT 0, 25# queryitems;
# Time: 2020-12-22T08:26:25.815422Z
# User@Host: scj[scj] @ localhost [] Id: 34927329
# Query_time: 48.987830 Lock_time: 0.000964 Rows_sent: 0 Rows_examined: 0
SET timestamp=1608625536;
SELECT gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gs.activation_date, gi.added_date, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats42 as gs JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id
JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id
JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id ^M
WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.tested_thumb = 'no' and gi.sponsor_id NOT IN ('3') and gs.group_id = 0 AND gs.gallery_id NOT IN ('14347','32153','31140','30172','31872','32546','38899','31823','30738','39027','29691','32567','32144','39259','32045','31333','30915','31964','32178','29871','29965','38550','30052','29964','28781') ORDER BY gs.total_ctr DESC LIMIT 25, 165# queryitems;
# Time: 2020-12-22T08:26:40.829372Z
# User@Host: scj[scj] @ localhost [] Id: 34928011
# Query_time: 44.268994 Lock_time: 0.000903 Rows_sent: 0 Rows_examined: 0
SET timestamp=1608625556;
SELECT gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gs.activation_date, gi.added_date, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats16 as gs FORCE INDEX (group_id) STRAIGHT_JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id
STRAIGHT_JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id

JOIN rot_gal2tag on rot_gal2tag.gallery_id = gs.gallery_id
JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id ^M
WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.best_thumb = 'yes' and gi.sponsor_id NOT IN ('3') and gs.group_id IN (1) AND rot_gal2tag.tag_id IN ('1203','81','1232','24','1173','5992','2564','93','382','15','238') and rot_gal2tag.site_id = '1' group by gs.gallery_id, gs.thumb_id ORDER BY tested_thumb DESC, gs.total_ctr DESC LIMIT 0, 25# queryitems;
# Time: 2020-12-22T08:26:50.115850Z
# User@Host: scj[scj] @ localhost [] Id: 34928506
# Query_time: 36.790158 Lock_time: 0.000865 Rows_sent: 75 Rows_examined: 493732
SET timestamp=1608625573;
SELECT gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gs.activation_date, gi.added_date, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats1 as gs JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id
JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id
JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id ^M
WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.tested_thumb = 'no' and gi.sponsor_id NOT IN ('3') and gs.group_id = 0 AND gs.gallery_id NOT IN ('40840','133376','69852','46298','39188','39109','47941','37756','41921','69130','46893','56898','35818','48495','46868','47748','40101','40676','39928','43225','50636','67624','46248','44626') ORDER BY gs.total_ctr DESC LIMIT 144, 75# queryitems;
# Time: 2020-12-22T08:26:50.464047Z
# User@Host: scj[scj] @ localhost [] Id: 34929020
# Query_time: 20.407359 Lock_time: 0.000729 Rows_sent: 75 Rows_examined: 449715
SET timestamp=1608625590;
SELECT gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gs.activation_date, gi.added_date, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats31 as gs JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id
JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id
JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id ^M
WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.tested_thumb = 'no' and gi.sponsor_id NOT IN ('3') and gs.group_id = 0 AND gs.gallery_id NOT IN ('52814','32039','58334','40794','31694','43614','33262','53178','41550','47243','39915','39414','32865','30092','30372','38768','54560','53504','61052','32377','55821','55816','32027','64655') ORDER BY gs.total_ctr DESC LIMIT 192, 75# queryitems;
# Time: 2020-12-22T08:27:43.203247Z
# User@Host: scj[scj] @ localhost [] Id: 34930908
# Query_time: 18.381577 Lock_time: 0.000682 Rows_sent: 75 Rows_examined: 502095
SET timestamp=1608625644;
SELECT gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gs.activation_date, gi.added_date, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats17 as gs JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id
JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id
JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id ^M
WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.tested_thumb = 'no' and gi.sponsor_id NOT IN ('3') and gs.group_id = 0 AND gs.gallery_id NOT IN ('64633','44410','38788','59646','47078','33299','32778','116882','58365','50422','51107','40090','41872','50412','30254','36303','51601','40668','59114','48546','48749','50835','54819','36906') ORDER BY gs.total_ctr DESC LIMIT 24, 75# queryitems;
# Time: 2020-12-22T08:28:03.952650Z
# User@Host: scj[scj] @ localhost [] Id: 34931627
# Query_time: 16.428554 Lock_time: 0.000988 Rows_sent: 75 Rows_examined: 493612
SET timestamp=1608625667;
SELECT gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gs.activation_date, gi.added_date, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats1 as gs JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id
JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id
JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id ^M
WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.tested_thumb = 'no' and gi.sponsor_id NOT IN ('3') and gs.group_id = 0 AND gs.gallery_id NOT IN ('72256','45164','275744','17117','263930','82782','39531','140612','48495','56712','49062','41642','67579','43023','58809','17160','43503','47603','39901','17923','48897','90898','92966','68084') ORDER BY gs.total_ctr DESC LIMIT 24, 75# queryitems;
# Time: 2020-12-22T08:28:04.156104Z
# User@Host: scj[scj] @ localhost [] Id: 34927956
# Query_time: 127.103353 Lock_time: 0.001049 Rows_sent: 0 Rows_examined: 0
SET timestamp=1608625557;
SELECT gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gs.activation_date, gi.added_date, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats44 as gs STRAIGHT_JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id
STRAIGHT_JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id

JOIN rot_gal2tag on rot_gal2tag.gallery_id = gs.gallery_id
JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id ^M
WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.tested_thumb = 'no' and gi.sponsor_id NOT IN ('3') and gs.group_id = 0 AND rot_gal2tag.tag_id IN ('31','502','8974','17234','10764','11939','119','767','783','226','35','239','234','4','843','37','394','222') and rot_gal2tag.site_id = '1' AND gs.gallery_id NOT IN ('39089','39401','30751','38969','38622','30949','43824','38677','38463','30754','47948','38586','30755','39033','39058','32702','41558','121106','38758','38727','38583','106313','46000','45605','106873') group by gs.gallery_id, gs.thumb_id ORDER BY gs.total_ctr DESC LIMIT 0, 180# queryitems;
# Time: 2020-12-22T08:28:23.935499Z
# User@Host: scj[scj] @ localhost [] Id: 34932083
# Query_time: 23.017529 Lock_time: 0.000946 Rows_sent: 165 Rows_examined: 503131
SET timestamp=1608625680;
SELECT gs.gallery_id, gs.thumb_id, gi.sponsor_id, gi.content_count, gi.content_type, gs.total_shows, gs.total_clicks, gs.total_ctr, gt.thumb_url, gd.*, gi.content_count, gi.crop_profile_id, gs.activation_date, gi.added_date, gi.duration, gi.url, gi.gallery_total_shows, gi.sponsor_id, gi.source_url, gi.custom_gallery FROM rot_gallery_stats42 as gs JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id
JOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id
JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id ^M
WHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.tested_thumb = 'no' and gi.sponsor_id NOT IN ('3') and gs.group_id = 0 AND gs.gallery_id NOT IN ('14347','32153','31140','30172','31872','32546','38899','31823','30738','39027','29691','32567','32144','39259','32045','31333','30915','31964','32178','29871','29965','38550','30052','29964','28781') ORDER BY gs.total_ctr DESC LIMIT 25, 165# queryitems;

rot_gal2tag таблица почти гигабайт весит и имеет 10 миллионов строк может в нем дело ?
Если да то как уменьшить размер этой таблицы почему она такая огромная?

вы ж говорите что мускл не нагружен

что селектит - можете развернуть запрос и увидеть его полностью, я по началу запроса тоже не могу определить

но суть очень простая: любой запрос, даже быстрый, будет медленным если перегружен сервак
представить это очень просто: условно у нас тонель через который идет люди, когда все ок они проходят за 1 секунду тоннель, когда их много они начинают толпиться и проходят за 10 например, а если очень много то могут вообще застрять там.

Lockmics wrote: Tue Dec 22, 2020 8:30 am rot_gal2tag таблица почти гигабайт весит

кол-во галер * кол-во тагов у кажддой галеры - вот и можете проверить сколько там записей

admin wrote: Tue Dec 22, 2020 8:31 am вы ж говорите что мускл не нагружен

ну то есть когда в query mysql ничего нет висящего лоад сервера все равно огромный (до 40) и top показывает что почти все ресурсы сервера сьел mysql
вот сейчас время низкой нагрузки
https://prnt.sc/w7njag

трафик сейчас 3К в час всего