Brief introduction to some of the MySQL InnoDB storage engine parameters

InnoDB is the most widely used transaction storage engine in MySQL, and many parts of the design and Oracle are common. For Oracle DBAs, learning can be more of an analogy to some of Oracle's features, and of course, to understand the difference.

Innodb_additional_mem_pool_size

Memory pool size used to cache InnoDB data dictionaries and other internal structures, similar to Oracle's library cache. This is not a mandatory parameter that can be breached.

Innodb_buffer_pool_size

Memory buffer pool size, for caching table and index data, and so on. Similar to Oracle's buffer cache, if possible, set as large as possible.

Innodb_log_buffer_size

Log buffer size, similar to Oracle log buffers

Innodb_log_file_size

Log file size. The default is to create 2 5M sizes of files named Ib_logfile0 and Ib_logfile1. The number of log files is specified by the parameter innodb_log_files_in_group. The storage location is specified by Innodb_log_group_home_dir.

Innodb_data_file_path

Specifies the InnoDB table space data file name, size, and other properties. All files should not add up to less than 10M. Multiple data files are separated by commas, and the attributes are separated by colons. The default is to create an automatically extensible data file of size 10MB named Ibdata1. Generally in the production environment need to be specified in accordance with the actual situation, because the table space to add data files need downtime, as far as possible in planning to prepare, if possible, it is best to open the last data file automatic growth properties. The number of data files in planning also need to consider another innodb_open_files parameter.

Innodb_file_per_table

Take the value on or off. Whether to save each table with a separate data file. If the number of tables in the system is not much, and there is no large table, the use of this parameter can make the maintenance of each table relatively independent, there are some benefits.

Innodb_autoextend_increment

When the automatic expansion table space is filled, the default value is 8 (in MB) for each size of the extended space. This parameter can be modified dynamically:

mysql> set global innodb_autoextend_increment=10;

Query OK, 0 rows affected (0.01 sec)

Innodb_status_file

Periodically save the results output of show INNDB status to a file, which is recommended for performance analysis.