数据库的配置是基础、SQL优化最重要(贯穿始终,每日必做),由图可知,越往上优化的面越小,最基本的SQL优化是最重要的,往上各个参数也没太多调的,也不可能说调一个innodb参数性能就会好多少,而动不动就加配置那更是不对的
Ⅰ、数据库配置
1.1 关于内存的
innodb_buffer_pool_size = 总内存的60%~80%甚至更高innodb_buffer_pool_instance = cpu数量或者一半innodb_page_size = 不要设置太小,现在的业务保持默认8k或者设置为16kinnodb_flush_method = O_DIRECT 必须设置,避免做两次缓存,不设置性能可能会好一些,但实际用了额外内存5.7.6 online resize buffer poolsetglobalinnodb_buffer_pool_size=256*1024*1024setglobalinnodb_disable_resize_buffer_pool_debug =off; 云数据库中用的多
1.2 关于刷新的
innodb_io_capacity= write的性能 fuzzy checkpoint 刷部分脏页,对系统影响较小,5.6独立刷新线程,5.7并行刷新线程innodb_page_cleaners= cpu的数量或者一半innodb_fast_shutdown=1innodb关闭时候,bp中dirty page刷但磁盘上 sharp checkpoint刷新全部脏页,系统hang住innodb_flush_neighbors= ssd设置为0
1.3 redo
记录page的操作日志,与二进制日志完全不同,它是循环覆盖写的,默认没有类似pg或者oracle的归档
innodb_log_file_size=4G5.6版本至少4G,存储性能非常好可以设置为8G或者16Ginnodb_log_buffer_size=8Minnodb_log_files_in_group=3innodb_log_group_home_dir= /redolog/
1.4 undo
undo段,实现回滚,实现mvcc功能,不怎么需要调整,问题不大
undo段的数量 5.5之前1024,5.5开始128*1024
undo的回收是purge来做
innodb_undo_directory=/undolog/innodb_undo_logs=128innodb_undo_table_spaces=3innodb_undo_log_truncate=1innodb_max_undo_log_siz=1Ginnodb_purge_reseg_truncate_frequency=128innodb_purge_batch_size=300innodb_purge_threads=4/8这个参数可以稍微多开几个，回收undo，真正删除记录会快一点
1.5 开启线程池
并发上千的情况下,线程池开和不开性能会相差很大(近百倍),秒杀等业务,还要做双保险,前端和数据库都做一层限流,前端可以用redis,数据库开线程池,保障高并发下的性能平稳
MariaDB线程池没有优先级队列,推荐MySQL/InnoSQL/Percona线程池
thread_handling=pool-of-threadsthread_pool_size=32thread_pool_oversubscribe=3extra_port=3333
1.6 日志配置
binary log、error log、slow log、general log(通常不推荐,用P_S中events_statements_current、events_statements_history、events_statements_history_long等来代替)
log_output= fileslow_query_log=1slow_query_log_file= slow.loglong_query_time=2min_examined_row_limit=100log_queries_not_using_indexes=1log_slow_admin_statements=1log_slow_slave_statements=1在从上开启慢日志,意义不大log_throttle_queries_not_using_indexes=10log_timestamps= +08:00不要设置system,性能会有损失,详见阿里数据库内核月报bind_address= xxx.xxx.xxx.xxx 绑定ip
Ⅱ、SQL优化
这里咱们都默认是简单查询只提两个重点
2.1 子查询
对于in,5.6之前是lazy(rewrite to exsits,poor performance)的,5.6开始(MariaDB 5.3)会弄成semi-join,固性能基本没问题
(root@localhost) [performance_schema]> show variableslike'optimizer_switch'\G***************************1.row ***************************Variable_name: optimizer_switch Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on1rowinset(0.00sec)
但是对exists,永远是相关子查询,性能比较糟糕,看exists后面的子查询有没有反复的迭代操作,有的话就很差,in的话问题不大
2.2 join
MySQL只支持nested-loop-join
具体算法等详见join算法分析
大表连接一定要创建索引,否则性能特别差,不支持hash join
行号问题,千万别弄子查询做,会变为相关子查询
rank排名的实现,尽量不要在MySQL中做,一般在redis里面搞
Ⅲ、软硬件配置
3.1 内存
关闭numa(Non-uniform Memory Access——非一致存储访问结构)
numa的内存分配策略有四种
linux下default 总是在本地节点分配内存
bind 强制分配到指定节点上
interleave 在所有节点或者指定节点上交织分配
preferred 在指定节点上分配,失败则在其他节点上分配
如果只在本地分配内存,那就存在还有内存,MySQL却oom了的情况,所以要用interleave模式来启动mysql
oom的时候发现系统还有剩余内存，首先考虑这个问题
yuminstall-y numactlnumactl--interleave=all mysqld&
写到启动脚本里去 vim /etc/init.d/mysql.server
单实例MySQL关闭numa的几种方法
MySQL启动时关(如上)
BIOS中关
系统启动关闭
vim/boot/grub/grub.conf numa=off
多实例MySQL可通过numa绑定指定cpu
numactl--hardwarenumactl -cpubind=0-localalloc
tips：
MySQL5.7版本推出了一个参数叫innodb_numa_interleave 默认off
很奇怪,5.7.9就说可以设置为interleave,但是到现在为止还是没这个参数,要自己编译才会有,二进制版本是没有的,5.7.17已经OK
另外请把swap关掉
echo"vm.swappiness=0">> /etc/sysctl.conf
3.2 网卡软中断
qps不超过一万基本上遇不到,淘宝双十一一台机器qps达到40w,现在可以跑到100w,这时候网卡就是瓶颈
这个优化通常不在数据库中做,数据库打不满CPU,一般在redis和memcached中做
top看下,cpu某个核的soft非常高,cpu要做上下文切换,会把整个性能拖慢,特别是在缓存系统中
解决方案：
启用网卡多队列,跑谷歌的一个脚本,set_irq_affinity.shservice irqbalancestop操作系统自带的中断平衡的服务关掉,它并不能平衡
3.3 RAID卡
现在一般都是lsi的raid卡
BBU
Battery Back Unit,非低端RAID卡都带BBU,需要电池保证写入的可靠性,电池有充放电时间
RAID卡缓存
Write Backup、Write Through,写缓存并非默认开启,打开性能会好很多
查看电量百分比
megacli -AdpBbuCmd -GetBbuStatus -aALL |grep"Relative State of Charge"
查看充电状态
megacli -AdpBbuCmd -GetBbuStatus -aALL |grep"Charger Status"
查看缓存策略
megacli -LDGetProp -LAll-a0
3.4 SSD
磁盘调度算法设置为：deadline或者noop
innodb存储引擎设置
innodb_flush_neighbors=0innodb_log_file_size=4G
Ⅳ、文件系统与操作系统
4.1 文件系统
推荐xfs/ext4
noatime可以提升5%,nobarrier,接raid卡影响不大,不接的话,影响比较大,写到存储的缓存就返回,并不一定要写到存储系统,ssd内置了cache,写到cache就可以了
4.2 操作系统
推荐linux、关闭swap
noatime、nobarrier
mount-o noatime,nobarrier /dev/sdb1/data
{附}：大家可以点击加入群：【java高级架构进阶】：https://jq.qq.com/?_wv=1027&k=575y0Kj里面有Java高级大牛直播讲解知识点 走的就是高端路线（如果你想跳槽换工作 但是技术又不够 或者工作上遇到了瓶颈 我这里有一个JAVA的免费直播课程 讲的是高端的知识点基础不好的误入哟 只要你有1-5年的开发经验可以加群找我要课堂链接 注意：是免费的 没有开发经验误入哦）
网友评论