Проблемы потребления БД ресурсов OSStore 1.5.3.1

[CaptainPower]

Пришло письмо от провайдера где ругается на превышение лимитов.

Запросил статистику.. думаю. товаров в магазине не более 4т. Посещаемость пока до 100 в день

Известно о каких то проблемах с базой в этой версии?

вот что прислал провайдер:

# Query 1: 0.93 QPS, 0.03x concurrency, ID 0x4A7378B436916589 at byte 1288709240
# This item is included in the report because it matches --limit.
# Scores: Apdex = 1.00 [1.0], V/M = 0.07
# Query_time sparkline: |    ^__ |
# Time range: 2013-01-07 01:29:42 to 2013-01-08 00:50:44
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         16   78263
# Exec time     64   2743s    24ms      3s    35ms    48ms    51ms    28ms
# Lock time     26     72s    70us      2s   918us   725us    19ms   103us
# Rows sent      4  76.43k       1       1       1       1       0       1
# Rows examine  35 564.93M   7.35k   7.61k   7.39k   7.31k       0   7.31k
# Rows affecte   0       0       0       0       0       0       0       0
# Rows read      4  76.43k       1       1       1       1       0       1
# Bytes sent     1   4.54M      60      62   60.81   59.77    0.00   59.77
# Tmp tables    97  76.43k       1       1       1       1       0       1
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Tmp tbl size   0       0       0       0       0       0       0       0
# Query size    42  29.07M     389     390  389.47  381.65       0  381.65
# String:
# Databases    p50444_sambyt
# Hosts
# Last errno   0
# Users        p50444_sambyt
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  #
#    1s  #
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `p50444_sambyt` LIKE 'product'\G
#    SHOW CREATE TABLE `p50444_sambyt`.`product`\G
#    SHOW TABLE STATUS FROM `p50444_sambyt` LIKE 'product_description'\G
#    SHOW CREATE TABLE `p50444_sambyt`.`product_description`\G
#    SHOW TABLE STATUS FROM `p50444_sambyt` LIKE 'product_to_store'\G
#    SHOW CREATE TABLE `p50444_sambyt`.`product_to_store`\G
#    SHOW TABLE STATUS FROM `p50444_sambyt` LIKE 'product_to_category'\G
#    SHOW CREATE TABLE `p50444_sambyt`.`product_to_category`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(DISTINCT p.product_id) AS total FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND (p2c.category_id = '60')\G

# Query 2: 4.65 QPS, 0.01x concurrency, ID 0x4E2FFD2953A6AA24 at byte 1397219384
# This item is included in the report because it matches --limit.
# Scores: Apdex = 1.00 [1.0], V/M = 0.11
# Query_time sparkline: |  _^___ |
# Time range: 2013-01-07 01:06:03 to 2013-01-08 01:05:52
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         83  401526
# Exec time     21    930s   924us      2s     2ms     3ms    16ms     2ms
# Lock time     57    157s    24us      2s   391us   348us    11ms    33us
# Rows sent     59   1.09M       0       3    2.85    2.90    0.40    2.90
# Rows examine  63 1015.90M   2.59k   2.59k   2.59k   2.59k       0   2.59k
# Rows affecte   0       0       0       0       0       0       0       0
# Rows read     59   1.09M       0       3    2.85    2.90    0.40    2.90
# Bytes sent    43 146.29M     242     456  382.04  400.73   27.50  381.65
# Tmp tables     0       0       0       0       0       0       0       0
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Tmp tbl size   0       0       0       0       0       0       0       0
# Query size    53  36.46M      54      99   95.22   97.36    7.40   92.72
# String:
# Databases    p50444_sambyt
# Hosts
# Last errno   0
# Users        p50444_sambyt
# Query_time distribution
#   1us
#  10us
# 100us  #
#   1ms  ################################################################
#  10ms  #
# 100ms  #
#    1s  #
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `p50444_sambyt` LIKE 'url_alias'\G
#    SHOW CREATE TABLE `p50444_sambyt`.`url_alias`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM url_alias WHERE `query` IN ('category_id=82', 'category_id=112', 'product_id=42699')\G

# Query 3: 0.01 QPS, 0.01x concurrency, ID 0xE82884ADFD12A089 at byte 693756908
# This item is included in the report because it matches --limit.
# Scores: Apdex = 0.99 [1.0], V/M = 0.05
# Query_time sparkline: |     ^_ |
# Time range: 2013-01-07 01:29:42 to 2013-01-08 00:50:42
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     967
# Exec time     11    499s   405ms      2s   516ms   740ms   165ms   455ms
# Lock time      0      2s   244us   846ms     3ms     3ms    32ms   332us
# Rows sent      0   3.78k       4       4       4       4       0       4
# Rows examine   0  11.37M  12.04k  12.04k  12.04k  12.04k       0  12.04k
# Rows affecte   0       0       0       0       0       0       0       0
# Rows read      0   3.78k       4       4       4       4       0       4
# Bytes sent     2   7.25M   7.03k  10.47k   7.67k   8.46k  554.35   7.31k
# Tmp tables     1     967       1       1       1       1       0       1
# Tmp disk tbl  87     967       1       1       1       1       0       1
# Tmp tbl size  90  19.52G  20.67M  20.67M  20.67M  20.67M       0  20.67M
# Query size     2   1.72M   1.82k   1.82k   1.82k   1.82k       0   1.82k
# String:
# Databases    p50444_sambyt
# Hosts
# Last errno   0
# Users        p50444_sambyt
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ##
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `p50444_sambyt` LIKE 'product_discount'\G
#    SHOW CREATE TABLE `p50444_sambyt`.`product_discount`\G
#    SHOW TABLE STATUS FROM `p50444_sambyt` LIKE 'product_special'\G
#    SHOW CREATE TABLE `p50444_sambyt`.`product_special`\G
#    SHOW TABLE STATUS FROM `p50444_sambyt` LIKE 'product_reward'\G
#    SHOW CREATE TABLE `p50444_sambyt`.`product_reward`\G
#    SHOW TABLE STATUS FROM `p50444_sambyt` LIKE 'stock_status'\G
#    SHOW CREATE TABLE `p50444_sambyt`.`stock_status`\G
#    SHOW TABLE STATUS FROM `p50444_sambyt` LIKE 'weight_class_description'\G
#    SHOW CREATE TABLE `p50444_sambyt`.`weight_class_description`\G
#    SHOW TABLE STATUS FROM `p50444_sambyt` LIKE 'length_class_description'\G
#    SHOW CREATE TABLE `p50444_sambyt`.`length_class_description`\G
#    SHOW TABLE STATUS FROM `p50444_sambyt` LIKE 'review'\G
#    SHOW CREATE TABLE `p50444_sambyt`.`review`\G
#    SHOW TABLE STATUS FROM `p50444_sambyt` LIKE 'product'\G
#    SHOW CREATE TABLE `p50444_sambyt`.`product`\G
#    SHOW TABLE STATUS FROM `p50444_sambyt` LIKE 'product_description'\G
#    SHOW CREATE TABLE `p50444_sambyt`.`product_description`\G
#    SHOW TABLE STATUS FROM `p50444_sambyt` LIKE 'product_to_store'\G
#    SHOW CREATE TABLE `p50444_sambyt`.`product_to_store`\G
#    SHOW TABLE STATUS FROM `p50444_sambyt` LIKE 'manufacturer'\G
#    SHOW CREATE TABLE `p50444_sambyt`.`manufacturer`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT 
		DISTINCT *, 
		pd.name AS name,
		p.image, 
		m.name AS manufacturer, 
		(SELECT price FROM product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '8' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, 
		(SELECT price FROM product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '8' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '8') AS reward, 
		(SELECT ss.name FROM stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '2') AS stock_status, (SELECT wcd.unit FROM weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '2') AS weight_class, 
		(SELECT lcd.unit FROM length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '2') AS length_class, 
		(SELECT AVG(rating) AS total FROM review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews 
	FROM product p 
	LEFT JOIN product_description pd ON (p.product_id = pd.product_id) 
	LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) 
	LEFT JOIN manufacturer m ON (p.manufacturer_id = m.manufacturer_id) 
	WHERE 
		pd.language_id = '2' AND 
		p.status = '1' AND p.date_available <= NOW() AND 
		p2s.store_id = '0'ORDER BY RAND() LIMIT 4\G

[Гість brk]

Перейти на другой тариф всё равно придется.

шаред хост не для магазинов они говорят Вам.

Можете проверить так ли это, отправьте им в ответ "что делать?"

[CaptainPower]

Дело пока не в тарифе

хостер пишет

Нагрузка за прошедшие сутки (2013-01-16) составила 2462 секунд при разрешённых 500

(это при том что по метрике было зарегистрировано всего 35 посещений!)

самый крутой тариф предполагает 1500 сек!!

т.е. что то сильно грузит базу

Раньше было все нормально после последнего добавления товара началась такая лабуда

Вот статистика хостера что конкретно означают эти строки куда копать?

# 301.2s user time, 1.4s system time, 41.39M rss, 215.84M vsz
# Current date: Thu Jan 17 15:55:06 2013
# Hostname: mysql3.ihc.ru
# Files: /var/log/mysql-slow.log
# Overall: 74.81k total, 26 unique, 1.95 QPS, 0.12x concurrency __________
# Time range: 2013-01-17 01:08:04 to 11:47:12
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          4711s   290us      2s    63ms   148ms    62ms    46ms
# Lock time            28s    17us   916ms   376us   273us     9ms    84us
# Rows sent        361.45k       0 185.84k    4.95    2.90  697.27    0.99
# Rows examine     445.47M   2.48k 185.84k   6.10k   7.31k   2.22k   7.31k
# Rows affecte           3       0       1    0.00       0    0.01       0
# Rows read        361.45k       0 185.84k    4.95    2.90  697.27    0.99
# Bytes sent        45.43M      11  30.25M  636.69  381.65 112.62k   59.77
# Tmp tables        52.03k       0       1    0.71    0.99    0.45    0.99
# Tmp disk tbl          11       0       1    0.00       0    0.01       0
# Tmp tbl size       1.33G       0  13.97M  18.59k       0 201.29k       0
# Query size        21.73M      51     571  304.51  381.65  135.69  381.65

# Profile
# Rank Query ID           Response time   Calls R/Call Apdx V/M   Item
# ==== ================== =============== ===== ====== ==== ===== ========
#    1 0x4A7378B436916589 4536.3592 96.3% 51875 0.0874 1.00  0.04 SELECT product product_description product_to_store product_to_category
# MISC 0xMISC              174.8162  3.7% 22936 0.0076   NS   0.0 <25 ITEMS>

# Query 1: 1.35 QPS, 0.12x concurrency, ID 0x4A7378B436916589 at byte 207232810
# This item is included in the report because it matches --limit.
# Scores: Apdex = 1.00 [1.0], V/M = 0.04
# Query_time sparkline: |    ^^_ |
# Time range: 2013-01-17 01:08:05 to 11:47:12
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         69   51875
# Exec time     96   4536s    22ms      2s    87ms   163ms    56ms    91ms
# Lock time     61     17s    58us   429ms   335us   287us     6ms    93us
# Rows sent     14  50.66k       1       1       1       1       0       1
# Rows examine  85 379.80M   7.45k   7.71k   7.50k   7.31k   49.58   7.31k
# Rows affecte   0       0       0       0       0       0       0       0
# Rows read     14  50.66k       1       1       1       1       0       1
# Bytes sent     6   3.01M      60      62   60.93   59.77       0   59.77
# Tmp tables    97  50.66k       1       1       1       1       0       1
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Tmp tbl size   0       0       0       0       0       0       0       0
# Query size    88  19.26M     389     390  389.39  381.65       0  381.65
# String:
# Databases    p95075
# Hosts
# Last errno   0
# Users        p95075
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  ############################################################
#    1s  #
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `p95075` LIKE 'product'\G
#    SHOW CREATE TABLE `p95075`.`product`\G
#    SHOW TABLE STATUS FROM `p95075` LIKE 'product_description'\G
#    SHOW CREATE TABLE `p95075`.`product_description`\G
#    SHOW TABLE STATUS FROM `p95075` LIKE 'product_to_store'\G
#    SHOW CREATE TABLE `p95075_sambyt`.`product_to_store`\G
#    SHOW TABLE STATUS FROM `p95075` LIKE 'product_to_category'\G
#    SHOW CREATE TABLE `p95075`.`product_to_category`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(DISTINCT p.product_id) AS total FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = '2' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND (p2c.category_id = '72')\G