0

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
1

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 = 2G is 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 = 1 and turn on the slowlog. Restart; wait a day; summarize the slowlog with either mysqldumpslow -s t or pt-query-digest. Then let's tackle the first couple of slow queries. Be sure to include SHOW CREATE TABLE and EXPLAIN SELECT ....

More (based on query-digest)

Doing hundreds of inserts/sec means you are doing hundreds of QC "prunes" per second for catalog_product_link and catalog_product_link_attribute_int and possibly other tables. I would either

  • Turn off the Query Cache, or
  • Lower query_cache_size to 50M, change to DEMAND and add SQL_NO_CACHE to all SELECTs against those two tables (and any other SELECTs that hit rapidly changing tables).

Since the execute times are mostly below 1ms, and concurrency is low, those INSERT..SELECTs tend 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 dozen INSERTs running 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-table WHERE and GROUP BY, hinting at "over-normalization". The aggregates worry me -- they may be inflated by the JOINs.

  • Hi Rick - thanks for the reply, I've updated the original comment after a change to the logging, and performed a load test. Hoping you can make comment, offer your thoughts on where to place indexes? – ChrisD Mar 9 '16 at 5:26
  • eav_entity_attribute needs PRIMARY KEY(entity_id, attribute_id). You are finding out one of many reasons why EAV schema sucks. My 2 cents. – Rick James Mar 9 '16 at 5:33
  • log-queries-not-using-indexes=1 clutters the slowlog with less important info. Suggest turning it off. – Rick James Mar 9 '16 at 5:34
  • t_d needs INDEX(store_id, entity_type_id, attribute_id, entity_id) -- More on building indexes. – Rick James Mar 9 '16 at 5:39
  • t_s needs INDEX(store_id, entity_id) – Rick James Mar 9 '16 at 5:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.