Percona 5.7 lent sur de nombreuses jonctions

Question

Nous avons récemment mis à niveau notre serveur sql percona 5.5 vers percona 5.7. Jusqu'à présent, tout va bien. Malheureusement, nous avons une requête énorme qui est extrêmement lente sous 5.7. Sous 5.5. cela prend moins d'une seconde, même avec sql_no_cache. Avec Percona 5.7., il faut jusqu'à 1 minute pour exécuter cette requête. Ce qui est étrange, c'est que plus nous utilisons d'indicateurs combinés, plus cela devient lent. En supprimant tous les indicateurs combinés, on obtient un temps d'exécution de 30 secondes. En forçant sql_straight_join, la requête s'exécute en moins d'une seconde.

Voici donc la requête :

SELECT t0_.tree_id AS tree_id0, t1_.treetype_name AS treetype_name1, c2_.contentelement_id AS contentelement_id2, t0_.tree_name AS tree_name3, (CASE WHEN t3_.treetype_name <> 'global' THEN t4_.tree_name ELSE t0_.tree_name END) AS sclr4, p5_.picture_id AS picture_id5, t6_.tree_misc_value_text AS tree_misc_value_text6, (CASE WHEN t3_.treetype_name <> 'global' THEN t7_.tree_misc_value_text ELSE t6_.tree_misc_value_text END) AS sclr7, w8_.widgetgeneral_slug AS widgetgeneral_slug8, (CASE WHEN t3_.treetype_name <> 'global' THEN w9_.widgetgeneral_slug ELSE w8_.widgetgeneral_slug END) AS sclr9, t10_.tree_misc_value_text AS tree_misc_value_text10, t11_.tree_misc_value_text AS tree_misc_value_text11
FROM tree_relation t12_
INNER JOIN tree t4_ ON t12_.tree_relation_parent = t4_.tree_id
INNER JOIN treetype t3_ ON t4_.tree_type_id = t3_.treetype_id AND (t3_.treetype_name IN ('global', 'country'))
INNER JOIN contentelement c13_ ON t4_.tree_id = c13_.contentelement_tree_id
INNER JOIN contentleaf c14_ ON c13_.contentelement_contentleaf_id = c14_.contentleaf_id AND (c14_.contentleaf_contentbranch_id = 1)
INNER JOIN widgetgeneral w9_
INNER JOIN widgetabstract w15_ ON w9_.widgetabstract_id = w15_.widgetabstract_id AND (w15_.widgetabstract_contentelement_id = c13_.contentelement_id AND w15_.widgetabstract_discriminator IN ('general') AND w15_.widgetabstract_state = 'preview')
INNER JOIN tree t0_ ON t12_.tree_relation_child = t0_.tree_id
INNER JOIN treetype t1_ ON t0_.tree_type_id = t1_.treetype_id AND (t1_.treetype_name IN ('city','region'))
INNER JOIN contentelement c2_ ON t0_.tree_id = c2_.contentelement_tree_id
INNER JOIN contentleaf c16_ ON c2_.contentelement_contentleaf_id = c16_.contentleaf_id AND (c16_.contentleaf_contentbranch_id = 1)
INNER JOIN widgetgeneral w8_
INNER JOIN widgetabstract w17_ ON w8_.widgetabstract_id = w17_.widgetabstract_id AND (w17_.widgetabstract_contentelement_id = c2_.contentelement_id AND w17_.widgetabstract_discriminator IN ('general') AND w17_.widgetabstract_state = 'preview')
INNER JOIN widgetgeneral w18_
INNER JOIN widgetabstract w19_ ON w18_.widgetabstract_id = w19_.widgetabstract_id AND (w19_.widgetabstract_contentleaf_id = c16_.contentleaf_id AND w19_.widgetabstract_discriminator IN ('general') AND w19_.widgetabstract_state = 'preview')
LEFT JOIN picture p5_ ON t0_.tree_picture_id = p5_.picture_id
LEFT JOIN tree_misc t6_ ON t0_.tree_id = t6_.tree_misc_tree_id AND (t6_.tree_misc_attributetype_key = 'flagId')
LEFT JOIN tree_misc t7_ ON t4_.tree_id = t7_.tree_misc_tree_id AND (t7_.tree_misc_attributetype_key = 'flagId')
LEFT JOIN tree_misc t10_ ON t0_.tree_id = t10_.tree_misc_tree_id AND (t10_.tree_misc_attributetype_key = 'latitude')
LEFT JOIN tree_misc t11_ ON t0_.tree_id = t11_.tree_misc_tree_id AND (t11_.tree_misc_attributetype_key = 'longitude')
WHERE w17_.widgetabstract_visibility = 'active' OR (w17_.widgetabstract_visibility = 'parent' AND w19_.widgetabstract_visibility = 'active')

et l'explication pour 5.7. :

nous avons essayé la mise à jour ainsi que l'installation vierge complète. nous avons activé et désactivé tous les modes sql et les options d'optimisation des requêtes. si vous avez besoin de plus d'informations ou de variables de serveur, faites-le moi savoir.

os : Debian GNU/Linux 8 (jessie) La version du serveur est : 5.7.14-7-log Serveur Percona (GPL), version '7', révision '083e298'.

peut-être avez-vous un indice de ce qui nous manque.

éditer : ajout de la configuration

[mysqld]
port                            = 3306
user                            = mysql
socket                          = /var/run/mysqld/mysqld.sock
pid-file                        = /var/run/mysqld/mysqld.pid
basedir                         = /usr
datadir                         = /var/lib/mysql
tmpdir                          = /tmp
lc-messages-dir                 = /usr/share/mysql
max_connect_errors              = 1000000
log-error                       = /var/log/mysql/error.log
skip-external-locking
myisam-recover-options          = BACKUP
character-set-server            = utf8
collation-server                = utf8_general_ci
interactive_timeout             = 28800
wait_timeout                    = 28800
skip-name-resolve
group_concat_max_len            = 268435456

innodb_file_per_table
innodb_buffer_pool_size               = 48G
innodb_buffer_pool_instances          = 1
innodb_flush_log_at_trx_commit        = 1
innodb_data_file_path                 = ibdata1:2G:autoextend
innodb_log_file_size                  = 256M
innodb_log_buffer_size                = 64M
innodb_file_format                    = barracuda
innodb_flush_method                   = O_DIRECT[mysqld_safe]
syslog
numa_interleave

# Per Thread
sort_buffer_size        = 4M
read_buffer_size        = 2M

# Cache/connection relevant
thread_cache_size       = 850
table_open_cache        = 4048
max_connections         = 1300

# MyISAM settings (also valid for queries with temporary tables)
key_buffer_size         = 128M
myisam_sort_buffer_size = 16M

# Misc
max_allowed_packet      = 256M
max_heap_table_size     = 16M
thread_stack            = 192K
tmp_table_size          = 16M

# Query cache
query_cache_limit       = 5M
query_cache_size        = 4024M

server-id              = 102
log_bin                = /var/log/mysql/mysql-bin.log
binlog_format          = mixed
expire_logs_days       = 10
max_binlog_size        = 100M
# enforce syncing of every transation to binlog (crash safe, with bbu this should be fast)
sync_binlog            = 1
sync_relay_log         = 1
sync_master_info       = 1
sync_relay_log_info    = 1
relay-log              = mysqld-relay-bin
skip-slave-start
log-slave-updates

slow_query_log                 = 1
slow_query_log_file            = /var/log/mysql/mysql-slow.log
long_query_time                = 1
log-queries-not-using-indexes

edit 2 : ajouter une explication pour 5.5

Answer 1

Le nouvel ordre de jointure est probablement dû au fait que MySQL 5.7 surestime l'effet du filtrage qui peut être effectué sur la base des clauses WHERE et ON. Dans MySQL 5.6, le filtrage n'était pas pris en compte, ce qui entraînait souvent le choix d'un ordre de jointure coûteux et inutile. En général, MySQL 5.7 sera souvent capable de trouver un meilleur ordre de jointure en prenant en compte le filtrage. Cependant, pour les conditions sur des colonnes non indexées, l'estimation du filtrage n'est qu'une supposition qui peut ne pas fonctionner correctement pour les conditions qui ne sont pas très sélectives.

Vous pouvez revenir au comportement de la version 5.6 en définissant l'option optimizer_switch='condition_fanout_filter=off', ou vous pouvez utiliser STRAIGHT_JOIN pour forcer un ordre de jointure spécifique.