数据库的配置是基础, 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 算法分析 https://www.cnblogs.com/---wunian/p/9227586.html
大表连接一定要创建索引, 否则性能特别差, 不支持 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 年的开发经验可以加群找我要课堂链接 注意: 是免费的 没有开发经验误入哦)
来源: http://www.jianshu.com/p/6387958ea978