(mysql.info) innodb-configuration

(mysql.info) innodb-contact-information

 
 14.2.3 `InnoDB' Configuration
 -----------------------------

 
* multiple-tablespaces         Using Per-Table Tablespaces
* innodb-raw-devices           Using Raw Devices for the Shared Tablespace
 
 The `InnoDB' storage engine is enabled by default. If you don't want to
 use `InnoDB' tables, you can add the `skip-innodb' option to your MySQL
 option file.
 
 * `InnoDB' provides MySQL with a transaction-safe (`ACID'
 compliant) storage engine that has commit, rollback, and crash recovery
 capabilities. *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 system call that
 should wait until all unwritten data for a file has been flushed --
 `fsync()' -- 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 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 ATAPI hard disks, 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.*
 
 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, you should explicitly provide `InnoDB' parameters
 as discussed in the following examples. Naturally, you should edit the
 settings to suit your hardware and requirements.
 
 innodb-parameters:: 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:
 
      innodb_data_file_path=DATAFILE_SPEC1[;DATAFILE_SPEC2]...
 
 For example, a setting that explicitly creates a tablespace having the
 same characteristics as the default is as follows:
 
      [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 `M' or `G' suffix letters to indicate units
 of 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 filename,
 its size, and several optional attributes:
 
      FILE_NAME:FILE_SIZE[:autoextend[:max:MAX_FILE_SIZE]]
 
 The `autoextend' attribute and those following 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. It can be modified by
 changing the `innodb_autoextend_increment' system variable.
 
 If the disk becomes full, you might want to add another data file on
 another disk. Instructions for reconfiguring an existing tablespace are
 given in  adding-and-removing.
 
 `InnoDB' is not aware of the filesystem maximum file size, so be
 cautious on filesystems 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. The following configuration allows `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.
 
 `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 pathname 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
 
 *A simple `my.cnf' example.* Suppose that you have a computer with 128MB
 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.  Also, the small archived
 `InnoDB' log file `ib_arch_log_0000000000' that `InnoDB' creates
 automatically ends up in the 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=70M
      innodb_additional_mem_pool_size=10M
      #
      # Set the log file size to about 25% of the buffer pool size
      innodb_log_file_size=20M
      innodb_log_buffer_size=8M
      #
      innodb_flush_log_at_trx_commit=1
 
 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.
 
 Note that data files must be less than 2GB in some filesystems.  The
 combined size of the log files must be less than 4GB. The combined size
 of data files must be at least 10MB.
 
 When you create an `InnoDB' 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.
 
 See  innodb-init, for an example of what the information
 displayed by `InnoDB' should look like.
 
 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  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 
 mysql-config-wizard-file-location.
 
 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' or `C:\WINNT'). 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
 
 If you want to make sure that `mysqld' reads options only from a
 specific file, you can 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
 
 *An advanced `my.cnf' example.* 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 = /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_buffer_pool_size=1G
      innodb_additional_mem_pool_size=20M
      innodb_log_group_home_dir = /dr3/iblogs
      #
      innodb_log_files_in_group = 2
      #
      # 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 lines if you want to use them
      #innodb_thread_concurrency=5
 
 In some cases, database performance improves the 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 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  innodb-raw-devices.
 
 *Warning:* On 32-bit GNU/Linux x86, you must be careful not to set
 memory usage too high.  `glibc' may allow 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 AB
 binaries) 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  innodb-parameters.
 
 *How to tune 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

Info Catalog

(mysql.info) innodb-contact-information

(mysql.info) innodb

(mysql.info) innodb-parameters

automatically generated byinfo2html

(mysql.info) innodb-configuration

Menu