Skip navigation links
MySQL 5.4 Feature Summary :: 7 Configuration Differences in MySQL 5.4 from MySQL 5.1
8 Acknowledgments »
Section Navigation      [Toggle]

7. Configuration Differences in MySQL 5.4 from MySQL 5.1

Several variables and options are new or changed in MySQL 5.4 to provide better “out of box” configuration of default values for MySQL operation on up to 16-way x86 servers and 64-way CMT servers with 4GB or more memory.

Note

The changes to innodb_log_file_size, innodb_log_files_in_group, and innodb_file_io_threads may cause issues if you upgrade to MySQL 5.4 from an existing MySQL installation. See Section 2, “Platform Availability and Installation/Upgrade Considerations”.

The new system variables described here are all global and do not have session values. They are not dynamic (cannot be changed at runtime). To assign values different from the defaults, you must set them at server startup. Variables that have values of ON or OFF as displayed by SHOW VARIABLES should be enabled or disabled at server startup by assigning a value of 1 or 0.

New system variables:

  • innodb_extra_dirty_writes: If enabled (the default), flushing of dirty buffer pages may occur when the server is idle, even if the percentage of dirty pages is less than the maximum dirty percent normally required for flushing (as indicated by innodb_max_dirty_pages_pct).

  • innodb_io_capacity: The limit on the maximum number of I/O operations per second (IOPS) the server can perform. Default: 200.

  • innodb_max_merged_io: The maximum number of background I/O requests that will be merged to issue a larger I/O request in a more contiguous manner. Default: 64.

  • innodb_read_io_threads, innodb_write_io_threads: The number of background I/O threads to use for read prefetch requests and for writing dirty pages from the buffer cache to disk. Default: 8.

  • innodb_thread_concurrency_timer_based: If enabled (the default), use a lock-free timer-based method of handling thread concurrency. If disabled, the original mutex-based method is used. For the lock-free concurrency method to be used, two requirements must be satisfied:

    • The innodb_thread_concurrency system variable must be set to a number greater than 0. The default value in MySQL 5.4 is 0, so you must change it to use the lock-free concurrency method.

    • The server must have been built with atomic instructions. That is, the Innodb_have_sync_atomic status variable must be ON.

    If innodb_thread_concurrency is 0 or Innodb_have_sync_atomic is OFF, enabling innodb_thread_concurrency_timer_based has no effect.

Changes to existing system variables:

  • innodb_additional_mem_pool_size: Default increased from 1MB to 8MB. Minimum increased from 512KB to 2MB.

  • innodb_autoextend_increment: Default increased from 8MB to 64MB.

  • innodb_buffer_pool_size: Default increased from 8 MB to 1GB. Minimum increased from 1MB to 64MB.

  • innodb_file_io_threads: Removed (replaced by innodb_read_io_threads and innodb_write_io_threads).

  • innodb_log_buffer_size: Default increased from 1MB to 16MB. Minimum increased from 256KB to 2MB.

  • innodb_log_file_size: Default increased from 5MB to 128MB. Minimum increased from 1MB to 32MB.

  • innodb_log_files_in_group: Default increased from 2 to 3.

  • innodb_max_dirty_pages_pct: Default decreased from 90 to 75. Maximum decreased from 100 to 99 to never allow a completely dirty buffer pool.

  • innodb_thread_concurrency: Default changed from 8 to 0. In effect, this changes concurrency from 8 to “infinite”.

  • table_definition_cache: Default and minimum increased from 256 to 400.

  • table_open_cache: Default increased from 64 to 400.

New status variables:

  • Innodb_have_sync_atomic: Indicates whether the server was built with atomic instructions.

  • Innodb_heap_enabled: Indicates whether the built-in InnoDB memory manager is used. (ON means it is used, OFF means it is not.)

  • Innodb_wake_ups: The number of wakeups that should not have occurred. These are wakeups in a condition on a mutex when the condition is not yet met so the thread must go back to sleep.

New option:

  • --super-large-pages: Boolean option. Large page support is enhanced for recent SPARC platforms. Standard use of large pages in MySQL attempts to use the largest size supported, up to 4MB. Under Solaris, a “super large pages” feature enables uses of pages up to 256MB. This feature is enabled by default on Solaris. It can be enabled or disabled explicitly by using the --super-large-pages or --skip-super-large-pages option.

Changes to existing options:

  • --large-pages: Enabled by default.