You searched for default innodb log file size - Page 2 of 10

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 [...]

In a very popular data loss scenario a table is dropped and empty one is created with the same name. This is because mysqldump in many cases generates the “DROP TABLE” instruction before the “CREATE TABLE”:

DROP TABLE IF EXISTS `actor`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `actor`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `actor` (

`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`first_name` varchar(45) NOT NULL,

`last_name` varchar(45) NOT NULL,

`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`actor_id`),

KEY `idx_actor_last_name` (`last_name`)

) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

If there were no subsequent CREATE TABLE the recovery would be trivial. Index_id of the PRIMARY index of [...]

MySQL 5.5 and MySQL 5.6 default variable values differences

Adventures in archiving

MySQL 5.6: Improvements in the Nutshell

Be productive with the MySQL command line

Percona Toolkit Webinar followup Q&A

Replaying database load with Percona Playback

Announcing Percona Server 5.5.27-29.0

Review of MySQL 5.6 Defaults Changes

Recovery after DROP & CREATE

MySQL Indexing Best Practices: Webinar Questions Followup

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA