Добрый день, я читал про уменьшение нагрузки, но не совсем разобрался, поэтому прошу помощи! внизу будет лог от рег ру про критические запросы.
Собственно проблема: у нас давно Joomla 1.5.25, база данных большая 500 мб, так как новостей 80 000.. Хостимся у рег ру, и по тарифу вроде мы можем использовать 100% нагрузки на cpu и на базы данных.
Но сегодня они пишут что у нас критическая нагрузка и ее надо оптимизировать или выбрать другой тариф. Вопрос можно ли как-то оптимизировать работу с MySQL? или нужно выбирать больший тариф типа VPS-сервер, облачный хостинг или выделенный сервер?
особенно ничего не делали только кэширование.
Дополнительные сведения:
Данные по нагрузке
USER: u0336850_glas
BUSY_TIME: 443055
CPU_TIME: 74787
выдержка из лога
2017-05-03 20:56:01: 10.08 (423%) 1528/92468 (1%) 7292/9643 (75%) 176/5537 (3%) u0336850_glas (in 60 sec)
2017-05-03 20:57:01: 11.87 (268%) 1215/96066 (1%) 946/3352 (28%) 164/5134 (3%) u0336850_glas (in 60 sec)
2017-05-03 20:58:01: 14.22 (983%) 3001/105825 (2%) 7313/9142 (79%) 172/5151 (3%) u0336850_glas (in 60 sec)
2017-05-03 20:59:02: 19.84 (1003%) 1965/104599 (1%) 212/1780 (11%) 138/4334 (3%) u0336850_glas (in 60 sec)
2017-05-03 21:00:02: 22.88 (2308%) 3237/83467 (3%) 260/1760 (14%) 136/3692 (3%) u0336850_glas (in 60 sec)
2017-05-03 21:01:01: 21.47 (936%) 3119/367203 (0%) 2199/4194 (52%) 326/3674 (8%) u0336850_glas (in 60 sec)
2017-05-03 21:02:02: 27.68 (2048%) 2305/284353 (0%) 241/11279 (2%) 148/5222 (2%) u0336850_glas (in 60 sec)
2017-05-03 21:03:01: 24.57 (2258%) 4509/252811 (1%) 2838/13859 (20%) 172/5280 (3%) u0336850_glas (in 60 sec)
2017-05-03 21:04:01: 19.31 (841%) 3127/307328 (1%) 1800/13245 (13%) 242/45210 (0%) u0336850_glas (in 60 sec)
2017-05-03 21:05:02: 18.12 (588%) 2592/285893 (0%) 742/10149 (7%) 208/15487 (1%) u0336850_glas (in 60 sec)
2017-05-03 21:06:02: 25.97 (351%) 2311/108509 (2%) 298/2328 (12%) 202/6201 (3%) u0336850_glas (in 60 sec)
2017-05-03 21:07:02: 22.56 (1940%) 3933/109107 (3%) 530/2246 (23%) 318/24611 (1%) u0336850_glas (in 60 sec)
2017-05-03 21:08:01: 16.24 (696%) 3131/108554 (2%) 670/2679 (25%) 412/4321 (9%) u0336850_glas (in 60 sec)
2017-05-03 21:09:02: 16.39 (835%) 4609/94869 (4%) 658/3421 (19%) 406/5766 (7%) u0336850_glas (in 60 sec)
2017-05-03 21:10:01: 14.66 (640%) 3973/98680 (4%) 422/3053 (13%) 258/5001 (5%) u0336850_glas (in 60 sec)
2017-05-03 21:11:01: 24.34 (1473%) 2864/104011 (2%) 266/3654 (7%) 144/4386 (3%) u0336850_glas (in 60 sec)
2017-05-03 21:12:01: 16.40 (826%) 2233/105385 (2%) 342/10908 (3%) 212/4983 (4%) u0336850_glas (in 60 sec)
2017-05-03 21:13:01: 15.49 (593%) 2514/136407 (1%) 291/10690 (2%) 164/7184 (2%) u0336850_glas (in 60 sec)
2017-05-03 21:14:01: 15.44 (400%) 1942/129819 (1%) 331/9817 (3%) 198/5388 (3%) u0336850_glas (in 60 sec)
2017-05-03 21:15:01: 13.42 (681%) 2088/96345 (2%) 302/2785 (10%) 166/6258 (2%) u0336850_glas (in 60 sec)
2017-05-03 21:16:01: 22.09 (1216%) 2715/123844 (2%) 265/3277 (8%) 150/4951 (3%) u0336850_glas (in 60 sec)
2017-05-03 21:17:01: 21.07 (1086%) 1956/108708 (1%) 404/21030 (1%) 260/6421 (4%) u0336850_glas (in 60 sec)
2017-05-03 21:18:01: 28.74 (871%) 1670/75598 (2%) 241/2748 (8%) 126/6231 (2%) u0336850_glas (in 60 sec)
2017-05-03 21:19:02: 36.57 (336%) 1407/105503 (1%) 198/15171 (1%) 112/4704 (2%) u0336850_glas (in 60 sec)
2017-05-03 21:20:02: 38.03 (1751%) 1188/111703 (1%) 160/4550 (3%) 108/6039 (1%) u0336850_glas (in 60 sec)
2017-05-03 21:21:01: 31.69 (1060%) 2132/126933 (1%) 324/6633 (4%) 184/4400 (4%) u0336850_glas (in 60 sec)
2017-05-03 21:22:02: 36.35 (1598%) 2456/145871 (1%) 253/7781 (3%) 144/5772 (2%) u0336850_glas (in 60 sec)
2017-05-03 21:23:01: 31.62 (505%) 1875/178954 (1%) 166/31868 (0%) 100/5770 (1%) u0336850_glas (in 60 sec)
2017-05-03 21:24:02: 37.78 (758%) 1313/154622 (0%) 168/26528 (0%) 98/4980 (1%) u0336850_glas (in 60 sec)
2017-05-03 21:25:02: 48.61 (1221%) 544/138896 (0%) 108/8864 (1%) 84/4420 (1%) u0336850_glas (in 60 sec)
2017-05-03 21:26:02: 51.31 (4375%) 2120/121653 (1%) 238/7281 (3%) 124/4656 (2%) u0336850_glas (in 60 sec)
2017-05-03 21:27:02: 50.04 (2703%) 2770/131406 (2%) 287/5062 (5%) 158/7828 (2%) u0336850_glas (in 60 sec)
2017-05-03 21:28:02: 48.10 (2845%) 2237/123965 (1%) 200/2636 (7%) 112/5759 (1%) u0336850_glas (in 60 sec)
2017-05-03 21:29:02: 45.41 (2685%) 2697/108538 (2%) 198/2268 (8%) 124/4380 (2%) u0336850_glas (in 60 sec)
2017-05-03 21:30:02: 46.53 (3106%) 3095/131608 (2%) 146/1954 (7%) 86/5852 (1%) u0336850_glas (in 60 sec)
2017-05-03 21:31:02: 43.90 (3166%) 1836/142653 (1%) 244/3564 (6%) 140/6616 (2%) u0336850_glas (in 60 sec)
2017-05-03 21:32:01: 48.83 (2710%) 3520/124625 (2%) 152/5027 (3%) 96/5681 (1%) u0336850_glas (in 60 sec)
2017-05-03 21:33:02: 40.16 (3661%) 1692/146226 (1%) 246/5380 (4%) 140/7182 (1%) u0336850_glas (in 60 sec)
2017-05-03 21:34:01: 40.13 (2363%) 2786/148378 (1%) 240/8959 (2%) 132/4164 (3%) u0336850_glas (in 60 sec)
2017-05-03 21:35:02: 34.79 (1335%) 2143/174999 (1%) 168/5828 (2%) 82/6660 (1%) u0336850_glas (in 60 sec)
2017-05-03 21:36:02: 38.47 (1783%) 1236/154525 (0%) 241/9858 (2%) 166/6494 (2%) u0336850_glas (in 60 sec)
2017-05-03 21:37:02: 35.53 (2068%) 2204/185398 (1%) 226/5540 (4%) 110/11732 (0%) u0336850_glas (in 60 sec)
2017-05-03 21:38:02: 38.90 (861%) 799/172545 (0%) 116/3397 (3%) 80/9232 (0%) u0336850_glas (in 60 sec)
2017-05-03 21:39:02: 38.88 (1875%) 2488/174363 (1%) 225/2875 (7%) 136/10645 (1%) u0336850_glas (in 60 sec)
2017-05-03 21:40:02: 43.89 (2163%) 1717/132805 (1%) 199/2328 (8%) 120/16043 (0%) u0336850_glas (in 60 sec)
2017-05-03 21:41:03: 48.11 (3081%) 1926/135253 (1%) 169/2289 (7%) 98/10667 (0%) u0336850_glas (in 60 sec)
2017-05-03 21:42:03: 53.33 (3071%) 2718/170932 (1%) 215/5308 (4%) 140/6516 (2%) u0336850_glas (in 60 sec)
2017-05-03 21:43:02: 41.94 (3296%) 1761/178977 (0%) 271/4752 (5%) 148/9859 (1%) u0336850_glas (in 60 sec)
2017-05-03 21:44:02: 35.82 (1943%) 3689/203998 (1%) 169/9437 (1%) 86/15074 (0%) u0336850_glas (in 60 sec)
2017-05-03 21:45:02: 28.52 (770%) 1186/215238 (0%) 233/9044 (2%) 128/8412 (1%) u0336850_glas (in 60 sec)
2017-05-03 21:46:01: 32.56 (1255%) 2110/167435 (1%) 279/7265 (3%) 182/6052 (3%) u0336850_glas (in 60 sec)
2017-05-03 21:47:01: 37.47 (1753%) 3389/185549 (1%) 216/2600 (8%) 122/4993 (2%) u0336850_glas (in 60 sec)
пример проблемных запросов
*************************** 21. row ***************************
Id: 41276376
User: u0336850_glas
Host: localhost
db: u0336850_glas
Command: Query
Time: 1
State: Waiting for table level lock
Info: SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END as catslug, g.name AS groups, s.published AS sec_pub, cc.published AS cat_pub, s.access AS sec_access, cc.access AS cat_access FROM s_content AS a LEFT JOIN s_categories AS cc ON cc.id = a.catid LEFT JOIN s_sections AS s ON s.id = cc.section AND s.scope = "content" LEFT JOIN s_users AS u ON u.id = a.created_by LEFT JOIN s_groups AS g ON a.access = g.id WHERE a.id = 82445 AND ( ( a.created_by = 0 ) OR ( a.state = 1 AND ( a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2017-05-03 18:49:00' ) AND ( a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2017-05-03 18:49:00' ) ) OR ( a.state = -1 ) )
Rows_sent: 0
Rows_examined: 0
*************************** 22. row ***************************
Id: 41276377
User: u0336850_glas
Host: localhost
db: u0336850_glas
Command: Query
Time: 1
State: Waiting for table level lock
Info: SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END as catslug, g.name AS groups, s.published AS sec_pub, cc.published AS cat_pub, s.access AS sec_access, cc.access AS cat_access FROM s_content AS a LEFT JOIN s_categories AS cc ON cc.id = a.catid LEFT JOIN s_sections AS s ON s.id = cc.section AND s.scope = "content" LEFT JOIN s_users AS u ON u.id = a.created_by LEFT JOIN s_groups AS g ON a.access = g.id WHERE a.id = 82443 AND ( ( a.created_by = 0 ) OR ( a.state = 1 AND ( a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2017-05-03 18:49:00' ) AND ( a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2017-05-03 18:49:00' ) ) OR ( a.state = -1 ) )
Rows_sent: 0
Rows_examined: 0
*************************** 23. row ***************************
Id: 41276378
User: u0336850_glas
Host: localhost
db: u0336850_glas
Command: Query
Time: 1
State: Waiting for table level lock
Info: SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END as catslug, g.name AS groups, s.published AS sec_pub, cc.published AS cat_pub, s.access AS sec_access, cc.access AS cat_access FROM s_content AS a LEFT JOIN s_categories AS cc ON cc.id = a.catid LEFT JOIN s_sections AS s ON s.id = cc.section AND s.scope = "content" LEFT JOIN s_users AS u ON u.id = a.created_by LEFT JOIN s_groups AS g ON a.access = g.id WHERE a.id = 82444 AND ( ( a.created_by = 0 ) OR ( a.state = 1 AND ( a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2017-05-03 18:49:00' ) AND ( a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2017-05-03 18:49:00' ) ) OR ( a.state = -1 ) )
Rows_sent: 0
Rows_examined: 0
*************************** 24. row ***************************
Id: 41276379
User: u0336850_glas
Host: localhost
db: u0336850_glas
Command: Query
Time: 1
State: Waiting for table level lock
Info: SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END as catslug, g.name AS groups, s.published AS sec_pub, cc.published AS cat_pub, s.access AS sec_access, cc.access AS cat_access FROM s_content AS a LEFT JOIN s_categories AS cc ON cc.id = a.catid LEFT JOIN s_sections AS s ON s.id = cc.section AND s.scope = "content" LEFT JOIN s_users AS u ON u.id = a.created_by LEFT JOIN s_groups AS g ON a.access = g.id WHERE a.id = 82446 AND ( ( a.created_by = 0 ) OR ( a.state = 1 AND ( a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2017-05-03 18:49:00' ) AND ( a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2017-05-03 18:49:00' ) ) OR ( a.state = -1 ) )
Rows_sent: 0
Rows_examined: 0
*************************** 25. row ***************************