MYSQL5.6がリリースされてから2週間経過して色々と検証してくださる方がいて、
MYSQL5.6からはPerformance_SchemaがDefaultになっているので少しオーバーヘッドが高いのでは?
などVariableの値が増えているなど色々と出てきました。
Review of MySQL 5.6 Defaults Changes
MySQL 5.5 and 5.6 default variable values differences
Is MySQL 5.6 slower than MySQL 5.5 ?
Performance Schema overhead
MySQL5.6で増えたexplicit_defaults_for_timestamp
Performance検証は今後時間をとって行いますが、先ずはVariableの変更部分だけ調べてみました。
variableの値は”331 rows in set”が返ってくる。
mysql> select @@hostname; +---------------------+ | @@hostname | +---------------------+ | HOME001.localdomain | +---------------------+ 1 row in set (0.00 sec) mysql> select @@version; +------------+ | @@version | +------------+ | 5.5.29-log | +------------+ 1 row in set (0.00 sec) mysql> [root@HOME001 ~]# mysql -u root -e "show variables" -p | awk '{ print $1}' > /home/mysql/5.5.29.txt Enter password: [root@HOME001 ~]#
variableの値は”435 rows in set”が返ってくる。
mysql> select @@hostname; +---------------------+ | @@hostname | +---------------------+ | HOME002.localdomain | +---------------------+ 1 row in set (0.00 sec) mysql> select @@version; +------------+ | @@version | +------------+ | 5.6.10-log | +------------+ 1 row in set (0.00 sec) mysql> [root@HOME002 ~]# mysql -u root -e "show variables" -p | awk '{ print $1}' > 5.6.10.txt Enter password: [root@HOME002 ~]#
上記で取得した値をdiffコマンドで、増加・変更のあった値を調査してみる。
[root@HOME002 mysql]# diff -y -y --suppress-common-lines 5.5.29.txt 5.6.10.txt > bind_address > binlog_checksum > binlog_max_flush_queue_time > binlog_order_commits > binlog_row_image > binlog_rows_query_log_events > core_file > default_tmp_storage_engine > disconnect_on_expired_password engine_condition_pushdown | end_markers_in_json > enforce_gtid_consistency > eq_range_index_dive_limit > explicit_defaults_for_timestamp > gtid_executed > gtid_mode > gtid_next > gtid_owned > gtid_purged have_csv < have_innodb < have_ndbcluster < have_partitioning < > host_cache_size > ignore_db_dirs > innodb_adaptive_flushing_lwm > innodb_api_bk_commit_interval > innodb_api_disable_rowlock > innodb_api_enable_binlog > innodb_api_enable_mdl > innodb_api_trx_level > innodb_buffer_pool_dump_at_shutdown > innodb_buffer_pool_dump_now > innodb_buffer_pool_filename > innodb_buffer_pool_load_abort > innodb_buffer_pool_load_at_startup > innodb_buffer_pool_load_now > innodb_change_buffer_max_size > innodb_checksum_algorithm > innodb_cmp_per_index_enabled > innodb_compression_failure_threshold_pct > innodb_compression_level > innodb_compression_pad_pct_max > innodb_disable_sort_file_cache > innodb_flush_log_at_timeout > innodb_flush_neighbors > innodb_flushing_avg_loops > innodb_ft_aux_table > innodb_ft_cache_size > innodb_ft_enable_diag_print > innodb_ft_enable_stopword > innodb_ft_max_token_size > innodb_ft_min_token_size > innodb_ft_num_word_optimize > innodb_ft_server_stopword_table > innodb_ft_sort_pll_degree > innodb_ft_user_stopword_table > innodb_io_capacity_max > innodb_lru_scan_depth > innodb_max_dirty_pages_pct_lwm > innodb_max_purge_lag_delay > innodb_monitor_disable > innodb_monitor_enable > innodb_monitor_reset > innodb_monitor_reset_all > innodb_online_alter_log_max_size > innodb_optimize_fulltext_only > innodb_page_size > innodb_print_all_deadlocks > innodb_read_only > innodb_sort_buffer_size > innodb_stats_auto_recalc > innodb_stats_persistent > innodb_stats_persistent_sample_pages > innodb_stats_transient_sample_pages > innodb_sync_array_size > innodb_undo_directory > innodb_undo_logs > innodb_undo_tablespaces log < > log_bin_basename > log_bin_index > log_bin_use_v1_row_events log_slow_queries | log_throttle_queries_not_using_indexes > master_info_repository > master_verify_checksum max_long_data_size < > metadata_locks_hash_instances > optimizer_trace > optimizer_trace_features > optimizer_trace_limit > optimizer_trace_max_mem_size > optimizer_trace_offset > performance_schema_accounts_size > performance_schema_digests_size > performance_schema_events_stages_history_long_size > performance_schema_events_stages_history_size > performance_schema_events_statements_history_long_size > performance_schema_events_statements_history_size > performance_schema_hosts_size > performance_schema_max_socket_classes > performance_schema_max_socket_instances > performance_schema_max_stage_classes > performance_schema_max_statement_classes > performance_schema_session_connect_attrs_size > performance_schema_setup_actors_size > performance_schema_setup_objects_size > performance_schema_users_size > pseudo_slave_mode > relay_log_basename > relay_log_info_repository rpl_recovery_rank | rpl_semi_sync_slave_enabled rpl_semi_sync_master_enabled | rpl_semi_sync_slave_trace_level rpl_semi_sync_master_timeout < rpl_semi_sync_master_trace_level < rpl_semi_sync_master_wait_no_slave < > server_id_bits > server_uuid > slave_allow_batching > slave_checkpoint_group > slave_checkpoint_period > slave_parallel_workers > slave_pending_jobs_size_max > slave_rows_search_algorithms > slave_sql_verify_checksum sql_big_tables < sql_low_priority_updates < sql_max_join_size < > ssl_crl > ssl_crlpath > table_open_cache_instances > transaction_allow_batching > tx_read_only [root@HOME002 mysql]#