I'm looking for some expert advice on solving a few problems being reported by mysqltuner. We run a few Magento ecommerce sites. PX121-SSD server with Hetzner, 128gb RAM. Running Mysql (Percona) on this server, but it's shared with nginx.
We've tried progressively upping the mysqltuner recommendations but it always seems to be saying increase table_cache, or join_buffer_size .. obviously there is a limit, and I'm starting to question the results it's producing. So, are these settings adequate? Should I be worried about any result or setting in particular? Any thoughts on how I could better optimize?
mysqltuner
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.28-76.1-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
[--] Data in MEMORY tables: 0B (Tables: 17)
[--] Data in InnoDB tables: 101M (Tables: 359)
[--] Data in MyISAM tables: 9M (Tables: 20)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 5
-------- Security Recommendations -------------------------------------------
Warning: Using a password on the command line interface can be insecure.
[OK] All database users have passwords assigned
Warning: Using a password on the command line interface can be insecure.
-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 0h 25m 46s (110M q [254.679 qps], 861K conn, TX: 80B, RX: 26B)
[--] Reads / Writes: 92% / 8%
[--] Total buffers: 8.8G global + 2.3G per thread (50 max threads)
[!!] Maximum possible memory usage: 121.3G (96% of installed RAM)
[OK] Slow queries: 0% (0/110M)
[OK] Highest usage of available connections: 50% (25/50)
[OK] Key buffer size / total MyISAM indexes: 2.0G/8.2M
[OK] Key buffer hit rate: 99.9% (124M cached / 72K reads)
[OK] Query cache efficiency: 75.4% (79M cached / 104M selects)
[!!] Query cache prunes per day: 4515972
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 446K sorts)
[!!] Joins performed without indexes: 198019
[OK] Temporary tables created on disk: 18% (135K on disk / 738K total)
[OK] Thread cache hit rate: 99% (25 created / 861K connections)
[!!] Table cache hit rate: 2% (655 open / 26K opened)
[OK] Open file limit used: 0% (79/65K)
[OK] Table locks acquired immediately: 100% (31M immediate / 31M locks)
[!!] Connections aborted: 7%
[OK] InnoDB data size / buffer pool: 101.9M/6.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Your applications are not closing MySQL connections properly
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 256M) [see warning above]
join_buffer_size (> 2.0G, or always use indexes with joins)
table_cache (> 2000)
my.cnf
# MyISAM #
key-buffer-size = 2G
myisam-recover-options = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1
binlog-format=MIXED
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query_cache_type = 1
query-cache-size = 256MB
max-connections = 50
thread-cache-size = 30
open_files_limit = 65535
table-definition-cache = 4096
query_cache_limit = 256M
join_buffer_size = 2G
sort_buffer_size = 256M
tmp_table_size = 512M
max_heap_table_size = 512M
bulk_insert_buffer_size = 64M
explicit_defaults_for_timestamp = 1
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 1500M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 6G
innodb_log_buffer_size = 32M
UPDATE: Rick James – I've run some load testing with the log-queries-not-using-indexes=1 and turned up these ones.
# User@Host: root[root] @ localhost [] Id: 485
# Schema: mage_st247_v4 Last_errno: 0 Killed: 0
# Query_time: 0.001564 Lock_time: 0.000544 Rows_sent: 22 Rows_examined: 88 Rows_affected: 0
# Bytes_sent: 2114
SET timestamp=1457499770;
SELECT `attr_table`.* FROM `catalog_product_entity_varchar` AS `attr_table`
INNER JOIN `eav_entity_attribute` AS `set_table` ON attr_table.attribute_id = set_table.attribute_id AND set_table.attribute_set_id = '4' WHERE (attr_table.entity_id = '4803') AND (attr_table.store_id IN (0, 1)) UNION ALL SELECT `attr_table`.* FROM `catalog_product_entity_decimal` AS `attr_table`
INNER JOIN `eav_entity_attribute` AS `set_table` ON attr_table.attribute_id = set_table.attribute_id AND set_table.attribute_set_id = '4' WHERE (attr_table.entity_id = '4803') AND (attr_table.store_id IN (0, 1)) UNION ALL SELECT `attr_table`.* FROM `catalog_product_entity_int` AS `attr_table`
INNER JOIN `eav_entity_attribute` AS `set_table` ON attr_table.attribute_id = set_table.attribute_id AND set_table.attribute_set_id = '4' WHERE (attr_table.entity_id = '4803') AND (attr_table.store_id IN (0, 1)) UNION ALL SELECT `attr_table`.* FROM `catalog_product_entity_text` AS `attr_table`
INNER JOIN `eav_entity_attribute` AS `set_table` ON attr_table.attribute_id = set_table.attribute_id AND set_table.attribute_set_id = '4' WHERE (attr_table.entity_id = '4803') AND (attr_table.store_id IN (0, 1)) UNION ALL SELECT `attr_table`.* FROM `catalog_product_entity_datetime` AS `attr_table`
INNER JOIN `eav_entity_attribute` AS `set_table` ON attr_table.attribute_id = set_table.attribute_id AND set_table.attribute_set_id = '4' WHERE (attr_table.entity_id = '4803') AND (attr_table.store_id IN (0, 1)) ORDER BY `store_id` ASC;
Another…
# User@Host: root[root] @ localhost [] Id: 485
# Schema: mage_st247_v4 Last_errno: 0 Killed: 0
# Query_time: 0.004065 Lock_time: 0.000733 Rows_sent: 163 Rows_examined: 717 Rows_affected: 0
# Bytes_sent: 9354
SET timestamp=1457499770;
SELECT `t_d`.`entity_id`, `t_d`.`attribute_id`, `t_d`.`value` AS `default_value`,
`t_s`.`value` AS `store_value`, IF(t_s.value_id IS NULL,
t_d.value, t_s.value
) AS `value`
FROM `catalog_product_entity_varchar` AS `t_d`
LEFT JOIN `catalog_product_entity_varchar` AS `t_s` ON t_s.attribute_id = t_d.attribute_id
AND t_s.entity_id = t_d.entity_id
AND t_s.store_id = 1
WHERE (t_d.entity_type_id = 4)
AND (t_d.entity_id IN (5117, 4415, 4234, 4178, 4107, 4066,
4019, 4018, 3971, 3970, 3796, 3762)
)
AND (t_d.attribute_id IN ('71', '86', '87', '97', '112',
'113', '114', '118', '119')
)
AND (t_d.store_id = 0)
UNION ALL
SELECT `t_d`.`entity_id`, `t_d`.`attribute_id`, `t_d`.`value` AS `default_value`,
`t_s`.`value` AS `store_value`, IF(t_s.value_id IS NULL,
t_d.value, t_s.value
) AS `value`
FROM `catalog_product_entity_text` AS `t_d`
LEFT JOIN `catalog_product_entity_text` AS `t_s` ON t_s.attribute_id = t_d.attribute_id
AND t_s.entity_id = t_d.entity_id
AND t_s.store_id = 1
WHERE (t_d.entity_type_id = 4)
AND (t_d.entity_id IN (5117, 4415, 4234, 4178, 4107, 4066,
4019, 4018, 3971, 3970, 3796, 3762)
)
AND (t_d.attribute_id IN ('73'))
AND (t_d.store_id = 0)
UNION ALL
SELECT `t_d`.`entity_id`, `t_d`.`attribute_id`, `t_d`.`value` AS `default_value`,
`t_s`.`value` AS `store_value`, IF(t_s.value_id IS NULL,
t_d.value, t_s.value
) AS `value`
FROM `catalog_product_entity_decimal` AS `t_d`
LEFT JOIN `catalog_product_entity_decimal` AS `t_s` ON t_s.attribute_id = t_d.attribute_id
AND t_s.entity_id = t_d.entity_id
AND t_s.store_id = 1
WHERE (t_d.entity_type_id = 4)
AND (t_d.entity_id IN (5117, 4415, 4234, 4178, 4107, 4066,
4019, 4018, 3971, 3970, 3796, 3762)
)
AND (t_d.attribute_id IN ('75', '76', '120'))
AND (t_d.store_id = 0)
UNION ALL
SELECT `t_d`.`entity_id`, `t_d`.`attribute_id`, `t_d`.`value` AS `default_value`,
`t_s`.`value` AS `store_value`, IF(t_s.value_id IS NULL,
t_d.value, t_s.value
) AS `value`
FROM `catalog_product_entity_datetime` AS `t_d`
LEFT JOIN `catalog_product_entity_datetime` AS `t_s` ON t_s.attribute_id = t_d.attribute_id
AND t_s.entity_id = t_d.entity_id
AND t_s.store_id = 1
WHERE (t_d.entity_type_id = 4)
AND (t_d.entity_id IN (5117, 4415, 4234, 4178, 4107, 4066,
4019, 4018, 3971, 3970, 3796, 3762)
)
AND (t_d.attribute_id IN ('77', '78', '93', '94'))
AND (t_d.store_id = 0)
UNION ALL
SELECT `t_d`.`entity_id`, `t_d`.`attribute_id`, `t_d`.`value` AS `default_value`,
`t_s`.`value` AS `store_value`, IF(t_s.value_id IS NULL,
t_d.value, t_s.value
) AS `value`
FROM `catalog_product_entity_int` AS `t_d`
LEFT JOIN `catalog_product_entity_int` AS `t_s` ON t_s.attribute_id = t_d.attribute_id
AND t_s.entity_id = t_d.entity_id
AND t_s.store_id = 1
WHERE (t_d.entity_type_id = 4)
AND (t_d.entity_id IN (5117, 4415, 4234, 4178, 4107, 4066,
4019, 4018, 3971, 3970, 3796, 3762)
)
AND (t_d.attribute_id IN ('96', '121', '123', '125', '126',
'127', '128', '131')
)
AND (t_d.store_id = 0);
I'm not very experienced in optimizing queries without indexes, but I've run explain in phpmyadmin
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY attr_table ref UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID,IDX... UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID 4 const 18 Using where
1 PRIMARY set_table eq_ref UNQ_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_SET_ID_ATTRIBUT... UNQ_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_SET_ID_ATTRIBUT... 4 const,mage_st247_v4.attr_table.attribute_id 1 Using index
2 UNION attr_table ref UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID,IDX_... UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID 4 const 3 Using where
2 UNION set_table eq_ref UNQ_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_SET_ID_ATTRIBUT... UNQ_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_SET_ID_ATTRIBUT... 4 const,mage_st247_v4.attr_table.attribute_id 1 Using index
3 UNION attr_table ref UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE... UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE... 4 const 9 Using where
3 UNION set_table eq_ref UNQ_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_SET_ID_ATTRIBUT... UNQ_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_SET_ID_ATTRIBUT... 4 const,mage_st247_v4.attr_table.attribute_id 1 Using index
4 UNION attr_table ref UNQ_CATALOG_PRODUCT_ENTITY_TEXT_ENTITY_ID_ATTRIBUT... UNQ_CATALOG_PRODUCT_ENTITY_TEXT_ENTITY_ID_ATTRIBUT... 4 const 3 Using where
4 UNION set_table eq_ref UNQ_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_SET_ID_ATTRIBUT... UNQ_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_SET_ID_ATTRIBUT... 4 const,mage_st247_v4.attr_table.attribute_id 1 Using index
5 UNION attr_table ref UNQ_CAT_PRD_ENTT_DTIME_ENTT_ID_ATTR_ID_STORE_ID,ID... UNQ_CAT_PRD_ENTT_DTIME_ENTT_ID_ATTR_ID_STORE_ID 4 const 1 Using where
5 UNION set_table eq_ref UNQ_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_SET_ID_ATTRIBUT... UNQ_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_SET_ID_ATTRIBUT... 4 const,mage_st247_v4.attr_table.attribute_id 1 Using index
NULL UNION RESULT <union1,2,3,4,5> ALL NULL NULL NULL NULL NULL Using filesort
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t_d range UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID,IDX... UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID 8 NULL 108 Using where
1 PRIMARY t_s eq_ref UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID,IDX... UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID 8 mage_st247_v4.t_d.entity_id,mage_st247_v4.t_d.attr... 1
2 UNION t_d range UNQ_CATALOG_PRODUCT_ENTITY_TEXT_ENTITY_ID_ATTRIBUT... UNQ_CATALOG_PRODUCT_ENTITY_TEXT_ENTITY_ID_ATTRIBUT... 8 NULL 12 Using where
2 UNION t_s eq_ref UNQ_CATALOG_PRODUCT_ENTITY_TEXT_ENTITY_ID_ATTRIBUT... UNQ_CATALOG_PRODUCT_ENTITY_TEXT_ENTITY_ID_ATTRIBUT... 8 mage_st247_v4.t_d.entity_id,mage_st247_v4.t_d.attr... 1
3 UNION t_d range UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID,IDX_... IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID 4 NULL 36 Using where
3 UNION t_s eq_ref UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID,IDX_... UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID 8 mage_st247_v4.t_d.entity_id,mage_st247_v4.t_d.attr... 1
4 UNION t_d range UNQ_CAT_PRD_ENTT_DTIME_ENTT_ID_ATTR_ID_STORE_ID,ID... IDX_CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID 4 NULL 12 Using where
4 UNION t_s eq_ref UNQ_CAT_PRD_ENTT_DTIME_ENTT_ID_ATTR_ID_STORE_ID,ID... UNQ_CAT_PRD_ENTT_DTIME_ENTT_ID_ATTR_ID_STORE_ID 8 mage_st247_v4.t_d.entity_id,mage_st247_v4.t_d.attr... 1
5 UNION t_d range UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE... IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID 4 NULL 111 Using where
5 UNION t_s eq_ref UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE... UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE... 8 mage_st247_v4.t_d.entity_id,mage_st247_v4.t_d.attr... 1
NULL UNION RESULT <union1,2,3,4,5> ALL NULL NULL NULL NULL NULL
Any suggestions/thoughts?
Based on your last suggestions, that would be ..
eav_entity_attribute > PRIMARY KEY(entity_id, attribute_id)
t_d EACH NEEDS > INDEX(store_id, entity_type_id, attribute_id, entity_id)
catalog_product_entity_varchar
catalog_product_entity_text
catalog_product_entity_decimal
catalog_product_entity_datetime
catalog_product_entity_int
t_s EACH NEEDS > INDEX(store_id, entity_id)
catalog_product_entity_varchar
catalog_product_entity_text
catalog_product_entity_decimal
catalog_product_entity_datetime
catalog_product_entity_int
Also, according to pt-query-digest these are the top 3 queries based on time to run * occurrences.
# Query 1: 329.74 QPS, 0.24x concurrency, ID 0x94AB56F1166B5415 at byte 22865378
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2016-03-09 16:50:51 to 16:51:37
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 20 15168
# Exec time 18 11s 381us 26ms 716us 925us 260us 690us
# Lock time 21 2s 62us 209us 115us 152us 25us 108us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 6 417.41k 10 37 28.18 33.28 8.29 33.28
# Rows affecte 13 27.71k 0 4 1.87 3.89 1.64 2.90
# Bytes sent 2 765.84k 50 53 51.70 51.63 1.22 51.63
# Query size 26 9.58M 661 665 662.49 652.75 0.00 652.75
# String:
# Databases mage_st247_v4
# Hosts localhost
# Last errno 0
# Users root
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms ##
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `mage_st247_v4` LIKE 'catalog_product_link_attribute_int'\G
# SHOW CREATE TABLE `mage_st247_v4`.`catalog_product_link_attribute_int`\G
# SHOW TABLE STATUS FROM `mage_st247_v4` LIKE 'catalog_product_entity'\G
# SHOW CREATE TABLE `mage_st247_v4`.`catalog_product_entity`\G
# SHOW TABLE STATUS FROM `mage_st247_v4` LIKE 'catalog_product_link_type'\G
# SHOW CREATE TABLE `mage_st247_v4`.`catalog_product_link_type`\G
# SHOW TABLE STATUS FROM `mage_st247_v4` LIKE 'catalog_product_link_attribute'\G
# SHOW CREATE TABLE `mage_st247_v4`.`catalog_product_link_attribute`\G
# SHOW TABLE STATUS FROM `mage_st247_v4` LIKE 'catalog_product_link'\G
# SHOW CREATE TABLE `mage_st247_v4`.`catalog_product_link`\G
INSERT IGNORE INTO catalog_product_link_attribute_int (link_id,product_link_attribute_id,value) SELECT cpl.link_id,cpla.product_link_attribute_id,0 as value
FROM catalog_product_entity AS cpe
JOIN catalog_product_entity AS cpe2 ON cpe2.entity_id!=cpe.entity_id
JOIN catalog_product_link_type AS cplt ON cplt.code='up_sell'
JOIN catalog_product_link_attribute AS cpla ON cpla.product_link_attribute_code='position' AND cpla.link_type_id=cplt.link_type_id
JOIN catalog_product_link AS cpl ON cpl.link_type_id=cplt.link_type_id AND cpl.product_id=cpe.entity_id AND cpl.linked_product_id=cpe2.entity_id
WHERE cpe.sku='SS-1000000134'\G
# Query 2: 219.83 QPS, 0.19x concurrency, ID 0x9E81BE63D9BCD798 at byte 22864673
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2016-03-09 16:50:51 to 16:51:37
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 13 10112
# Exec time 15 9s 488us 26ms 883us 1ms 322us 839us
# Lock time 12 1s 54us 182us 99us 131us 22us 93us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 9.88k 1 1 1 1 0 1
# Rows affecte 9 19.35k 0 4 1.96 3.89 1.72 2.90
# Bytes sent 1 510.57k 50 53 51.70 51.63 1.22 51.63
# Query size 12 4.44M 437 473 460.38 463.90 11.09 463.90
# String:
# Databases mage_st247_v4
# Hosts localhost
# Last errno 0
# Users root
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms ############################
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `mage_st247_v4` LIKE 'catalog_product_link'\G
# SHOW CREATE TABLE `mage_st247_v4`.`catalog_product_link`\G
# SHOW TABLE STATUS FROM `mage_st247_v4` LIKE 'catalog_product_entity'\G
# SHOW CREATE TABLE `mage_st247_v4`.`catalog_product_entity`\G
# SHOW TABLE STATUS FROM `mage_st247_v4` LIKE 'catalog_product_link_type'\G
# SHOW CREATE TABLE `mage_st247_v4`.`catalog_product_link_type`\G
INSERT IGNORE INTO catalog_product_link (link_type_id,product_id,linked_product_id) SELECT cplt.link_type_id,cpe.entity_id as product_id,cpe2.entity_id as linked_product_id
FROM catalog_product_entity as cpe
JOIN catalog_product_entity as cpe2 ON cpe2.entity_id!=cpe.entity_id AND (cpe2.sku IN ('CV-1818240008','CV-1828640003','CV-1831600001','CV-1844630025'))
JOIN catalog_product_link_type as cplt ON cplt.code='up_sell'
WHERE cpe.sku='SS-1000000134'\G
# Query 3: 4.32 QPS, 0.01x concurrency, ID 0xE06C89F6D6B1A4CD at byte 6404791
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2016-03-09 16:20:09 to 16:27:27
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 2 1892
# Exec time 9 5s 1ms 8ms 3ms 4ms 1ms 2ms
# Lock time 2 224ms 60us 282us 118us 185us 40us 103us
# Rows sent 0 350 0 2 0.18 1.96 0.57 0
# Rows examine 51 3.07M 1.66k 1.73k 1.66k 1.61k 7.78 1.61k
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 1 486.17k 260 298 263.13 284.79 7.67 258.32
# Query size 3 1.20M 660 663 662.88 652.75 0 652.75
# String:
# Databases mage_st247_v4
# Hosts localhost
# Last errno 0
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ################################################################
# 10ms
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `mage_st247_v4` LIKE 'rating_option_vote'\G
# SHOW CREATE TABLE `mage_st247_v4`.`rating_option_vote`\G
# SHOW TABLE STATUS FROM `mage_st247_v4` LIKE 'review'\G
# SHOW CREATE TABLE `mage_st247_v4`.`review`\G
# SHOW TABLE STATUS FROM `mage_st247_v4` LIKE 'review_store'\G
# SHOW CREATE TABLE `mage_st247_v4`.`review_store`\G
# SHOW TABLE STATUS FROM `mage_st247_v4` LIKE 'rating_store'\G
# SHOW CREATE TABLE `mage_st247_v4`.`rating_store`\G
# SHOW TABLE STATUS FROM `mage_st247_v4` LIKE 'review_status'\G
# SHOW CREATE TABLE `mage_st247_v4`.`review_status`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `rating_vote`.`entity_pk_value`, SUM(rating_vote.`percent`) AS `sum`, COUNT(*) AS `count`, `review_store`.`store_id` FROM `rating_option_vote` AS `rating_vote`
INNER JOIN `review` ON rating_vote.review_id=review.review_id
LEFT JOIN `review_store` ON rating_vote.review_id=review_store.review_id
INNER JOIN `rating_store` ON rating_store.rating_id = rating_vote.rating_id AND rating_store.store_id = review_store.store_id
INNER JOIN `review_status` ON review.status_id = review_status.status_id WHERE (review_status.status_code = 'Approved') AND (rating_vote.entity_pk_value = '1407') GROUP BY `rating_vote`.`entity_pk_value`,
`review_store`.`store_id`\G
Best Answer
Ignore "fragmentation"; it is bogus.
Query Cache... Do not increase its size; that will only slow things down. Even 256M is dangerously high. You have a lot of prunes. This says there are a lot of writes. Since every entry for a table in the QC is removed when any change is made to the table, this is a lot of work.
Probably the QC should be turned off.
table_open_cache -- Yes 2000 should be OK (and better that what you have?)
Are you running both MyISAM and InnoDB? Consider moving entirely to InnoDB.
join_buffer_size = 2Gis excessive. If you need that for each of 50 connections, guess what happens to your RAM? Recommend only 256M for your big RAM. (Beware of randomly increasing settings; we may have to revert those settings before getting to the real issues.)JOINs without indexes -- Well, look for where you need to add indexes. Add
long_query_time = 1and turn on the slowlog. Restart; wait a day; summarize the slowlog with eithermysqldumpslow -s torpt-query-digest. Then let's tackle the first couple of slow queries. Be sure to includeSHOW CREATE TABLEandEXPLAIN SELECT ....More (based on query-digest)
Doing hundreds of inserts/sec means you are doing hundreds of QC "prunes" per second for
catalog_product_linkandcatalog_product_link_attribute_intand possibly other tables. I would eitherquery_cache_sizeto 50M, change toDEMANDand addSQL_NO_CACHEto allSELECTsagainst those two tables (and any otherSELECTsthat hit rapidly changing tables).Since the execute times are mostly below 1ms, and concurrency is low, those
INSERT..SELECTstend to get in, do their work, and get out -- very fast. However, when things hiccup, the queries may be stumbling over each other. (Note 26ms max time. -- There may have been over a dozenINSERTsrunning at the same time.) When such stumbling occurs, it can cascade into 'disaster'. My preferred 'cure' is to limit the number of threads that can be doing that task; it's better to have a few threads that will finish than have a lot of threads, all vying for resources and "never" finishing.Is there any way in the application to decrease the number of times that it calls them? Inserting into one table stuff from other tables is generally a 'no-no' in databases -- usually better to compute on the way out.
The third query (
SELECT FROM mage_*) looks at about 1700 rows and takes a few milliseconds -- not bad. It has cross-tableWHEREandGROUP BY, hinting at "over-normalization". The aggregates worry me -- they may be inflated by theJOINs.