( InnoDB configuration

Info Catalog ( InnoDB in MySQL 3 ( InnoDB ( InnoDB start
 15.4 `InnoDB' Configuration
 To enable `InnoDB' tables in MySQL 3.23, see  InnoDB in MySQL
 From MySQL 4.0 on, 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.
 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 4.0 and above
 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.  (In MySQL 4.0.0 and 4.0.1, the data file is 64MB and not
 auto-extending.)  In MySQL 3.23, `InnoDB' will not start if you provide
 no configuration options.
 * 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.
 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, a setting that explicitly creates a tablespace having the
 same characteristics as the MySQL 4.0 default is as follows:
 This setting configures a single 10MB data file named `ibdata1' that is
 auto-extending.  No location for the file is given, so the default is
 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:
 The full syntax for a data file specification includes the filename,
 its size, and several optional attributes:
 The `autoextend' attribute and those following can be used only for the
 last data file in the `innodb_data_file_path' line.  `autoextend' is
 available starting from MySQL 3.23.50 and 4.0.2.
 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.
 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 maximum file size, so be cautious on
 filesystems where the maximum file size is 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:
 `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.
 `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 slash or backslash between if needed.  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
 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 =
 *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'. The
 example assumes the use of MySQL-Max 3.23.50 or later or MySQL 4.0.2 or
 later because it uses the `autoextend' attribute.
 This example suits most users, both on Unix and Windows, who do not
 want to distribute `InnoDB' data files and log files on 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.
      # 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-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
 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' will
 then print the information about the database creation to the screen,
 so you can see what is happening.  For example, on Windows, if
 `mysqld-max' is located in `C:\mysql\bin', you can start it like this:
      C:\> C:\mysql\bin\mysqld-max --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 InnoDB init. for an example of what the
 information displayed by `InnoDB' should look like.
 *Where to specify options on Windows?* The rules for option files on
 Windows are as follows:
    * Only one of `my.cnf' or `my.ini' should be created.
    * The `my.cnf' file should be placed in the root directory of the
      `C:' drive.
    * The `my.ini' file should be placed in the `WINDIR' directory; for
      example, `C:\WINDOWS' or `C:\WINNT'. You can use the `SET' command
      at the command prompt in a console window to print the value of
           C:\> SET WINDIR
    * 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.
 *Where to specify options on Unix?* On Unix, `mysqld' reads options
 from the following files, if they exist, in the following order:
    * `/etc/my.cnf'
      Global options.
    * `DATADIR/my.cnf'
      Server-specific options.
    * `defaults-extra-file'
      The file specified with the `--defaults-extra-file' option.
    * `~/.my.cnf'
      User-specific options.
 DATADIR represents the MySQL data directory that 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 want to make sure that `mysqld' reads options only from a
 specific file, you can use the `--defaults-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'.
      # 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
      set-variable = innodb_buffer_pool_size=1G
      set-variable = innodb_additional_mem_pool_size=20M
      innodb_log_group_home_dir = /dr3/iblogs
      # innodb_log_arch_dir must be the same as innodb_log_group_home_dir
      # (starting from 4.0.6, you can omit it)
      innodb_log_arch_dir = /dr3/iblogs
      set-variable = innodb_log_files_in_group=2
      # Set the log file size to about 25% of the buffer pool size
      set-variable = innodb_log_file_size=250M
      set-variable = innodb_log_buffer_size=8M
      set-variable = innodb_lock_wait_timeout=50
      # Uncomment the next lines if you want to use them
      #set-variable = innodb_thread_concurrency=5
 Note that the example places the two data files on different disks.
 `InnoDB' will fill the tablespace beginning with the first data file.
 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 `InnoDB' data files,
 which may speed up I/O.  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 will crash 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 will use 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.
 Starting from MySQL 4.1, 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' start InnoDB start.
 *How to tune other `mysqld' server parameters?* The following values
 are typical and suit most users:
      set-variable = max_connections=200
      set-variable = read_buffer_size=1M
      set-variable = 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
      set-variable = key_buffer_size=...
Info Catalog ( InnoDB in MySQL 3 ( InnoDB ( InnoDB start
automatically generated byinfo2html