innodb读写优化
读优化相关参数:
innodb_buffer_poll_size;
innodb_thread_concurrency:
innodb会保存一定数量的操作系统线程,该值小于等于这个变量的值。一旦线程数到达了限制,额外的线程会以“先进先出”的方式,进入等待状态。等待线程数不会统计到并发执行线程数中。这个值的范围是0到1000。0是默认值,代表不限制线程数。关闭线程并发检查,同时也会关闭<>SHOW ENGINE INNODB STATUS输出ROW OPERATIONS部分的queries inside InnoDB
和queries 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也能保留。