7.5.3 InnoDB Startup Options

To enable InnoDB tables in MySQL version 3.23, see section 7.5.2 InnoDB in MySQL Version 3.23.

In MySQL-4.0 you are not required to do anything specific to enable InnoDB tables.

The default behaviour in MySQL-4.0 and MySQL-4.1 is to create an auto-extending 10 MB file `ibdata1' in the datadir of MySQL and two 5 MB `ib_logfile's to the datadir. (In MySQL-4.0.0 and 4.0.1 the datafile is 64 MB and not auto-extending.)

Note: To get good performance you should explicitly set the InnoDB parameters listed in the following examples.

If you don't want to use InnoDB tables, you can add the skip-innodb option to your MySQL option file.

Starting from versions 3.23.50 and 4.0.2, InnoDB allows the last datafile on the innodb_data_file_path line to be specified as auto-extending. The syntax for innodb_data_file_path is then the following:

pathtodatafile:sizespecification;pathtodatafile:sizespecification;...
...  ;pathtodatafile:sizespecification[:autoextend[:max:sizespecification]]

If you specify the last datafile with the autoextend option, InnoDB will extend the last datafile if it runs out of free space in the tablespace. The increment is 8 MB at a time. An example:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend

instructs InnoDB to create just a single datafile whose initial size is 100 MB and which is extended in 8 MB blocks when space runs out. If the disk becomes full you may want to add another datafile to another disk, for example. Then you have to look at the size of `ibdata1', round the size downward to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the rounded size of `ibdata1' explicitly in innodb_data_file_path. After that you can add another datafile:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

Be cautious on filesystems where the maximum file-size is 2 GB. InnoDB is not aware of the OS maximum file-size. On those filesystems you might want to specify the max size for the datafile:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M

A simple `my.cnf' example. Suppose you have a computer with 128 MB RAM and one hard disk. Below is an example of possible configuration parameters in `my.cnf' or `my.ini' for InnoDB. We assume you are running MySQL-Max-3.23.50 or later, or MySQL-4.0.2 or later. This example suits most users, both on Unix and Windows, who do not want to distribute InnoDB datafiles and log files on several disks. This creates an auto-extending datafile `ibdata1' and two InnoDB log files `ib_logfile0' and `ib_logfile1' to the datadir of MySQL (typically `/mysql/data'). Also the small archived InnoDB log file `ib_arch_log_0000000000' ends up in the datadir.

[mysqld]
# You can write your other MySQL server options here
# ...
#                                  Datafile(s) must be able to
#                                  hold your data and indexes.
#                                  Make sure you have enough
#                                  free disk space.
innodb_data_file_path = ibdata1:10M:autoextend
#                                  Set buffer pool size to
#                                  50 - 80 % of your computer's
#                                  memory
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
#                                  Set the log file size to about
#                                  25 % of the buffer pool size
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
#                                  Set ..flush_log_at_trx_commit
#                                  to 0 if you can afford losing
#                                  some last transactions 
innodb_flush_log_at_trx_commit=1

Check that the MySQL server has the rights to create files in datadir.

Note that datafiles must be < 2 GB in some file systems! The combined size of the log files must be < 4 GB. The combined size of datafiles must be >= 10 MB.

When you for the first time create an InnoDB database, it is best that you start the MySQL server from the command prompt. Then InnoDB will print the information about the database creation to the screen, and you see what is happening. See below next section what the printout should look like. For example, in Windows you can start `mysqld-max.exe' with:

your-path-to-mysqld\mysqld-max --console

Where to put `my.cnf' or `my.ini' in Windows? The rules for Windows are the following:

• Only one of `my.cnf' or `my.ini' should be created.
• The `my.cnf' file should be placed in the root directory of the drive `C:'.
• The `my.ini' file should be placed in the WINDIR directory, e.g, `C:\WINDOWS' or `C:\WINNT'. You can use the SET command of MS-DOS to print the value of WINDIR.
• If your PC uses a boot loader where the `C:' drive is not the boot drive, then your only option is to use the `my.ini' file.

Where to specify options in Unix? On Unix `mysqld' reads options from the following files, if they exist, in the following order:

• `/etc/my.cnf' Global options.
• `COMPILATION_DATADIR/my.cnf' Server-specific options.
• `defaults-extra-file' The file specified with --defaults-extra-file=....
• `~/.my.cnf' User-specific options.

`COMPILATION_DATADIR' is the MySQL data directory which was specified as a ./configure option when `mysqld' was compiled (typically `/usr/local/mysql/data' for a binary installation or `/usr/local/var' for a source installation).

If you are not sure from where `mysqld' reads its `my.cnf' or `my.ini', you can give the path as the first command-line option to the server: mysqld --defaults-file=your_path_to_my_cnf.

InnoDB forms the directory path to a datafile by textually catenating innodb_data_home_dir to a datafile name or path in innodb_data_file_path, adding a possible slash or backslash in between if needed. If the keyword innodb_data_home_dir is not mentioned in `my.cnf' at all, the default for it is the 'dot' directory `./' which means the datadir of MySQL.

An advanced `my.cnf' example. Suppose you have a Linux computer with 2 GB RAM and three 60 GB hard disks (at directory paths `/', `/dr2' and `/dr3'). Below is an example of possible configuration parameters in `my.cnf' for InnoDB.

Note that InnoDB does not create directories: you have to create them yourself. Use the Unix or MS-DOS mkdir command to create the data and log group home directories.

[mysqld]
# You can write your other MySQL server options here
# ...
innodb_data_home_dir =
#                                  Datafiles must be able to
#                                  hold your data and indexes
innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend
#                                  Set buffer pool size to
#                                  50 - 80 % of your computer's
#                                  memory, but make sure on Linux
#                                  x86 total memory usage is
#                                  < 2 GB
set-variable = innodb_buffer_pool_size=1G
set-variable = innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs
#                                  .._log_arch_dir must be the same
#                                  as .._log_group_home_dir
innodb_log_arch_dir = /dr3/iblogs
set-variable = innodb_log_files_in_group=3
#                                  Set the log file size to about
#                                  15 % of the buffer pool size
set-variable = innodb_log_file_size=150M
set-variable = innodb_log_buffer_size=8M
#                                  Set ..flush_log_at_trx_commit to
#                                  0 if you can afford losing
#                                  some last transactions 
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
#innodb_flush_method=fdatasync
#set-variable = innodb_thread_concurrency=5

Note that we have placed the two datafiles on different disks. InnoDB will fill the tablespace formed by the datafiles 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. You can also use raw disk partitions (raw devices) as datafiles. In some Unixes they speed up I/O. See the manual section on InnoDB file space management about how to specify them in `my.cnf'.

Warning: on Linux x86 you must be careful you do not set memory usage too high. glibc will allow the process heap to grow over thread stacks, which will crash your server. It is a risk if the value of

innodb_buffer_pool_size + key_buffer +
max_connections * (sort_buffer + read_buffer_size) + max_connections * 2 MB

is close to 2 GB or exceeds 2 GB. Each thread will use a stack (often 2 MB, but in MySQL AB binaries only 256 KB) and in the worst case also sort_buffer + read_buffer_size additional memory.

How to tune other `mysqld' server parameters? Typical values which suit most users are:

skip-locking
set-variable = max_connections=200
set-variable = read_buffer_size=1M
set-variable = sort_buffer=1M
#                                  Set key_buffer to 5 - 50%
#                                  of your RAM depending on how
#                                  much you use MyISAM tables, but
#                                  keep key_buffer + InnoDB
#                                  buffer pool size < 80% of
#                                  your RAM
set-variable = key_buffer=...

Note that some parameters are given using the numeric `my.cnf' parameter format: set-variable = innodb... = 123, others (string and boolean parameters) with another format: innodb_... = ... .

The meanings of the configuration parameters are the following:

User Comments

In the first example, it says that innodb_log_file_size should
be approx 25% of innodb_buffer_pool_size, yet the value
listed is only about 15%. In the second example, it says
that innodb_log_file_size should be 15%, and the value
listed is about 15%. I think the initial "25%" is probably
incorrect.

It will seems good if it's as step by step. It's not clear about the topic.
Regards

The file mysqld-max is discussed above:
"For example, in Windows you can start `mysqld-max.exe' with:
your-path-to-mysqld\mysqld-max --console

However, I am able to run this command:
mysql> /usr/sbin/mysqld --console
and, with my system properly configured, have the InnoDB engine run properly.

Overall, there is ambiguity in this document regarding a MySQL-max RPM. Particularly, an RPM is explicitly mentioned here:
http://www.mysql.com/doc/en/mysqld-max.html
but not here:
http://www.mysql.com/doc/en/Linux-RPM.html

ASFIsee, Red Hat Linux does *not* distribute this RPM, but rather includes it in the standard (or devel?) distribution.

I have submitted a doc bug report regarding this issue.

As to the complaint about mysqld-max on Linux:

You quotes the line which said IN WINDOWS, therefore it is irrelevant what you can or cannot do under Linux.

Add your own comment.