The first decisions to make about InnoDB configuration involve how to lay out InnoDB data files, and how much memory to allocate for the InnoDB storage engine. You record these choices either by recording them in a configuration file that MySQL reads at startup, or by specifying them as command-line options in a startup script.
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 10MB data file named ibdata1
and two 5MB log files named ib_logfile0 and
ib_logfile1 in the MySQL data directory. To
get good performance, explicitly provide InnoDB
parameters as discussed in the following examples. Naturally, edit
the settings to suit your hardware and requirements.
The examples shown here are representative. See
Section 13.6.4, “InnoDB Startup Options and System Variables” for additional information
about InnoDB-related configuration parameters.
Considerations for Storage Devices
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 13.6.3.1, “Using Raw Devices for the Shared Tablespace”.
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, perform some “pull-the-plug” tests before
using anything in production. On Mac 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.
If reliability is a consideration for your data, do not
configure InnoDB to use data files or log
files on NFS volumes. Potential problems vary according to OS
and version of NFS, and include such issues as lack of
protection from conflicting writes, and limitations on maximum
file sizes.
Specifying the Location and Size for InnoDB Tablespace Files
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:
innodb_data_file_path=datafile_spec1[;datafile_spec2]...
For example, the following setting explicitly creates a tablespace having the same characteristics as the default:
[mysqld] innodb_data_file_path=ibdata1:10M:autoextend
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:
[mysqld] innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
The full syntax for a data file specification includes the file name, its size, and several optional attributes:
file_name:file_size[:autoextend[:max:max_file_size]]
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 13.6.6, “Adding, Removing, or Resizing InnoDB Data and Log
Files”.
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.
Use the max attribute only in cases where
constraining disk usage is of critical importance, because
exceeding the maximum size causes a fatal error, possibly
including a crash. The following configuration permits
ibdata1 to grow up to a limit of 500MB:
[mysqld] innodb_data_file_path=ibdata1:10M:autoextend:max:500M
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:
[mysqld] innodb_data_home_dir = /ibdata innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
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 mentioned 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:
[mysqld] innodb_data_home_dir = innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend
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.
[mysqld] # 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 innodb_buffer_pool_size=256M innodb_additional_mem_pool_size=20M # # Set the log file size to about 25% of the buffer pool size innodb_log_file_size=64M innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1
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 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.5\bin, you can
start it like this:
C:\> "C:\Program Files\MySQL\MySQL Server 5.5\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 13.6.3.2, “Creating the InnoDB Tablespace”.
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.3.3, “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
The Location of the my.ini File.
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:
C:\> SET WINDIR
windir=C:\WINDOWS
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.
[mysqld] # 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 = /db/ibdata1:2000M;/dr2/db/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_buffer_pool_size=1G innodb_additional_mem_pool_size=20M innodb_log_group_home_dir = /dr3/iblogs # # Set the log file size to about 25% of the buffer pool size innodb_log_file_size=250M innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1 innodb_lock_wait_timeout=50 # # Uncomment the next line if you want to use it #innodb_thread_concurrency=5
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:
innodb_buffer_pool_size + 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.
Tuning other mysqld server parameters. The following values are typical and suit most users:
[mysqld]
skip-external-locking
max_connections=200
read_buffer_size=1M
sort_buffer_size=1M
#
# 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
key_buffer_size=value
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 7.11.4.2, “Enabling Large Page Support”.
Turning Off InnoDB
Oracle recommends InnoDB as the preferred storage engine for typical database applications, from single-user wikis and blogs running on a local system, to high-end applications pushing the limits of performance. In MySQL 5.5, InnoDB is is the default storage engine for new tables.
If you do not want to use InnoDB tables, start
the server with the
--innodb=OFF or
--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.