More Statistics for Slow Queries: log_slow_extra

The slow query log is the trusted old method of recording slow query, so the database administrator can determine which queries are in the most need for optimization. Since MySQL 5.6, it has to some extend been overshadowed by the Performance Schema which has lower overhead and thus allows collecting statistics about all queries. The slow query log has one major advantage though: the data is persisted. In MySQL 8.0.14 which was recently released, there is an improvement for the slow query log: additional statistics about the recorded queries.

The slow query log with log_slow_extra enabled.
The slow query log with log_slow_extra enabled.

Contribution

Thanks for Facebook for contributing a patch for the new feature.

The additional information is not recorded by default. To enable the feature, enable the log_slow_extra option:

mysql> SET PERSIST log_slow_extra = ON;
Query OK, 0 rows affected (0.05 sec)

Here, SET PERSIST is used, so the configuration change is persisted when MySQL is restarted. If you just want to try the feature, you can use SET GLOBAL, then decide later whether you want to keep it enabled.

That is all that is required. You can now execute a “slow” query and take a look at the recorded data. An easy way to execute a slow query is to execute DO SLEEP(...) where you can replace ... with the number of seconds you want to sleep. However that is not a very interesting query. Another option is to lower long_query_time to ensure your query is captured. If you set long_query_time to zero, all queries are recorded:

mysql> SET SESSION long_query_time = 0.0;
Query OK, 0 rows affected (0.01 sec)

Here SET SESSION is used so only the current connection is affected.

Be Aware

The slow query log does have overhead. It is for most systems not recommended to set long_query_time to zero for all connections all the time. Doing so can cause performance problems.

Finally, execute a query:

mysql> SELECT * FROM world.city ORDER BY Population DESC LIMIT 10;
+------+------------------+-------------+------------------+------------+
| ID   | Name             | CountryCode | District         | Population |
+------+------------------+-------------+------------------+------------+
| 1024 | Mumbai (Bombay)  | IND         | Maharashtra      |   10500000 |
| 2331 | Seoul            | KOR         | Seoul            |    9981619 |
|  206 | São Paulo        | BRA         | São Paulo        |    9968485 |
| 1890 | Shanghai         | CHN         | Shanghai         |    9696300 |
|  939 | Jakarta          | IDN         | Jakarta Raya     |    9604900 |
| 2822 | Karachi          | PAK         | Sindh            |    9269265 |
| 3357 | Istanbul         | TUR         | Istanbul         |    8787958 |
| 2515 | Ciudad de México | MEX         | Distrito Federal |    8591309 |
| 3580 | Moscow           | RUS         | Moscow (City)    |    8389200 |
| 3793 | New York         | USA         | New York         |    8008278 |
+------+------------------+-------------+------------------+------------+
10 rows in set (0.04 sec)

The resulting slow query log record is:

# Time: 2019-01-31T07:24:20.518505Z
# User@Host: root[root] @ localhost [::1]  Id:    15
# Query_time: 0.001827  Lock_time: 0.000087 Rows_sent: 10  Rows_examined: 4089 Thread_id: 15 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 694 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 4080 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 10 Sort_scan_count: 1 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2019-01-31T07:24:20.516678Z End: 2019-01-31T07:24:20.518505Z
SET timestamp=1548919460;
SELECT * FROM world.city ORDER BY Population DESC LIMIT 10;

The extra data can be a little hard to see due to the formatting of the blog – the extra fields are:

  • Thread_id: 15
  • Errno: 0
  • Killed: 0
  • Bytes_received: 0
  • Bytes_sent: 694
  • Read_first: 1
  • Read_last: 0
  • Read_key: 1
  • Read_next: 0
  • Read_prev: 0
  • Read_rnd: 0
  • Read_rnd_next: 4080
  • Sort_merge_passes: 0
  • Sort_range_count: 0
  • Sort_rows: 10
  • Sort_scan_count: 1
  • Created_tmp_disk_tables: 0
  • Created_tmp_tables: 0
  • Start: 2019-01-31T07:24:20.516678Z
  • End: 2019-01-31T07:24:20.518505Z

As comparison, here is the information for the same query with log_slow_extra = OFF:

# Time: 2019-01-31T07:24:06.100447Z
# User@Host: root[root] @ localhost [::1]  Id:    15
# Query_time: 0.002286  Lock_time: 0.000133 Rows_sent: 10  Rows_examined: 4089
SET timestamp=1548919446;
SELECT * FROM world.city ORDER BY Population DESC LIMIT 10;

Enjoy.

MySQL 8.0: Persisted Variables

MySQL 8.0 introduced a new feature that allows you to persist configuration changes from inside MySQL. Previously you could execute SET GLOBAL to change the configuration at runtime, but you needed to update your MySQL configuration file in order to persist the change. In MySQL 8.0 you can skip the second step. This blog discuss how this works and how to backup and restore the configuration.

Using SET PERSIST to set a variable and the persisted_variables table in the Performance Schema to get a list of persisted variables.
Using SET PERSIST to set a variable and the persisted_variables table in the Performance Schema to get a list of persisted variables.

Persisting Variables

You persist changes with either the SET PERSIST or SET PERSIST_ONLY statement. The different is that SET PERSIST_ONLY only updates the configuration whereas SET PERSIST essentially combines SET GLOBAL and SET PERSIST_ONLY.

Information

Some variables such as innodb_buffer_pool_instances can only use PERSIST_ONLY, i.e. it requires a restart to make the changes take effect. Still others, such as datadir, requires cryptographically signed SET statements which are available in MySQL 8.0.14 and later.

mysqld-auto.cnf and variables_info

The persisted variables are stored in the file mysqld-auto.cnf located in the data directory using the JSON format. It includes more information than just the persisted value. It also includes information such as who made the change and when. An example file is:

shell$ cat mysqld-auto.cnf 
{ "Version" : 1 , "mysql_server" : { "sort_buffer_size" : { "Value" : "32768" , "Metadata" : { "Timestamp" : 1534230053297668 , "User" : "root" , "Host" : "localhost" } } , "join_buffer_size" : { "Value" : "131072" , "Metadata" : { "Timestamp" : 1534230072956789 , "User" : "root" , "Host" : "localhost" } } , "mysql_server_static_options" : { "slave_parallel_type" : { "Value" : "LOGICAL_CLOCK" , "Metadata" : { "Timestamp" : 1534230099583642 , "User" : "root" , "Host" : "localhost" } } } } }

Since it is JSON, it is easy to reformat to make easier to read, for example:

shell$ cat mysqld-auto.cnf | python -m json.tool
{
    "Version": 1,
    "mysql_server": {
        "join_buffer_size": {
            "Metadata": {
                "Host": "localhost",
                "Timestamp": 1534230072956789,
                "User": "root"
            },
            "Value": "131072"
        },
        "mysql_server_static_options": {
            "slave_parallel_type": {
                "Metadata": {
                    "Host": "localhost",
                    "Timestamp": 1534230099583642,
                    "User": "root"
                },
                "Value": "LOGICAL_CLOCK"
            }
        },
        "sort_buffer_size": {
            "Metadata": {
                "Host": "localhost",
                "Timestamp": 1534230053297668,
                "User": "root"
            },
            "Value": "32768"
        }
    }
}

This information is also available from the performance_schema.variables_info table:

mysql> SELECT VARIABLE_NAME, VARIABLE_SOURCE, sys.format_path(VARIABLE_PATH) AS Path,
              SET_TIME, SET_USER, SET_HOST
         FROM performance_schema.variables_info
  WHERE VARIABLE_NAME IN ('join_buffer_size', 'slave_parallel_type', 'sort_buffer_size');
+---------------------+-----------------+-------------------------------------------+----------------------------+----------+-----------+
| VARIABLE_NAME       | VARIABLE_SOURCE | Path                                      | SET_TIME                   | SET_USER | SET_HOST  |
+---------------------+-----------------+-------------------------------------------+----------------------------+----------+-----------+
| join_buffer_size    | DYNAMIC         |                                           | 2018-08-14 17:08:15.526750 | root     | localhost |
| slave_parallel_type | PERSISTED       | @@datadir/mysqld-auto.cnf/mysqld-auto.cnf | 2018-08-14 17:01:39.583642 | root     | localhost |
| sort_buffer_size    | PERSISTED       | @@datadir/mysqld-auto.cnf                 | 2018-08-14 17:00:53.297668 | root     | localhost |
+---------------------+-----------------+-------------------------------------------+----------------------------+----------+-----------+
3 rows in set (0.36 sec)

Notice that the source for join_buffer_size is DYNAMIC whereas the two other variables have the source set to PERSISTED. Why? After all they all three existed in the mysqld-auto.cnf file. DYNAMIC means that the variable was changes since the last restart either using SET GLOBAL or SET PERSIST. Another thing to be aware of is that variables changed with SET PERSIST_ONLY will not show up in variables_info until after the next restart. I will soon get back to show a way to get the variables that have been persisted in one way or another.

Backup and Restore

As a simple way to back up the configuration is simply copy the mysqld-auto.cnf file to a safe location. Similarly, you can restore the configuration by copying it back.

However, what if you want most of the configuration but not everything or you want to edit some of the values? In that case you need another way of exporting the configuration as you should not manually edit mysqld-auto.cnf.

Warning

Do not edit the mysqld-auto.cnf file manually. It should only be changed with SET PERSIST and SET PERSIST_ONLY. If there are any errors in the file, MySQL will refuse to start.

Fortunately as it turns out, it is easy to export all persisted variables. The table performance_schema.persisted_variables includes all variables that has either been read from mysqld-auto.cnf or has been changed with SET PERSIST or SET PERSIST_ONLY since the last restart. The table include the persisted values. For example:

mysql> SELECT * FROM performance_schema.persisted_variables;
+---------------------+----------------+
| VARIABLE_NAME       | VARIABLE_VALUE |
+---------------------+----------------+
| sort_buffer_size    | 32768          |
| join_buffer_size    | 131072         |
| slave_parallel_type | LOGICAL_CLOCK  |
+---------------------+----------------+
3 rows in set (0.01 sec)

This can be used to create SET statements that can be used to recreate the configuration on another instance. For example:

SELECT CONCAT('SET PERSIST_ONLY ', VARIABLE_NAME, ' = ',
              IF(VARIABLE_VALUE REGEXP '^([0-9]+|ON|OFF|YES|NO)$',
                 VARIABLE_VALUE,
                 QUOTE(VARIABLE_VALUE)), ';'
             ) AS SetStmt
  FROM performance_schema.persisted_variables;

Using the mysql command-line client, you can avoid the column names and table format by using the --skip-column-names and --batch options:

shell$ mysql --skip-column-names --batch \
             -e "SELECT CONCAT('SET PERSIST_ONLY ', VARIABLE_NAME, ' = ', IF(VARIABLE_VALUE REGEXP '^([0-9]+|ON|OFF|YES|NO)$', VARIABLE_VALUE, QUOTE(VARIABLE_VALUE)), ';') FROM performance_schema.persisted_variables;" \
             > config.sql

Note

On Microsoft Windows ensure everything is on one line and the backslashes are removed.

Now the file config.sql contains an export of the persisted variables:

shell$ cat config.sql 
SET PERSIST_ONLY sort_buffer_size = 32768;
SET PERSIST_ONLY join_buffer_size = 131072;
SET PERSIST_ONLY slave_parallel_type = 'LOGICAL_CLOCK';

This example creates SET PERSIST_ONLY statement as those will work with all persistable variables. When you replay the SET statements, it will require a restart of MySQL for the changes to take effect. If you want to use SET PERSIST where possible, then you need to take into consideration whether the variable support SET PERSIST.  A list of variables that require SET PERSIST_ONLY are included at the end.

The configuration can now be restored as:

mysql> SOURCE config.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> RESTART;
Query OK, 0 rows affected (0.00 sec)

PERSIST_ONLY Variables

As promised, I will conclude with a list of persistable variables that only supports SET PERSIST_ONLY. As of MySQL 8.0.12 without any plugins installed, the variables are:

back_log
binlog_gtid_simple_recovery
disabled_storage_engines
disconnect_on_expired_password
ft_max_word_len
ft_min_word_len
ft_query_expansion_limit
innodb_adaptive_hash_index_parts
innodb_api_disable_rowlock
innodb_api_enable_binlog
innodb_api_enable_mdl
innodb_autoinc_lock_mode
innodb_buffer_pool_chunk_size
innodb_buffer_pool_instances
innodb_doublewrite
innodb_flush_method
innodb_force_recovery
innodb_ft_aux_table
innodb_ft_cache_size
innodb_ft_min_token_size
innodb_ft_server_stopword_table
innodb_ft_sort_pll_degree
innodb_ft_total_cache_size
innodb_ft_user_stopword_table
innodb_log_file_size
innodb_log_files_in_group
innodb_monitor_disable
innodb_monitor_enable
innodb_monitor_reset
innodb_monitor_reset_all
innodb_numa_interleave
innodb_open_files
innodb_page_cleaners
innodb_purge_threads
innodb_read_io_threads
innodb_rollback_on_timeout
innodb_sort_buffer_size
innodb_sync_array_size
innodb_tmpdir
innodb_use_native_aio
innodb_write_io_threads
large_pages
log_slave_updates
log_syslog
log_syslog_facility
log_syslog_include_pid
log_syslog_tag
lower_case_table_names
max_digest_length
metadata_locks_cache_size
metadata_locks_hash_instances
myisam_mmap_size
myisam_recover_options
mysqlx_bind_address
mysqlx_port
mysqlx_port_open_timeout
mysqlx_socket
mysqlx_ssl_ca
mysqlx_ssl_capath
mysqlx_ssl_cert
mysqlx_ssl_cipher
mysqlx_ssl_crl
mysqlx_ssl_crlpath
mysqlx_ssl_key
ngram_token_size
old
open_files_limit
performance_schema
performance_schema_digests_size
performance_schema_error_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_events_transactions_history_long_size
performance_schema_events_transactions_history_size
performance_schema_events_waits_history_long_size
performance_schema_events_waits_history_size
performance_schema_max_cond_classes
performance_schema_max_digest_length
performance_schema_max_file_classes
performance_schema_max_file_handles
performance_schema_max_memory_classes
performance_schema_max_mutex_classes
performance_schema_max_rwlock_classes
performance_schema_max_socket_classes
performance_schema_max_sql_text_length
performance_schema_max_stage_classes
performance_schema_max_statement_classes
performance_schema_max_statement_stack
performance_schema_max_thread_classes
performance_schema_session_connect_attrs_size
rbr_exec_mode
relay_log_recovery
relay_log_space_limit
report_host
report_password
report_port
report_user
skip_name_resolve
skip_show_database
slave_skip_errors
ssl_cipher
table_open_cache_instances
thread_handling
thread_stack
tls_version

Changes to Options and Variables in MySQL 5.6

With MySQL 5.6 just gone GA, I thought it would be good to take a look at the changes in options and variables that comes with the new release.

First of all, several of the existing options have get new default values. As James Day already have written a good post about that in his blog, I will refer to that instead of going through the changes. For a general overview of the new features and improvements, the recent blogs by Rob Young and Peter Saitsev are good starting points together with the What is New in MySQL 5.6 page in the Reference Manual are good places to start.

Instead I will focus a little on the new options that has been introduced. The first thing to note is that a in the current 5.5. release (5.5.30) there are 323 variables whereas 5.6 GA (5.6.10) returns 440 rows.

MySQL 5.5.29> SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES;
+----------+
| COUNT(*) |
+----------+
|      323 |
+----------+
1 row in set (0.04 sec)

MySQL 5.6.10> SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES;
+----------+
| COUNT(*) |
+----------+
|      440 |
+----------+
1 row in set (0.02 sec)

Note

This post is written using the Enterprise versions with the semi-synchronous replication plugins enabled in both versions plus the memcached and password validation plugins in 5.6.

Actually the number of new variables is not 117 but 129 as 12 variables have been removed in 5.6.

So what are all of these 129 new variables good for? Actually there is a good chance that you will never need to touch many of them as the default value is good enough, they simply have been added to provide the value of options already present in 5.5 but not exposed through SHOW GLOBAL VARIABLES, or that they are for features you are not using. If we try to group the new variables the distribution comes out as:

FeatureNew Variables
Global Transaction IDs5
Other Replication19
Memcached Plugin6
Validate Password Plugin6
Other Security Related5
InnoDB54
Optimizer Traces5
Performance Schema15
Exposing Previously Existing Variables2
Other12

New Variables in MySQL 5.6

The 54 new InnoDB variables span a number of different changes and additions such as:

  • New adaptive flushing algorithm
  • Buffer Pool dumps to disk and restore
  • Support for additional checksum algorithms
  • Improvements for compression
  • Full text indexes
  • New monitoring options (the information_schema.metrics table)
  • Configurable page size
  • Persistent statistics
  • Undo logs improvements
  • And more …

For reference I have added a list of the new variables with the release they were introduced and the default value (additionally innodb_print_all_deadlocks is also new, but that was also added to 5.5.30):

+--------------------------------------------------------+--------------+------------------------------------------------------------------------+
| Variable_name                                          | From Version | Default (Linux)                                                        |
+--------------------------------------------------------+--------------+------------------------------------------------------------------------+
| bind_address                                           | 5.6.1        | *                                                                      |
| binlog_checksum                                        | 5.6.2        | NONE                                                                   |
| binlog_max_flush_queue_time                            | 5.6.6        | 0                                                                      |
| binlog_order_commits                                   | 5.6.6        | ON                                                                     |
| binlog_row_image                                       | 5.6.2        | FULL                                                                   |
| binlog_rows_query_log_events                           | 5.6.2        | OFF                                                                    |
| core_file                                              | 5.6.2        | OFF                                                                    |
| daemon_memcached_enable_binlog                         | 5.6.6        | OFF                                                                    |
| daemon_memcached_engine_lib_name                       | 5.6.6        | innodb_engine.so                                                       |
| daemon_memcached_engine_lib_path                       | 5.6.6        |                                                                        |
| daemon_memcached_option                                | 5.6.6        |                                                                        |
| daemon_memcached_r_batch_size                          | 5.6.6        | 1                                                                      |
| daemon_memcached_w_batch_size                          | 5.6.6        | 1                                                                      |
| default_authentication_plugin                          | 5.6.6        | MYSQL_NATIVE_PASSWORD                                                  |
| default_tmp_storage_engine                             | 5.6.2        | InnoDB                                                                 |
| end_markers_in_json                                    | 5.6.5        | OFF                                                                    |
| enforce_gtid_consistency                               | 5.6.9        | OFF                                                                    |
| eq_range_index_dive_limit                              | 5.6.5        | 10                                                                     |
| explicit_defaults_for_timestamp                        | 5.6.6        | OFF                                                                    |
| gtid_executed                                          | 5.6.9        |                                                                        |
| gtid_mode                                              | 5.6.5        | OFF                                                                    |
| gtid_next                                              | 5.6.5        | AUTOMATIC                                                              |
| gtid_purged                                            | 5.6.9        |                                                                        |
| host_cache_size                                        | 5.6.5        | 128                                                                    |
| ignore_db_dirs                                         | 5.6.3        |                                                                        |
| innodb_adaptive_flushing_lwm                           | 5.6.6        | 10                                                                     |
| innodb_adaptive_max_sleep_delay                        | 5.6.3        | 0                                                                      |
| innodb_api_bk_commit_interval                          | 5.6.7        | 5                                                                      |
| innodb_api_disable_rowlock                             | 5.6.6        | OFF                                                                    |
| innodb_api_enable_binlog                               | 5.6.6        | OFF                                                                    |
| innodb_api_enable_mdl                                  | 5.6.6        | OFF                                                                    |
| innodb_api_trx_level                                   | 5.6.6        | 0                                                                      |
| innodb_buffer_pool_dump_at_shutdown                    | 5.6.3        | OFF                                                                    |
| innodb_buffer_pool_dump_now                            | 5.6.3        | OFF                                                                    |
| innodb_buffer_pool_filename                            | 5.6.3        | ib_buffer_pool                                                         |
| innodb_buffer_pool_load_abort                          | 5.6.3        | OFF                                                                    |
| innodb_buffer_pool_load_at_startup                     | 5.6.3        | OFF                                                                    |
| innodb_buffer_pool_load_now                            | 5.6.3        | ON                                                                     |
| innodb_change_buffer_max_size                          | 5.6.2        | 25                                                                     |
| innodb_checksum_algorithm                              | 5.6.3        | innodb                                                                 |
| innodb_cmp_per_index_enabled                           | 5.6.7        | OFF                                                                    |
| innodb_compression_failure_threshold_pct               | 5.6.7        | 5                                                                      |
| innodb_compression_level                               | 5.6.7        | 6                                                                      |
| innodb_compression_pad_pct_max                         | 5.6.7        | 50                                                                     |
| innodb_disable_sort_file_cache                         | 5.6.4        | OFF                                                                    |
| innodb_flush_log_at_timeout                            | 5.6.6        | 1                                                                      |
| innodb_flush_neighbors                                 | 5.6.3        | 1                                                                      |
| innodb_flushing_avg_loops                              | 5.6.6        | 30                                                                     |
| innodb_ft_cache_size                                   | 5.6.4        | 32M                                                                    |
| innodb_ft_enable_diag_print                            | 5.6.4        | OFF                                                                    |
| innodb_ft_enable_stopword                              | 5.6.4        | ON                                                                     |
| innodb_ft_max_token_size                               | 5.6.4        | 84                                                                     |
| innodb_ft_min_token_size                               | 5.6.4        | 3                                                                      |
| innodb_ft_num_word_optimize                            | 5.6.4        | 2000                                                                   |
| innodb_ft_server_stopword_table                        | 5.6.4        | NULL                                                                   |
| innodb_ft_sort_pll_degree                              | 5.6.4        | 2                                                                      |
| innodb_ft_user_stopword_table                          | 5.6.4        | NULL                                                                   |
| innodb_io_capacity_max                                 | 5.6.6        | 2000                                                                   |
| innodb_lru_scan_depth                                  | 5.6.3        | 1024                                                                   |
| innodb_max_dirty_pages_pct_lwm                         | 5.6.6        | 0                                                                      |
| innodb_max_purge_lag_delay                             | 5.6.5        | 0                                                                      |
| innodb_monitor_disable                                 | 5.6.2        |                                                                        |
| innodb_monitor_enable                                  | 5.6.2        |                                                                        |
| innodb_monitor_reset                                   | 5.6.2        |                                                                        |
| innodb_monitor_reset_all                               | 5.6.2        |                                                                        |
| innodb_online_alter_log_max_size                       | 5.6.6        | 128M                                                                   |
| innodb_optimize_fulltext_only                          | 5.6.4        | OFF                                                                    |
| innodb_page_size                                       | 5.6.4        | 16k                                                                    |
| innodb_print_all_deadlocks                             | 5.6.2        | OFF                                                                    |
| innodb_read_only                                       | 5.6.7        | OFF                                                                    |
| innodb_sort_buffer_size                                | 5.6.4        | 1M                                                                     |
| innodb_stats_auto_recalc                               | 5.6.6        | ON                                                                     |
| innodb_stats_persistent                                | 5.6.6        | ON                                                                     |
| innodb_stats_persistent_sample_pages                   | 5.6.2        | 20                                                                     |
| innodb_stats_transient_sample_pages                    | 5.6.2        | 8                                                                      |
| innodb_sync_array_size                                 | 5.6.3        | 1                                                                      |
| innodb_undo_directory                                  | 5.6.3        | .                                                                      |
| innodb_undo_logs                                       | 5.6.3        | 128                                                                    |
| innodb_undo_tablespaces                                | 5.6.3        | 0                                                                      |
| log_bin_basename                                       | 5.6.1        |                                                                        |
| log_bin_index                                          | 5.6.1        |                                                                        |
| log_bin_use_v1_row_events                              | 5.6.6        | OFF                                                                    |
| log_throttle_queries_not_using_indexes                 | 5.6.5        | 0                                                                      |
| master_info_repository                                 | 5.6.2        | FILE                                                                   |
| master_verify_checksum                                 | 5.6.2        | OFF                                                                    |
| metadata_locks_hash_instances                          | 5.6.8        | 8                                                                      |
| optimizer_trace                                        | 5.6.3        | enabled=off,one_line=off                                               |
| optimizer_trace_features                               | 5.6.3        | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselec |
| optimizer_trace_limit                                  | 5.6.3        | 1                                                                      |
| optimizer_trace_max_mem_size                           | 5.6.3        | 16k                                                                    |
| optimizer_trace_offset                                 | 5.6.3        | -1                                                                     |
| performance_schema_accounts_size                       | 5.6.3        | -1 (autosized)                                                         |
| performance_schema_digests_size                        | 5.6.5        | -1 (autosized)                                                         |
| performance_schema_events_stages_history_long_size     | 5.6.3        | -1 (autosized)                                                         |
| performance_schema_events_stages_history_size          | 5.6.3        | -1 (autosized)                                                         |
| performance_schema_events_statements_history_long_size | 5.6.3        | -1 (autosized)                                                         |
| performance_schema_events_statements_history_size      | 5.6.3        | -1 (autosized)                                                         |
| performance_schema_hosts_size                          | 5.6.3        | -1 (autosized)                                                         |
| performance_schema_max_socket_classes                  | 5.6.3        | 10                                                                     |
| performance_schema_max_socket_instances                | 5.6.3        | -1 (autosized)                                                         |
| performance_schema_max_stage_classes                   | 5.6.3        | 100                                                                    |
| performance_schema_max_statement_classes               | 5.6.3        | 100                                                                    |
| performance_schema_session_connect_attrs_size          | 5.6.6        | -1 (autosized)                                                         |
| performance_schema_setup_actors_size                   | 5.6.1        | 100                                                                    |
| performance_schema_setup_objects_size                  | 5.6.1        | 100                                                                    |
| performance_schema_users_size                          | 5.6.3        | -1 (autosized)                                                         |
| relay_log_basename                                     | 5.6.2        | %{datadir}%{hostname}-relay-bin                                        |
| relay_log_info_repository                              | 5.6.2        | FILE                                                                   |
| server_id_bits                                         | 5.6.6        | 32                                                                     |
| server_uuid                                            | 5.6.0        |                                                                        |
| sha256_password_private_key_path                       | 5.6.6        | private_key.pem                                                        |
| sha256_password_public_key_path                        | 5.6.6        | public_key.pem                                                         |
| slave_allow_batching                                   | 5.6.6        | OFF                                                                    |
| slave_checkpoint_group                                 | 5.6.3        | 512                                                                    |
| slave_checkpoint_period                                | 5.6.3        | 300                                                                    |
| slave_parallel_workers                                 | 5.6.3        | 0                                                                      |
| slave_pending_jobs_size_max                            | 5.6.3        | 1k                                                                     |
| slave_rows_search_algorithms                           | 5.6.6        | TABLE_SCAN,INDEX_SCAN                                                  |
| slave_sql_verify_checksum                              | 5.6.1        | ON                                                                     |
| ssl_crl                                                | 5.6.3        |                                                                        |
| ssl_crlpath                                            | 5.6.3        |                                                                        |
| table_open_cache_instances                             | 5.6.6        | 1                                                                      |
| tx_read_only                                           | 5.6.5        | OFF                                                                    |
| validate_password_dictionary_file                      | 5.6.6        |                                                                        |
| validate_password_length                               | 5.6.6        | 8                                                                      |
| validate_password_mixed_case_count                     | 5.6.6        | 1                                                                      |
| validate_password_number_count                         | 5.6.6        | 1                                                                      |
| validate_password_policy                               | 5.6.10       | MEDIUM                                                                 |
| validate_password_special_char_count                   | 5.6.6        | 1                                                                      |
+--------------------------------------------------------+--------------+------------------------------------------------------------------------+

Note that while the default values are for an installation on Linux, most will also apply to other platforms. See also the Reference Manual.

For good measure here is a list of the variables that have been removed in 5.6:

  • engine_condition_pushdown – deprecated in 5.5.3, use optimizer_switch instead.
  • have_csv – use SHOW ENGINES or information_schema.ENGINES instead.
  • have_innodb – use SHOW ENGINES or information_schema.ENGINES instead.
  • have_ndbcluster – use SHOW ENGINES or information_schema.ENGINES instead.
  • have_partitioning – use SHOW ENGINES or information_schema.ENGINES instead.
  • log – deprecated in 5.1.29, use general_log instead.
  • log_slow_queries – deprecated in 5.1.29, use slow_query_log instead.
  • max_long_data_size – deprecated in 5.5.11, is now automatically controlled by max_allowed_packet.
  • rpl_recovery_rank – previously unused.
  • sql_big_tables – hasn’t really been needed since 3.23.2.
  • sql_low_priority_updates – Use low_priority_updates instead.
  • sql_max_join_size