mysql的8组后台线程
- master thread:主要负责将脏缓存页刷新到数据文件,执行purge操作,触发检查点,合并插入缓冲区等
- insert buffer thread:负责插入缓冲区的合并操作
- read thread:负责数据库读取操作,可配置多个读线程
- write thread:负责数据库写操作,可配置多个写线程
- log thread:用于将重做日志刷新到logfile中
- purge thread:purge thread执行purge操作
- lock thread:负责锁控制和死锁检测等
- 错误监控线程:主要负责错误监控和错误处理
查看这些线程的状态
show engine innodb status
内存优化原则
- 将尽量多的内存分配给mysql做缓存,但要给操作系统和其他程序的运行预留足够的内存,否则如果产生swap页交换,将严重影响系统性能
- MyISAM的数据文件读取依赖于操作系统自身的IO缓存,因此,如果有MyISAM表,就要预留更多的内存给操作系统做IO缓存
- 排序区、连接区等缓存是分配给每个数据库会话(session)专用的,其默认值的设置要根据最大连接数合理分配,如果设置太大,不但浪费内存资源,而且在并发连接较高时会导致物理内存耗尽
MyISAM存储引擎使用key buffer缓存索引快,以加速MyISAM索引的读写速度,对于MyISAM表的数据块,Mysql没有特别的缓存机制,完全依赖于操作系统的IO缓存。
将1/4可用内存分配给key_buffer_size。可以通过key_read_requests,key_reads, key_write_requests和key_writes等mysql状态变量来评估索引缓存的效率,一般来说,物理读比率key_reads/key_read_requests应小于0.01,物理写比率key_writes/key_write_requests也应尽可能小,对于更新和删除操作比较特别多的应用key_writes/key_write_requests可能会接近1,而对于每次更新很多行记录的应用,key_writes/key_write_requests就会比较小。
key_buffer使用率
1-((key_blocks_unused*key_cache_block_size)/key_buffer_size)
一般,使用率在80%左右比较合适,大于80%,可能因索引缓存不足而导致性能下降,小于80%,会导致内存浪费。
多个session共享key buffer,可能会出现多个session对key bufferd的竞争,从而出现性能问题,使用多个索引缓存可以将不同表的索引缓存到不同的key buffer中
创建新的key_buffer
set global hot_cache_name.key_buffer_size=128*1024;
删除key_buffer(默认的key_buffer是不让删的)
set global hot_cache_name.key_buffer_size=0;
查看key_buffer
show variable like 'key_buffer_size';
mysql采用LRU策略淘汰索引数据块,这种算法会出现一个问题,在某些情况下会导致真正的热块被淘汰。可以用多个索引缓存机制优化,也可以采用中点插入策略来优化索引块淘汰算法。将LRU链分成两个部分:hot子表和warm子表,这两个子表的尾部表示最热,头部表示最不热,hot子表的数据块一直不用就会降温,直到头部就降级为warm,反之,一直用,就升温,直到warm尾部,晋升至hot子表。
调整read_buffer_size和read_rnd_buffer_size,如果需要经常顺序扫描MyISAM表,可以通过增大read_buffer_size的值来改善性能,但是read_buffer_size是每个session独占的,如果太大,就会造成内存浪费,甚至导致物流内存耗尽。
如带有order by子句的sql,适当增大read_rnd_buffer_size的值。
InnoDB内存优化
InnoDB缓存机制是用内存区做IO缓存池,既缓存索引,也缓存数据
InnoDB缓存池结构
- free list:空闲缓存块列表
- flush list:需要刷新到磁盘的缓存块列表
- LRU list:正在使用的缓存块,是InnoDB buffer pool的核心
- young sublist
- old sublist
innodb_buffer_pool_size的设置,在保证操作系统及其他程序有足够的内存的情况下,innodb_buffer_pool_size越大,性能越高,一般是物理内存的80%
查看buffer pool的使用情况
mysqladmin -S /tmp/mysql.sock ext|grep -i innodb_buffer_pool
InnoDB缓存池的命中率:
(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_request)*100
如果命中率太低,应考虑扩充内存,增加innodb_buffer_pool_size的值。
调整old sublist大小
old sublist的比例由系统参数innodb_old_blocks_pct决定,取值范围5~95,默认37。
查看innodb_old_blocks_pct
show global variables like '%innodb_old_blocks_pct%';
在没有较大表扫描或者索引扫描的情况下,如果young/s的值很低,就要适当增大innodb_old_blocks_pct的值。
- 调整innodb_old_blocks_time的设置,innodb_old_blocks_time决定了缓存数据块由old sublist转移到young sublist的快慢,如果non-youngs/s很低,young/s很高,就应该考虑将innodb_old_blocks_time适当调大,以防止表扫描将真正的热数据淘汰。
- 调整缓存池数量,减少内部对缓存池数据结构的争用。高并发和buffer pool较大的情况下,mysql内部不同线程对InnoDB缓存池的访问在某些阶段是互斥的,内部竞争会产生性能问题。innodb_buffer_pool_instances调整可以解决这一问题,如果缓存池较大,则增大innodb_buffer_pool_instances的值,可以降低并发导致的内部缓存访问冲突,改善性能。
- 控制innodb buffer刷新,延长数据缓存时间,减缓磁盘I/O
- 缓存刷新:InnoDB找不到干净的可用缓存页或检查点被触发,InnoDB的后台线程就会开始吧“脏的缓存页”回写到磁盘文件中
InnoDB buffer pool的刷新快慢主要取决于两个参数
- innodb_max_dirty_pages_pct:控制缓存池中脏页的最大比例,默认75%,如果脏页的数量达到这个值,InnoDB的后台线程将开始缓存刷新,将innodb_io_capacity个缓存页刷新到磁盘
- innodb_io_capacity,它代表磁盘系统的IO能力,默认是200,如果是7200转的磁盘,可以降低到100,如果是固态硬盘,可以适当增大这个值。
当还没达到innodb_max_dirty_pages_pct时,如果innodb_adaptive_flushing的设置为true,InnoDB将根据函数buf_flush_get_desired_flush_rate返回的重做日志产出速度来确定要刷新的脏页数。在合并插入缓存时,InnoDB每次合并的页数是0.05*innodb_io_capacity。
如果innodb_buffer_pool_wait_free的值增长较快,则说明空闲的缓存少,此时可以增大缓存池,如果缓存池无法增大,可以将innodb_max_dirty_pages_pct调小,将innodb_io_capacity调大,加快脏页的刷新。
mysql的页是16K,操作系统是4K,如果写了4K进去,断电了,那么另外12K数据将丢失,无法恢复,mysql用 doublewrite解决这个问题,就是先将数据写入一个2M的缓存,此缓存是mysql的磁盘,然后将缓存的数据写入系统磁盘,这样一旦出问题,可以从2M的缓存中恢复。
查看InnoDB doublewrite是否开启,默认开启
show global variable like '%doublewrite%';
如果对性能要求非常高,且容忍极端情况下,少量数据丢失,那么可以关闭双写
innodb_doublewrite=0
调整用户服务线程排序缓存区,sort buffer和join buffer都是面向客户服务线程分配的,如果设置过大,可能造成内存浪费,甚至导致内存交换,尤其是join buffer,如果是多表关联的复杂查询,还可能会分配多个 join buffer,因此,最好的策略是设置较小的全局join_buffer_size,而对需要做复杂连续操作的session单独设置较大的join_buffer_size。
更新数据过程
- 将数据读入InnoDB buffer pool,并对相关记录加独占锁
- 将UNDO信息写入undo表空间的回滚段中
- 更改缓存页中的数据,并将更新记录写入redo buffer中
- 提交时,根据innodb_flush_log_at_trx_commit的设置,用不同的方式将redo buffer中的更新记录刷新到InnoDB redo log file中,然后释放独占锁;
- 最后,后台IO线程根据需要择机将缓存中更新过的数据刷新到磁盘文件中
LSN(Log Sequence Number)称之为日志序列号,新的LSN=旧的LSN+写入的日志大小
innodb_flush_log_at_trx_commit的设置
- 0:事务提交时,InnoDB不会立即触发将缓存日志写到磁盘文件的操作,而是每秒触发一次缓存日志回写磁盘操作,并调用操作系统fsync刷新IO缓存
- 1:在每次事务提交时,InnoDB立即将缓存中的redo日志回写到日志文件,并调用操作系统fsync刷新IO缓存
- 2:每个事务提交时,InnoDB立即将缓存中的redo日志回写到日志文件,但并不马上调用fsync来刷新IO缓存,而是每秒只做一次磁盘IO缓存刷新操作。
innodb_flush_log_at_trx_commit默认为1,最安全,但是性能不好;设置0,如果数据库崩溃,最后1秒钟的事务重做日志可能会由于未及写入磁盘文件而丢失,这种方式是效率最高的,但也是最不安全的;设置2是两者的折中。
设置log file size,控制检查点
一般来说,半小时写满1个日志文件比较合适。
pager grep -i "Log sequence number"
show engine innodb status\G select sleep(60);show engine innodb status\G
nopager
select round((最后的日志号-开始的日志号)/1024/1024) as MB;
上面的命令可以计算出1分钟的日志大小,然后乘以30就是log_file_size的大小
调整innodb_log_buffer_size
默认是8MB,对于可能产生大量更新记录的大事务,增加innodb_log_buffer_size的大小,从而减少日志写磁盘操作。
调整max_connections,提高并发连接
默认值151,如果connection_errors_max_connections不为零,并且一直增长,说明不断有连接请求因数据库达到最大允许的值而失败,可以考虑增大max_connections的值,增大max_connetions时,也要注意评估open-files-limit的设置是否够用。
调整back_log
设置积压请求栈大小,默认50,最大不能超过900
调整table_open_cache
max_connections*N,N表示每个连接关联查询所涉及的表的最大个数,未执行flush tables命令的情况下,如果open_tables的值较大,就说明table_open_cache设置得太小,应适当增大。增加table_open_cache会增加mysql对文件描述符的使用量,也要注意open-files-limit的设置是否够用。
调整thread_cache_size
缓存客户服务线程的数量
设置innodb_lock_wait_timeout
参数innodb_lock_wait_timeout可以控制InnoDB事务等待行锁的时间,默认是50ms,可以动态设置。对于后台运行的批处理操作,可以将行锁等待超时时间调大,避免发生大的回滚操作。