![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
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]#
