mysql优化

mysql优化重点方向:硬件层面和磁盘IO方面

服务器与系统具体优化事项

#修改bios
    开启DAPC,发挥cpu最大性能
    内存频率设为最大
    内存设置菜单中,启用node interleaving ,避免NUMA问题

#磁盘
    ssd
    若是磁盘存储阵列,建议同时配备cache及BBU模块,可明显提升IOPS(BBU模块可能要单独购买)
    raid级别尽量选择raid10,不建议使用raid5

#文件系统层优化
    使用deadline/noop这两种调度器,千万别用cfg
    使用xfs文件系统,千万别用ext3,ext4勉强可用,但业务量大的话一定要用xfs
    文件系统mount参数中增加:noatime,nodiratime,nobarrier几个选项(nobarrier是xfs文件系统特有的)

#内核参数优化
    修改vm.swappiness参数,降低swap使用率,rhel7/centos7以上的版本则慎用0,可能发生OOM,(注意:这个参数默认是60,像内存型环境如redis,建议设为10,不要设为0,上面提到了)
    调整vm.dirty_background_ratio    vm.dirty_ratio这两个参数,以确保能持续将数据刷新到磁盘,避免瞬间I/O写.产生等待
    调整net.ipv4.tcp_tw_recyle   net.ipv4.tcp_tw_reuse都为1,减少TIME_WAT,提供TCP效率

mysql参数优化建议

mysql参数优化建议
    建议设置默认存储引擎为innoDB,default-storage-engine=innoDB(mysql5.5之后不用调整,默认就是innoDB)

    调整innodb_buffer_pool_size的大小,如果是单实例且绝大多数是innoDB引擎表的话,可考虑设置物理内存的50%-70%左右    (缓存,用内存来存,越大越好)
    设置innodb_file_per_table=1,使用独立表空间(5.5版本是关闭的,5.6默认是开启的)
    调整innodb_data_file_path=ibdata1:1G:autoextend,不要使用默认的10M,在高并发场景下,性能会有很大的提升(ibdata1这是数据文件的名称,1g是大小,autoextend表示自动扩展)
    设置innodb_log_file_size=256M,设置innodb_log_files_in_group=2,基本可以满足大多数应用场景(回滚日志大小,后面有2个回滚日志的镜像)
    innodb_log_buffer_size 这项决定了为尚未执行的事务分配的缓存.默认值为1M,一般够用了,但是如果你的事务中包含有二进制大对象或则大文本字段的话,这点缓存很快就会被填满并触发额外的I/O操作.看看innodb_log_waits状态的变量,如果不是0,则需要增加innodb_log_buffer_size的大小

    另外,open_files_limit   innodb_open_files  table_open_cache  table_definition_cache可以设置大约为max_connection 的10倍左右大小
    key_buffer_size建议调小,32M左右即可,另外建议关闭query_cache
    mp_table_size 和 max_heap_table_size 设置不要过大,sort_buffer_size   join_buffer_size  read_buffer_size  read_rnd_buffer_size等设置也不要过大

    max_connections   最大连接数,如果你经常看到"too many connections"错误,(极有可能是程序有bug,没有释放连接)
    innodb_flush_log_at_trx_commit 默认是1,表示每次事务的提交或事务外的指令都需要把日志写入flush硬盘,这很费时,但是保证了数据的安全性,当值为2时,允许数据有一点丢失,工作流程,不是直接写道硬盘,而是先写入缓存,内存等,再利用文件系统的机制写入硬盘,这对数据的安全时要求不高的,(如备份节点),如果值为0,速度就更快了,但系统崩溃时可能丢失一些数据,只适用与备份节点

    innodb_flush_method 这项配置决定了数据和日志写入硬盘的方式,有三个值.默认是fdatasync ,还有O_DYNC  O_DIRECT   (注意:如果有断电保护的功能,建议设置为O_DIRECT ,因为这是性能最好的)

    query_cache_size (查询缓存,是一个众所周知的瓶颈,甚至并发不多时也是如此)最佳选项是将其从一开始就停用,设置query_cache_size=0 (mysql5.6的默认值),并利用其他方法加速查询,优化索引,增加拷贝,分散负载或则启用额外的缓存(比如memcache或redis).如果你已经为应用启用了query_cache_size并且还没有发现任何问题,query_cache_size可能对你有用.这时如果你想停用它,就需要特别小心了

    log_bin 启用二进制日志记录功能,适用两个场景如mysql的主从同步,以及可以恢复数据(如果长期开启,该文件可能很大很大,需要清除,有两种方法,设置过期时间:expire_logs_days,二是手动清除 purge binary logs)(如果不是复制环境,建议关闭,对性能有很大的影响)
    skip_name_resolve 当客户端连接数据库时,服务器会进行主机名解析,并且当dns很慢时,建立连接也会很慢,因此建议在启动服务器时关闭skip_name_resolve 选项而不进行dns查找,唯一的局限是之后 GRANT 语法中只能使用IP 地址了 ,因此添加此项到一个已有的系统中必须格外小心


扩展:
    innodb_buffer_pool_size:这是你安装完innoDB中第一个设置的地方,缓冲池是数据和索引缓存的地方
声明:本文为原创,作者为 辣条①号,转载时请保留本声明及附带文章链接:https://boke.wsfnk.com/archives/508.html
微信打赏微信打赏

如果文章对你有帮助,欢迎点击上方按钮打赏作者

最后编辑于:2020/2/19作者: 辣条①号

暂无评论

发表评论

电子邮件地址不会被公开。 必填项已用*标注