Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is the InnoDB equivalent of MyISAM's key_buffer_size?

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?

like image 560
knorv Avatar asked Aug 02 '09 00:08

knorv


People also ask

What is key_buffer_size MySQL?

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.

What is InnoDB buffer pool size?

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.

What is Innodb_log_file_size in MySQL?

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.

What is Join_buffer_size?

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


2 Answers

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.

like image 196
zombat Avatar answered Nov 14 '22 22:11

zombat


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.

like image 26
Artem Russakovskii Avatar answered Nov 14 '22 23:11

Artem Russakovskii