Высокая нагрузка на сервер

Post Reply
rembo1978
Posts: 540
Joined: Thu Jul 26, 2012 5:12 am

Высокая нагрузка на сервер

Post by rembo1978 »

Здравствуйте. Начал тормозить сервак. Админы хостинга написали :
Постоянно создается запрос вида:
11987 admin_my_db localhost admin_my_db Query 3 Sending data 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) JOIN rot_gallery_info as gi on gi.gallery_id = gs.gallery_id\nJOIN rot_gallery_data1 as gd on gd.gallery_id = gs.gallery_id\n JOIN rot_thumbs as gt on gt.thumb_id = gs.thumb_id ^M\n\t\t\tWHERE 1 = 1 AND gi.gallery_status = 'active' AND gi.gallery_type = 0 and gs.best_thumb = 'yes' and gs.group_id IN (2678,3,4,5,6,7,8,9,10,180,1222,1724,1750,1751,1752,1753,11,12,14,15,16,17,18,19,2139,2140,2141,3222,3223,3392,3393,3394,3395,3396,20,182,183,1452,1453,1760,1761,1762,1763,1764,1765,3390,3391,1454,1455,1456,1766,1767,1768,2137,2138,21,22,184,185,186,187,188,189,190,191,192,193,194,1094,1769,1770,1771,1772,23,24,195,196,197,198,199,200,1223,201,202,203,204,205,206,207,208,209,1773,138,139,181,1754,1755,1756,1757,1758,1759,2136,210,211,212,213,214,215,216,217,218,219,1774,2142,25,26,27,244,245,246,247,1785,1786,1787,1788,1789,1790,248,249,250,251,252,253,1791,2145,3397,254,28,143,144,145,146,147,2146,3398,3399,3400,3401,3402,233,234,235,236,1783,1784,237,238,239,240,241,242,243,255,1232,1458,2144,29,30,31,32,256,257,258,259,260,262,3224,1095,1792,33,34,35,36,37,39,2152,263,264,265,266,267,268,269,270,271,3403,273,274,275,1233,3404,40,41,42,43,349,350,351,352,353,354,1238,1239,2161,2162,2163,3411,3412,3413,3414,355,356,357,358,359,360,362,363,364,365,366,367,368,369,3415,3416,3417,3418,3419,370,371,1465,2164,44,45,372,373,374,375,1798,2165,46,47,376,377,1098,1099,1799,1800,1801,1802,1803,3420,3421,3422,1100,1101,1102,1103,2166,3423,148,149,150,290,291,292,293,294,295,296,297,298,299,300,1235,1726,1795,2154,301,302,1727,1728,1729,1730,1731,1732,1733,1734,303,304,305,306,307,308,309,310,311,312,2155,313,314,315,316,317,318,319,2157,2158,3408,320,321,322,323,324,3409,325,326,1236,1796,2156,151,152,327,328,330,331,332,1237,3225,153,154,337,338,339,340,341,342,343,345,346,347,348,2160,3410,333,334,335,1097,1797,2159,3226,48,49,50,51,378,379,1241,1242,1243,1811,380,381,382,1106,1244,1245,52,53,383,384,385,386,387,388,389,390,391,392,393,2171,1104,1105,1240,1466,2170,1246,1247,1248,1249,1250,1804,1805,1806,1807,1808,1809,1810,2167,2168,2169,140,141,142,225,1225,1226,1778,1779,1780,2143,226,227,228,1227,1228,1229,1230,1231,229,230,231,232,1781,1782,220,221,1457,1775,1776,222,223,224,1224,1777,276,277,278,279,1096,1234,1725,3405,280,281,282,283,284,285,286,287,288,289,1460,1461,1462,1463,1464,3406,3407,1793,1794,2153,394,395,396,397,398,1459,2147,2148,2149,2150,2151,2936,2937,2938,2940,2941,2942,2932,3353,2933,2934,2935,2958,2959,2960,2961,2962,2963,2964,2965,2966,4324,2967,4318,4319,4320,4321,4322,4323,3890,2916,3349,2920,2921,2922,2923,2924,3351,4263,4264,2927,3352,2929,3016,3017,3018,3359,3360,3361,4334,3009,3010,4331,4332,3011,3012,4333,3013,3014,3015,3019,3020,3021,3022,3023,4335,3024,3025,3362,3026,3028,3029,3030,2971,2972,2973,2974,4325,2998,2999,3000,3001,4329,2985,2986,2987,2991,2992,2993,4327,4328,2811,2812,2813,2814,2815,2816,3695,3696,2799,2800,3316,3692,2805,2806,2807,2808,2809,3694,2818,2819,2820,2821,2822,2823,2824,2825,2826,2827,2828,2829,2830,2796,2764,3309,3684,2767,2769,3310,3685,2773,2774,2775,2776,3311,3312,3686,2777,3313,3314,3687,2778,2779,2780,2781,2782,2783,2784,3315,3688,3689,3690,3691,2785,2786,2787,2789,2790,2791,2792,2793,2794,2795,2797,2798,2801,2802,2803,2804,2810,3317,3693,2866,2871,2864,2865,2867,3331,2863,2868,2845,2846,2847,2848,2849,3326,3703,3327,2850,2832,2833,2834,2835,3318,3319,2836,2837,2838,3697,3698,2839,2840,2841,2842,2843,3325,2844,3320,3321,3322,3701,3323,3324,3702,3328,3699,3700,2862,3330,2856,2857,2858,3329,2853,2852,2854,2855,2859,2860,3032,3033,3034,3235,3516,3206,3207,4363,3180,3181,3182,3380,3198,3199,3201,3202,3203,3204,3172,3173,3174,3175,3075,3080,3081,3082,3367,3083,3084,3085,3368,3369,3086,3087,4346,4343,4344,4345,3090,3091,3067,3068,3069,3070,3071,3072,3073,3366,3037,3038,3039,3040,3041,3042,3043,3044,3045,3046,3047,3048,3049,3050,3051,3052,4338,3053,3054,4339,3055,3056,3057,4342,4340,4341,3066,3074,3076,3077,3078,3059,3062,3169,3170,3150,3151,4357,3156,3157,3378,4359,3166,3167,3168,3171,3176,3177,3178,3379,3104,3105,3106,3107,3371,4347,4348,3109,3110,3111,3112,3132,3133,3122,4349,4350,2387,2388,2389,2390,2391,3253,2392,2393,2394,2395,2396,3254,2397,3255,3535,2399,2400,2401,2402,2403,2404,2405,2406,2407,2408,2409,2410,2411,2412,2413,2414,2415,2416,2417,2418,3257,3536,2419,2420,2421,3256,2384,2385,2358,2359,2360,2361,2362,3248,3530,2370,2371,2372,2373,2374,2375,3249,2376,2377,2379,2380,2381,3250,3251,3252,3531,3532,3533,3534,2465,3261,2453,2454,2455,2456,3260,3540,3541,2478,2479,2481,2482,3262,3263,3264,3544,3545,3546,3547,3548,2452,2457,2458,2459,2460,2461,2462,2463,2464,2466,2467,2468,2469,2470,3542,2423,2424,2425,2426,2427,3537,2429,2430,2431,2432,2433,2434,2435,2436,2437,2438,2439,3258,2441,2442,2443,2444,2445,2446,3538,2449,2450,3259,3539,2350,2351,2352,2353,2354,2355,3529,2486,2487,2488,2489,3266,2252,2253,2254,3520,2256,2257,2260,2261,2262,2263,2264,2265,2266,2268,2269,2272,2273,2279,2280,2281,2282,2283,3242,2284,2285,2286,2287,2288,2289,2290,2291,2292,2293,2294,3243,3522,2295,2296,2297,2298,2299,3244,2300,2301,2302,2303,3521,3523,2304,2305,2306,2307,2308,2309,2310,2311,3524,2247,2248,2249,3241,2225,3515,2228,2229,2230,2231,2232,3236,2233,2234,2235,2236,2237,2238,2239,2240,2241,2242,2243,2244,2245,3237,3238,3239,3240,3517,3518,3519,2246,2250,2251,2255,2258,2259,2267,3234,2349,2356,2357,2363,2323,2324,2325,2326,2327,2328,2329,2330,2331,2332,2333,2334,3246,3247,3526,3527,2336,2337,2338,2339,2340,2341,2342,2343,2344,3528,2345,2346,2347,2348,2364,2365,2366,2367,2368,2369,2378,2382,2383,2313,2314,2315,2316,2317,2318,2319,2320,2321,3245,3525,2759,2760,2664,2665,2666,2667,2668,2669,3292,3293,3655,3656,2671,2672,3657,2676,2677,3659,2679,2680,2681,2682,3294) ORDER BY tested_thumb DESC, gs.total_ctr DESC LIMIT 0, 816 0.000
========
Помогите решить проблему. Спасибо
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: Высокая нагрузка на сервер

Post by admin »

как то слишком много групп

parent_id где то юзается в <thumb ?
Don't forget to run script update
rembo1978
Posts: 540
Joined: Thu Jul 26, 2012 5:12 am

Re: Высокая нагрузка на сервер

Post by rembo1978 »

admin wrote: Sat Oct 09, 2021 7:33 am как то слишком много групп

parent_id где то юзается в <thumb ?
Да груп - 4300 и они все через parent_id. Ещё к нему подключен слейв. Но это начилось недавно до этого работало нормально. Возможно что-то прилетело в обновлениях скрипта ?
Что делать.
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: Высокая нагрузка на сервер

Post by admin »

4300 групп это похоже на таги и это неправильно, сильно растит базу

апдейт как раз делал это быстрее но в разумных пределах

можно представить 200-300 групп но не 4300 ..
Don't forget to run script update
rembo1978
Posts: 540
Joined: Thu Jul 26, 2012 5:12 am

Re: Высокая нагрузка на сервер

Post by rembo1978 »

Ok удалю последние sub категории. Перестанет это запрос грузить процессор ?
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: Высокая нагрузка на сервер

Post by admin »

так не от последних зависит

у вас 4300 категоирий
если средняя галера имеет 10 тубм и входит в 10% категорий (430) то это 4300 записей на галеру * например 100к галер = 430 000 000 записей статистики
Don't forget to run script update
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: Высокая нагрузка на сервер

Post by admin »

посление это 10? так это ничего не поменяет глобально
можно тестировать конечно но вероятнее всего придется приводить в нормальный вид где может 300 категорий, а ваши 4300 - это скорее всего таги
Don't forget to run script update
rembo1978
Posts: 540
Joined: Thu Jul 26, 2012 5:12 am

Re: Высокая нагрузка на сервер

Post by rembo1978 »

ok так и сделаю спасибо
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: Высокая нагрузка на сервер

Post by admin »

NP
тогда и проверим нагрузку
Don't forget to run script update
Post Reply