DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(mysql.info.gz) InnoDB start

Info Catalog (mysql.info.gz) InnoDB configuration (mysql.info.gz) InnoDB (mysql.info.gz) InnoDB init
 
 15.5 `InnoDB' Startup Options
 =============================
 
 This section describes the `InnoDB'-related server options.  In MySQL
 4.0 and up, all of them can be specified in `--OPT_NAME=VALUE' form on
 the command line or in option files.  Before MySQL 4.0, numeric options
 should be specified using `--set-variable=OPT_NAME=VALUE' or `-O
 OPT_NAME=VALUE' syntax.
 
 `innodb_additional_mem_pool_size'
      The size of a memory pool `InnoDB' uses to store data dictionary
      information and other internal data structures.  The more tables
      you have in your application, the more memory you will need to
      allocate here. If `InnoDB' runs out of memory in this pool, it
      will start to allocate memory from the operating system, and write
      warning messages to the MySQL error log. The default value is 1MB.
 
 `innodb_autoextend_increment'
      The increment size (in megabytes) for extending the size of an
      autoextending tablespace when it becomes full.  The default value
      is 8.  This option is available starting from MySQL 4.0.24 and
      4.1.5.  As of MySQL 4.0.24 and 4.1.6, it can be changed at runtime
      as a global system variable.
 
 `innodb_buffer_pool_awe_mem_mb'
      The size of the buffer pool (in MB), if it is placed in the AWE
      memory of 32-bit Windows. Available from MySQL 4.1.0 and relevant
      only in 32-bit Windows. If your 32-bit Windows operating system
      supports more than 4GB memory, so-called "Address Windowing
      Extensions," you can allocate the `InnoDB' buffer pool into the
      AWE physical memory using this parameter. The maximum possible
      value for this is 64000. If this parameter is specified,
      `innodb_buffer_pool_size' is the window in the 32-bit address
      space of `mysqld' where `InnoDB' maps that AWE memory. A good
      value for `innodb_buffer_pool_size' is 500MB.
 
 `innodb_buffer_pool_size'
      The size of the memory buffer `InnoDB' uses to cache data and
      indexes of its tables.  The larger you set this value, the less
      disk I/O is needed to access data in tables. On a dedicated
      database server, you may set this to up to 80% of the machine
      physical memory size. However, do not set it too large because
      competition for the physical memory might cause paging in the
      operating system.
 
 `innodb_checksums'
      `InnoDB' uses checksum validation on all pages read from the disk
      to ensure extra fault tolerance against broken hardware or data
      files. However, under some rare circumstances (such as when
      running benchmarks) this "extra safety" feature is unneeded.  In
      such cases, this option (which is enabled by default) can be
      turned off with `--skip-innodb-checksums'.  This option was added
      in MySQL 5.0.3.
 
 `innodb_data_file_path'
      The paths to individual data files and their sizes. The full
      directory path to each data file is acquired by concatenating
      `innodb_data_home_dir' to each path specified here. The file sizes
      are specified in megabytes or gigabytes (1024MB) by appending `M'
      or `G' to the size value.  The sum of the sizes of the files must
      be at least 10MB. On some operating systems, files must be less
      than 2GB. If you do not specify `innodb_data_file_path', the
      default behavior starting from 4.0 is to create a single 10MB
      auto-extending data file named `ibdata1'. Starting from 3.23.44,
      you can set the file size bigger than 4GB on those operating
      systems that support big files. You can also use raw disk
      partitions as data files.  InnoDB Raw Devices.
 
 `innodb_data_home_dir'
      The common part of the directory path for all `InnoDB' data files.
      If you do not set this value, the default is the MySQL data
      directory. You can specify this also as an empty string, in which
      case you can use absolute file paths in `innodb_data_file_path'.
 
 `innodb_doublewrite'
      By default, `InnoDB' stores all data twice, first to the
      doublewrite buffer, and then to the actual data files.  This
      option can be used to disable this functionality.  Like
      `innodb_checksums', this option is enabled by default; it can be
      turned off with `--skip-innodb-doublewrite' for benchmarks or
      cases when top performance is needed rather than concern for data
      integrity or possible failures.  This option was added in MySQL
      5.0.3.
 
 `innodb_fast_shutdown'
      By default, `InnoDB' does a full purge and an insert buffer merge
      before a shutdown. These operations can take minutes, or even
      hours in extreme cases.  If you set this parameter to 1, `InnoDB'
      skips these operations at shutdown. This option is available
      starting from MySQL 3.23.44 and 4.0.1. Its default value is 1
      starting from 3.23.50.
 
 `innodb_file_io_threads'
      The number of file I/O threads in `InnoDB'. Normally this should be
      left at the default value of 4, but disk I/O on Windows may
      benefit from a larger number. On Unix, increasing the number has
      no effect; `InnoDB' always uses the default value.  This option is
      available as of MySQL 3.23.37.
 
 `innodb_file_per_table'
      * CRITICAL BUG in 4.1 if you specify `innodb_file_per_table'
      in `my.cnf'! If you shut down `mysqld', then records may disappear
      from the secondary indexes of a table.  See (Bug #7496) for more
      information and workarounds. This is fixed in 4.1.9, but another
      bug (Bug #8021) bit the Windows version in 4.1.9, and in the
      Windows version of 4.1.9 you must put the line
      `innodb_flush_method=unbuffered' to your `my.cnf' or `my.ini' to
      get `mysqld' to work.
 
      This option causes `InnoDB' to create each new table using its own
      `.ibd' file for storing data and indexes, rather than in the
      shared tablespace.  Multiple tablespaces.  This option is
      available as of MySQL 4.1.1.
 
 `innodb_flush_log_at_trx_commit'
      When `innodb_flush_log_at_trx_commit' is set to 0, once per second
      the log buffer is written out to the log file, and the flush to
      disk operation is performed on the log file, but nothing is done
      at a transaction commit. When this value is 1 (the default), at
      each transaction commit the log buffer is written out to the log
      file, and the flush to disk operation is performed on the log
      file. When set to 2, at each commit the log buffer is written out
      to the file, but the flush to disk operation is not performed on
      it. However, the flushing on the log file takes place once per
      second also in the case of 2.
 
      We must note that the once-per-second flushing is not 100%
      guaranteed to happen every second, due to process scheduling
      issues.
 
      You can achieve better performance by setting the value different
      from 1, but then you can lose at most one second worth of
      transactions in a crash. If you set the value to 0, then any
      `mysqld' process crash can erase the last second of transactions.
      If you set the value to 2, then only an operating system crash or
      a power outage can erase the last second of transactions.
 
      Note that many operating systems and some disk hardware fool in the
      flush-to-disk operation. They may tell to `mysqld' that the flush
      has taken place, though it has not. Then the durability of
      transactions is not guaranteed even with the setting 1, and in the
      worst case a power outage can even corrupt the InnoDB database.
      Using a battery-backed disk cache in the SCSI disk controller or
      in the disk itself speeds up file flushes, and makes the operation
      safer. You can also try using the Unix command `hdparm' to disable
      the caching of disk writes in hardware caches, or use some other
      command specific to the hardware vendor.
 
      The default value of this option is 1 (prior to MySQL 4.0.13, the
      default is 0).
 
 `innodb_flush_method'
      This option is relevant only on Unix systems. If set to
      `fdatasync', `InnoDB' uses `fsync()' to flush both the data and log
      files. If set to `O_DSYNC', `InnoDB' uses `O_SYNC' to open and
      flush the log files, but uses `fsync()' to flush the data files.
      If `O_DIRECT' is specified (available on some GNU/Linux versions
      starting from MySQL 4.0.14), `InnoDB' uses `O_DIRECT' to open the
      data files, and uses `fsync()' to flush both the data and log
      files. Note that `InnoDB' does not use `fdatasync' or `O_DSYNC' by
      default because there have been problems with them on many Unix
      flavors.  This option is available as of MySQL 3.23.40.
 
 `innodb_force_recovery'
      Warning: This option should be defined only in an emergency
      situation when you want to dump your tables from a corrupt
      database! Possible values are from 1 to 6. The meanings of these
      values are described in  Forcing recovery. As a safety
      measure, `InnoDB' prevents a user from modifying data when this
      option is greater than 0. This option is available starting from
      MySQL 3.23.44.
 
 `innodb_lock_wait_timeout'
      The timeout in seconds an `InnoDB' transaction may wait for a lock
      before being rolled back.  `InnoDB' automatically detects
      transaction deadlocks in its own lock table and rolls back the
      transaction. Beginning with MySQL 4.0.20 and 4.1.2, InnoDB notices
      locks set using the `LOCK TABLES' statement. Before that, if you
      use the `LOCK TABLES' statement, or other transaction-safe storage
      engines than `InnoDB' in the same transaction, a deadlock may arise
      that `InnoDB' cannot notice. In cases like this, the timeout is
      useful to resolve the situation. The default is 50 seconds.
 
 `innodb_locks_unsafe_for_binlog'
      This option turns off next-key locking in `InnoDB' searches and
      index scans. Default value for this option is false.
 
      Normally `InnoDB' uses an algorithm called "next-key locking."
      `InnoDB' does the row-level locking in such a way that when it
      searches or scans an index of a table, it sets shared or exclusive
      locks on the index records it encounters. Thus the row-level locks
      are actually index record locks. The locks `InnoDB' sets on index
      records also affect the "gap" before that index record.  If a user
      has a shared or exclusive lock on record R in an index, another
      user cannot insert a new index record immediately before R in the
      index order. This option causes `InnoDB' not to use next-key
      locking in searches or index scans. Next-key locking is still used
      to ensure foreign key constraints and duplicate key checking.
      Note that using this option may cause phantom problems: Suppose
      that you want to read and lock all children from the `child' table
      with an identifier value larger than 100, with the intent of
      updating some column in the selected rows later:
 
           SELECT * FROM child WHERE id > 100 FOR UPDATE;
 
      Suppose that there is an index on the `id' column. The query will
      scan that index starting from the first record where id is bigger
      than 100.  If the locks set on the index records do not lock out
      inserts made in the gaps, a new row will meanwhile be inserted to
      the table.  If you execute the same `SELECT' within the same
      transaction, you will see a new row in the result set returned by
      the query.  This also means, that if new items are added to the
      database, InnoDB does not guarantee serializability instead
      conflict serializability is still guaranteed. Therefore, if this
      option is used InnoDB guarantees at most isolation level `READ
      COMMITTED'.  This option is available as of MySQL 4.1.4.
 
      Starting from MySQL 5.0.2 this option is even more unsafe. InnoDB
      in an `UPDATE' or a `DELETE' only locks rows that it updates or
      deletes. This greatly reduces the probability of deadlocks but they
      can happen. Note that this option still does not allow e.g.
      `UPDATE' to overtake other `UPDATE' even the case when both
      updates different rows. Consider following example:
 
           CREATE TABLE A(A INT NOT NULL, B INT);
           INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
           COMMIT;
 
      If one connection executes a query:
 
           SET AUTOCOMMIT = 0;
           UPDATE A SET B = 5 WHERE B = 3;
 
      and the other connection executes after the first one a query:
 
           SET AUTOCOMMIT = 0;
           UPDATE A SET B = 4 WHERE B = 2;
 
      Then query two has to wait for a commit or rollback of query one,
      because query one has an exclusive lock to a row (2,3), and query
      two while scanning rows also tries to take an exclusive lock to
      the row (2,3) which it cannot have. This is because query two first
      takes an exclusive lock to a row and then checks does this row
      belong to the result set and if not then releases the unnecessary
      lock when option `innodb_locks_unsafe_for_binlog' is used.
 
      Therefore, query one is executed as follows:
 
           x-lock(1,2)
           unlock(1,2)
           x-lock(2,3)
           update(2,3) to (2,5)
           x-lock(3,2)
           unlock(3,2)
           x-lock(4,3)
           update(4,3) to (4,5)
           x-lock(5,2)
           unlock(5,2)
 
      and then query two is executed as follows:
 
           x-lock(1,2)
           update(1,2) to (1,4)
           x-lock(2,3) - wait for query one to commit or rollback
 
 `innodb_log_arch_dir'
      The directory where fully written log files would be archived if
      we used log archiving.  The value of this parameter should
      currently be set the same as `innodb_log_group_home_dir'. Starting
      from MySQL 4.0.6, you may omit this option.
 
 `innodb_log_archive'
      This value should currently be set to 0.  Because recovery from a
      backup is done by MySQL using its own log files, there is
      currently no need to archive `InnoDB' log files. The default for
      this option is 0.
 
 `innodb_log_buffer_size'
      The size of the buffer that `InnoDB' uses to write to the log files
      on disk.  Sensible values range from 1MB to 8MB.  The default is
      1MB. A large log buffer allows large transactions to run without a
      need to write the log to disk before the transactions commit.
      Thus, if you have big transactions, making the log buffer larger
      will save disk I/O.
 
 `innodb_log_file_size'
      The size of each log file in a log group. The combined size of log
      files must be less than 4GB on 32-bit computers. The default is
      5MB.  Sensible values range from 1MB to 1/N-th of the size of the
      buffer pool, below, where N is the number of log files in the
      group. The larger the value, the less checkpoint flush activity is
      needed in the buffer pool, saving disk I/O. But larger log files
      also mean that recovery will be slower in case of a crash.
 
 `innodb_log_files_in_group'
      The number of log files in the log group. `InnoDB' writes to the
      files in a circular fashion. The default is 2 (recommended).
 
 `innodb_log_group_home_dir'
      The directory path to the `InnoDB' log files.  It must have the
      same value as `innodb_log_arch_dir'.  If you do not specify any
      `InnoDB' log parameters, the default is to create two 5MB files
      names `ib_logfile0' and `ib_logfile1' in the MySQL data directory.
 
 `innodb_max_dirty_pages_pct'
      This is an integer in the range from 0 to 100. The default is 90.
      The main thread in `InnoDB' tries to flush pages from the buffer
      pool so that at most this many percent of pages may not yet
      flushed been flushed at any particular time. Available starting
      from 4.0.13 and 4.1.1. If you have the `SUPER' privilege, this
      percentage can be changed while the server is running:
 
           SET GLOBAL innodb_max_dirty_pages_pct = VALUE;
 
 `innodb_max_purge_lag'
      This option controls how to delay `INSERT', `UPDATE' and `DELETE'
      operations when the purge operations are lagging.  The default
      value of this parameter is zero, meaning that there will not be
      any delays. When the value is greater than zero, `InnoDB' may
      delay new row operations, as described in  InnoDB
      Multi-Versioning.  This option can be changed at runtime as a
      global system variable.  `innodb_max_purge_lag' is available as of
      MySQL 4.0.22 and 4.1.6.
 
 `innodb_mirrored_log_groups'
      The number of identical copies of log groups we keep for the
      database. Currently this should be set to 1.
 
 `innodb_open_files'
      This option is relevant only if you use multiple tablespaces in
      `InnoDB'. It specifies the maximum number of `.ibd' files that
      `InnoDB' can keep open at one time. The minimum value is 10. The
      default is 300.  This option is available as of MySQL 4.1.1.
 
      The file descriptors used for `.ibd' files are for `InnoDB' only.
      They are independent of those specified by the `--open-files-limit'
      server option, and do not affect the operation of the table cache.
 
 `innodb_table_locks'
      Starting from MySQL 4.0.20, and 4.1.2, `InnoDB' honors `LOCK
      TABLES'; MySQL will not return from `LOCK TABLE .. WRITE' until
      all other threads have released all their locks to the table.  In
      MySQL 4.0.19 and before, InnoDB ignored table locks, which allowed
      one to more easily simulate transactions with a combination of
      MyISAM and InnoDB tables.  The default value is 1, which means that
      `LOCK TABLES' causes also InnoDB internally to take a table lock.
      In applications using `AUTOCOMMIT=1', InnoDB's internal table
      locks can cause deadlocks. You can set `innodb_table_locks=0' in
      `my.cnf' to remove that problem.
 
 `innodb_thread_concurrency'
      `InnoDB' tries to keep the number of operating system threads
      concurrently inside `InnoDB' less than or equal to the limit given
      by this parameter. The default value is 8. If you have low
      performance and `SHOW INNODB STATUS' reveals many threads waiting
      for semaphores, you may have thread thrashing and should try
      setting this parameter lower or higher.  If you have a computer
      with many processors and disks, you can try setting the value
      higher to better utilize the resources of you computer. A
      recommended value is the sum of the number of processors and disks
      your system has.  A value of 500 or greater disables the
      concurrency checking.  This option is available starting from
      MySQL 3.23.44 and 4.0.1.
 
 `innodb_status_file'
      This option causes `InnoDB' to create a file
      `<DATADIR>/innodb_status.<PID>' for periodical `SHOW INNODB
      STATUS' output.  This option is available as of MySQL 4.0.21.
 
 
Info Catalog (mysql.info.gz) InnoDB configuration (mysql.info.gz) InnoDB (mysql.info.gz) InnoDB init
automatically generated byinfo2html