You searched for change default value - Page 2 of 11

As the part of analyzing surprising MySQL 5.5 vs MySQL 5.6 performance results I’ve been looking at changes to default variable values. To do that I’ve loaded the values from MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query:

mysql [localhost] {msandbox} (test) > select var55.variable_name,left(var55.variable_value,40) value55, left(var56.variable_value,40) var56  from var55 left join var56 on var55.variable_name=var56.variable_name where  var55.variable_value!=var56.variable_value;
+---------------------------------------------------+------------------------------------------+------------------------------------------+
| variable_name                                     | value55                                  | var56                                    |
+---------------------------------------------------+------------------------------------------+------------------------------------------+
| PERFORMANCE_SCHEMA                                | OFF                                      | ON                                       |
| PID_FILE                                          | /mnt/data/sandboxes/msb_5_5_30/data/mysq | /mnt/data/sandboxes/msb_5_6_10/data/mysq |
| CHARACTER_SETS_DIR                                | /mnt/nfs/dist/mysql-5.5.30-linux2.6-x86_ | /mnt/nfs/dist/mysql-5.6.10-linux-glibc2. |
| PERFORMANCE_SCHEMA_MAX_COND_INSTANCES             | 1000                                     | 836                                      |
| PERFORMANCE_SCHEMA_MAX_MUTEX_INSTANCES            | 1000000                                  | 3282                                     |
| OLD_PASSWORDS                                     | OFF                                      | 0                                        |
| INNODB_STATS_ON_METADATA                          | ON                                       | OFF                                      |
| PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_SIZE      | 10                                       | 5                                        |
| PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_LONG_SIZE | 10000                                    | 100                                      |
| PERFORMANCE_SCHEMA_MAX_RWLOCK_INSTANCES           | 1000000                                  | 1724                                     |
| PERFORMANCE_SCHEMA_MAX_TABLE_HANDLES              | 100000                                   | 2223                                     |
| INNODB_LOG_FILE_SIZE                              | 5242880                                  | 50331648                                 |
| BASEDIR                                           | /mnt/nfs/dist/5.5.30                     | /mnt/nfs/dist/5.6.10                     |
| BACK_LOG                                          | 50                                       | 80                                       |
| OPEN_FILES_LIMIT                                  | 1024                                     | 5000                                     |
| INNODB_AUTOEXTEND_INCREMENT                       | 8                                        | 64                                       |
| MAX_CONNECT_ERRORS                                | 10                                       | 100                                      |
| SORT_BUFFER_SIZE                                  | 2097152                                  | 262144                                   |
| LC_MESSAGES_DIR                                   | /mnt/nfs/dist/mysql-5.5.30-linux2.6-x86_ | /mnt/nfs/dist/mysql-5.6.10-linux-glibc2. |
| MAX_ALLOWED_PACKET                                | 1048576                                  | 4194304                                  |
| JOIN_BUFFER_SIZE                                  | 131072                                   | 262144                                   |
| TMPDIR                                            | /mnt/data/sandboxes/msb_5_5_30/tmp       | /mnt/data/sandboxes/msb_5_6_10/tmp       |
| TABLE_OPEN_CACHE                                  | 400                                      | 2000                                     |
| INNODB_VERSION                                    | 5.5.30                                   | 1.2.10                                   |
| INNODB_BUFFER_POOL_INSTANCES                      | 1                                        | 8                                        |
| QUERY_CACHE_SIZE                                  | 0                                        | 1048576                                  |
| SLOW_QUERY_LOG_FILE                               | /mnt/data/sandboxes/msb_5_5_30/data/dpe0 | /mnt/data/sandboxes/msb_5_6_10/data/dpe0 |
| TABLE_DEFINITION_CACHE                            | 400                                      | 1400                                     |
| PORT                                              | 5530                                     | 5610                                     |
| QUERY_CACHE_TYPE                                  | ON                                       | OFF                                      |
| REPORT_PORT                                       | 5530                                     | 5610                                     |
| PERFORMANCE_SCHEMA_MAX_FILE_INSTANCES             | 10000                                    | 1556                                     |
| SQL_MODE                                          |                                          | NO_ENGINE_SUBSTITUTION                   |
| INNODB_OLD_BLOCKS_TIME                            | 0                                        | 1000                                     |
| LOG_ERROR                                         | /mnt/data/sandboxes/msb_5_5_30/data/msan | /mnt/data/sandboxes/msb_5_6_10/data/msan |
| VERSION_COMPILE_OS                                | linux2.6                                 | linux-glibc2.5                           |
| THREAD_CACHE_SIZE                                 | 0                                        | 9                                        |
| PLUGIN_DIR                                        | /mnt/nfs/dist/5.5.30/lib/plugin/         | /mnt/nfs/dist/5.6.10/lib/plugin/         |
| SYNC_RELAY_LOG                                    | 0                                        | 10000                                    |
| GENERAL_LOG_FILE                                  | /mnt/data/sandboxes/msb_5_5_30/data/dpe0 | /mnt/data/sandboxes/msb_5_6_10/data/dpe0 |
| PERFORMANCE_SCHEMA_MAX_TABLE_INSTANCES            | 50000                                    | 445                                      |
| SYNC_RELAY_LOG_INFO                               | 0                                        | 10000                                    |
| SLAVE_LOAD_TMPDIR                                 | /mnt/data/sandboxes/msb_5_5_30/tmp       | /mnt/data/sandboxes/msb_5_6_10/tmp       |
| SECURE_AUTH                                       | OFF                                      | ON                                       |
| VERSION                                           | 5.5.30                                   | 5.6.10                                   |
| INNODB_CONCURRENCY_TICKETS                        | 500                                      | 5000                                     |
| INNODB_PURGE_THREADS                              | 0                                        | 1                                        |
| INNODB_OPEN_FILES                                 | 300                                      | 2000                                     |
| INNODB_DATA_FILE_PATH                             | ibdata1:10M:autoextend                   | ibdata1:12M:autoextend                   |
| INNODB_PURGE_BATCH_SIZE                           | 20                                       | 300                                      |
| PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES           | 1000                                     | 224                                      |
| SOCKET                                            | /tmp/mysql_sandbox5530.sock              | /tmp/mysql_sandbox5610.sock              |
| INNODB_FILE_PER_TABLE                             | OFF                                      | ON                                       |
| SYNC_MASTER_INFO                                  | 0                                        | 10000                                    |
| DATADIR                                           | /mnt/data/sandboxes/msb_5_5_30/data/     | /mnt/data/sandboxes/msb_5_6_10/data/     |
| OPTIMIZER_SWITCH                                  | index_merge=on,index_merge_union=on,inde | index_merge=on,index_merge_union=on,inde |
+---------------------------------------------------+------------------------------------------+------------------------------------------+
56 rows in set (0.05 sec)

mysql [localhost] {msandbox} (test) > select var55.variable_name,left(var55.variable_value,40) value55, left(var56.variable_value,40) var56 from var55 left join var56 on var55.variable_name=var56.variable_name where var55.variable_value!=var56.variable_value;

+---------------------------------------------------+------------------------------------------+------------------------------------------+

| variable_name | value55 | var56 |

+---------------------------------------------------+------------------------------------------+------------------------------------------+

| PERFORMANCE_SCHEMA | OFF | ON |

| PID_FILE | /mnt/data/sandboxes/msb_5_5_30/data/mysq | /mnt/data/sandboxes/msb_5_6_10/data/mysq |

| CHARACTER_SETS_DIR | /mnt/nfs/dist/mysql-5.5.30-linux2.6-x86_ | /mnt/nfs/dist/mysql-5.6.10-linux-glibc2. |

| PERFORMANCE_SCHEMA_MAX_COND_INSTANCES | 1000 | 836 |

| PERFORMANCE_SCHEMA_MAX_MUTEX_INSTANCES | 1000000 | 3282 |

| OLD_PASSWORDS | OFF | 0 |

| INNODB_STATS_ON_METADATA | ON | OFF |

| PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_SIZE | 10 | 5 |

| PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_LONG_SIZE | 10000 | 100 |

| PERFORMANCE_SCHEMA_MAX_RWLOCK_INSTANCES | 1000000 | 1724 |

| PERFORMANCE_SCHEMA_MAX_TABLE_HANDLES | 100000 | 2223 |

| INNODB_LOG_FILE_SIZE | 5242880 | 50331648 |

| BASEDIR | /mnt/nfs/dist/5.5.30 | /mnt/nfs/dist/5.6.10 |

| BACK_LOG | 50 | 80 |

| OPEN_FILES_LIMIT | 1024 | 5000 |

| INNODB_AUTOEXTEND_INCREMENT | 8 | 64 |

| MAX_CONNECT_ERRORS | 10 | 100 |

| SORT_BUFFER_SIZE | 2097152 | 262144 |

| LC_MESSAGES_DIR | /mnt/nfs/dist/mysql-5.5.30-linux2.6-x86_ | /mnt/nfs/dist/mysql-5.6.10-linux-glibc2. |

| MAX_ALLOWED_PACKET | 1048576 | 4194304 |

| JOIN_BUFFER_SIZE | 131072 | 262144 |

| TMPDIR | /mnt/data/sandboxes/msb_5_5_30/tmp | /mnt/data/sandboxes/msb_5_6_10/tmp |

| TABLE_OPEN_CACHE | 400 | 2000 |

| INNODB_VERSION | 5.5.30 | 1.2.10 |

| INNODB_BUFFER_POOL_INSTANCES | 1 | 8 |

| QUERY_CACHE_SIZE | 0 | 1048576 |

| SLOW_QUERY_LOG_FILE | /mnt/data/sandboxes/msb_5_5_30/data/dpe0 | /mnt/data/sandboxes/msb_5_6_10/data/dpe0 |

| TABLE_DEFINITION_CACHE | 400 | 1400 |

| PORT | 5530 | 5610 |

| QUERY_CACHE_TYPE | ON | OFF |

| REPORT_PORT | 5530 | 5610 |

| PERFORMANCE_SCHEMA_MAX_FILE_INSTANCES | 10000 | 1556 |

| SQL_MODE | | NO_ENGINE_SUBSTITUTION |

| INNODB_OLD_BLOCKS_TIME | 0 | 1000 |

| LOG_ERROR | /mnt/data/sandboxes/msb_5_5_30/data/msan | /mnt/data/sandboxes/msb_5_6_10/data/msan |

| VERSION_COMPILE_OS | linux2.6 | linux-glibc2.5 |

| THREAD_CACHE_SIZE | 0 | 9 |

| PLUGIN_DIR | /mnt/nfs/dist/5.5.30/lib/plugin/ | /mnt/nfs/dist/5.6.10/lib/plugin/ |

| SYNC_RELAY_LOG | 0 | 10000 |

| GENERAL_LOG_FILE | /mnt/data/sandboxes/msb_5_5_30/data/dpe0 | /mnt/data/sandboxes/msb_5_6_10/data/dpe0 |

| PERFORMANCE_SCHEMA_MAX_TABLE_INSTANCES | 50000 | 445 |

| SYNC_RELAY_LOG_INFO | 0 | 10000 |

| SLAVE_LOAD_TMPDIR | /mnt/data/sandboxes/msb_5_5_30/tmp | /mnt/data/sandboxes/msb_5_6_10/tmp |

| SECURE_AUTH | OFF | ON |

| VERSION | 5.5.30 | 5.6.10 |

| INNODB_CONCURRENCY_TICKETS | 500 | 5000 |

| INNODB_PURGE_THREADS | 0 | 1 |

| INNODB_OPEN_FILES | 300 | 2000 |

| INNODB_DATA_FILE_PATH | ibdata1:10M:autoextend | ibdata1:12M:autoextend |

| INNODB_PURGE_BATCH_SIZE | 20 | 300 |

| PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES | 1000 | 224 |

| SOCKET | /tmp/mysql_sandbox5530.sock | /tmp/mysql_sandbox5610.sock |

| INNODB_FILE_PER_TABLE | OFF | ON |

| SYNC_MASTER_INFO | 0 | 10000 |

| DATADIR | /mnt/data/sandboxes/msb_5_5_30/data/ | /mnt/data/sandboxes/msb_5_6_10/data/ |

| OPTIMIZER_SWITCH | index_merge=on,index_merge_union=on,inde | index_merge=on,index_merge_union=on,inde |

+---------------------------------------------------+------------------------------------------+------------------------------------------+

56 rows in set (0.05 sec)

Lets go over to see what are the most important changes [...]

Notice the result of the NOW() function in the following query. The query was run on a real database server and I didn’t change the clock of the server or change anything in the database configuration settings.

mysql> SELECT NOW(),SYSDATE();
+---------------------+---------------------+
| NOW()               | SYSDATE()           |
+---------------------+---------------------+
| 1999-01-01 00:00:00 | 2012-11-29 05:50:03 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW(),SYSDATE();

+---------------------+---------------------+

| NOW() | SYSDATE() |

+---------------------+---------------------+

| 1999-01-01 00:00:00 | 2012-11-29 05:50:03 |

+---------------------+---------------------+

1 row in set (0.00 sec)

You may proceed to party like it is 1999. How can the NOW() function return a value [...]

MySQL 5.5 and MySQL 5.6 default variable values differences

Replication checksums in MySQL 5.6

Can’t Create Thread: Errno 11 (A Tale of Two Ulimits)

Implications of Metadata Locking Changes in MySQL 5.5

Replication of the NOW() function (also, time travel)

Realtime stats to pay attention to in Percona XtraDB Cluster and Galera

Knowing what pt-online-schema-change will do

Edge-case behavior of INSERT…ODKU

Replaying database load with Percona Playback

Review of MySQL 5.6 Defaults Changes

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA