В рамках анализа удивительных результатов производительности MySQL 5.5 против 5.6 я рассмотрел изменения значений переменных по умолчанию. Для этого я загрузил значения из MySQL 5.5.30 и 5.6.10 в разные таблицы и выполнил запрос:
mysql [localhost] {msandbox} (test) > select var55.variable_name,left(var55.variable_value,40) value55, left(var56.variable_value,40) var56 from var55 left join var56 on var55.variable_name=var56.variable_name where var55.variable_value!=var56.variable_value;
+---------------------------------------------------+------------------------------------------+------------------------------------------+
| variable_name | value55 | var56 |
+---------------------------------------------------+------------------------------------------+------------------------------------------+
| PERFORMANCE_SCHEMA | OFF | ON |
| PID_FILE | /mnt/data/sandboxes/msb_5_5_30/data/mysq | /mnt/data/sandboxes/msb_5_6_10/data/mysq |
| CHARACTER_SETS_DIR | /mnt/nfs/dist/mysql-5.5.30-linux2.6-x86_ | /mnt/nfs/dist/mysql-5.6.10-linux-glibc2. |
| PERFORMANCE_SCHEMA_MAX_COND_INSTANCES | 1000 | 836 |
| PERFORMANCE_SCHEMA_MAX_MUTEX_INSTANCES | 1000000 | 3282 |
| OLD_PASSWORDS | OFF | 0 |
| INNODB_STATS_ON_METADATA | ON | OFF |
| PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_SIZE | 10 | 5 |
| PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_LONG_SIZE | 10000 | 100 |
| PERFORMANCE_SCHEMA_MAX_RWLOCK_INSTANCES | 1000000 | 1724 |
| PERFORMANCE_SCHEMA_MAX_TABLE_HANDLES | 100000 | 2223 |
| INNODB_LOG_FILE_SIZE | 5242880 | 50331648 |
| BASEDIR | /mnt/nfs/dist/5.5.30 | /mnt/nfs/dist/5.6.10 |
| BACK_LOG | 50 | 80 |
| OPEN_FILES_LIMIT | 1024 | 5000 |
| INNODB_AUTOEXTEND_INCREMENT | 8 | 64 |
| MAX_CONNECT_ERRORS | 10 | 100 |
| SORT_BUFFER_SIZE | 2097152 | 262144 |
| LC_MESSAGES_DIR | /mnt/nfs/dist/mysql-5.5.30-linux2.6-x86_ | /mnt/nfs/dist/mysql-5.6.10-linux-glibc2. |
| MAX_ALLOWED_PACKET | 1048576 | 4194304 |
| JOIN_BUFFER_SIZE | 131072 | 262144 |
| TMPDIR | /mnt/data/sandboxes/msb_5_5_30/tmp | /mnt/data/sandboxes/msb_5_6_10/tmp |
| TABLE_OPEN_CACHE | 400 | 2000 |
| INNODB_VERSION | 5.5.30 | 1.2.10 |
| INNODB_BUFFER_POOL_INSTANCES | 1 | 8 |
| QUERY_CACHE_SIZE | 0 | 1048576 |
| SLOW_QUERY_LOG_FILE | /mnt/data/sandboxes/msb_5_5_30/data/dpe0 | /mnt/data/sandboxes/msb_5_6_10/data/dpe0 |
| TABLE_DEFINITION_CACHE | 400 | 1400 |
| PORT | 5530 | 5610 |
| QUERY_CACHE_TYPE | ON | OFF |
| REPORT_PORT | 5530 | 5610 |
| PERFORMANCE_SCHEMA_MAX_FILE_INSTANCES | 10000 | 1556 |
| SQL_MODE | | NO_ENGINE_SUBSTITUTION |
| INNODB_OLD_BLOCKS_TIME | 0 | 1000 |
| LOG_ERROR | /mnt/data/sandboxes/msb_5_5_30/data/msan | /mnt/data/sandboxes/msb_5_6_10/data/msan |
| VERSION_COMPILE_OS | linux2.6 | linux-glibc2.5 |
| THREAD_CACHE_SIZE | 0 | 9 |
| PLUGIN_DIR | /mnt/nfs/dist/5.5.30/lib/plugin/ | /mnt/nfs/dist/5.6.10/lib/plugin/ |
| SYNC_RELAY_LOG | 0 | 10000 |
| GENERAL_LOG_FILE | /mnt/data/sandboxes/msb_5_5_30/data/dpe0 | /mnt/data/sandboxes/msb_5_6_10/data/dpe0 |
| PERFORMANCE_SCHEMA_MAX_TABLE_INSTANCES | 50000 | 445 |
| SYNC_RELAY_LOG_INFO | 0 | 10000 |
| SLAVE_LOAD_TMPDIR | /mnt/data/sandboxes/msb_5_5_30/tmp | /mnt/data/sandboxes/msb_5_6_10/tmp |
| SECURE_AUTH | OFF | ON |
| VERSION | 5.5.30 | 5.6.10 |
| INNODB_CONCURRENCY_TICKETS | 500 | 5000 |
| INNODB_PURGE_THREADS | 0 | 1 |
| INNODB_OPEN_FILES | 300 | 2000 |
| INNODB_DATA_FILE_PATH | ibdata1:10M:autoextend | ibdata1:12M:autoextend |
| INNODB_PURGE_BATCH_SIZE | 20 | 300 |
| PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES | 1000 | 224 |
| SOCKET | /tmp/mysql_sandbox5530.sock | /tmp/mysql_sandbox5610.sock |
| INNODB_FILE_PER_TABLE | OFF | ON |
| SYNC_MASTER_INFO | 0 | 10000 |
| DATADIR | /mnt/data/sandboxes/msb_5_5_30/data/ | /mnt/data/sandboxes/msb_5_6_10/data/ |
| OPTIMIZER_SWITCH | index_merge=on,index_merge_union=on,inde | index_merge=on,index_merge_union=on,inde |
+---------------------------------------------------+------------------------------------------+------------------------------------------+
56 rows in set (0.05 sec)
Давайте посмотрим, какие наиболее важные изменения нужно учитывать, и их возможное влияние:
performance_schema включен по умолчанию в MySQL 5.6, но вы можете видеть, что многие параметры могут быть уменьшены по сравнению со значениями по умолчанию MySQL 5.5. Такие как performance_schema автоматически масштабируются для подсчета до 445 таблиц и 224 потоков в этом случае, ниже, чем 5,5 значений. Хотя это имеет смысл, поскольку max_connections всего 150, а в этой системе менее 200 таблиц.
innodb_stats_on_metadata по умолчанию отключена в MySQL 5.6 Добро пожаловать на гораздо более быстрые запросы information_schema!
innodb_log_file_size — значение по умолчанию увеличено с 5 МБ до приблизительно 50 МБ, что является хорошим изменением, хотя я думаю, что значение по умолчанию можно было бы сделать еще больше. Интенсивная рабочая нагрузка намного лучше работает на MySQL 5.6 с конфигурацией по умолчанию
back_log Незначительное увеличение от 50 до 80 не имеет большого значения. Системы с большим количеством соединений в секунду все равно должны будут значительно увеличить его.
open_files_limit по умолчанию теперь 5000 против 1024.
Размер innodb_auto_extend_increment теперь составляет 64 МБ вместо 8 МБ, что должно помочь уменьшить фрагментацию и сделать рост файла более редким событием.
max_connect_errors был увеличен с 10 до 100, что является хорошим изменением для уменьшения вероятности заблокированных ошибок хоста, хотя я думаю, что можно пойти с еще более высоким значением по умолчанию.
sort_buffer_size был уменьшен до 256K с 2M. Это изменение должно помочь многим мелким сортировкам, для которых выделение 2М для буфера сортировки было очень дорогим. Это может негативно повлиять на некоторые крупные сортировки, хотя смещение для сортировки слияния происходит намного раньше.
max_allowed_packet составляет 4 МБ вместо 1 МБ, что позволяет MySQL обрабатывать большие запросы. Имеет смысл, так как объем доступной памяти в наши дни намного больше.
join_buffer_size был увеличен до 256K с 128K. Это, вероятно, сделано для большей согласованности значений переменных. Я не ожидаю большого воздействия здесь.
table_open_cache увеличен с 400 до 2000 по умолчанию. Хорошее изменение делает практичным использование по умолчанию на большем наборе установок
Значение innodb_buffer_pool_instances теперь равно 8 вместо 1, что оптимизировано для рабочих нагрузок с более высоким уровнем параллелизма. Имеет смысл, поскольку в наши дни на серверах доступно гораздо больше ядер.
query_cache_type и query_cache_size . Поведение по умолчанию «без кеша» по-прежнему, но теперь оно выполняется иначе Query_cache_type теперь отключен по умолчанию с размером по умолчанию 1 МБ, в то время как в MySQL 5.5 и раньше он был «включен» по умолчанию с размером кэша запросов 0, что делает его отключенным. Хотелось бы, чтобы query_cache_size был больше по умолчанию, так как значение 1M слишком мало, чтобы быть практичным, если кто-то попытается включить его.
sql_mode имеет значение NO_ENGINE_SUBSTITUTION по умолчанию, что является хорошим изменением, если попытаться создать таблицу Innodb, но получить MyISAM, потому что Innodb по какой-то причине был отключен, что приводило к ошибкам. Обратите внимание, что это касается MySQL 5.6 — STRICT_MODE и другие безопасные поведения по умолчанию не включены.
innodb_old_blocks_time теперь установлено на 1000, что делает сканирование размера пула пула Innodb по умолчанию устойчивым к сканированию. Очень долгожданное изменение!
thread_cache_size включен по умолчанию, хотя мне интересно, почему значение по умолчанию равно 9. В любом случае очень приветствуются изменения, чтобы помочь рабочим нагрузкам со многими соединениями / разъединениями
sync_relay_log_info и sync_master_info теперь имеют значение по умолчанию 10000 вместо 0, что означает «никогда». 10000 действительно почти так же хорош, как никогда, хотя он предназначен для обеспечения некоторого уровня гарантии независимо от политики очистки файловой системы. Это, вероятно, не повлияет на большинство рабочих нагрузок.
secure_auth теперь включен по умолчанию, требуя нового рукопожатия пароля, по существу блокируя старый небезопасный. Хороший.
innodb_concurrency_tickets был увеличен с 500 до 5000. Если вы используете innodb_thread_concurrency, это уменьшит накладные расходы, связанные с захватом и освобождением слота innodb_thread_concurrency, но увеличит потенциальное истощение потоков в очереди, особенно для рабочих нагрузок, связанных с IO. На большинство пользователей это не повлияет, поскольку innodb_thread_concurrency по умолчанию равен 0, поэтому эта функция очереди отключена.
Значение innodb_purge_threads теперь равно 1 по умолчанию с использованием выделенного потока фоновой очистки. Хорошее изменение для большинства рабочих нагрузок.
innodb_open_files был увеличен до 2000 с 300. Хорошие изменения, учитывая open_files_limit, также были подняты. Маловероятно, что это принесет значительный выигрыш, если только открытие и закрытие файлов не является дорогостоящей операцией (например, использование MySQL в файловой системе NFS).
innodb_data_file_path получил небольшое изменение с начальным размером ibdata1, увеличенным с 10M до 12M. Я не уверен, какова цель этого изменения, но вряд ли оно будет иметь какое-либо практическое значение для пользователей. Учитывая, что значение по умолчанию innodb_auto_extend_increment равно 64, начиная с 64M, возможно, имело бы больше смысла.
innodb_purge_patch_size был увеличен с 20 до 300, что, я думаю, является одним из компонентов очистки тонкой настройки в MySQL 5.6
innodb_file_per_table теперь включен по умолчанию. Это очень большая перемена и хорошая. Мы выполняем innodb_file_per_table = 1 для большинства рабочих нагрузок уже много лет. Исключение составляют случаи, когда у вас есть большое количество таблиц или если вы создаете / удаляете таблицы innodb.
optimizer_switch — это переменная catch all для множества опций оптимизатора. Интересно, почему он не был реализован в виде ряда различных переменных, которые, по моему мнению, имели бы больше смысла. MySQL 5.6 добавляет намного больше переключателей оптимизатора, с которыми вы можете играть:
mysql [localhost] {msandbox} (test) > select * from var55 where variable_name='OPTIMIZER_SWITCH' \G
*************************** 1. row ***************************
VARIABLE_NAME: OPTIMIZER_SWITCH
VARIABLE_VALUE: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > select * from var56 where variable_name='OPTIMIZER_SWITCH' \G
*************************** 1. row ***************************
VARIABLE_NAME: OPTIMIZER_SWITCH
VARIABLE_VALUE: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)