When using MyISAM the configuration setting key_buffer_size
defines the size of the global buffer where MySQL caches frequently used blocks of index data.
What is the corresponding setting for InnoDB?
The key_buffer_size variable controls the amount of memory available for the MySQL index buffer. The higher this value, the more memory available for indexes and the better the performance. Typically, you would want to keep this value near 25 to 30 percent of the total available memory on the server.
The InnoDB buffer pool size sets how much memory (RAM) is allocated to MySQL for caching data and indexes. It should be set to as large a value as possible without causing excessive paging by other processes.
innodb_log_file_size is the size of a log file (in bytes). There exist multiple log file for MySQL to write the data to. This particular system-variable tells the file size of one of such log files.
From MySQL's documentation, the join_buffer_size is described as: “The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.” It goes on to say: “Memory allocation time can cause substantial performance drops if the global ...
innodb_buffer_pool_size
is the setting that controls the size of the memory buffer that InnoDB uses to cache indexes and data. It's an important performance option.
See the manual page for the full explanation. The MySQL Performance Blog also has an article about how to choose a proper size for it.
As far as I know, the best setting you can adjust for InnoDB is innodb_buffer_pool_size
.
The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 8MB. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With