innodb引擎几个比较重要的配置参数

原创 2013年12月04日 23:46:12

最近一直在学习mysql,以下内容是阅读mysql技术内幕--innodb存储引擎后做的笔记,主要是1-3章的内容


一.innodb和myisam的主要区别

innodb
      1. 支持事务
      2.锁粒度==>行级别
      3.支持mvcc多版本并发控制
      4.不支持地理空间
      5.最大支持64TB存储
      6.支持哈希索引
      7.不支持全文索引(可以结合sphinx)
      8.支持聚集索引
      9.不支持压缩数据
      10.支持外键
      11.支持缓冲数据
myisam
      1.不支持事务
      2.锁粒度==>表级别
      3.不支持mvcc多版本并发控制
      4.支持地理空间
      5.没有存储限制
      6.不支持哈希索引
      7.支持全文索引
      8.不支持聚集索引
      9.支持压缩数据
      10.不支持外键
      11.不支持缓冲数据

二.innodb相关重要配置参数

innodb中大量使用了AIO(Async IO)来处理写IO请求,这样可以极大提高数据库的性能。而IO Thread的工作主要是负责这些IO请求的回调(call back)

innodb_read_io_threads
设置read thread(读线程个数,默认是4个)

innodb_write_io_threads
设置write thread(写线程个数,默认是4个)

事务被提交后,其所使用的undolog(撤销日志)可能不再需要,因此需要Purge Thread(清理线程)来回收已经使用并分配的undo页。
innodb1.1版本之前,purge操作仅在innodb引擎中的Master Thread中完成。而从innodb1.1版本开始。purge操作可以独立到单独的线程中进行。以此来减轻Master Thread的工作。提高cpu使用率以及提升存储引擎性能。
innodb_purge_thread = 1 
注意:在innodb1.1版本中,即使该值设置大于1,innodb启动时也会将其设置为1。并在错误文件中给出警告。
innodb1.2支持多个Pugre Thread线程。

innodb_buffer_pool_instances = 2
设置多个缓冲池实例,每个页根据哈希值平均分配到不同缓冲池实例中。好处在于减少数据库内部资源竞争。增加数据库并发处理能力。

innodb_old_blocks_pct
LRU算法,默认值是37,插入到LRU列表端的37%,差不多3/8的位置。innodb把midpoint之后的列表称为old列表,之前的列表称为new列表,可以理解为new列表中的页都是最为活跃的热点数据。

innodb_old_blocks_time
当第一次读取到的新页,innodb会根据innodb_old_blocks_pct设置的值将读取到的页插入LRU列表指定的midpoint(中间点)位置,如果没有修改,默认是37%,3/8的位置。防止将真正的new列表中的热点数据刷出。当再次读取到old列表中的页时也就是midpoint之后的页,innodb_old_blocks_time用于读取到old列表中的页时需要等待多久才会被加入到LRU列表的首部。防止new列中的热点数据被刷出。innodb_old_blocks_time默认值是0,没有等待。

innodb_log_buffer_size
默认是8M,用来缓冲日志数据的缓冲区的大小,当此值快满时, InnoDB将必须刷新数据到磁盘上. 由于基本上每秒都会刷新一次,所以没有必要将此值设置的太大(甚至对于长事务而言) 

innodb_max_dirty_pages_pct = 75
在InnoDB缓冲池中最大允许的脏页面的比例,当缓冲池中脏页的数量占据75%时,强制进行checkpoin,刷新一部分的脏页到磁盘。

innodb_io_capacity
表示磁盘io的吞吐量,默认值是200.对于刷新到磁盘页的数量,会按照inodb_io_capacity的百分比来进行控制。
规则如下:
在合并插入缓冲时,合并插入缓冲数量为innodb_io_capacity 值的5%
在从缓冲池刷新脏页时,刷新脏页的数量为innodb_io_capacity的值,也就是默认值200.
如果使用了ssd,或者raid磁盘时,磁盘拥有更高的io速度,可以适当增加该参数的值。

innodb_adaptive_flushing
自适应刷新脏页。默认已经开启。

innodb_purge_batch_size
在进行full purge时,回收Undo页的个数,默认是20,可以适当加大。

innodb_fast_shutdown
在关闭时,该参数影响表innodb存储引擎行为。参数可取值为0,1,2,默认值是1. 
0
  表示在mysql数据库关闭时,innodb需要完成所有的full purge,merge insert buffer,并且将所有脏页刷新回磁盘。如果在innodb进行升级时,必须将这个参数设为0.然后在关闭数据库.
1
  默认值,表示不需要完成full purge和merge insert buffer,但是在缓冲池中的一些数据脏页还是会刷新回磁盘。
2
  表示不完成full purge和merge inser buffer操作,也不将缓冲池中的数据脏页刷新回磁盘,而是将日志写入日志文件,这样不会有事务丢失,但是下次数据库启动时,会进行恢复操作(recovery)

innodb_force_recovery
该参数影响了整个innodb存储引擎的恢复状况。该参数默认值为0,代表发生需要恢复时,进行所有的恢复操作,当不能进行有效恢复时,如数据页发生了损坏,把错误写入错误日志。
还可以设置为6个非零值:1-6,大的数字包含了前面所有小数字的影响。具体如下
1
   忽略检查到的corrupt页
2
   阻止master thread线程运行。如master thred需要进行full purge操作,会导致crash。
3
   不进行事务的回滚。
4
   不进行插入缓冲的合并操作
5
   不查看撤销日志(undo log),innodb存储引擎会将未提交的事务视为已提交。
6
   不进行回滚的操作
注意:
    当该参数设置大于0以后,用户可以对表进行select,create,drop,但是innsert,update,delete这类DML操作却是不允许的。

以下参数影响着二进制日志记录信息的行为:
max_binlog_size
binlog_cache_szie
sync_binlog
binlog-do-db
binlog-ignore-db
log-slave-update
master==>slave==>slave 架构必须开启此参数(slave端开启)
binlog_format

其中比较重要的是sync_binlog参数,如果想让replication得到最大的可用性。最好将该值设置为1.表示采用同步写磁盘的方式来写二进制日志。不过有一点是会给IO带来一定压力。
当sysnc_binlog设置为1以后,还有一个参数需要设置。innodb_support_xa.也需要将该值设置为1.避免事务不能被回滚的问题。
当进行replication时,最好将二进制格式改为row,避免master使用了rand,uuid等函数,或者触发器,导致主从服务器数据不一致。通常将binlog_format设置为row格式。

innodb_data_file_path
表空间的设置,默认配置下有一个初始大小为10MB,名为ibdata1的文件。自动增长。
用户可以通过多个文件组成一个表空间,同时定制文件属性,设置如下:
    innodb_data_file_path= /db/ibdata1:2000M;/db2/ibdata2:2000M:autoextend
    若这两个文件位于不同磁盘上,磁盘的负载可能被平均。因此可以提高数据库整体性能。

innodb_file_per_table
默认没有开启
将每个基于innodb引擎的表使用独立的表空间。

以下参数影响重做日志文件属性:
innodb_log_file_size
重做日志文件的大小。innodb1.2以前,大小不得超过4G。1.2x以后可以最大到512G
innodb_log_files_in_group
指定了日志文件组中重做日志文件的数量。默认为2
innodb_mirrored_log_groups
指定了日志镜像文件组的数量,默认为1.表示只有一个文件组,没有使用镜像。若磁盘使用了类似磁盘阵列,可以不开启重做日志镜像功能。
innodb_log_group_home_dir
指定日志文件组所在路径。默认为./,表示在mysql数据库目录下。
注意:
    重做日志文件不能设置的太大,否则在恢复时可能需要很长的时间;如果设置的太小,可能导致一个事务日志需要多次切换重做日志文件。此外,重做日志文件太小会导致频繁的发生async checkpoint,导致性能抖动。


innodb_flush_log_at_trx_commit
该参数表示在提交操作时(commit),处理重做日志的方式。有效值有0,1,2。
0
   代表当提交事务时,并不将事务的重做日志写入磁盘上的日志文件,而是等待主线程(master thread)每秒刷新。
1,2
不同的地方在于:
    1 
      表示在执行commit时将重做日志缓冲同步到写到磁盘。既有fsync的调度。
    2 
      表示将重做日志异步写到磁盘,既写到文件系统的缓冲中。因此不能完全保证在执行commit时肯定会写入重做日志文件,只是有这个动作发生。
所以当为了保证事务的ACID中的持久性,必须将该值设置为1 。也就是每当有事务提交时,就必须确保事务都已经写入重做日志文件。

相关文章推荐

innodb purge操作

零散的记录,做备忘用 --------------------------------------------- 由于每次DML操作都会生成Undo页,系统需要定期对这些undo页进行清理,也...

Innodb buffer相关参数及查看方式(待续)

buffer相关参数: innodb_buffer_pool_size innodb_buffer 大小 innodb_buffer_pool_instances innodb_buffer inst...

innodb purge操作

Purge搜集的几个感觉不错的文章 一、 http://blog.sina.com.cn/s/blog_aed82f6f010195me.html 由于每次DML操作...

MySQL5.6版本性能调优my.cnf详解

[client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock basedi...

SSD 下的 MySQL IO 优化

https://dbarobin.com/2015/08/29/mysql-optimization-under-ssd/ 一 目录 一 目录二 背景三 SSD 特性四 基...
  • xtjsxtj
  • xtjsxtj
  • 2016年04月06日 13:38
  • 1756

innodb的读写参数优化

(1)    读取参数,global buffer pool以及 local buffer          Innodb_buffer_pool_size,理论上越大越好,建议服务器50...

每日一参数--innodb_old_blocks_pct、innodb_old_blocks_time理解

innodb缓存池有2个区域一个是sublist of old blocks存放不经常被访问到的数据,另外一个是sublist of new blocks存放经常被访问到的数据 innodb_old...

InnoDB缓存池介绍

InnoDB会在内存中维护一个缓冲池,用于缓存数据和索引。缓冲池分为两个区域,一个是sublist of new blocks区域(经常被访问的数据-热数据),一个是sublist of old ...

innodb buffer pool

InnoDB主索引是聚簇索引,索引与数据共用表空间,对于InnoDB而言,数据就是索引,索引就是数据。InnoDB缓存机制和MyISAM缓存机制的最大区别就是在于,InnoDB不仅仅是缓存索引,还会是...

开源MySQLMTOP配置系统资源监控功能

开源MySQLMTOP配置系统资源监控功能 MySQLMTOP是通过snmp协议进行数据采集的!所以需要在监控器开启snmp服务。 1 什么是snmp ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:innodb引擎几个比较重要的配置参数
举报原因:
原因补充:

(最多只允许输入30个字)