GNU Info

(mysql.info)InnoDB start

---

Next: InnoDB init Prev: InnoDB overview Up: InnoDB

---

Enter node , (file) or (file)node

InnoDB startup options
----------------------

Beginning from MySQL-3.23.37 the prefix of the options is changed from
`innobase_...' to `innodb_...'.

To use InnoDB tables you *MUST* specify configuration parameters in the
MySQL configuration file in the `[mysqld]' section of the configuration
file `my.cnf'. Note: Option files.

The only required parameter to use InnoDB is `innodb_data_file_path',
but you should set others if you want to get a better performance.

Suppose you have a Windows NT machine with 128 MB RAM and a single 10 GB
hard disk.  Below is an example of possible configuration parameters in
`my.cnf' for InnoDB:

     innodb_data_file_path = ibdata1:2000M;ibdata2:2000M
     innodb_data_home_dir = c:\ibdata
     set-variable = innodb_mirrored_log_groups=1
     innodb_log_group_home_dir = c:\iblogs
     set-variable = innodb_log_files_in_group=3
     set-variable = innodb_log_file_size=30M
     set-variable = innodb_log_buffer_size=8M
     innodb_flush_log_at_trx_commit=1
     innodb_log_arch_dir = c:\iblogs
     innodb_log_archive=0
     set-variable = innodb_buffer_pool_size=80M
     set-variable = innodb_additional_mem_pool_size=10M
     set-variable = innodb_file_io_threads=4
     set-variable = innodb_lock_wait_timeout=50

Note that data files must be < 4G, and < 2G on some file systems! The
total size of data files has to be >= 10 MB.  InnoDB does not create
directories: you have to create them yourself.

Suppose you have a Linux machine with 512 MB RAM and three 20 GB hard
disks (at directory paths `/', `/dr2' and `/dr3').  Below is an example
of possible configuration parameters in `my.cnf' for InnoDB:

     innodb_data_file_path = ibdata/ibdata1:2000M;dr2/ibdata/ibdata2:2000M
     innodb_data_home_dir = /
     set-variable = innodb_mirrored_log_groups=1
     innodb_log_group_home_dir = /dr3
     set-variable = innodb_log_files_in_group=3
     set-variable = innodb_log_file_size=50M
     set-variable = innodb_log_buffer_size=8M
     innodb_flush_log_at_trx_commit=1
     innodb_log_arch_dir = /dr3/iblogs
     innodb_log_archive=0
     set-variable = innodb_buffer_pool_size=400M
     set-variable = innodb_additional_mem_pool_size=20M
     set-variable = innodb_file_io_threads=4
     set-variable = innodb_lock_wait_timeout=50

Note that we have placed the two data files on different disks.  The
reason for the name `innodb_data_file_path' is that you can also
specify paths to your data files, and `innodb_data_home_dir' is just
textually catenated before your data file paths, adding a possible
slash or backslash in between. InnoDB will fill the tablespace formed
by the data files from bottom up. In some cases it will improve the
performance of the database if all data is not placed on the same
physical disk. Putting log files on a different disk from data is very
often beneficial for performance.

The meanings of the configuration parameters are the following:

`innodb_data_home_dir'  The common part of the directory path for all
                       InnoDB data files.
`innodb_data_file_path' Paths to individual data files and their sizes.
                       The full directory path to each data file is
                       acquired by concatenating innodb_data_home_dir to
                       the paths specified here. The file sizes are
                       specified in megabytes, hence the 'M' after the
                       size specification above. Do not set a file size
                       bigger than 4000M, and on most operating systems
                       not bigger than 2000M.  InnoDB also understands
                       the abbreviation 'G', 1G meaning 1024M.  The sum
                       of the sizes of the files must be at least 10 MB.
`innodb_mirrored_log_groups' Number of identical copies of log groups we keep
                       for the database. Currently this should be set to
                       1.
`innodb_log_group_home_dir' Directory path to InnoDB log files.
`innodb_log_files_in_group' Number of log files in the log group. InnoDB
                       writes to the files in a circular fashion. Value
                       3 is recommended here.
`innodb_log_file_size'  Size of each log file in a log group in
                       megabytes. Sensible values range from 1M to the
                       size of the buffer pool specified below. The
                       bigger the value, the less checkpoint flush
                       activity is needed in the buffer pool, saving
                       disk i/o. But bigger log files also mean that
                       recovery will be slower in case of a crash. File
                       size restriction as for a data file.
`innodb_log_buffer_size' The size of the buffer which InnoDB uses to
                       write log to the log files on disk.  Sensible
                       values range from 1M to half the combined size of
                       log files. A big log buffer allows large
                       transactions to run without a need to write the
                       log to disk until the transaction commit. Thus,
                       if you have big transactions, making the log
                       buffer big will save disk i/o.
`innodb_flush_log_at_trx_commit' Normally this is set to 1, meaning that at a
                       transaction commit the log is flushed to disk,
                       and the modifications made by the transaction
                       become permanent, and survive a database crash.
                       If you are willing to compromise this safety, and
                       you are running small transactions, you may set
                       this to 0 to reduce disk i/o to the logs.
`innodb_log_arch_dir'   The directory where fully written log files
                       would be archived if we used log archiving.  The
                       value of this parameter should currently be set
                       the same as `innodb_log_group_home_dir'.
`innodb_log_archive'    This value should currently be set to 0.  As
                       recovery from a backup is done by MySQL using its
                       own log files, there is currently no need to
                       archive InnoDB log files.
`innodb_buffer_pool_size' The size of the memory buffer InnoDB uses to
                       cache data and indexes of its tables.  The bigger
                       you set this the less disk i/o is needed to
                       access data in tables. On a dedicated database
                       server you may set this parameter up to 90 % of
                       the machine physical memory size. Do not set it
                       too large, though, because competition of the
                       physical memory may cause paging in the operating
                       system.
`innodb_additional_mem_pool_size' Size of a memory pool InnoDB uses to store data
                       dictionary information and other internal data
                       structures. A sensible value for this might be
                       2M, but the more tables you have in your
                       application the more you will need to allocate
                       here. If InnoDB runs out of memory in this pool,
                       it will start to allocate memory from the
                       operating system, and write warning messages to
                       the MySQL error log.
`innodb_file_io_threads' Number of file i/o threads in InnoDB. Normally,
                       this should be 4, but on Windows NT disk i/o may
                       benefit from a larger number.
`innodb_lock_wait_timeout' Timeout in seconds an InnoDB transaction may
                       wait for a lock before being rolled back.  InnoDB
                       automatically detects transaction deadlocks in
                       its own lock table and rolls back the
                       transaction. If you use `LOCK TABLES' command, or
                       other transaction-safe table handlers than InnoDB
                       in the same transaction, then a deadlock may
                       arise which InnoDB cannot notice. In cases like
                       this the timeout is useful to resolve the
                       situation.
`innodb_flush_method'   (Available from 3.23.40 up.)  The default value
                       for this is `fdatasync'.  Another option is
                       `O_DSYNC'.