Skip to content

Instantly share code, notes, and snippets.

@nirbhayc

nirbhayc/MDEV-7635

Last active August 8, 2016 14:37
Show Gist options
  • Save nirbhayc/ad0a6d64052deccefb6d576f008b76ef to your computer and use it in GitHub Desktop.
Save nirbhayc/ad0a6d64052deccefb6d576f008b76ef to your computer and use it in GitHub Desktop.
Download ZIP
Raw
MDEV-7635
List #1 : 5.7.14 vs 5.6.32 vs 10.2.2
------------------------------------
MariaDB [test]> SELECT _57.name Variable, _57.value _57, _56.value _56, _102.value _102
-> FROM sysvars_mysql_5_7_14 _57, sysvars_mysql_5_6_32 _56, sysvars_maria_10_2_2 _102
-> WHERE _57.name = _56.name
-> AND _57.name = _102.name
-> AND _57.value != _56.value
-> AND _57.name NOT IN ('sql_mode', 'optimizer_switch', 'log_error', 'version', 'timestamp');
+------------------------------------------+-----------------+-----------+-------------+
| Variable | _57 | _56 | _102 |
+------------------------------------------+-----------------+-----------+-------------+
| binlog_format | ROW | STATEMENT | STATEMENT | <- D. Black (required for Galera)
| have_openssl | YES | DISABLED | YES |
| have_ssl | YES | DISABLED | DISABLED |
| innodb_buffer_pool_dump_at_shutdown | ON | OFF | OFF | <- D. Black
| innodb_buffer_pool_instances | 1 | 8 | 8 | # Nirbhay: There is no point in raising this value unless we raise innodb_buffer_pool_size.
| innodb_buffer_pool_load_at_startup | ON | OFF | OFF | <- D. Black
| innodb_checksum_algorithm | crc32 | innodb | INNODB | <- D. Black
| innodb_file_format | Barracuda | Antelope | Antelope | <- D. Black
| innodb_file_format_max | Barracuda | Antelope | Antelope |
| innodb_large_prefix | ON | OFF | OFF | <- D. Black
| innodb_log_buffer_size | 16777216 | 8388608 | 16777216 |
| innodb_max_dirty_pages_pct | 75.000000 | 75 | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000 | 0 | 0.001000 |
| innodb_purge_threads | 4 | 1 | 1 | <- D. Black
| innodb_strict_mode | ON | OFF | OFF | <- D. Black
| log_warnings | 2 | 1 | 1 |
| performance_schema_accounts_size | -1 | 100 | -1 |
| performance_schema_hosts_size | -1 | 100 | -1 |
| performance_schema_max_cond_instances | -1 | 1382 | -1 |
| performance_schema_max_file_classes | 80 | 50 | 50 |
| performance_schema_max_file_instances | -1 | 2557 | -1 |
| performance_schema_max_mutex_instances | -1 | 5755 | -1 |
| performance_schema_max_rwlock_instances | -1 | 3138 | -1 |
| performance_schema_max_socket_instances | -1 | 230 | -1 |
| performance_schema_max_statement_classes | 193 | 168 | 181 |
| performance_schema_max_table_handles | -1 | 616 | -1 |
| performance_schema_max_table_instances | -1 | 684 | -1 |
| performance_schema_max_thread_instances | -1 | 288 | -1 |
| performance_schema_setup_actors_size | -1 | 100 | 100 |
| performance_schema_setup_objects_size | -1 | 100 | 100 |
| performance_schema_users_size | -1 | 100 | -1 |
| slave_net_timeout | 60 | 3600 | 3600 | <- D. Black
| ssl_ca | ca.pem | | |
| ssl_cert | server-cert.pem | | |
| ssl_key | server-key.pem | | |
| sync_binlog | 1 | 0 | 0 | <- D. Black
+------------------------------------------+-----------------+-----------+-------------+
39 rows in set (0.06 sec)
List #2 : 5.7.14 vs 5.6.32 vs 10.2.2 (only sql_mode, optimizer_switch)
----------------------------------------------------------------------
MariaDB [test]> SELECT _57.name Variable, _57.value _57, _56.value _56, _102.value _102
-> FROM sysvars_mysql_5_7_14 _57, sysvars_mysql_5_6_32 _56, sysvars_maria_10_2_2 _102
-> WHERE _57.name = _56.name
-> AND _57.name = _102.name
-> AND _57.value != _56.value
-> AND _57.name IN ('sql_mode', 'optimizer_switch')\G;
*************************** 1. row ***************************
Variable: optimizer_switch
_57: 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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
_56: 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
_102: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=off
*************************** 2. row ***************************
Variable: sql_mode
_57: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
_56: NO_ENGINE_SUBSTITUTION
_102: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
2 rows in set (0.00 sec)
List #3 : 5.7.14 vs vs 10.2.2 (minus common variables from list #1 above)
-------------------------------------------------------------------------
MariaDB [test]> SELECT _57.name Variable, _57.value _57, _102.value _102
-> FROM sysvars_mysql_5_7_14 _57, sysvars_maria_10_2_2 _102
-> WHERE _57.name = _102.name
-> AND _57.value != _102.value
-> AND _57.name NOT IN ('sql_mode', 'optimizer_switch', 'log_error', 'version',
-> 'timestamp', 'pid_file', 'plugin_dir', 'slow_query_log_file',
-> 'lc_messages_dir', 'character_sets_dir', 'datadir',
-> 'general_log_file');
+--------------------------------------------------------+----------------------+-------------+
| Variable | _57 | _102 |
+--------------------------------------------------------+----------------------+-------------+
| default_tmp_storage_engine | InnoDB | |
| innodb_adaptive_flushing_lwm | 10 | 10.000000 |
| innodb_buffer_pool_dump_pct | 25 | 100 |
| innodb_log_compressed_pages | ON | OFF |
| key_buffer_size | 8388608 | 134217728 |
| max_relay_log_size | 0 | 1073741824 |
| max_seeks_for_key | 18446744073709551615 | 4294967295 |
| max_write_lock_count | 18446744073709551615 | 4294967295 |
| myisam_recover_options | OFF | DEFAULT |
| myisam_sort_buffer_size | 8388608 | 134216704 |
| old_passwords | 0 | OFF |
| performance_schema | ON | OFF |
| performance_schema_digests_size | 5000 | -1 |
| performance_schema_events_stages_history_long_size | 1000 | -1 |
| performance_schema_events_stages_history_size | 10 | -1 |
| performance_schema_events_statements_history_long_size | 1000 | -1 |
| performance_schema_events_statements_history_size | 10 | -1 |
| performance_schema_events_waits_history_long_size | 1000 | -1 |
| performance_schema_events_waits_history_size | 10 | -1 |
| performance_schema_session_connect_attrs_size | 512 | -1 |
| query_alloc_block_size | 8192 | 16384 |
| query_prealloc_size | 8192 | 24576 |
| sort_buffer_size | 262144 | 2097152 |
| table_definition_cache | 615 | 400 |
| thread_cache_size | 9 | 151 |
| thread_stack | 262144 | 297984 |
+--------------------------------------------------------+----------------------+-------------+
53 rows in set (0.05 sec)
Other suggestions
-----------------
log_slow_admin_statements = ON
log_slow_slave_statements = ON
long-query-time = 2 (Nirbhay: MariaDB 10.2.2 & MySQL 5.7.14 both has it set to 10.0)
log-queries-not-using-indexes = ON (Nirbhay: MariaDB 10.2.2 & MySQL 5.7.14 both has it set to OFF)
min-examined-row-limit = 1000 (Nirbhay: MariaDB 10.2.2 & MySQL 5.7.14 both has it set to 0)
group_concat_max_len = 1M (Jan Steinman: default to something much higher than 1K,
Nirbhay: MariaDB 10.2.2 & MySQL 5.7.14 both has it set to 1K)
max_allowed_packet = 16M (Nirbhay: MariaDB 10.2.2 & MySQL 5.7.14 both has it set to 4M)
innodb_autoinc_lock_mode = 2 (Required for Galera)
innodb_log_file_size = 128M
sync_binlog = 1 (or at most 10)
binlog_annotate_row_events = ON
replicate_annotate_row_events = ON
innodb_stats_traditional = FALSE
use_stat_tables = complementary
histogram_size = 255
aria_recover = QUICK, BACKUP (Federico Razzoli : FORCE, BACKUP)
myisam-recover = QUICK, BACKUP (Federico Razzoli : FORCE, BACKUP)
table_definition_cache autosize to max(2000, 400+table_open_cache / 2) like 5.7
query_cache_strip_comments = ON
lower_case_table_names = 1 (default value is annoying and useless)
If label=order-by-optimisations are resolved:
optimizer_switch = mrr=on,mrr_sort_keys=on,optimize_join_buffer_size=on
optimizer_use_condition_selectivity = 4
join_cache_level = 8
sql_mode = ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION
* performance schema - Enable events_statements_history and events_transactions_history consumers by default
* disable query cache, it's not useful for common OLTP workloads
* lock_wait_timeout - ALTER TABLEs can be long, but having transactions waiting for 1 year is a potential disaster
* updatable_views_with_limit - having YES by default seems to me very dangerous
* symbolic-links OFF (??)
MySQL client
------------
* mysql client prompt="\u@\h [\d] > "
* enable show_warnings
References
----------
* http://www.tocker.ca/2015/01/23/proposal-to-change-additional-defaults-in-mysql-5-7.html
* http://www.tocker.ca/2015/01/14/proposal-to-change-replication-and-innodb-settings-in-mysql-5-7.html
* http://www.tocker.ca/2015/02/24/proposal-to-change-additional-defaults-in-mysql-5-7-february-edition.html
* https://major.io/2007/08/03/obscure-mysql-variable-explained-max_seeks_for_key/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment