Блог

Недавно мы обновили наш sql-сервер percona 5.5 до версии percona 5.7. Пока все работает нормально. к сожалению, у нас огромный запрос, который работает крайне медленно под 5.7. Под 5.5. это занимает меньше секунды, даже с sql_no_cache. С Percona 5.7. выполнение этого запроса занимает до 1 минуты. странно то, что чем больше комбинированных показателей мы используем, тем медленнее он становится. удаление всех объединенных признаков приводит к времени выполнения 30 секунд. принудительное выполнение sql_straight_join заставляет запрос выполняться менее чем за секунду.

итак, вот запрос:

 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')
  

и объяснение для 5.7.:

мы попробовали обновление, а также полную пустую установку. включал и выключал все режимы sql и параметры оптимизации запросов. если вам нужна дополнительная информация или переменные сервера, дайте мне знать.

ОС: Debian GNU / Linux 8 (jessie) версия сервера: 5.7.14-7-log Percona Server (GPL), выпуск ‘7’, редакция ‘083e298’

может быть, у вас есть подсказка, чего нам не хватает.

редактировать: добавление конфигурации

 [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
  

редактировать 2:
добавьте объяснение для 5.5

Новый порядок соединений, вероятно, связан с тем, что MySQL 5.7 переоценивает эффект фильтрации, который может быть выполнен на основе предложений WHERE и ON . В MySQL 5.6 фильтрация не учитывалась, что часто приводило к выбору ненужного дорогостоящего порядка соединений. В общем, MySQL 5.7 часто сможет найти лучший порядок соединений, принимая во внимание фильтрацию. Однако для условий в неиндексированных столбцах оценка фильтрации — это всего лишь предположение, которое может плохо работать для условий, которые не очень избирательны.

Вы можете вернуться к поведению 5.6, установив optimizer_switch=’condition_fanout_filter=off’, или вы можете использовать STRAIGHT_JOIN для принудительного упорядочения соединений.