innodb related options

innodb_fast_shutdown

 

Default value: 1

 

Valid value:

 

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.

 

1: InnoDB skips these operations at shutdown.

 

2: InnoDB will just flush its logs and then shut down cold, as if MySQL had crashed; no committed transaction will be lost, but crash recovery will be done at the next startup.

 

 

innodb_adptive_hash_index

 

Default value: ON

 

ON:   Use adptive hash index.

 

OFF:  Disable adptive hash index.

 

If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash indexes. InnoDB has a mechanism that monitors index searches made to the indexes defined for a table. If InnoDB notices that queries could benefit from building a hash index, it does so automatically.

 

 

innodb insert buffer:

 

Innodb uses insert buffer to “cheat” and not to update index leaf pages when at once but “buffer” such updates so several updates to the same page can be performed with single sweep.

 

Insert buffer only can work for non-unique keys because until the merge is performed it is impossible to check if the value is unique.

 

Innodb unfortunately offers no control for insert buffer while it surely would be benefiting for different workloads and hardware configuration. For example there is very good question if insert buffer really makes sense for SSD because saving random IO is not so much needed for these devices.

 

 

innodb_flush_log_at_commit:

合法值：0，1，2

默认值：1

 

0： 如果设置为0，则表示innodb会以每秒一次的频率将log_buffer中的数据写回到log-file，与此同时会执行一次flush回disk的动作。

       the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit.

1： 如果设置为1，则表示innodb在每次commit ttransaction时，都会将log buffer中的数据写回到log file，与此同时执行一次flush的操作。

       the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file.

2： 如果设置为2，则表示innodb在每次commit ttransaction时，都会将log buffer中的数据写回到log file，但是并不主动的去执行flush操作。

       the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it.

 

为0时，其performance可以达到最高。但是此时当engine或者系统出现crash的时候，innodb很有可能丢失最后一秒的transaction，破坏其ACID的特性。

为2时，可以保证当engine发生crash，但是系统仍然工作时的ACID特性。但是当系统crash时，也很有可能丢失最后一秒的transaction。

为1时，其performance是最低的，但是可保证在engine或者系统crash时，仍能满足ACID特性。

 

Caution

Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even 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.

 

下面是一张转自Percona blog的一张图片，非常清晰的讲述了innodb存储引擎在Memory和disk的使用管理。

 

 

 

innodb locks:

mutex, spinlocks? Need more research....