Table types in MySQL: Part 2 – InnoDB Tables Page 2

InnoDB configuration options

There are a number of
important configuration variables to consider. All of these are set in the MySQL
configuration file (usually my.cnf or my.ini, and located in the
data directory). The most important of these is innodb_data_file_path,
which specifies the space available to the tables (including both data and
indexes). An example:

innodb_data_file_path = ibdata1:10M:autoextend 

This is the default
setting, indicating that there is an initial file, ibdata1 of 10MB,
which is auto-extending. You can set data files to appear on different disks as
well. For example:

innodb_data_file_path = 
/disk1/ibdata1:500M;/disk2/ibdata2:300MB;/disk3/ibdata3:100MB:autoextend

You will usually want to
make sure that the final file is auto-extending, just to cover you if you run
out of ‘logical’ space, but of course, nothing covers you if the physical disk
runs out of space. If you want to use absolute file paths in this parameter,
you need to set the value of innodb_data_home_dir to an empty string. To
make any changes to your configuration file take effect, you will need to
restart the server.

Here is a list of the
other InnoDB configuration variables, with a brief description of their
function:

• innodb_data_home_dir:
  Where InnoDB data files are stored. If left blank, this will be the same as datadir,
  and if set to an empty string, you can use absolute file paths in innodb_data_file_path.

• innodb_mirrored_log_groups:
  Should be set to 1 (the number of identical copies of log groups kept for the
  database.)

• innodb_log_group_home_dir:
  Where InnoDB log files are kept (If this is not set, datadir is used.

• innodb_log_files_in_group:
  Number of log files in the log group (logs are written in rotation). Defaults
  to 2, which is usually sufficient.

• innodb_log_file_size:
  Size in megabytes of each log file. Defaults to 5MB. The larger it is, the
  slower a recovery after a crash will be, but marginal disk I/O will be saved.

• innodb_log_buffer_size:
  Size of the buffer used to write log files. The larger it is, the less need to
  write to disk in the middle of a transaction. 8MB is suggested.

• innodb_flush_log_at_trx_commit:
  0, 1 or 2. Should almost always be set to 1 for safety reasons, which writes
  the log to disk and flushes the disk when a transaction is committed. 0 does
  this about once a second, while 2 writes the log to disk immediately, but
  flushes the disk about once a second.

• innodb_log_arch_dir:
  If log archiving was used, this would be the directory where these files are
  stored. Should currently be the same as innodb_log_group_home_dir.

• innodb_log_archive:
  Set to 0, as MySQL recovers using its own log files.

• innodb_buffer_pool_size:
  Size in bytes of the memory buffer used to cache table data and indexes. The
  larger the better if you can spare it (up to 80% on dedicated database
  servers).

• innodb_buffer_pool_awe_mem_mb:
  Used from MySQL 4.1, and on Windows only, this variable sets the size of the
  buffer pool placed in Address Windowing Extension (AWE) memory (maximum 64000).

• innodb_additional_mem_pool_size:
  Size in bytes of a memory pool that stores information about the internal data
  structures. 2MB is suggested initially, but if you see warning messages in the MySQL
  error log about allocating memory from the operating system, you should
  increase this.

• innodb_file_io_threads:
  Number of file I/O threads in InnoDB. 4 is suggested on Unix systems, often
  higher for Windows systems.

• innodb_lock_wait_timeout:
  Time in seconds an InnoDB transaction waits before rolling back (used only in
  case of external deadlocks, such as from LOCK TABLES statements)

• innodb_flush_method:
  Flushing method (default fdatasync,
  which is usually faster, although on some systems O_DSYNC can be
  faster).

• innodb_force_recovery:
  Only set this when you want to dump data from a corrupt database! See the
  manual for more if you need to use this option.

If something goes wrong

When you start the
server, MySQL creates the tablespace, data files and log files. If something
goes wrong, the problem is usually one of two things: permissions, or a syntax
error in the configuration file. Make sure all directories you are trying to
store data and logs in already exist, and that the server is assigned
permission to write in them. Delete all files created in a failed startup
before trying again.

In future articles we
will look in more detail at some of the options available when using InnoDB
tables, but now that you know how to create them, the best way to find out more
is to begin experimenting – on a development server of course! Good luck.

This article originally appeared on DatabaseJournal.com.