Setting InnoDB Configuration Options

In the sample options file we looked at, we set some options relating to InnoDB. You can use InnoDB without setting any of these options, but you should set them for better performance. These are the options we set previously:

• innodb_data_file_path = ibdata1:10M: autoextend

  This option tells MySQL where to store InnoDB data. Unlike MyISAM tables, in which each table gets its own file, InnoDB tables are stored in a shared tablespace, which may consist of one or more files. This particular example tells MySQL to store all the InnoDB data in a single file called ibdata1 , to set the initial file size to 10MB, and to automatically make it bigger (8MB at a time) if the tablespace becomes full.

  The general format of this option is

    filename:filesize  [;  filename:filesize  ;...][:autoextend[:max:  size  ]] 

  The autoextend option allows the tablespace to grow. The max option allows you to set a maximum size to which it can grow.

• innodb_buffer_pool_size=70M

  This option sets the size of the buffer used to cache InnoDB table data and indexes. As with any cache, the bigger it is, the less disk I/O you will have. How much you put into the buffer pool will depend on whether there are other applications and users on the server and how much memory you have.

• innodb_additional_mem_pool_size=10M

  This option sets aside memory to store internal MySQL data structures. If MySQL is running out of room here, it will begin writing warnings to the error log.

• innodb_log_file_size=20M

  This option sets the size of each log file. InnoDB rotates between n log files ”where n is the value set in the innodb_log_files_in_group option, which defaults to 2 , the recommended value.

• innodb_log_buffer_size=8M

  This option sets the size of the buffer in which logs are stored before they are written to disk.

• innodb_flush_log_at_trx_commit=1

  Setting this option to 1 means that every time a transaction is committed the log will be flushed to disk. This is the normal behavior. If it is set to zero, the log will be written to and flushed to disk only roughly once per second. If it is set to 2 , the log will be written to with each commit, but flushed only once per second. Values of or 2 will improve performance, but are obviously a fairly risky proposition.

There are various other InnoDB configuration options. See the MySQL manual for details.