Заблокировали сайт rc-mag.ru из-за большой нагрузки.
Статистика запросов предоставлена ниже.
# 355.1s user time, 1.4s system time, 24.52M rss, 199.02M vsz
# Current date: Tue May 7 02:24:41 2013
# Hostname: mysql3.ihc.ru
# Files: /var/log/mysql-slow.log-20130507
# Overall: 976 total, 31 unique, 0.01 QPS, 0.13x concurrency _____________
# Time range: 2013-05-06 01:37:41 to 2013-05-07 00:41:28
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 10890s 811us 521s 11s 30s 54s 44ms
# Lock time 2s 21us 1s 2ms 1ms 33ms 159us
# Rows sent 13.82M 0 382.12k 14.50k 42.34k 46.55k 271.23
# Rows examine 5.75G 1.19k 326.65M 6.04M 10.76M 31.32M 10.80k
# Rows affecte 35.13k 0 10.26k 36.85 0 519.83 0
# Rows read 5.75G 1.19k 326.65M 6.04M 10.76M 31.32M 10.80k
# Bytes sent 471.96M 11 4.81M 495.17k 3.68M 1.03M 18.47k
# Query size 602.53k 33 47.88k 632.17 1.26k 2.22k 329.68
# Profile
# Rank Query ID Response time Calls R/Call Apdx V/M Item
# ==== ================== =============== ===== ======= ==== ===== =======
# 1 0xF7AF78BAC1E189B7 5673.6741 52.1% 81 70.0454 0.22 24... SELECT ps_search_word ps_search_index ps_product ps_product_lang ps_tax_rule ps_tax ps_manufacturer ps_image ps_image_lang
# 2 0x1C050D3DEFA60575 3399.5007 31.2% 95 35.7842 0.41 17... SELECT ps_search_word ps_search_index
# 3 0x376D1E73331D9BAC 1240.6039 11.4% 20 62.0302 0.42 26... SELECT ps_search_word ps_search_index ps_product ps_product_lang ps_tax_rule ps_tax ps_manufacturer ps_image ps_image_lang
# 4 0x8E0904562BDE9EE6 317.4138 2.9% 15 21.1609 0.67 12... SELECT ps_search_word ps_search_index
# MISC 0xMISC 258.6900 2.4% 765 0.3382 NS 0.0 <27 ITEMS>
# Query 1: 0.02 QPS, 1.17x concurrency, ID 0xF7AF78BAC1E189B7 at byte 979850167
# This item is included in the report because it matches --limit.
# Scores: Apdex = 0.22 [1.0]*, V/M = 245.17
# Query_time sparkline: | _-^|
# Time range: 2013-05-06 23:18:12 to 2013-05-07 00:38:57
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 8 81
# Exec time 52 5674s 776ms 521s 70s 382s 131s 11s
# Lock time 12 244ms 213us 137ms 3ms 5ms 15ms 690us
# Rows sent 0 1.02k 1 20 12.89 19.46 8.23 19.46
# Rows examine 56 3.27G 3.84k 326.65M 41.30M 232.77M 81.70M 8.86M
# Rows affecte 0 0 0 0 0 0 0 0
# Rows read 56 3.27G 3.84k 326.65M 41.30M 232.77M 81.70M 8.86M
# Bytes sent 0 692.18k 3.75k 13.36k 8.55k 11.91k 3.35k 10.80k
# Query size 38 233.98k 1.15k 15.98k 2.89k 12.50k 3.82k 1.26k
# String:
# Databases p140949_db
# Hosts
# Last errno 0
# Users p140949_db
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ########
# 1s ###############################################
# 10s+ ################################################################
# Tables
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_search_word'\G
# SHOW CREATE TABLE `p140949_db`.`ps_search_word`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_search_index'\G
# SHOW CREATE TABLE `p140949_db`.`ps_search_index`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_product'\G
# SHOW CREATE TABLE `p140949_db`.`ps_product`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_product_lang'\G
# SHOW CREATE TABLE `p140949_db`.`ps_product_lang`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_tax_rule'\G
# SHOW CREATE TABLE `p140949_db`.`ps_tax_rule`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_tax'\G
# SHOW CREATE TABLE `p140949_db`.`ps_tax`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_manufacturer'\G
# SHOW CREATE TABLE `p140949_db`.`ps_manufacturer`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_image'\G
# SHOW CREATE TABLE `p140949_db`.`ps_image`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_image_lang'\G
# SHOW CREATE TABLE `p140949_db`.`ps_image_lang`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
tax.`rate`, i.`id_image`, il.`legend`, m.`name` manufacturer_name ,(
SELECT SUM(weight)
FROM ps_search_word sw
LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
WHERE sw.id_lang = 2
AND si.id_product = p.id_product
AND (sw.word LIKE 'ви%')
) position, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL 0 DAY)) > 0 new
FROM ps_product p
INNER JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 2)
LEFT JOIN `ps_tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
AND tr.`id_country` = 177
AND tr.`id_state` = 0)
LEFT JOIN `ps_tax` tax ON (tax.`id_tax` = tr.`id_tax`)
LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 2)
WHERE p.`id_product` IN (23881,23990,24070,6451,6452,6457,8486,9254,9255,17031,17396,45414,426607,17966,22821,23990,24070,23881,23639,48322/*... omitted 2699 items ...*/)
ORDER BY position asc
LIMIT 0,20\G
# Query 2: 0.02 QPS, 0.68x concurrency, ID 0x1C050D3DEFA60575 at byte 1024206503
# This item is included in the report because it matches --limit.
# Scores: Apdex = 0.41 [1.0]*, V/M = 173.92
# Query_time sparkline: | _.-^|
# Time range: 2013-05-06 23:15:50 to 2013-05-07 00:39:24
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 9 95
# Exec time 31 3400s 36ms 470s 36s 167s 79s 4s
# Lock time 2 43ms 48us 11ms 455us 799us 1ms 159us
# Rows sent 0 18.58k 0 1.65k 200.32 1012.63 394.46 33.32
# Rows examine 32 1.84G 25.81k 234.69M 19.87M 136.10M 40.70M 3.18M
# Rows affecte 0 0 0 0 0 0 0 0
# Rows read 32 1.84G 25.81k 234.69M 19.87M 136.10M 40.70M 3.18M
# Bytes sent 0 194.07k 96 16.64k 2.04k 9.80k 3.79k 420.77
# Query size 2 15.82k 167 175 170.52 166.51 1.67 166.51
# String:
# Databases p140949_db
# Hosts
# Last errno 0
# Users p140949_db
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms ############
# 100ms ############################
# 1s #######################################
# 10s+ ################################################################
# Tables
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_search_word'\G
# SHOW CREATE TABLE `p140949_db`.`ps_search_word`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_search_index'\G
# SHOW CREATE TABLE `p140949_db`.`ps_search_index`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT id_product
FROM ps_search_word sw
LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
WHERE sw.id_lang = 2
AND sw.word LIKE
'%101%%'\G
# Query 3: 0.00 QPS, 0.29x concurrency, ID 0x376D1E73331D9BAC at byte 979175939
# This item is included in the report because it matches --limit.
# Scores: Apdex = 0.42 [1.0]*, V/M = 265.68
# Query_time sparkline: | _^-|
# Time range: 2013-05-06 23:27:48 to 2013-05-07 00:38:38
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 2 20
# Exec time 11 1241s 908ms 477s 62s 363s 128s 2s
# Lock time 1 23ms 242us 9ms 1ms 2ms 2ms 657us
# Rows sent 0 164 1 20 8.20 19.46 8.73 0.99
# Rows examine 9 538.79M 3.84k 300.43M 26.94M 173.70M 72.45M 790.84k
# Rows affecte 0 0 0 0 0 0 0 0
# Rows read 9 538.79M 3.84k 300.43M 26.94M 173.70M 72.45M 790.84k
# Bytes sent 0 128.90k 3.75k 11.68k 6.44k 11.34k 3.14k 3.88k
# Query size 9 55.59k 1.15k 15.22k 2.78k 12.50k 3.86k 1.20k
# String:
# Databases p140949_db
# Hosts
# Last errno 0
# Users p140949_db
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ############################
# 1s ################################################################
# 10s+ #################################################
# Tables
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_search_word'\G
# SHOW CREATE TABLE `p140949_db`.`ps_search_word`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_search_index'\G
# SHOW CREATE TABLE `p140949_db`.`ps_search_index`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_product'\G
# SHOW CREATE TABLE `p140949_db`.`ps_product`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_product_lang'\G
# SHOW CREATE TABLE `p140949_db`.`ps_product_lang`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_tax_rule'\G
# SHOW CREATE TABLE `p140949_db`.`ps_tax_rule`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_tax'\G
# SHOW CREATE TABLE `p140949_db`.`ps_tax`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_manufacturer'\G
# SHOW CREATE TABLE `p140949_db`.`ps_manufacturer`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_image'\G
# SHOW CREATE TABLE `p140949_db`.`ps_image`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_image_lang'\G
# SHOW CREATE TABLE `p140949_db`.`ps_image_lang`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
tax.`rate`, i.`id_image`, il.`legend`, m.`name` manufacturer_name ,(
SELECT SUM(weight)
FROM ps_search_word sw
LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
WHERE sw.id_lang = 2
AND si.id_product = p.id_product
AND (sw.word LIKE 'винт%')
) position, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL 0 DAY)) > 0 new
FROM ps_product p
INNER JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 2)
LEFT JOIN `ps_tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
AND tr.`id_country` = 177
AND tr.`id_state` = 0)
LEFT JOIN `ps_tax` tax ON (tax.`id_tax` = tr.`id_tax`)
LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 2)
WHERE p.`id_product` IN (23639,48180,105,6593,20932,88,47854,21782,300,301,302,303,304,305,307,308,309,310,311,374/*... omitted 2566 items ...*/)
ORDER BY position desc
LIMIT 0,20\G
# Query 4: 0.08 QPS, 1.73x concurrency, ID 0x8E0904562BDE9EE6 at byte 1029706930
# This item is included in the report because it matches --limit.
# Scores: Apdex = 0.67 [1.0]*, V/M = 120.56
# Query_time sparkline: | ^ ._|
# Time range: 2013-05-07 00:33:56 to 00:36:59
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 15
# Exec time 2 317s 40ms 180s 21s 118s 51s 44ms
# Lock time 0 12ms 54us 7ms 809us 3ms 2ms 113us
# Rows sent 12 1.78M 17.64k 382.12k 121.27k 380.41k 152.99k 20.37k
# Rows examine 0 1.89M 24.42k 393.25k 129.29k 380.41k 153.22k 31.59k
# Rows affecte 0 0 0 0 0 0 0 0
# Rows read 0 1.89M 24.42k 393.25k 129.29k 380.41k 153.22k 31.59k
# Bytes sent 3 17.21M 158.74k 3.68M 1.15M 3.50M 1.47M 174.27k
# Query size 0 2.31k 156 159 157.93 158.58 1.50 151.03
# String:
# Databases p140949_db
# Hosts
# Last errno 1160
# Users p140949_db
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms ################################################################
# 100ms
# 1s ############################
# 10s+ ##############
# Tables
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_search_word'\G
# SHOW CREATE TABLE `p140949_db`.`ps_search_word`\G
# SHOW TABLE STATUS FROM `p140949_db` LIKE 'ps_search_index'\G
# SHOW CREATE TABLE `p140949_db`.`ps_search_index`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT id_product
FROM ps_search_word sw
LEFT JOIN ps_search_index si ON sw.id_word
WHERE sw.id_lang = 2
AND sw.word LIKE
'%105505%%'\G
Подскажите из-за чего идет перегруз сайта?
Как можно снизить нагрузку?