innodb读写优化

innodb读写优化

读优化相关参数:

innodb_buffer_poll_size;

innodb_thread_concurrency:

innodb会保存一定数量的操作系统线程,该值小于等于这个变量的值。一旦线程数到达了限制,额外的线程会以“先进先出”的方式,进入等待状态。等待线程数不会统计到并发执行线程数中。这个值的范围是0到1000。0是默认值,代表不限制线程数。关闭线程并发检查,同时也会关闭<>SHOW ENGINE INNODB STATUS输出ROW OPERATIONS部分的queries inside InnoDBqueries in queue counters值。

 

写优化相关参数:

innodb_flush_log_at_trx_commit(redo log事务日志);sync_binlog(bin log);

innodb_log_write_ahead_size:

write-ahead块大小用于redo log,单位是字节。为了避免“read-on-write”,设置innodb_log_write_ahead_size值和操作系统或文件系统缓存块大小一致。Read-on-write发生在由于write-ahead块大小和系统块或文件系统块大小不一致,redo log块不能全部缓存到系统或文件系统中。

有效值是InnoDB日志块大小的整数倍(2^n)。最小值是512。当设置了最小值时,write-ahead不会发生。最大值等于innodb_page_size。如果设置的值大于innodb_page_size该值会被截断到innodb_page_size大小。

设置该值太小会引发“read-on-write”。设置太大会因为几个块一次写入,对fsync有略微影响。

innodb_io_capacity

  • Innodb每次刷多少个脏页,决定InnoDB存储引擎的吞吐能力
  • 在SSD等高性能存储介质下,应该提高该参数以提高数据库的性能。

innodb_change_buffering和innodb_change_buffer_max_size

innoDB是否执行change buffering,这个优化可以延迟写操作。SQL语句通常 是调用随机IO操作,这个参数可以阻止随机IO并在后台以一个线程周期的执行。被阻止的操作可以组成操作序列,操作序列可以利用索引更高效的执行,而不是每个值立即写到磁盘中。

change buffer:一种特殊的数据结构,它将改变的记录存入secondary indexes页中。这个值可以由INSERT, UPDATE, 或DELETE语句产生。

当相来自secondary index的相关页不在buffer pool中时,改变的数据只存入change buffer中。当相关的索引页被放入buffer pool中时,对于这些数据的改变在buffer pool中发生。当系统空闲或由于过于缓慢关闭时,周期的运行purge操作,写新索引页到磁盘。purge操作能利用索引值高效的写磁盘块。

change buffer是系统表空间的一部分,所以即使数据库重启index change也能保留。

发表评论