--myisam-recover=mode

Set the mode for automatic recovery of crashed MyISAM tables.

--delay-key-write=ALL

Don't flush key buffers between writes for any MyISAM table.

Note: If you do this, you should not use MyISAM tables from another program (such as from another MySQL server or with myisamchk) when the table is in use. Doing so leads to index corruption.

Using --external-locking does not help for tables that use --delay-key-write.

bulk_insert_buffer_size

The size of the tree cache used in bulk insert optimization. Note: This is a limit per thread!

myisam_max_extra_sort_file_size

Used to help MySQL to decide when to use the slow but safe key cache index creation method. Note: This parameter was given in bytes before MySQL 5.0.6, when it was removed.

myisam_max_sort_file_size

Don't use the fast sort index method to create an index if the temporary file would become larger than this. Note: This parameter is given in bytes.

myisam_sort_buffer_size

Set the size of the buffer used when recovering tables.

The table is checked for errors.

If the server finds an error, it tries to do a fast table repair (with sorting and without re-creating the data file).

If the repair fails because of an error in the data file (for example, a duplicate-key error), the server tries again, this time re-creating the data file.

If the repair still fails, the server tries once more with the old repair option method (write row by row without sorting). This method should be able to repair any type of error and has low disk space requirements.

For the InnoDB storage engine, there's a dedicated forum available on http://forums.mysql.com/list.php?22.

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 WINDIR:

C:\> SET WINDIR
windir=C:\WINNT

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.

If you installed MySQL using the installation and configuration wizards, the my.ini file is located in your MySQL installation directory. See Section 2.3.5.14, “The Location of the my.ini File”.

/etc/my.cnf

Global options.

$MYSQL_HOME/my.cnf

Server-specific options.

defaults-extra-file

The file specified with the --defaults-extra-file option.

~/.my.cnf

User-specific options.

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 need to allocate here. If InnoDB runs out of memory in this pool, it starts to allocate memory from the operating system, and writes 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 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. (Relevant only in 32-bit Windows.) If your 32-bit Windows operating system supports more than 4GB memory, using 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.

To take advantage of AWE memory, you will need to recompile MySQL yourself. The current project settings needed for doing this can be found in the innobase/os/os0proj.c source file.

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_commit_concurrency

The number of threads that can commit at the same time. A value of 0 disables concurrency control. This option was added in MySQL 5.0.12.

innodb_concurrency_tickets

The number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable. A thread is placed in a queue when it tries to enter InnoDB if the number of threads has already reached the concurrency limit. When a thread is allowed to enter InnoDB, it is given a number of “free tickets” equal to the value of innodb_concurrency_tickets, and the thread can enter and leave InnoDB freely until it has used up its tickets. After that point, the thread again becomes subject to the concurrency check (and possible queuing) the next time it tries to enter InnoDB. 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 is to create a single 10MB auto-extending data file named ibdata1. You can set the file size to more than 4GB on those operating systems supporting big files. You can also use raw disk partitions as data files. See Section 14.2.14.2, “Using Raw Devices for the Tablespace”.

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

If you set this to 0, 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. The default value is 1. If you set it to 2 (available starting from MySQL 5.0.5, except on Netware), InnoDB will just flush its logs and then shut down cold, as if MySQL had crashed; no committed transaction will be lost, but a crash recovery will be done at next startup.

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.

innodb_file_per_table

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. See Section 14.2.6.6, “Using Per-Table Tablespaces”.

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. However, InnoDB's crash recovery is not affected and thus crash recovery does work regardless of the value. Note that many operating systems and some disk hardware fool the flush-to-disk operation. They may tell 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.

innodb_flush_method

This option is relevant only on Unix systems. If set to fdatasync (the default), 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), InnoDB uses O_DIRECT to open the data files, and uses fsync() to flush both the data and log files. Note that InnoDB uses fsync() instead of fdatasync(), and it does not use O_DSYNC by default because there have been problems with this on many varieties of Unix.

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 Section 14.2.8.1, “Forcing Recovery”. As a safety measure, InnoDB prevents a user from modifying data when this option is greater than 0.

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. InnoDB notices locks set using the LOCK TABLES statement. The default is 50 seconds.

For the greatest possible durability and consistency in a replication setup you should use innodb_flush_log_at_trx_commit=1, sync-binlog=1, and, before MySQL 5.0.3, innodb_safe_binlog in your master my.cnf file. (innodb_safe_binlog is not needed from 5.0.3 on.)

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 performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on any index records it encounters. Thus, the row-level locks are actually index record locks. The locks that InnoDB sets on index records also affect the “gap” preceeding 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 order of the index. 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 intention 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 scans that index starting from the first record where id is greater than 100. If the locks set on the index records do not lock out inserts made in the gaps, a new row is meanwhile inserted into the table. If you execute the same SELECT within the same transaction, you 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; however, conflict serializability is still guaranteed. Therefore, if this option is used InnoDB guarantees at most isolation level READ COMMITTED.

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 operations such as UPDATE to overtake like operations (such as another UPDATE) even in the case when they affect different rows. Consider the 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, following the first one, another 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 row (2,3), and query two while scanning rows also tries to take an exclusive lock to the same row (2,3), which it cannot have. This is because query two first takes an exclusive lock on a row and then determines whether this row belongs to the result set, and if not then releases the unnecessary lock, when the 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. If used, the value of this parameter should be set the same as innodb_log_group_home_dir. However, it is not required.

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 saves 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 is 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 write pages from the buffer pool so that the percentage of dirty (not yet written) pages will not exceed this value. 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 (see Section 14.2.12, “Implementation of Multi-Versioning”) are lagging. The default value of this parameter is zero, meaning that there are no delays. This option can be changed at runtime as a global system variable.

The InnoDB transaction system maintains a list of transactions that have delete-marked index records by UPDATE or DELETE operations. Let the length of this list be purge_lag. When purge_lag exceeds innodb_max_purge_lag, each INSERT, UPDATE and DELETE operation is delayed by ((purge_lag/innodb_max_purge_lag)*10)-5 milliseconds. The delay is computed in the beginning of a purge batch, every ten seconds. The operations are not delayed if purge cannot run because of an old consistent read view that could see the rows to be purged.

A typical setting for a problematic workload might be 1 million, assuming that our transactions are small, only 100 bytes in size, and we can allow 100 MB of unpurged rows in our tables.

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.

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_safe_binlog

Adds consistency guarantees between the content of InnoDB tables and the binary log. See Section 5.11.3, “The Binary Log”. This variable was removed in MySQL 5.0.3, having been made obsolete by the introduction of XA transaction support.

innodb_status_file

This option causes InnoDB to create a file <datadir>/innodb_status.<pid> for periodical SHOW ENGINE INNODB STATUS output.

innodb_support_xa

When set to ON or 1 (the default), this variable enables InnoDB support for two-phase commit in XA transactions. Enabling innodb_support_xa causes an extra disk flush for transaction preparation. If you don't care about using XA, you can disable this variable by setting it to OFF or 0 to reduce the number of disk flushes and get better InnoDB performance. This variable was added in MySQL 5.0.3.

innodb_sync_spin_loops

The number of times a thread waits for an InnoDB mutex to be freed before the thread is suspended. This option was added in MySQL 5.0.3.

innodb_table_locks

InnoDB honors LOCK TABLES; MySQL does not return from LOCK TABLE .. WRITE until all other threads have released all their locks to the table. The default value is 1, which means that LOCK TABLES causes InnoDB to lock a table internally. In applications using AUTOCOMMIT=1, InnoDB's internal table locks can cause deadlocks. You can set innodb_table_locks=0 in my.cnf (or my.ini on Windows) 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. Before MySQL 5.0.8, the default value is 8. If you have performance issues, 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 make better use of your computer's resources. A recommended value is the sum of the number of processors and disks your system has. A value of 500 or greater disables concurrency checking. Starting with MySQL 5.0.8, the default value is 20, and concurrency checking will be disabled if the setting is greater than or equal to 20.