以下内容是我看文档时做的摘录,记录在这边,为自己查找方便:
innodb_addtional_mem_pool_size存储数据目录及其他数据结构的内存池的大小,表越多需要分配的也却多,若不足,则innodb开始从操作系统分配内存,且往错误日志写警告信息,默认1m
innodb_autoextend_increment
当自动扩展表空间被填满之时,为扩展而增加的尺寸(MB为单位)。默认值是8。这个选项可以在运行时作为全局系统变量而改变。
innodb_buffer_pool_size
InnoDB用来缓存它的数据和索引的内存缓冲区的大小。你把这个值设得越高,访问表中数据需要得磁盘I/O越少。在一个专用的数据库服务器上,你可以设置这个参数达机器物理内存大小的80%。尽管如此,还是不要把它设置得太大,因为对物理内存的竞争可能在操作系统上导致内存调度。
innodb_data_file_path
到单独数据文件和它们尺寸的路径。通过把innodb_data_home_dir连接到这里指定的每个路径,到每个数据文件的完整目录路径可被获得。文件大小通过给尺寸值尾加M或G以MB或者GB(1024MB)为单位被指定。文件尺寸的和至少是10MB。在一些操作系统上,文件必须小于2GB。如果你没有指定innodb_data_file_path,开始的默认行为是创建一个单独的大小10MB名为ibdata1的自扩展数据文件。在那些支持大文件的操作系统上,你可以设置文件大小超过4GB。你也可以使用原始磁盘分区作为数据文件,请参阅15.2.14.2节,“为表空间使用原始设备”。
· innodb_data_home_dir
目录路径对所有InnoDB数据文件的共同部分。如果你不设置这个值, 默认是MySQL数据目录。你也可以指定这个值为一个空字符串,在这种情况下,你可以在innodb_data_file_path中使用绝对文件路径。
innodb_doublewrite
默认地,InnoDB存储所有数据两次,第一次存储到doublewrite缓冲,然后存储到确实的数据文件。这个选项可以被用来禁止这个功能。类似于innodb_checksums,这个选项默认是允许的;因为标准检查或在对顶级性能的需要超过对数据完整性或可能故障的关注之时,这个选项用--skip-innodb-doublewrite来关闭。
· innodb_fast_shutdown
如果你把这个参数设置为0,InnoDB在关闭之前做一个完全净化和一个插入缓冲合并。这些操作要花几分钟时间,设置在极端情况下要几个小时。如果你设置这个参数为1,InnoDB在关闭之时跳过这些操作。默认值为1。如果你设置这个值为2 (在Netware无此值), InnoDB将刷新它的日志然后冷关机,仿佛MySQL崩溃一样。已提交的事务不会被丢失,但在下一次启动之时会做一个崩溃恢复。
innodb_file_per_table
innodb_flush_method
这个选项只在Unix系统上有效。如果这个选项被设置为fdatasync (默认值),InnoDB使用fsync()来刷新数据和日志文件。如果被设置为O_DSYNC,InnoDB使用O_SYNC来打开并刷新日志文件,但使用fsync()来刷新数据文件。如果O_DIRECT被指定了(在一些GNU/Linux版本商可用),InnoDB使用O_DIRECT来打开数据文件,并使用fsync()来刷新数据和日志文件。注意,InnoDB使用fsync()来替代fdatasync(),并且它默认不使用O_DSYNC,因为这个值在许多Unix变种上已经发生问题。
innodb_lock_wait_timeout
InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒。
为在一个复制建立中最大可能的持久程度和连贯性,你应该在主服务器上的my.cnf文件里使
用innodb_flush_log_at_trx_commit=1和sync-binlog=1
innodb_log_buffer_size
InnoDB用来往磁盘上的日志文件写操作的缓冲区的大小。明智的值是从1MB到8MB。默认的是1MB。一个大的日志缓冲允许大型事务运行而不需要在事务提交之前往磁盘写日志。因此,如果你有大型事务,使日志缓冲区更大以节约磁盘I/O。
· innodb_log_file_size
在日志组里每个日志文件的大小。在32位计算机上日志文件的合并大小必须少于4GB。默认是5MB。明智的值从1MB到N分之一缓冲池大小,其中N是组里日志文件的数目。值越大,在缓冲池越少需要检查点刷新行为,以节约磁盘I/O。但更大的日志文件也意味这在崩溃时恢复得更慢。
· innodb_log_files_in_group
在日志组里日志文件的数目。InnoDB以循环方式写进文件。默认是2(推荐)。
innodb_log_group_home_dir
到InnoDB日志文件的目录路径。它必须有和innodb_log_arch_dir一样的值。如果你不指定任何InnoDB日志参数, 默认的是在MySQL数据目录里创建两个5MB大小名为ib_logfile0和ib_logfile1的文件。
· innodb_max_dirty_pages_pct
这是一个范围从0到100的整数。默认是90。InnoDB中的主线程试着从缓冲池写页面,使得脏页(没有被写的页面)的百分比不超过这个值。如果你有SUPER权限,这个百分比可以在服务器运行时按下面来改变:
SET GLOBAL innodb_max_dirty_pages_pct = value;
innodb_open_files
在InnoDB中,这个选项仅与你使用多表空间时有关。它指定InnoDB一次可以保持打开的.ibd文件的最大数目。最小值是10。默认值300。
对.ibd文件的文件描述符是仅对InnoDB的。它们独立于那些由--open-files-limit服务器选项指定的描述符,且不影响表缓存的操作。
innodb_table_locks
InnoDB重视LOCK TABLES,直到所有其它线程已经释放他们所有对表的锁定,MySQL才从LOCK TABLE ..WRITE返回。默认值是1,这意为LOCK TABLES让InnoDB内部锁定一个表。在使用AUTOCOMMIT=1的应用里,InnoDB的内部表锁定会导致死锁。你可以在my.cnf文件(Windows上是my.ini文件)里设置innodb_table_locks=0 来消除这个问题。
innodb_thread_concurrency
InnoDB试着在InnoDB内保持操作系统线程的数量少于或等于这个参数给出的限制。如果有性能问题,并且SHOW INNODB STATUS显示许多线程在等待信号,可以让线程“thrashing” ,并且设置这个参数更小或更大。如果你的计算机有多个处理器和磁盘,你可以试着这个值更大以更好地利用计算机的资源。一个推荐的值是系统上处理器和磁盘的个数之和。值为500或比500大会禁止调用并发检查。默认值是20,并且如果设置大于或等于20,并发检查将被禁止。
· innodb_status_file
这个选项让InnoDB为周期的SHOW INNODB STATUS输出创建一个文件<datadir>/innodb_status.<pid>。
要点:你不应该在mysql数据库(比如,user或者host)里把MySQL系统表转换为InnoDB类型。系统表总是MyISAM型。
如果你想要所有(非系统)表都被创建成InnoDB表,你可以简单地把default-table-type=innodb行添加到my.cnf或my.ini文件的[mysqld]节里。
InnoDB对MyISAM存储引擎采用的单独索引创建方法没有做专门的优化。因此,它不值得导出或导入表以及随后创建索引。改变一个表为InnoDB型最快的办法就是直接插入进一个InnoDB表。即,使用ALTER TABLE ...ENGINE=INNODB,或用相同的定义创建一个空InnoDB表,并且用INSERT INTO ... SELECT * FROM ...插入行。
如果你对第二个键有UNIQUE约束,你可以在导入阶段设置:SET UNIQUE_CHECKS=0,以临时关掉唯一性检查好加速表的导入。对于大表,这节省了大量的磁盘I/O,因为InnoDB随后可以使用它的插入缓冲区来第二个索引记录作为一批来写入。
为获得对插入进程的更好控制,分段插入大表可能比较好:
INSERT INTO newtable SELECT * FROM oldtable
WHERE yourkey > something AND yourkey <= somethingelse;
所有记录已经本插入之后,你可以重命名表。
在大表的转换中,你应该增加InnoDB缓冲池的大小来减少磁盘I/O。尽管如此,不要使用超过80%的内部内存。你也可以增加InnoDB日志文件和日志文件的大小。
确信你没有填满表空间:InnoDB表比MyISAM表需要大得多的磁盘空间。如果一个ALTER TABLE耗尽了空间,它就开始一个回滚,并且如果它是磁盘绑定的,回滚可能要几个小时。对于插入,InnoDB使用插入缓冲区来以成批地合并第二个索引记录到索引中。那样节省了大量磁盘I/O。在回滚中,没有使用这样的机制,而回滚要花比插入长30倍的时间来完成。
。如果自动增长计数器没有被初始化,而且用户调用为表T显示输出的SHOW TABLE STATUS语句,则计数器被初始化(但不是增加计数)并被存储以供随后的插入使用。注意,在这个初始化中,我们对表做一个正常的独占读锁定,这个锁持续到事务的结束。
InnoDB对为新创建表的初始化自动增长计数器允许同样的过程。
注意,如果用户在INSERT中为AUTO_INCREMENT列指定NULL或者0,InnoDB处理行,就仿佛值还没有被指定,且为它生成一个新值。
自动增长计数器被初始化之后,如果用户插入一个明确指定该列值的行,而且该值大于当前计数器值,则计数器被设置为指定列值。如果没有明确指定一个值,InnoDB给计数器增加一,并且赋新值给该列。当访问自动增长计数器之时,InnoDB使用专用的表级的AUTO-INC锁定,该锁持续到当前SQL语句的结束而不是到业务的结束。引入了专用锁释放策略,来为对一个含AUTO_INCREMENT列的表的插入改善部署。两个事务不能同时对同一表有AUTO-INC锁定。
注意,如果你回滚从计数器获得数的事务,你可能会在赋给AUTO_INCREMENT列的值的序列中发现间隙。如果用户给列赋一个赋值,或者,如果值大过可被以指定整数格式存储的最大整数,自动增长机制的行为不被定义。
InnoDB需要对外键和被引用键的索引以便外键检查可以快速进行且不需要一个表扫描。对外键的索引被自动创建。
从这个版本起,mysqldump也将表的正确定义生成到转储文件中,且并不忘记外键。
你可以如下对一个表显示外键约束:
SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';
外键约束被列在输出的Comment列。
当执行外键检查之时,InnoDB对它照看着的子或父记录设置共享的行级锁。InnoDB立即检查外键约束,检查不对事务提交延迟。
要使得对有外键关系的表重新载入转储文件变得更容易,mysqldump自动在转储输出中包括一个语句设置FOREIGN_KEY_CHECKS为0。这避免在转储被重新装载之时,与不得不被以特别顺序重新装载的表相关的问题。也可以手动设置这个变量:
mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;
如果转储文件包含对外键是不正确顺序的表,这就以任何顺序导入该表。这样也加快导入操作。设置FOREIGN_KEY_CHECKS为0,对于在LOAD DATA和ALTER TABLE操作中忽略外键限制也是非常有用的。
在一个给定的MySQL安装里,你可以用RENAME TABLE语句把.ibd文件和关联的表从一个数据库移到另一个数据库:
RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name;
如果你有.ibd文件的一个干净的备份,你可以按如下操作从被起源的地方恢复它到MySQL安装中:
1. 发出这个ALTER TABLE语句:
2. ALTER TABLE tbl_name DISCARD TABLESPACE;
警告:这个语句删除当前.ibd文件。
3. 把备份的.ibd文件放回到恰当的数据库目录。
4. 发出这个ALTER TABLE语句:
5. ALTER TABLE tbl_name IMPORT TABLESPACE;
在上下文中,一个.ibd文件干净的备份意为:
· .ibd文件里没有尚未提交的事务做的修改。
· .ibd文件里无未合并的插入混充条目。
· 净化已经从.ibd文件移除所有已标注删除的索引记录。
mysqld已经把.ibd文件的所有已修改页面从缓冲池刷新到文件。
你可以用下列方法生成一个.ibd文件的干净备份:
1. 停止所有来自mysqld服务器的活动,并提交所有事务。
2. 等待直至SHOW INNODB STATUS显示在数据库被已经没有激活的事务,并且InnoDB主线程的状态是Waiting for server activity。然后你就可以复制.ibd文件了。
生成一个.ibd文件的干净复制的另一个方法是使用商业的InnoDB热备份工具:
1. 使用InnoDB热备份工具备份InnoDB安装。
2. 在备份上启动第二个mysqld服务器,让它清洁备份里的.ibd文件。
作为替代,你可以通过添加另一个数据文件来增加表空间的尺寸。要这么做的话,你必须停止MySQL服务器,
编辑my.cnf文件,添加一个新数据文件到innodb_data_file_path的末尾,然后再次启动服务器。
如果最后一个数据文件是用关键字autoextend定义的,编辑my.cnf文件的步骤必须考虑最后一个数据文件已经增长到多大。获取数据文件的尺寸,把它四舍五入到最接近乘积1024 × 1024bytes (= 1MB),然后在innodb_data_file_path中明确指定大致的尺寸。然后你可以添加另一个数据文件。记得只有innodb_data_file_path里最后一个数据可以被指定为自动扩展。
作为一个例子。假设表空间正好有一个自动扩展文件ibdata1:
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend
假设这个数据文件过一段时间已经长到988MB。下面是添加另一个总扩展数据文件之后的配置行:
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
当你添加一个新文件到表空间的之后,请确信它并不存在。当你重启服务器之时,InnoDB创建并初始化这个文件。
如果你没有适合查询的索引,MySQL不得不扫描整个表来处理查询,表的每行变成被锁定的,这样反过来阻止其它用户的所有对表的插入。创建一个好的索引让你的查询不必要扫描很多行是很重要的。
· SELECT ... FROM是一个持续读,读取数据库的快照并且设置不锁定,除非事务隔离级别被设
为SERIALIZABLE。对于 SERIALIZABLE级别,这个设置对它遇到的索引记录设置共享的next-key锁定。
· SELECT ... FROM ... LOCK IN SHARE MODE对读遇到的所有索引记录设置共享的next-key锁定。
· SELECT ... FROM ... FOR UPDATE对读遇到的所有索引记录设置独占的next-key锁定。
· INSERT INTO ... VALUES (...)对被插入的行设置独占锁定。注意,这不是一个next-key锁定,并且不阻止其它用户在已插入行之前的间隙插入。如果发生重复键错误,对重复的索引记录设置共享锁定。
· 在一个表上初始化之前指定的AUTO_INCREMENT列之时,InnoDB在与AUTO_INCREMENT列相关联的索引的末尾设置独占锁定。在访问自动增长计数器中,InnoDB使用专用的表锁定模式AUTO-INC,其中锁定仅持续到当前SQL语句的结束,而不是到整个事务的结束。
INSERT INTO T SELECT ... FROM S WHERE ... 对每个插入到T的行设置独占(非next-key)锁定。它
在S上把搜索当作一个持续读,但是如果MySQL二进制日志功能被打开,它就对S设置一个共享的next-key锁
定。InnoDB在后一种情况不得不设置锁定:在从一个备份的前滚恢复中,每个SQL语句不得不以与它最初被执
行的方式完全同样的方式执行。
· CREATE TABLE ... SELECT ... 把SELECT当作一个持续读来执行,或者带着共享锁定来执行,如前面的
条目所述。
· 如果唯一键没有冲突,REPLACE象一个插入一样被做。另外,对必须更新的行设置一个独占的nextkey
锁定。
· UPDATE ... WHERE ... 对搜索遇到的每个记录设置一个独占的next-key锁定。
· DELETE FROM ... WHERE ... 对搜索遇到的每个记录设置一个独占的next-key锁定。
· 如果对一个表定义FOREIGN KEY约束,任何需要检查约束条件的插入,更新或删除对它看着检查约束的记录设置共享行级锁定。InnoDB在约束失败的情况下也设置这些锁定。
InnoDB性能调节提示
如果Unix顶层工具或者Windows任务管理器显示,你的数据库的工作负荷的CPU使用率小于70%,则你的工作负荷可能是磁盘绑定的,可能你正生成太多的事务和提交,或者缓冲池太小。使得缓冲池更大一些会有帮助的,但不要设置缓冲池等于或超过物理内存的80%.
· 把数个修改裹进一个事务里。如果事务对数据库修改,InnoDB在该事务提交时必须刷新日志到磁盘。
因为磁盘旋转的速度至多167转/秒,如果磁盘没有骗操作系统的话,这就限制提交的数目为同样的每秒167次。
· 如果你可以接受损失一些最近的已提交事务,你可以设置my.cnf文件里的参
数innodb_flush_log_at_trx_commit为0。无论如何InnoDB试着每秒刷新一次日志,尽管刷新不被许可。
· 使用大的日志文件,让它甚至与缓冲池一样大。当InnoDB写满日志文件时,它不得不在一个检查点把缓冲池已修改的内容写进磁盘。小日志文件导致许多不必要的吸盘写操作。大日志文件的缺点时恢复时间更长。
· 也让日志缓冲相当大(与8MB相似的数量)。
如果你存储变长度字符串,或者列可能包含很多NULL值,则使用VARCHAR列类型而不是CHAR类型。
一个CHAR(N)列总是占据N 字节来存储,即使字符串更短或字符串的值是NULL。越小的表越好地
并且减少磁盘I/O。
当使用row_format=compact (MySQL 5.1中默认的InnoDB记录格式)和可变长度字符集,比如UTF-8或sjis,CHAR(N)将占据可变数量的空间,至少为N 字节。
· 在一些版本的GNU/Linux和Unix上,用Unix的fsync()(InnoDB默认使用的)把文件刷新到磁盘,并且其他相似的方法是惊人的慢。如果你不满意数据库的写性能,你可以试着设置my.cnf里的innodb_flush_method为O_DSYNC,虽然O_DSYNC在多数系统上看起来更慢。
当导入数据到InnoDB中之时,请确信MySQL没有允许autocommit模式,因为允许autocommit模式会需要每次插入都要刷新日志到磁盘。要在导入操作规程中禁止autocommit模式,用SET
AUTOCOMMIT和COMMIT语句来包住导入语句:
· SET AUTOCOMMIT=0;
· /* SQL import statements ... */
· COMMIT;
如果你使用mysqldump 选项--opt,即使不用SET AUTOCOMMIT和COMMIT语句来包裹,你也使得快速的转储文件被导入到InnoDB表中。
· 小心大宗插入的大回滚:InnoDB在插入中使用插入缓冲来节约磁盘I/O, 但是在相应的回滚中没有使用这样的机制。一个磁盘绑定的回滚可以用相应插入花费时间的30倍来执行。杀掉数据库进程没有是帮助的,因为回滚在服务器启动时会再次启动。除掉一个失控的回滚的唯一方法是增大缓冲池使得回滚变成CPU绑定且跑得快,或者使用专用步骤,请参阅15.2.8.1节,“强制恢复”。
· 也要小心其它大的磁盘绑定操作。用DROP TABLE或CREATE TABLE来清空一个表,而不是用DELETE FROM tbl_name。
如果你需要插入许多行,则使用多行插入语法来减少客户端和服务器之间的通讯开支:
· INSERT INTO yourtable VALUES (1,2), (5,5), ...;
这个提示对到任何表类型的插入都是合法的,不仅仅是对InnoDB类型。
· 如果你在第二个键上有UNIQUE约束,你可以在导入会话中暂时关闭唯一性检查以加速表的导入:
· SET UNIQUE_CHECKS=0;
对于大表,这节约了大量磁盘I/O,因为InnoDB可以使用它的插入缓冲来在一批内写第二个索引记录。
· 如果你对你的表有FOREIGN KEY约束,你可以在导入会话过程中通过关闭外键检查来提速表的导入:
· SET FOREIGN_KEY_CHECKS=0;
对于大表,这可以节约大量的磁盘I/O。
如果你经常有对不经常更新的表的重发查询,请使用查询缓存:
· [mysqld]
· query_cache_type = ON
· query_cache_size = 10M
关于这个输出一些要注意的:
· 如果TRANSACTIONS节报告锁定等待,你的应用程序可能有锁定竞争。输出也帮助跟踪事务死锁的原因。
· SEMAPHORES节报告等待一个信号的线程和关于线程对一个互斥体或rw-lock信号需要多少次回滚或等待的统计数据。大量等待信号的线程可能是磁盘I/O的结果或者InnoDB内竞争问题的结果。竞争可能是因为查询的严重并行,或者操作系统线程计划的问题。设置innodb_thread_concurrency小于默认值在这种情况下是有帮助的。
· BUFFER POOL AND MEMORY节给你关于页面读写的统计数据。你可以从这些数计算你当前的查询正做多少文件数据I/O操作。
· ROW OPERATIONS节显示主线程正在做什么。
InnoDB发送诊断输出到stderr或文件,而不是到stdout或者固定尺寸内存缓冲,以避免底层缓冲溢出。作为一个副效果,SHOW INNODB STATUS的输出每15秒钟写到一个状态文件。这个文件的名字是innodb_status.pid,其中pid 是服务器进程ID。这个文件在MySQL数据目录里创建。正常关机之时,InnoDB删除这个文件。如果发生不正常的关机, 这些状态文件的实例可能被展示,而且必须被手动删除。在移除它们之前,你可能想要检查它们来看它们是否包含有关不正常关机的原因的有用信息。仅在配置选项innodb_status_file=1被设置之时,innodb_status.pid文件被创建。
注意,哈希索引总是基于表上已存在的B树索引来建立。根据InnoDB对B树索引观察的搜索方式,InnoDB会在为该B树定义的任何长度的键的一个前缀上建立哈希索引。 哈希索引可以是部分的:它不要求整个B树索引被缓存在缓冲池。InnoDB根据需要对被经常访问的索引的那些页面建立哈希索引。
在某种意义上,InnoDB通过针对丰富主内存的适应的哈希索引机制来剪裁自己,更加靠近主内存数据库的架构。
当你创建一个新数据文件之时,你必须在innodb_data_file_path里紧接着数据文件尺寸之后放置关键字newraw。分区必须至少和你指定的尺寸一样大,注意,在InnoDB中,1MB是1024x1024字节, 但是在磁盘规格中,1MB通常意为1,000,000字节。
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw
下次你启动服务器之时,InnoDB注意到关键字newraw并初始化新分区。但是仍然并不创建或改变任
何InnoDB表。另外,当你重启服务器之时,InnoDB重新初始化分区,你的改变会丢失。(从3.23.44启动,作
为一个安全措施, 当用newraw指定任何分区之时,InnoDB阻止用户修改数据)。
InnoDB初始化新分区之后,停止服务器,在对行的数据文件规格中改变newraw:
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:5Graw;/dev/hdd2:2Graw
然后重启动服务器,并且InnoDB允许做改变。
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
你可以按照错误日志里给的指示移除一个孤表。如果还是不能成功地使用DROP TABLE,问题可能是因为在mysql客户端里的名字完成。要解决这个问题,用--disable-auto-rehash选项来启动mysql客户端并再次尝试DROP TABLE 。(有名字完成打开着,mysql试着构建个表名字的列表,当一个正如描述的问题存在之时,这个列表就不起作用)。
不同步数据词典的另一个“同义词”是MySQL打印一个不能打开.InnoDB文件的错误:
ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)
这意味这有一个孤单的.frm文件,在InnoDB内没有相对应的表。你可以通过手动删除来移除这个孤单的.frm文件。
如果MySQL在一个 ALTER TABLE操作的中间崩溃,你可以用InnoDB表空间内临时孤表来结束。你可以用innodb_table_monitor看一个列出的表,名为#sql-...。如果你把表的名字包在`(backticks)里,你可以在名字包含“#”字符的表上执行SQL语句。因此,你可以用前述的的方法象移除其它孤表一样移除这样一个孤表。
注意,要在Unix外壳里复制或重命名一个文件,如果文件名包含"#"字符,你需要把文件名放在双引号里。
innodb_addtional_mem_pool_size存储数据目录及其他数据结构的内存池的大小,表越多需要分配的也却多,若不足,则innodb开始从操作系统分配内存,且往错误日志写警告信息,默认1m
innodb_autoextend_increment
当自动扩展表空间被填满之时,为扩展而增加的尺寸(MB为单位)。默认值是8。这个选项可以在运行时作为全局系统变量而改变。
innodb_buffer_pool_size
InnoDB用来缓存它的数据和索引的内存缓冲区的大小。你把这个值设得越高,访问表中数据需要得磁盘I/O越少。在一个专用的数据库服务器上,你可以设置这个参数达机器物理内存大小的80%。尽管如此,还是不要把它设置得太大,因为对物理内存的竞争可能在操作系统上导致内存调度。
innodb_data_file_path
到单独数据文件和它们尺寸的路径。通过把innodb_data_home_dir连接到这里指定的每个路径,到每个数据文件的完整目录路径可被获得。文件大小通过给尺寸值尾加M或G以MB或者GB(1024MB)为单位被指定。文件尺寸的和至少是10MB。在一些操作系统上,文件必须小于2GB。如果你没有指定innodb_data_file_path,开始的默认行为是创建一个单独的大小10MB名为ibdata1的自扩展数据文件。在那些支持大文件的操作系统上,你可以设置文件大小超过4GB。你也可以使用原始磁盘分区作为数据文件,请参阅15.2.14.2节,“为表空间使用原始设备”。
· innodb_data_home_dir
目录路径对所有InnoDB数据文件的共同部分。如果你不设置这个值, 默认是MySQL数据目录。你也可以指定这个值为一个空字符串,在这种情况下,你可以在innodb_data_file_path中使用绝对文件路径。
innodb_doublewrite
默认地,InnoDB存储所有数据两次,第一次存储到doublewrite缓冲,然后存储到确实的数据文件。这个选项可以被用来禁止这个功能。类似于innodb_checksums,这个选项默认是允许的;因为标准检查或在对顶级性能的需要超过对数据完整性或可能故障的关注之时,这个选项用--skip-innodb-doublewrite来关闭。
· innodb_fast_shutdown
如果你把这个参数设置为0,InnoDB在关闭之前做一个完全净化和一个插入缓冲合并。这些操作要花几分钟时间,设置在极端情况下要几个小时。如果你设置这个参数为1,InnoDB在关闭之时跳过这些操作。默认值为1。如果你设置这个值为2 (在Netware无此值), InnoDB将刷新它的日志然后冷关机,仿佛MySQL崩溃一样。已提交的事务不会被丢失,但在下一次启动之时会做一个崩溃恢复。
innodb_file_per_table
innodb_flush_method
这个选项只在Unix系统上有效。如果这个选项被设置为fdatasync (默认值),InnoDB使用fsync()来刷新数据和日志文件。如果被设置为O_DSYNC,InnoDB使用O_SYNC来打开并刷新日志文件,但使用fsync()来刷新数据文件。如果O_DIRECT被指定了(在一些GNU/Linux版本商可用),InnoDB使用O_DIRECT来打开数据文件,并使用fsync()来刷新数据和日志文件。注意,InnoDB使用fsync()来替代fdatasync(),并且它默认不使用O_DSYNC,因为这个值在许多Unix变种上已经发生问题。
innodb_lock_wait_timeout
InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒。
为在一个复制建立中最大可能的持久程度和连贯性,你应该在主服务器上的my.cnf文件里使
用innodb_flush_log_at_trx_commit=1和sync-binlog=1
innodb_log_buffer_size
InnoDB用来往磁盘上的日志文件写操作的缓冲区的大小。明智的值是从1MB到8MB。默认的是1MB。一个大的日志缓冲允许大型事务运行而不需要在事务提交之前往磁盘写日志。因此,如果你有大型事务,使日志缓冲区更大以节约磁盘I/O。
· innodb_log_file_size
在日志组里每个日志文件的大小。在32位计算机上日志文件的合并大小必须少于4GB。默认是5MB。明智的值从1MB到N分之一缓冲池大小,其中N是组里日志文件的数目。值越大,在缓冲池越少需要检查点刷新行为,以节约磁盘I/O。但更大的日志文件也意味这在崩溃时恢复得更慢。
· innodb_log_files_in_group
在日志组里日志文件的数目。InnoDB以循环方式写进文件。默认是2(推荐)。
innodb_log_group_home_dir
到InnoDB日志文件的目录路径。它必须有和innodb_log_arch_dir一样的值。如果你不指定任何InnoDB日志参数, 默认的是在MySQL数据目录里创建两个5MB大小名为ib_logfile0和ib_logfile1的文件。
· innodb_max_dirty_pages_pct
这是一个范围从0到100的整数。默认是90。InnoDB中的主线程试着从缓冲池写页面,使得脏页(没有被写的页面)的百分比不超过这个值。如果你有SUPER权限,这个百分比可以在服务器运行时按下面来改变:
SET GLOBAL innodb_max_dirty_pages_pct = value;
innodb_open_files
在InnoDB中,这个选项仅与你使用多表空间时有关。它指定InnoDB一次可以保持打开的.ibd文件的最大数目。最小值是10。默认值300。
对.ibd文件的文件描述符是仅对InnoDB的。它们独立于那些由--open-files-limit服务器选项指定的描述符,且不影响表缓存的操作。
innodb_table_locks
InnoDB重视LOCK TABLES,直到所有其它线程已经释放他们所有对表的锁定,MySQL才从LOCK TABLE ..WRITE返回。默认值是1,这意为LOCK TABLES让InnoDB内部锁定一个表。在使用AUTOCOMMIT=1的应用里,InnoDB的内部表锁定会导致死锁。你可以在my.cnf文件(Windows上是my.ini文件)里设置innodb_table_locks=0 来消除这个问题。
innodb_thread_concurrency
InnoDB试着在InnoDB内保持操作系统线程的数量少于或等于这个参数给出的限制。如果有性能问题,并且SHOW INNODB STATUS显示许多线程在等待信号,可以让线程“thrashing” ,并且设置这个参数更小或更大。如果你的计算机有多个处理器和磁盘,你可以试着这个值更大以更好地利用计算机的资源。一个推荐的值是系统上处理器和磁盘的个数之和。值为500或比500大会禁止调用并发检查。默认值是20,并且如果设置大于或等于20,并发检查将被禁止。
· innodb_status_file
这个选项让InnoDB为周期的SHOW INNODB STATUS输出创建一个文件<datadir>/innodb_status.<pid>。
要点:你不应该在mysql数据库(比如,user或者host)里把MySQL系统表转换为InnoDB类型。系统表总是MyISAM型。
如果你想要所有(非系统)表都被创建成InnoDB表,你可以简单地把default-table-type=innodb行添加到my.cnf或my.ini文件的[mysqld]节里。
InnoDB对MyISAM存储引擎采用的单独索引创建方法没有做专门的优化。因此,它不值得导出或导入表以及随后创建索引。改变一个表为InnoDB型最快的办法就是直接插入进一个InnoDB表。即,使用ALTER TABLE ...ENGINE=INNODB,或用相同的定义创建一个空InnoDB表,并且用INSERT INTO ... SELECT * FROM ...插入行。
如果你对第二个键有UNIQUE约束,你可以在导入阶段设置:SET UNIQUE_CHECKS=0,以临时关掉唯一性检查好加速表的导入。对于大表,这节省了大量的磁盘I/O,因为InnoDB随后可以使用它的插入缓冲区来第二个索引记录作为一批来写入。
为获得对插入进程的更好控制,分段插入大表可能比较好:
INSERT INTO newtable SELECT * FROM oldtable
WHERE yourkey > something AND yourkey <= somethingelse;
所有记录已经本插入之后,你可以重命名表。
在大表的转换中,你应该增加InnoDB缓冲池的大小来减少磁盘I/O。尽管如此,不要使用超过80%的内部内存。你也可以增加InnoDB日志文件和日志文件的大小。
确信你没有填满表空间:InnoDB表比MyISAM表需要大得多的磁盘空间。如果一个ALTER TABLE耗尽了空间,它就开始一个回滚,并且如果它是磁盘绑定的,回滚可能要几个小时。对于插入,InnoDB使用插入缓冲区来以成批地合并第二个索引记录到索引中。那样节省了大量磁盘I/O。在回滚中,没有使用这样的机制,而回滚要花比插入长30倍的时间来完成。
。如果自动增长计数器没有被初始化,而且用户调用为表T显示输出的SHOW TABLE STATUS语句,则计数器被初始化(但不是增加计数)并被存储以供随后的插入使用。注意,在这个初始化中,我们对表做一个正常的独占读锁定,这个锁持续到事务的结束。
InnoDB对为新创建表的初始化自动增长计数器允许同样的过程。
注意,如果用户在INSERT中为AUTO_INCREMENT列指定NULL或者0,InnoDB处理行,就仿佛值还没有被指定,且为它生成一个新值。
自动增长计数器被初始化之后,如果用户插入一个明确指定该列值的行,而且该值大于当前计数器值,则计数器被设置为指定列值。如果没有明确指定一个值,InnoDB给计数器增加一,并且赋新值给该列。当访问自动增长计数器之时,InnoDB使用专用的表级的AUTO-INC锁定,该锁持续到当前SQL语句的结束而不是到业务的结束。引入了专用锁释放策略,来为对一个含AUTO_INCREMENT列的表的插入改善部署。两个事务不能同时对同一表有AUTO-INC锁定。
注意,如果你回滚从计数器获得数的事务,你可能会在赋给AUTO_INCREMENT列的值的序列中发现间隙。如果用户给列赋一个赋值,或者,如果值大过可被以指定整数格式存储的最大整数,自动增长机制的行为不被定义。
InnoDB需要对外键和被引用键的索引以便外键检查可以快速进行且不需要一个表扫描。对外键的索引被自动创建。
从这个版本起,mysqldump也将表的正确定义生成到转储文件中,且并不忘记外键。
你可以如下对一个表显示外键约束:
SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';
外键约束被列在输出的Comment列。
当执行外键检查之时,InnoDB对它照看着的子或父记录设置共享的行级锁。InnoDB立即检查外键约束,检查不对事务提交延迟。
要使得对有外键关系的表重新载入转储文件变得更容易,mysqldump自动在转储输出中包括一个语句设置FOREIGN_KEY_CHECKS为0。这避免在转储被重新装载之时,与不得不被以特别顺序重新装载的表相关的问题。也可以手动设置这个变量:
mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;
如果转储文件包含对外键是不正确顺序的表,这就以任何顺序导入该表。这样也加快导入操作。设置FOREIGN_KEY_CHECKS为0,对于在LOAD DATA和ALTER TABLE操作中忽略外键限制也是非常有用的。
在一个给定的MySQL安装里,你可以用RENAME TABLE语句把.ibd文件和关联的表从一个数据库移到另一个数据库:
RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name;
如果你有.ibd文件的一个干净的备份,你可以按如下操作从被起源的地方恢复它到MySQL安装中:
1. 发出这个ALTER TABLE语句:
2. ALTER TABLE tbl_name DISCARD TABLESPACE;
警告:这个语句删除当前.ibd文件。
3. 把备份的.ibd文件放回到恰当的数据库目录。
4. 发出这个ALTER TABLE语句:
5. ALTER TABLE tbl_name IMPORT TABLESPACE;
在上下文中,一个.ibd文件干净的备份意为:
· .ibd文件里没有尚未提交的事务做的修改。
· .ibd文件里无未合并的插入混充条目。
· 净化已经从.ibd文件移除所有已标注删除的索引记录。
mysqld已经把.ibd文件的所有已修改页面从缓冲池刷新到文件。
你可以用下列方法生成一个.ibd文件的干净备份:
1. 停止所有来自mysqld服务器的活动,并提交所有事务。
2. 等待直至SHOW INNODB STATUS显示在数据库被已经没有激活的事务,并且InnoDB主线程的状态是Waiting for server activity。然后你就可以复制.ibd文件了。
生成一个.ibd文件的干净复制的另一个方法是使用商业的InnoDB热备份工具:
1. 使用InnoDB热备份工具备份InnoDB安装。
2. 在备份上启动第二个mysqld服务器,让它清洁备份里的.ibd文件。
作为替代,你可以通过添加另一个数据文件来增加表空间的尺寸。要这么做的话,你必须停止MySQL服务器,
编辑my.cnf文件,添加一个新数据文件到innodb_data_file_path的末尾,然后再次启动服务器。
如果最后一个数据文件是用关键字autoextend定义的,编辑my.cnf文件的步骤必须考虑最后一个数据文件已经增长到多大。获取数据文件的尺寸,把它四舍五入到最接近乘积1024 × 1024bytes (= 1MB),然后在innodb_data_file_path中明确指定大致的尺寸。然后你可以添加另一个数据文件。记得只有innodb_data_file_path里最后一个数据可以被指定为自动扩展。
作为一个例子。假设表空间正好有一个自动扩展文件ibdata1:
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend
假设这个数据文件过一段时间已经长到988MB。下面是添加另一个总扩展数据文件之后的配置行:
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
当你添加一个新文件到表空间的之后,请确信它并不存在。当你重启服务器之时,InnoDB创建并初始化这个文件。
如果你没有适合查询的索引,MySQL不得不扫描整个表来处理查询,表的每行变成被锁定的,这样反过来阻止其它用户的所有对表的插入。创建一个好的索引让你的查询不必要扫描很多行是很重要的。
· SELECT ... FROM是一个持续读,读取数据库的快照并且设置不锁定,除非事务隔离级别被设
为SERIALIZABLE。对于 SERIALIZABLE级别,这个设置对它遇到的索引记录设置共享的next-key锁定。
· SELECT ... FROM ... LOCK IN SHARE MODE对读遇到的所有索引记录设置共享的next-key锁定。
· SELECT ... FROM ... FOR UPDATE对读遇到的所有索引记录设置独占的next-key锁定。
· INSERT INTO ... VALUES (...)对被插入的行设置独占锁定。注意,这不是一个next-key锁定,并且不阻止其它用户在已插入行之前的间隙插入。如果发生重复键错误,对重复的索引记录设置共享锁定。
· 在一个表上初始化之前指定的AUTO_INCREMENT列之时,InnoDB在与AUTO_INCREMENT列相关联的索引的末尾设置独占锁定。在访问自动增长计数器中,InnoDB使用专用的表锁定模式AUTO-INC,其中锁定仅持续到当前SQL语句的结束,而不是到整个事务的结束。
INSERT INTO T SELECT ... FROM S WHERE ... 对每个插入到T的行设置独占(非next-key)锁定。它
在S上把搜索当作一个持续读,但是如果MySQL二进制日志功能被打开,它就对S设置一个共享的next-key锁
定。InnoDB在后一种情况不得不设置锁定:在从一个备份的前滚恢复中,每个SQL语句不得不以与它最初被执
行的方式完全同样的方式执行。
· CREATE TABLE ... SELECT ... 把SELECT当作一个持续读来执行,或者带着共享锁定来执行,如前面的
条目所述。
· 如果唯一键没有冲突,REPLACE象一个插入一样被做。另外,对必须更新的行设置一个独占的nextkey
锁定。
· UPDATE ... WHERE ... 对搜索遇到的每个记录设置一个独占的next-key锁定。
· DELETE FROM ... WHERE ... 对搜索遇到的每个记录设置一个独占的next-key锁定。
· 如果对一个表定义FOREIGN KEY约束,任何需要检查约束条件的插入,更新或删除对它看着检查约束的记录设置共享行级锁定。InnoDB在约束失败的情况下也设置这些锁定。
InnoDB性能调节提示
如果Unix顶层工具或者Windows任务管理器显示,你的数据库的工作负荷的CPU使用率小于70%,则你的工作负荷可能是磁盘绑定的,可能你正生成太多的事务和提交,或者缓冲池太小。使得缓冲池更大一些会有帮助的,但不要设置缓冲池等于或超过物理内存的80%.
· 把数个修改裹进一个事务里。如果事务对数据库修改,InnoDB在该事务提交时必须刷新日志到磁盘。
因为磁盘旋转的速度至多167转/秒,如果磁盘没有骗操作系统的话,这就限制提交的数目为同样的每秒167次。
· 如果你可以接受损失一些最近的已提交事务,你可以设置my.cnf文件里的参
数innodb_flush_log_at_trx_commit为0。无论如何InnoDB试着每秒刷新一次日志,尽管刷新不被许可。
· 使用大的日志文件,让它甚至与缓冲池一样大。当InnoDB写满日志文件时,它不得不在一个检查点把缓冲池已修改的内容写进磁盘。小日志文件导致许多不必要的吸盘写操作。大日志文件的缺点时恢复时间更长。
· 也让日志缓冲相当大(与8MB相似的数量)。
如果你存储变长度字符串,或者列可能包含很多NULL值,则使用VARCHAR列类型而不是CHAR类型。
一个CHAR(N)列总是占据N 字节来存储,即使字符串更短或字符串的值是NULL。越小的表越好地
并且减少磁盘I/O。
当使用row_format=compact (MySQL 5.1中默认的InnoDB记录格式)和可变长度字符集,比如UTF-8或sjis,CHAR(N)将占据可变数量的空间,至少为N 字节。
· 在一些版本的GNU/Linux和Unix上,用Unix的fsync()(InnoDB默认使用的)把文件刷新到磁盘,并且其他相似的方法是惊人的慢。如果你不满意数据库的写性能,你可以试着设置my.cnf里的innodb_flush_method为O_DSYNC,虽然O_DSYNC在多数系统上看起来更慢。
当导入数据到InnoDB中之时,请确信MySQL没有允许autocommit模式,因为允许autocommit模式会需要每次插入都要刷新日志到磁盘。要在导入操作规程中禁止autocommit模式,用SET
AUTOCOMMIT和COMMIT语句来包住导入语句:
· SET AUTOCOMMIT=0;
· /* SQL import statements ... */
· COMMIT;
如果你使用mysqldump 选项--opt,即使不用SET AUTOCOMMIT和COMMIT语句来包裹,你也使得快速的转储文件被导入到InnoDB表中。
· 小心大宗插入的大回滚:InnoDB在插入中使用插入缓冲来节约磁盘I/O, 但是在相应的回滚中没有使用这样的机制。一个磁盘绑定的回滚可以用相应插入花费时间的30倍来执行。杀掉数据库进程没有是帮助的,因为回滚在服务器启动时会再次启动。除掉一个失控的回滚的唯一方法是增大缓冲池使得回滚变成CPU绑定且跑得快,或者使用专用步骤,请参阅15.2.8.1节,“强制恢复”。
· 也要小心其它大的磁盘绑定操作。用DROP TABLE或CREATE TABLE来清空一个表,而不是用DELETE FROM tbl_name。
如果你需要插入许多行,则使用多行插入语法来减少客户端和服务器之间的通讯开支:
· INSERT INTO yourtable VALUES (1,2), (5,5), ...;
这个提示对到任何表类型的插入都是合法的,不仅仅是对InnoDB类型。
· 如果你在第二个键上有UNIQUE约束,你可以在导入会话中暂时关闭唯一性检查以加速表的导入:
· SET UNIQUE_CHECKS=0;
对于大表,这节约了大量磁盘I/O,因为InnoDB可以使用它的插入缓冲来在一批内写第二个索引记录。
· 如果你对你的表有FOREIGN KEY约束,你可以在导入会话过程中通过关闭外键检查来提速表的导入:
· SET FOREIGN_KEY_CHECKS=0;
对于大表,这可以节约大量的磁盘I/O。
如果你经常有对不经常更新的表的重发查询,请使用查询缓存:
· [mysqld]
· query_cache_type = ON
· query_cache_size = 10M
关于这个输出一些要注意的:
· 如果TRANSACTIONS节报告锁定等待,你的应用程序可能有锁定竞争。输出也帮助跟踪事务死锁的原因。
· SEMAPHORES节报告等待一个信号的线程和关于线程对一个互斥体或rw-lock信号需要多少次回滚或等待的统计数据。大量等待信号的线程可能是磁盘I/O的结果或者InnoDB内竞争问题的结果。竞争可能是因为查询的严重并行,或者操作系统线程计划的问题。设置innodb_thread_concurrency小于默认值在这种情况下是有帮助的。
· BUFFER POOL AND MEMORY节给你关于页面读写的统计数据。你可以从这些数计算你当前的查询正做多少文件数据I/O操作。
· ROW OPERATIONS节显示主线程正在做什么。
InnoDB发送诊断输出到stderr或文件,而不是到stdout或者固定尺寸内存缓冲,以避免底层缓冲溢出。作为一个副效果,SHOW INNODB STATUS的输出每15秒钟写到一个状态文件。这个文件的名字是innodb_status.pid,其中pid 是服务器进程ID。这个文件在MySQL数据目录里创建。正常关机之时,InnoDB删除这个文件。如果发生不正常的关机, 这些状态文件的实例可能被展示,而且必须被手动删除。在移除它们之前,你可能想要检查它们来看它们是否包含有关不正常关机的原因的有用信息。仅在配置选项innodb_status_file=1被设置之时,innodb_status.pid文件被创建。
注意,哈希索引总是基于表上已存在的B树索引来建立。根据InnoDB对B树索引观察的搜索方式,InnoDB会在为该B树定义的任何长度的键的一个前缀上建立哈希索引。 哈希索引可以是部分的:它不要求整个B树索引被缓存在缓冲池。InnoDB根据需要对被经常访问的索引的那些页面建立哈希索引。
在某种意义上,InnoDB通过针对丰富主内存的适应的哈希索引机制来剪裁自己,更加靠近主内存数据库的架构。
当你创建一个新数据文件之时,你必须在innodb_data_file_path里紧接着数据文件尺寸之后放置关键字newraw。分区必须至少和你指定的尺寸一样大,注意,在InnoDB中,1MB是1024x1024字节, 但是在磁盘规格中,1MB通常意为1,000,000字节。
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw
下次你启动服务器之时,InnoDB注意到关键字newraw并初始化新分区。但是仍然并不创建或改变任
何InnoDB表。另外,当你重启服务器之时,InnoDB重新初始化分区,你的改变会丢失。(从3.23.44启动,作
为一个安全措施, 当用newraw指定任何分区之时,InnoDB阻止用户修改数据)。
InnoDB初始化新分区之后,停止服务器,在对行的数据文件规格中改变newraw:
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:5Graw;/dev/hdd2:2Graw
然后重启动服务器,并且InnoDB允许做改变。
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
你可以按照错误日志里给的指示移除一个孤表。如果还是不能成功地使用DROP TABLE,问题可能是因为在mysql客户端里的名字完成。要解决这个问题,用--disable-auto-rehash选项来启动mysql客户端并再次尝试DROP TABLE 。(有名字完成打开着,mysql试着构建个表名字的列表,当一个正如描述的问题存在之时,这个列表就不起作用)。
不同步数据词典的另一个“同义词”是MySQL打印一个不能打开.InnoDB文件的错误:
ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)
这意味这有一个孤单的.frm文件,在InnoDB内没有相对应的表。你可以通过手动删除来移除这个孤单的.frm文件。
如果MySQL在一个 ALTER TABLE操作的中间崩溃,你可以用InnoDB表空间内临时孤表来结束。你可以用innodb_table_monitor看一个列出的表,名为#sql-...。如果你把表的名字包在`(backticks)里,你可以在名字包含“#”字符的表上执行SQL语句。因此,你可以用前述的的方法象移除其它孤表一样移除这样一个孤表。
注意,要在Unix外壳里复制或重命名一个文件,如果文件名包含"#"字符,你需要把文件名放在双引号里。