360SDN.COM

首页/MySQL/列表

MySQL的innodb的读写参数优化【多核处理器 innodb_read_io_threads】

来源:  2018-08-03 11:37:24    评论:0点击:


(1)    读取参数,global buffer pool以及 local buffer 

 

      Innodb_buffer_pool_size,理论上越大越好,建议服务器50%~80%,实际为数据大小80%~90%即可;

 

      Innodb_read_io_thread,根据处理器内核数决定;

 

      Read_buffer_size;

 

      Sort_buffer_size

 

  (2)    写入参数

 

      Insert_buffer_size;

 

      Innodb_double_write;

 

      Innodb_write_io_thread

 

      innodb_flush_method

 

  (3)    与IO相关的参数

 

      Innodb_log_buffer_size

 

      innodb_flush_log_at_trx_commit

 

      innodb_file_io_threads

 

      innodb_max_dirty_pages_pct

 

     (4)缓存参数以及缓存的适用场景


在工作中可能遇到这样的情况,随着业务的增长,用户量也在逐渐增长,终究有一天,一到高峰期,数据库服务器CPU利用率直飚100%。

最简单的做法就是直接提升硬件性能,简单粗暴,直接有效。

假如我们最开始的服务器CPU核数是4,然后我们觉得4个有点扛不住,那就直接给他搞16个核。这下应该没啥问题了吧,用户访问站点貌似也比以前快了。但是不要高兴太早,当你打开任务管理器,你会发现利用率高的还是以前那几个核,或者说只有4个用的比较多,其它12位大爷感觉不接茬。

如果你去查一下innodb_read_io_threads和innodb_write_io_threads这两个变量的时候,你就回发现,它们的默认值是4。可以用以下语句查看这两个变量的值:

show variables like '%_io_threads'
 
那就往大放呗。如果你直接用set命令去修改,你就回发现,人家会告你这俩命令是只读变量。那好吧,只能在初始化文件中添加这两个变量了。找到mysql的安装目录,用管理员身份打开mysql.ini文件,在最后添加这么两句:

    innodb_read_io_threads=16
    innodb_read_write_threads=16

 

内存相关
  • innodb_buffer_pool_size 缓冲池,会缓冲索引页、数据页、undo页、插入缓冲、自适应哈希索引、innodb存储的锁信息、数字字典信息等
  • innodb_buffer_pool_instances 允许多个缓冲池实例,每页根据哈希平均分配到不同缓冲池实例中,减少数据库内部资源竞争,增加数据库并发处理能力
  • innodb_old_blocks_pct 确定modpoint位置,默认37,modpoint指新读取到的页放入LRU(最近最少使用算法)列表中的位置,modpoint之后的列表称为old列表,之前的称为new列表
  • innodb_old_blocks_time 表示页读入mid位置后需要等待多久才会被加入到LRU列表的热端
  • innodb_purge_batch_size 控制每次full purge回收的undo页的数量
  • innodb_change_buffer_max_size 控制change buffer最大使用内存数量
 
IO相关
  • MySQL 5.6 开始支持 Multi-Range Read (MRR),减少磁盘随机访问,将随机访问转化为较为顺序的访问,适用于 range, ref, eq_ref 类型的查询
    • 查看是否开启 show variables like '%optimizer_switch%' mrr=on,mrr_cost_based=off(off为不判断,总是开启)'
    • read_rnd_buffer_size控制键值的缓冲区大小,当大于该值时,执行器对已经缓存的数据根据RowID进行排序,并通过RowID来取得行数据,默认256K
  • 5.6 开始支持Index Condition Pushdown(ICP),取出索引的同时,判断是否可以进行条件过滤,过滤后再去获取记录,可以大大减少上层SQL对记录的索取,支持range,ref,eq_ref,ref_or_null类型的查询。
  • innodb_flush_log_at_trx_commit 用来控制重做日志刷新到磁盘的策略
    • 默认值为1,事务提交时必须调用一次fsync,将日志刷新到磁盘
    • 0,事务提交时不写入重做日志,这个操作仅在master thread中完成,master thread每1秒会进行一次重做日志文件的fsync操作
    • 2,事务提交时将重做日志写入重做日志文件,但仅写入文件系统的缓存中,不进行fsync操作
    • 优缺点,#1数据有保障但是依赖于磁盘的性能;#2在操作系统宕机时会造成事务丢失。#0&#2的设置都会使事务丧失ACID特性
  • innodb_purge_batch_size
  • innodb_max_purge_lag & innodb_max_purge_lag_delay
  • binlog_max_flush_queue_time
  • innodb_read_io_threads & innodb_write_io_threads,多核cpu可以通过这两个参数更有效的利用cpu性能
  • innodb_io_capacity 可以充分利用固态硬盘带来的高IOPS特性
  • innodb_purge_threads 将purge线程从master线程分离出来,提高cpu使用率提升存储引擎性能,innodb1.2之后可以设置多个purge线程
  • innodb_flush_neighbors 刷新邻接页,对于高iops的磁盘,建议关闭此特性
系统相关
  • innodb_fast_shutdown 0表示完成所有full purge和merge insert buffer,并将所有脏页刷新回磁盘;1表示不需要完成full purge和merge insert buffer,但要刷新脏页;2表示不执行以上所有操作,但将日志全部写入日志文件,下次启动时需要恢复(recovery)
  • innodb_force_recovery
SLOW QUERY 查询相关
  • long_query_time
  • log_slow_queries
  • log_queries_not_useing_indexes 开启后,没有用到索引的查询也会记录到slowlog里
  • log_throttle_queries_not_using_indexes 5.6.5新增,表示每分钟允许记录到slow log的未使用索引的sql语句次数
  • 可以使用mysqldumpslow命令分析slow log
  • log_output,有FILE和TABLE两种,动态修改且是全局的
数据安全性相关
  • sync_binlog=[N]表示每写缓冲多少次就同步到磁盘,1表示同步写磁盘的方式写二进制日志。默认为0,由操作系统决定同步
  • innodb_support_xa=1,可以保证二进制日志和InnoDB存储引擎数据文件的同步
  • innodb_log_file_size 指每个重做日志文件的大小,innodb1.2之前必须小于4G,之后扩大到512G
  • innodb_log_files_in_group 指定了日志文件组中重做日志文件的数量,默认为2
  • innodb_mirrored_log_groups 指定了日志镜像文件组的数量,默认为1
在线DDL相关
  • innodb_online_alter_log_max_size Online DDL 原理是在执行创建或删除操作的同时,将INSERT、UPDATE、DELETE 这类 DML 操作日志写入到一个缓存中,此参数控制缓存大小,默认128M


来源:https://yq.aliyun.com/articles/59492

 

为您推荐

友情链接 |九搜汽车网 |手机ok生活信息网|ok生活信息网|ok微生活
 Powered by www.360SDN.COM   京ICP备11022651号-4 © 2012-2016 版权