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]#

variables

Comments are closed.

Post Navigation