MySQL 5.0 Reference Manual  /  ...  /  Configuring InnoDB

14.2.1 Configuring InnoDB

If you do not want to use InnoDB tables, start the server with the --skip-innodb option to disable the InnoDB storage engine. In this case, the server will not start if the default storage engine is set to InnoDB. Use --default-storage-engine to set the default to some other engine if necessary.


InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. However, it cannot do so if the underlying operating system or hardware does not work as advertised. Many operating systems or disk subsystems may delay or reorder write operations to improve performance. On some operating systems, the very fsync() system call that should wait until all unwritten data for a file has been flushed might actually return before the data has been flushed to stable storage. Because of this, an operating system crash or a power outage may destroy recently committed data, or in the worst case, even corrupt the database because of write operations having been reordered. If data integrity is important to you, you should perform some pull-the-plug tests before using anything in production. On OS X 10.3 and up, InnoDB uses a special fcntl() file flush method. Under Linux, it is advisable to disable the write-back cache.

On ATA/SATA disk drives, a command such hdparm -W0 /dev/hda may work to disable the write-back cache. Beware that some drives or disk controllers may be unable to disable the write-back cache.

With regard to InnoDB recovery capabilities that protect user data, InnoDB uses a file flush technique involving a structure called the doublewrite buffer, which is enabled by default (innodb_doublewrite=ON). The doublewrite buffer adds safety to recovery following a crash or power outage, and improves performance on most varieties of Unix by reducing the need for fsync() operations. It is recommended that the innodb_doublewrite option remains enabled if you are concerned with data integrity or possible failures. For additional information about the doublewrite buffer, see Section 14.2.11, “InnoDB Disk I/O and File Space Management”.

Overview of InnoDB Tablespace and Log Files

Two important disk-based resources managed by the InnoDB storage engine are its tablespace data files and its log files. If you specify no InnoDB configuration options, MySQL creates an auto-extending data file, slightly larger than 10MB, named ibdata1 and two log files named ib_logfile0 and ib_logfile1 in the MySQL data directory. Their size is given by the size of the innodb_log_file_size system variable. To get good performance, explicitly provide InnoDB parameters as discussed in the following examples. Naturally, edit the settings to suit your hardware and requirements.


It is not a good idea to configure InnoDB to use data files or log files on NFS volumes. Otherwise, the files might be locked by other processes and become unavailable for use by MySQL.

The examples shown here are representative. See Section 14.2.2, “InnoDB Startup Options and System Variables” for additional information about InnoDB-related configuration parameters.

To set up the InnoDB tablespace files, use the innodb_data_file_path option in the [mysqld] section of the my.cnf option file. On Windows, you can use my.ini instead. The value of innodb_data_file_path should be a list of one or more data file specifications. If you name more than one data file, separate them by semicolon (;) characters:


For example, the following setting explicitly creates a tablespace having the same characteristics as the default:


This setting configures a single 10MB data file named ibdata1 that is auto-extending. No location for the file is given, so by default, InnoDB creates it in the MySQL data directory.

Sizes are specified using K, M, or G suffix letters to indicate units of KB, MB, or GB.

A tablespace containing a fixed-size 50MB data file named ibdata1 and a 50MB auto-extending file named ibdata2 in the data directory can be configured like this:


The full syntax for a data file specification includes the file name, its size, and several optional attributes:


The autoextend and max attributes can be used only for the last data file in the innodb_data_file_path line.

If you specify the autoextend option for the last data file, InnoDB extends the data file if it runs out of free space in the tablespace. The increment is 8MB at a time by default. To modify the increment, change the innodb_autoextend_increment system variable.

If the disk becomes full, you might want to add another data file on another disk. For tablespace reconfiguration instructions, see Section 14.2.5, “Resizing the InnoDB System Tablespace”.

InnoDB is not aware of the file system maximum file size, so be cautious on file systems where the maximum file size is a small value such as 2GB. To specify a maximum size for an auto-extending data file, use the max attribute following the autoextend attribute. The following configuration permits ibdata1 to grow up to a limit of 500MB:


InnoDB creates tablespace files in the MySQL data directory by default. To specify a location explicitly, use the innodb_data_home_dir option. For example, to use two files named ibdata1 and ibdata2 but create them in the /ibdata directory, configure InnoDB like this:

innodb_data_home_dir = /ibdata

InnoDB does not create directories, so make sure that the /ibdata directory exists before you start the server. This is also true of any log file directories that you configure. Use the Unix or DOS mkdir command to create any necessary directories.

Make sure that the MySQL server has the proper access rights to create files in the data directory. More generally, the server must have access rights in any directory where it needs to create data files or log files.

InnoDB forms the directory path for each data file by textually concatenating the value of innodb_data_home_dir to the data file name, adding a path name separator (slash or backslash) between values if necessary. If the innodb_data_home_dir option is not specified in my.cnf at all, the default value is the dot directory ./, which means the MySQL data directory. (The MySQL server changes its current working directory to its data directory when it begins executing.)

If you specify innodb_data_home_dir as an empty string, you can specify absolute paths for the data files listed in the innodb_data_file_path value. The following example is equivalent to the preceding one:

innodb_data_home_dir =

Specifying InnoDB Configuration Options

Sample my.cnf file for small systems. Suppose that you have a computer with 512MB RAM and one hard disk. The following example shows possible configuration parameters in my.cnf or my.ini for InnoDB, including the autoextend attribute. The example suits most users, both on Unix and Windows, who do not want to distribute InnoDB data files and log files onto several disks. It creates an auto-extending data file ibdata1 and two InnoDB log files ib_logfile0 and ib_logfile1 in the MySQL data directory.

# You can write your other MySQL server options here
# ...
# Data files must be able to hold your data and indexes.
# Make sure that 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 the log file size to about 25% of the buffer pool size

Note that data files must be less than 2GB in some file systems. The combined size of the log files must be less than 4GB. The combined size of data files must be at least slightly larger than 10MB.

Setting Up the InnoDB System Tablespace

When you create an InnoDB system tablespace for the first time, it is best that you start the MySQL server from the command prompt. InnoDB then prints the information about the database creation to the screen, so you can see what is happening. For example, on Windows, if mysqld is located in C:\Program Files\MySQL\MySQL Server 5.0\bin, you can start it like this:

C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld" --console

If you do not send server output to the screen, check the server's error log to see what InnoDB prints during the startup process.

For an example of what the information displayed by InnoDB should look like, see Section, “Initializing InnoDB”.

Editing the MySQL Configuration File

You can place InnoDB options in the [mysqld] group of any option file that your server reads when it starts. The locations for option files are described in Section 4.2.6, “Using Option Files”.

If you installed MySQL on Windows using the installation and configuration wizards, the option file will be the my.ini file located in your MySQL installation directory. See Section, “Starting the MySQL Server Instance Configuration Wizard”.

If your PC uses a boot loader where the C: drive is not the boot drive, your only option is to use the my.ini file in your Windows directory (typically C:\WINDOWS). You can use the SET command at the command prompt in a console window to print the value of WINDIR:


To make sure that mysqld reads options only from a specific file, use the --defaults-file option as the first option on the command line when starting the server:

mysqld --defaults-file=your_path_to_my_cnf

Sample my.cnf file for large systems. Suppose that you have a Linux computer with 2GB RAM and three 60GB hard disks at directory paths /, /dr2 and /dr3. The following example shows possible configuration parameters in my.cnf for InnoDB.

# You can write your other MySQL server options here
# ...
innodb_data_home_dir =
# Data files 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 < 2GB
innodb_log_group_home_dir = /dr3/iblogs
# Set the log file size to about 25% of the buffer pool size
# Uncomment the next line if you want to use it

In some cases, database performance improves if the data is not all placed on the same physical disk. Putting log files on a different disk from data is very often beneficial for performance. The example illustrates how to do this. It places the two data files on different disks and places the log files on the third disk. InnoDB fills the tablespace beginning with the first data file. You can also use raw disk partitions (raw devices) as InnoDB data files, which may speed up I/O. See Section, “Using Raw Devices for the System Tablespace”.

Determining the Maximum Memory Allocation for InnoDB


On 32-bit GNU/Linux x86, be careful not to set memory usage too high. glibc may permit the process heap to grow over thread stacks, which crashes your server. It is a risk if the value of the following expression is close to or exceeds 2GB:

+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB

Each thread uses a stack (often 2MB, but only 256KB in MySQL binaries provided by Oracle Corporation.) and in the worst case also uses sort_buffer_size + read_buffer_size additional memory.

By compiling MySQL yourself, you can use up to 64GB of physical memory in 32-bit Windows. See the description for innodb_buffer_pool_awe_mem_mb in Section 14.2.2, “InnoDB Startup Options and System Variables”.

Tuning other mysqld server parameters. The following values are typical and suit most users:

# Set key_buffer to 5 - 50% of your RAM depending on how much
# you use MyISAM tables, but keep key_buffer_size + InnoDB
# buffer pool size < 80% of your RAM

On Linux, if the kernel is enabled for large page support, InnoDB can use large pages to allocate memory for its buffer pool and additional memory pool. See Section, “Enabling Large Page Support”.

User Comments
  Posted by Brian Krispinsky on April 14, 2005
I've found quite a few people having issues initializing MySQL 4.0.x with InnoDB support under Suse 9.x Pro. Server startup will fail with a message like:

unknown variable 'innodb_data_home_dir=/var/lib/mysql'

If this happens, make sure you've installed the mySQL-Max package (after installing the base mySQL server). Then invoke the server like:

$ /usr/sbin/mysqld-max

You should then see your InnoDB tables initialize.
  Posted by Jose Pablo Ezequiel Fernandez Silva on July 12, 2005
InnoDB on Gentoo is not enabled unless you specify the "innodb" USE flag. Knowing this may save you from having to do an extra compilation.
  Posted by Jaime Montoya on January 8, 2006
Hola, la forma de activar las tablas innodb en mysql 3.23.54 me funciono de esta forma usando Red Hat Linux 9:

Primero me fui a /etc/my.cnf, y agregue algunas lineas que me faltaban en ese archivo de modo que al final quedara de esta manera:

# skip_innodb
set-variable = innodb_buffer_pool_size=80M
set-variable = innodb_additional_mem_pool_size=2M
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M



Espero que les sirva y si tienen preguntas me escriben a, adios.
  Posted by Toby Thain on April 9, 2006
Per Jose's comment on Gentoo above - on my Gentoo system, the USE flags depend on the version of MySQL you are installing:

mysql-3.23.58-r1 : 'innodb' flag enables InnoDB support
mysql-4.0.25-r2 through mysql-5.0.19 include InnoDB support by default. After 5.1 this was again made dependent on flag 'innodb'.

If installing 4.1.3 or later you can use the 'extraengine' flag to enable ARCHIVE, CSV (4.1.4+), BLACKHOLE (4.1.11+) and FEDERATED (5.0.3+). (Also see 'berkdb', 'cluster' and 'geometry' flags.)
  Posted by Jon Evans on December 11, 2006
Another thing to watch for with Gentoo is the way it configures the max file size to 128M:

[extract from /etc/mysql/my.cnf]
# you may wish to change this size to be more suitable for your system
# the max is there to avoid run-away growth on your machine
innodb_data_file_path = ibdata1:10M:autoextend:max:128M

If you start getting "Table 'foo' is full" errors, check your config file.
  Posted by David Toth on December 11, 2006
This is kind of a got ya and either isn't in the documentation or is as clear as mud. When you want to change the location of the innodb data files you have to set both the innodb_data_home_dir and innodb_data_file_path variables. So in my case, I was trying to move the data file location to /History/mysql. So I tried adding to my.cnf


Upon starting mysql, I got an error unable to open database. When I looked in syslog, the innodb_data_file_path looked like this

I added to the my.cnf


and modified innodb_data_file_path to


and mysql started up using the correct file paths. Remember to change the permissions on the /History/mysql directory to chown mysql:mysql mysql. All is well and I was also able to change the log file locations as well.
  Posted by Charly Molter on July 18, 2013
From this part of the doc:
# Set key_buffer to 5 - 50% of your RAM depending on how much
# you use MyISAM tables, but keep key_buffer_size + InnoDB
# buffer pool size < 80% of your RAM
It is not really clear but 'key_buffer_size' is a MyISAM parameter so the sentence above means that the more you use MyISAM table the higher key_buffer_size should be.

It wasn't really clear to me at the beginning.

Sign Up Login You must be logged in to post a comment.