InnoDB性能配置参数

 

<span>1. innodb_additional_mem_pool_size </span>



<span style="font-family:宋体;">除了缓存表数据和索引外</span><span>,</span><span style="font-family:宋体;">可以为操作所需的其他内部项分配缓存来提升</span><span>InnoDB</span><span style="font-family:宋体;">的性能。这些内存就可以通过此参数来分配。推荐此参数至少设置为</span><span>2MB</span><span style="font-family:宋体;">,实际上,是需要根据项目的</span><span>InnoDB</span><span style="font-family:宋体;">表的数目相应地增加</span><span> </span>



<span>2.innodb_data_pool_size </span>



<span style="font-family:宋体;">此参数类似于</span><span>MySQL</span><span style="font-family:宋体;">的</span><span>key_buffer</span><span style="font-family:宋体;">参数,但特定用于</span><span>InnoDB</span><span style="font-family:宋体;">表</span><span>.</span><span style="font-family:宋体;">这个参数确定了要预留多少内存来缓存表数据和索引。与</span><span>key_buffer</span><span style="font-family:宋体;">一样,更高的设置会提升性能,可以是服务器的内存</span><span>70-80% </span>



<span>3.innodb_data_file_path </span>



<span style="font-family:宋体;">参数的名字和实际的用途有点出入,它不仅指定了所有</span><span>InnoDB</span><span style="font-family:宋体;">数据文件的路径,还指定了初始大小分配,最大分配以及超出起始分配界线时是否应当增加文件的大小。此参数的一般格式如下</span><span>: </span>



<span>path-to-datafile:size-allocation[:autoextend[:max-size-allocation]] </span>



<span style="font-family:宋体;">例如,假设希望创建一个数据文件</span><span>sales</span><span style="font-family:宋体;">,初始大小为</span><span>100MB</span><span style="font-family:宋体;">,并希望在每次达到当前大小限制时,自动增加</span><span>8MB</span><span style="font-family:宋体;">(</span><span>8MB</span><span style="font-family:宋体;">是指定</span><span>autoextend</span><span style="font-family:宋体;">时的默认扩展大小</span><span>).</span><span style="font-family:宋体;">但是,不希望此文件超过</span><span>1GB</span><span style="font-family:宋体;">,可以使用如下配置</span><span>: </span>



<span>innodb_data_home_dir = </span>



<span>innodb_data_file_path = /data/sales:100M:autoextend:8M: max:1GB </span>



<span style="font-family:宋体;">如果此文件增加到预定的</span><span>1G</span><span style="font-family:宋体;">的限制,可以再增加另外一个数据文件</span><span>,</span><span style="font-family:宋体;">如下</span><span>: </span>



<span>innodb_data_file_path = /data/sales:100M:autoextend:8M: max:1GB;innodb_data_file_path = /data2/sales2:100M:autoextend:8M: max:2GB </span>



<span style="font-family:宋体;">要注意的是,在这些示例中,</span><span>inndb_data_home_dir</span><span style="font-family:宋体;">参数开始设置为空,因为最终数据文件位于单独的位置</span><span>(/data/</span><span style="font-family:宋体;">和</span><span>/data2/</span><span style="font-family:宋体;">)</span><span>.</span><span style="font-family:宋体;">如果希望所有</span><span> InnoDB</span><span style="font-family:宋体;">数据文件都位于相同的位置,就可以使用</span><span>innodb_data_home_dir</span><span style="font-family:宋体;">来指定共同位置,然后在通过</span><span> inndo_data_file_path</span><span style="font-family:宋体;">来指定文件名即可。如果没有定义这些值,将在</span><span>datadir</span><span style="font-family:宋体;">中创建一个</span><span>sales</span><span style="font-family:宋体;">。</span><span> </span>



<span>4 innodb_data_home_dir </span>



<span style="font-family:宋体;">此参数指定创建</span><span>InnoDB</span><span style="font-family:宋体;">表空间的路径的公共部分</span><span>,</span><span style="font-family:宋体;">默认情况下,这是</span><span>MySQL</span><span style="font-family:宋体;">的默认数据,由</span><span>MySQL</span><span style="font-family:宋体;">参数</span><span>datadir</span><span style="font-family:宋体;">指定</span><span> </span>



<span>5. innodb_file_io_threads </span>



<span style="font-family:宋体;">此参数指定</span><span>InnoDB</span><span style="font-family:宋体;">表可用的文件</span><span>I</span><span style="font-family:宋体;">/</span><span>O</span><span style="font-family:宋体;">线程数,</span><span>MySQL</span><span style="font-family:宋体;">开发人员建议在非</span><span>Windows</span><span style="font-family:宋体;">平台中这个参数设置为</span><span>4 </span>



<span>6. innodb_flush_log_at_trx_commit </span>



<span style="font-family:宋体;">如果将此参数设置为</span><span>1</span><span style="font-family:宋体;">,将在每次提交事务后将日志写入磁盘。为提供性能,可以设置为</span><span>0</span><span style="font-family:宋体;">或</span><span>2</span><span style="font-family:宋体;">,但要承担在发生故障时丢失数据的风险。设置为</span><span>0</span><span style="font-family:宋体;">表示事务日志写入日志文件,而日志文件每秒刷新到磁盘一次。设置为</span><span>2</span><span style="font-family:宋体;">表示事务日志将在提交时写入日志,但日志文件每次刷新到磁盘一次。</span><span> </span>



<span>7.innodb_log_archive </span>



<span style="font-family:宋体;">因为</span><span>MySQL</span><span style="font-family:宋体;">目前使用自己的日志文件恢复</span><span>InnoDB</span><span style="font-family:宋体;">表,此参数可设置为</span><span>0 </span>



<span>8.innodb_log_arch_dir </span>



<span>MySQL</span><span style="font-family:宋体;">目前忽略此参数,但会在未来的版本中使用。目前,应当将其设置为与</span><span>innodb_log_group_home_dir</span><span style="font-family:宋体;">相同的值</span><span> </span>



<span>9.innodb_log_buffer_size </span>



<span style="font-family:宋体;">此参数确定些日志文件所用的内存大小,以</span><span>M</span><span style="font-family:宋体;">为单位。缓冲区更大能提高性能,但意外的故障将会丢失数据</span><span>.MySQL</span><span style="font-family:宋体;">开发人员建议设置为</span><span>1</span><span style="font-family:宋体;">-</span><span>8M</span><span style="font-family:宋体;">之间</span><span> </span>



<span>10. innodb_log_file_size </span>



<span style="font-family:宋体;">此参数确定数据日志文件的大小,以</span><span>M</span><span style="font-family:宋体;">为单位,更大的设置可以提高性能,但也会增加恢复故障数据库所需的时间</span><span> </span>



<span>11.innodb_log_files_in_group </span>



<span style="font-family:宋体;">为提高性能,</span><span>MySQL</span><span style="font-family:宋体;">可以以循环方式将日志文件写到多个文件。推荐设置为</span><span>3M </span>



<span>12. innodb_log_group_home_dir </span>



<span style="font-family:宋体;">此参数确定日志文件组中的文件的位置,日志组中文件的个数由</span><span>innodb_log_files_in_group</span><span style="font-family:宋体;">确定,此位置设置默认为</span><span>MySQL</span><span style="font-family:宋体;">的</span><span>datadir </span>



<span>13.innodb_lock_wait_timeout </span>



<span>InnoDB </span><span style="font-family:宋体;">有其内置的死锁检测机制,能导致未完成的事务回滚。但是,如果结合</span><span>InnoDB</span><span style="font-family:宋体;">使用</span><span>MyISAM</span><span style="font-family:宋体;">的</span><span>lock tables </span><span style="font-family:宋体;">语句或第三方事务引擎</span><span>,</span><span style="font-family:宋体;">则</span><span>InnoDB</span><span style="font-family:宋体;">无法识别死锁。为消除这种可能性,可以将</span><span>innodb_lock_wait_timeout</span><span style="font-family:宋体;">设置为一个整数值,指示</span><span> MySQL</span><span style="font-family:宋体;">在允许其他事务修改那些最终受事务回滚的数据之前要等待多长时间</span><span>(</span><span style="font-family:宋体;">秒数</span><span>) </span>



<span>14.skip-innodb </span>



<span style="font-family:宋体;">启用此参数能防止夹杂</span><span>InnoDB</span><span style="font-family:宋体;">表驱动程序,不使用</span><span>InnoDB</span><span style="font-family:宋体;">表时推荐此设置</span><span> </span>

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注