mysql配置my.cnf包含innodb配置详解

文章来源:http://blog.csdn.net/zzq900503/article/details/17005955

 

innodb相关设置变量

| innodb_adaptive_hash_index | ON |是否开启适应性hash索引
| innodb_additional_mem_pool_size | 1048576 |InnoDB用来存储数据目录信息&其它内部数据结构的内存池的大小
| innodb_autoextend_increment | 8 |当自动扩展表空间被填满之时,为扩展而增加的尺寸(MB为单位)
| innodb_autoinc_lock_mode | 1 |The locking mode to use for generating auto-increment values,The allowable values are 0, 1, or 2, for “traditional”, “consecutive”, or “interleaved” lock mode
| innodb_buffer_pool_size | 8388608 |缓冲池大小
| innodb_checksums | ON |InnoDB在所有对磁盘的页面读取上使用校验和验证以确保额外容错防止硬件损坏或数据文件
| innodb_commit_concurrency | 0 |The number of threads that can commit at the same time
| innodb_concurrency_tickets | 500 |直到线程用尽了这个值,在再次进入innodb时才需要检查
| innodb_data_file_path | ibdata1:10M:autoextend |innodb数据文件
| innodb_data_home_dir |  |innodb文件目录
| innodb_doublewrite | ON |InnoDB存储所有数据两次,双写
| innodb_fast_shutdown | 1 |0,1,2 innodb在关闭时所要做的操作类型
| innodb_file_io_threads | 4 |InnoDB中文件I/O线程的数量
| innodb_file_per_table | OFF |InnoDB用自己的.ibd文件为存储数据和索引创建每一个新表,而不是在共享表空间中创建
| innodb_flush_log_at_trx_commit | 1 |0,1,2日志缓冲->日志文件->磁盘的刷新时机
| innodb_flush_method | |使用什么方法来flush
| innodb_force_recovery | 0 |1-6当这个选项值大于零之时,InnoDB阻止用户修改数据
| innodb_lock_wait_timeout | 50 |InnoDB事务在被回滚之前可以等待一个锁定的超时秒数
| innodb_locks_unsafe_for_binlog | OFF |索引锁+gap锁(幻读)
| innodb_log_buffer_size | 1048576 |InnoDB用来往磁盘上的日志文件写操作的缓冲区的大小
| innodb_log_file_size | 5242880 |在日志组里每个日志文件的大小
| innodb_log_files_in_group | 2 |在日志组里日志文件的数目
| innodb_log_group_home_dir | ./ |到InnoDB日志文件的目录路径
| innodb_max_dirty_pages_pct | 90 |InnoDB中的主线程试着从缓冲池写页面,使得脏页(没有被写的页面)的百分比不超过这个值
| innodb_max_purge_lag | 0 |这个选项控制在净化操作被滞后之时,如何延迟INSERT, UPDATE和DELETE操作。这个参数的默认值是零,意为无延迟InnoDB事务系统维持一个事务列表,该列表有被UPDATE或DELETE操作标志为删除的索引记录。让这个列表的长度为purge_lag。当purge_lag超过innodb_max_purge_lag之时,每个INSERT, UPDATE和DELETE操作延迟 ((purge_lag/innodb_max_purge_lag)*10)-5毫秒
| innodb_mirrored_log_groups | 1 |我们为数据库保持的日志组内同样拷贝的数量
| innodb_open_files | 300 |在InnoDB中,这个选项仅与你使用多表空间时有关。它指定InnoDB一次可以保持打开的.ibd文件的最大数目
| innodb_rollback_on_timeout | OFF |InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction
| innodb_stats_on_metadata | ON |When this variable is enabled (which is the default, as before the variable was created), InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS
| innodb_support_xa | ON |InnoDB support for two-phase commit in XA transactions is enabled
| innodb_sync_spin_loops | 20 |The number of times a thread waits for an InnoDB mutex to be freed before the thread is suspended
| innodb_table_locks | ON |InnoDB重视LOCK TABLES,直到所有其它线程已经释放他们所有对表的锁定
| innodb_thread_concurrency | 8 |InnoDB试着保持操作系统线程的数量少于或等于这个参数给出的限制
| innodb_thread_sleep_delay | 10000 |How long InnoDB threads sleep before joining the InnoDB queue, in microseconds
| innodb_use_legacy_cardinality_algorithm | ON |是否可使用原始的random cardinality算法

用上面 的命令 查看 有哪些相关配置。

一、innodb_autoinc_lock_mode = 0 (“traditional” lock mode)

这种方式就和mysql5.1.22以前一样,为了向后兼容而保留了这种模式,如同前面介绍的一样,这种方式的特点就是“表级锁定”,并发性较差

二、innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)

这种方式是新版本中的默认方式,推荐使用,并发性相对较高,特点是“consecutive”,即保证同一条insert语句中新插入的auto_increment id都是连续的。

这种模式下:

“Simple inserts”:直接通过分析语句,获得要插入的数量,然后一次性分配足够的auto_increment id,只会将整个分配的过程锁住。

“Bulk inserts”:因为不能确定插入的数量,因此使用和以前的模式相同的表级锁定。

“Mixed-mode inserts”:直接分析语句,获得最坏情况下需要插入的数量,然后一次性分配足够的auto_increment id,只会将整个分配的过程锁住。需要注意的是,这种方式下,会分配过多的id,而导致”浪费“。比如INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');会一次性的分配5个id,而不管用户是否指定了部分id;INSERT … ON DUPLICATE KEY UPDATE一次性分配,而不管将来插入过程中是否会因为duplicate key而仅仅执行update操作。

注意:当master mysql版本<5.1.22,slave mysql版本>=5.1.22时,slave需要将innodb_autoinc_lock_mode设置为0,因为默认的innodb_autoinc_lock_mode为1,对于INSERT … ON DUPLICATE KEY UPDATE和INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');的执行结果不同,现实环境一般会使用INSERT … ON DUPLICATE KEY UPDATE。

三、innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)

这种模式是来一个分配一个,而不会锁表,只会锁住分配id的过程,和innodb_autoinc_lock_mode = 1的区别在于,不会预分配多个,这种方式并发性最高。但是在replication中当binlog_format为statement-based时(简称SBR statement-based replication)存在问题,因为是来一个分配一个,这样当并发执行时,“Bulk inserts”在分配的时会同时向其他的INSERT分配,会出现主从不一致(从库执行结果和主库执行结果不一样),因为binlog只会记录开始的insert id。

测试SBR,执行begin;insert values(),();insert values(),();commit;会在binlog中每条insert values(),();前增加SET INSERT_ID=18/*!*/;。

但是row-based replication RBR时不会存在问题。

另外RBR的主要缺点是日志数量在包括语句中包含大量的update delete(update多条语句,delete多条语句)时,日志会比SBR大很多;假如实际语句中这样语句不是很多的时候(现实中存在很多这样的情况),推荐使用RBR配合innodb_autoinc_lock_mode,不过话说回来,现实生产中“Bulk inserts”本来就很少,因此innodb_autoinc_lock_mode = 1应该是够用了。

下面是官方文档举得几个例子,这里就不翻译的

For example, assume c1 is an AUTO_INCREMENT column of table t1, and that the most recent automatically generated sequence number is 100. Consider the following “mixed-mode insert” statement:

 

INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');

With innodb_autoinc_lock_mode set to 0 (“traditional”), the four new rows will be:

 

+—–+——+

| c1  | c2   |

+—–+——+

|   1 | a    |

| 101 | b    |

|   5 | c    |

| 102 | d    |

+—–+——+

The next available auto-increment value will be 103 because the auto-increment values are allocated one at a time, not all at once at the beginning of statement execution. This result is true whether or not there are concurrently executing “INSERT-like” statements (of any type).

 

With innodb_autoinc_lock_mode set to 1 (“consecutive”), the four new rows will also be:

 

+—–+——+

| c1  | c2   |

+—–+——+

|   1 | a    |

| 101 | b    |

|   5 | c    |

| 102 | d    |

+—–+——+

However, in this case, the next available auto-increment value will be 105, not 103 because four auto-increment values are allocated at the time the statement is processed, but only two are used. This result is true whether or not there are concurrently executing “INSERT-like” statements (of any type).

 

With innodb_autoinc_lock_mode set to mode 2 (“interleaved”), the four new rows will be:

 

+—–+——+

| c1  | c2   |

+—–+——+

|   1 | a    |

|   x | b    |

|   5 | c    |

|   y | d    |

+—–+——+

The values of x and y will be unique and larger than any previously generated rows. However, the specific values of x and y will depend on the number of auto-increment values generated by concurrently executing statements.

 

Finally, consider the following statement, issued when the most-recently generated sequence number was the value 4:

 

INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');

With any innodb_autoinc_lock_mode setting, this statement will generate a duplicate-key error 23000 (Can’t write; duplicate key in table) because 5 will be allocated for the row (NULL, ‘b’) and insertion of the row (5, ‘c’) will fail.

程序本天成,妙手偶得之!我们只是代码的搬运工!

转载请注明:http://www.521php.com/archives/1979/

目前有1 条留言

  1. 2016年09月13日 下午 3:31 青岛礼品公司   |  引用  |  #1     

    通篇看下来,看不太懂啊

发表评论

昵称:

网址:

eg.博客主题调用的是Gravatar头像,你可以通过邮箱注册获得头像.
/ 快捷键:Ctrl+Enter