WL#4980: Update InnoDB defaults
Affects: Server-5.4 — Status: Complete — Priority: Medium
Updated InnoDB defaults. In MySQL 5.4.0 1) Buffer Pool size now defaults to 1GB and a minimum of 64MB 2) Log files are 3 by default and each 128MB in size 3) Removed innodb-file-io-threads config variable since no longer used 4) Set read io and write io threads to 8 by default for better default performance 5) Set log buffer size to 16 MB by default and minimum to 2MB 6) Set additional memory buffer pool to 8 MB and minimum to 2MB 7) Set max dirty percent to 75% and decreased to 99% to never allow a completely dirty buffer pool 8) Increased io capacity to 200 for a good default
In MySQL 5.4.0, change the following InnoDB defaults: 1) innodb_buffer_pool_size: Increase default from 8MB to 1GB; increase minimum from 1MB to 64MB. 2) innodb_log_files_in_group: Increase default from 2 to 3, with each log file 128MB in size. 3) Removed innodb_file_io_threads (no longer used); replaced by innodb_read_io_threads and innodb_write_io_threads variables, each with a default of 8 background I/O threads to use for read prefetch requests and for writing dirty pages from the buffer cache to disk. 4) innodb_log_buffer_size: Increase default from 1MB to 16MB; increase minimum from 256KB to 2MB. 5) innodb_additional_mem_pool_size: Increase default from 1MB to 8MB; increase minimum from 512KB to 2MB. 6) innodb_max_dirty_pages_pct: Decrease default from 90 to 75; decrease maximum from 100 to 99 to never allow a completely dirty buffer pool. 7) innodb_autoextend_increment: Increase default from 8MB to 64MB. 8) innodb_log_file_size: Increase default from 5MB to 128MB; increase minimum from 1MB to 32MB. 9) table_definition_cache: Increase default from 64 to 400. These changes are made 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.
Default value of an existing variable changed; no further LLD required.
Copyright (c) 2000, 2016, Oracle Corporation and/or its affiliates. All rights reserved.