) MySQL InnoDB Storage engine parameters

Http://www.ningoo.net/html/2008/mysql_innodb_parametes.html#more-322

As the most widely used transaction storage engine for MySQL, InnoDB is designed in many places in common with Oracle. For Oracle DBAs, it can be compared with some features of Oracle. Of course, you must understand the differences between them.

Innodb_additional_mem_pool_size

Memory pool size used to cache InnoDB data dictionaries and other internal structures, similar to the Oracle library cache. This is not a mandatory parameter and can be broken through.

Innodb_buffer_pool_size

Memory buffer pool size, used to cache tables and index data. It is similar to the buffer cache of Oracle. If possible, set it as bigger as possible.

Innodb_log_buffer_size

Log buffer size, similar to the Oracle log Buffer

Innodb_log_file_size

Log File size. By default, two 5 MB files named ib_logfile0 and ib_logfile1 are created. The number of log files is specified by the innodb_log_files_in_group parameter. The storage location is specified by innodb_log_group_home_dir.

Innodb_data_file_path

Specify the data file name, size, and other attributes of the InnoDB tablespace. The total size of all files cannot be less than 10 MB. Multiple data files are separated by commas, and attributes are separated by colons. By default, a data file with a size of 10 MB and the name ibdata1 can be automatically expanded. Generally, it needs to be specified in the production environment according to the actual situation. Due to the need to add data files to the tablespace, the system will be suspended, we recommend that you make preparations during planning. If you can, you should enable the automatic growth attribute of the last data file. When planning the number of data files, you also need to consider another innodb_open_files parameter.

Innodb_file_per_table

The value is on or off. Whether to use a separate data file for each table. If the number of tables in the system is small and there is no large table, using this parameter can make maintenance between tables relatively independent, with some benefits.

Innodb_autoextend_increment

When the automatically expanded tablespace is filled, the size of each extended tablespace is 8 by default (in MB ). This parameter can be dynamically modified:

Mysql> set global innodb_autoextend_increment = 10;

Query OK, 0 rows affected (0.01 Sec)

Innodb_status_file

Regularly save the results of show inndb status to the file. We recommend that you enable this function to analyze performance.

The following is the default MySQL parameter query result on Windows:

Mysql> show variables like 'innodb % ';