My studying notes for Java,Ruby,Ajax and other any interesting things.

星期二, 八月 01, 2017

mysql性能问题排查及故障诊断思路


思路:
——————————————————————— 
1.找到瓶颈处
2.针对瓶颈进行优化
3.数据库优化策略分为三步走:
  • 配置优化,cpu,内存,io
  • 表结构优化:表引擎,索引,表拆分(大表转小表,减少大字段,控制表数量及表大小),分表分库,主从分离,读写分离,多主
  • SQL优化:简化SQL,减少join,减少sql计算,减少存储过程,使用preparedstatement,找到慢sql逐个优化
4.程序优化:
  • 减少事务,降低锁的概率
  • 使用缓存应对读(但要保证数据的及时性)
  • 异步写操作(但要保证事务顺序和成功)


实践:
——————————————————————— 
寻找瓶颈,有两个思路:
  • 根据监控找到耗时较大的功能、业务:
    • 程序级别的监控,每个请求的时间记录,access_log或者程序在某个controller的切面中将时间打印出来;
    • 根据用户反馈,这个往往不准,因为用户看到慢的时候可能已经形成了雪崩效应;
    • 捋日志,找第一个慢的,或者找到累计最多的,然后单独测试该功能复现和模拟;
    • 原因可能会很多:比如数据库锁住了,内存不够,SQL性能太低....根据原因找;
  • 根据性能监控,查看资源使用情况
    • 在应用服务器,数据库服务器多方面监控资源使用情况,核心关注CPU,内存,IO和网络三点的使用情况
    • CPU忙:
      • 通常在进行计算,计算包括数据处理,图像,正则,科学计算等;从直观上来看,就是循环多,有很多for循环..
      • 常用监控命令:vmstat,mpstat
      • 如果要看某个进程的情况,可以使用"while :; do ps -eo pid,ni,pri,pcpu,psr,comm | grep 'firefox'; sleep 1; done" 类似的语句来查看
    • IO忙:大数据load到内存中,比如数据库、文件检索,本身匹配并不耗CPU,但是数据比较大,比较多需要频繁换页;
      • 监控换页数量,io读写次数,以及iowait数量
      • 常用命令:iostat,lsof
      • 需要注意,swap其实挺慢的,可以通过top、cat /proc/meminfo、vmstat 来监控swap的使用情况;
    • 网络监控:
      • 监控起来最复杂,主要监控网络流出和流入情况,以及丢包、延迟等;
      • 在linux下面可以使用:iptraf,netperf,tcpdump,tcptrace,tcpcopy
  • 工具:salt+zabbix,negios等都可以实现细粒度的监控;
  • 压力测试:针对某些怀疑的点进行压力测试,模拟发现对应的问题,同时检查系统的吞吐能力;
  • 常用工具及命令:Vmstat、sar、iostat、netstat、free、ps、top等
  • 常用组合方式
    • 用vmstat、sar、iostat检测是否是CPU瓶颈
    • 用free、vmstat检测是否是内存瓶颈
    • 用iostat检测是否是磁盘I/O瓶颈
    • 用netstat检测是否是网络带宽瓶颈

针对瓶颈优化:
  • 不同的问题原因不同,优化方案也不一样,通常来讲,分为代码优化和配置优化两条腿,配置优化又分为操作系统,服务和应用的优化。

mysql配置优化:
————————— 
  • 安装以后在mysql的配置文件中有:my-huge.cnf,my-mediam.cnf,my-small.cnf,可以根据需要自己拷贝一份做修改;/usr/local/share/mysql/
  • innodb_buffer_pool_size:数据缓冲池,是数据和索引缓存的地方,这个值越大越好,这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存),20-25GB(32GB内存),100-120GB(128GB内存);根据内存情况来设定。
  • innodb_log_file_size:redo日志的大小,用于确保写操作快速可靠,且在崩溃的时候可以恢复;默认512M,4G比较合适;
  • max_connections:最大连接数,默认151,根据实际情况扩大,但要注意程序端不会无限的增加(连接池)
  • innodb专属设置:
    • innodb_file_per_table,建议为on,每个表单独有自己的idb文件,可以再drop,truncate或者delete的时候会收表空间,如果表特别多的话,不建议使用(比如超过10k)
    • innodb_flush_log_at_trx_commit,是否支持事务一致性,通常为1,当性能要求大于数据一致性要求是可以设为0,比如从库;
    • innodb_flush_method,数据和日志写入方式,如果有raid,且不会down机,使用O_Driect,否则用fdatasync,可以用sysbench来测试;
    • inodb_log_buffer_size,为未执行的事务分配的缓存,默认1M,可以根据innodb_log_waits状态来看,如果不够的话可以适当增加;
  • 其他设置:
    • query_cache_size,查询缓存,这个可能会有坑,建议设为0,通过其他方式来提升缓存;
    • 设定主从:log_bin,
    • 是否跳过域名解析:skip_name_resolve,这样数据中只能使用ip来进行授权;
    • table_cache,表缓存,可以根据open_tables(show global status)来进行优化

mysql性能诊断命令:
  • show global status;show status like 'xxx';
  • show variables
  • show innodb status
  • show processlist
  • show tables status
  • show processlist
  • 事务和锁的检查
mysql的慢查询开启:
  • slow_query_log,设置为on
  • long_query_time,超时时间,比如设置为1,就是1s
  • slow_query_log_file,记录慢日志的文件
  • log_queries_not_using_indexes,记录没有使用到索引的查询语句

根据某个语句进行优化:
  • desc(explain) 某条语句,查看执行计划,可以看到该语句是否使用了索引,临时表、文件排序检索行数等;
  • 设置profiling,然后执行语句,查看profiling的日志:set profiling=1;execute sql;show profiles;show profile for query 1;


mysql结构优化:
————————— 
  • 每个表有主键,
  • 字段简单:
    • 类型选择好,
      • 数字:尽量用整型,包括枚举、时间、ip等都可以数字化,decimal少用
      • 文本:varchar/char,禁用text
      • 时间:性能考虑可以用bigint,否则用timestamp(不用datetime)
    • 二进制、文本、图片等不要用数据库存储,大字段可以分开表存放,减少io;
    • 字符集编码统一,join的时候可能会出现字符集不匹配无法使用索引的问题;
    • 尽量not null
    • 可以适当冗余字段,减少join
    • 拆表拆库:主要将资源分配到更多cpu和内存上,对结构简化有帮助
    • 单表不超过:1000w,有字符串的尽量在500w以内,单库不超过400张表;字段在30-50个;
    • 每个表都有主键,且建议为数据库自增int
使用语句优化:
————————— 
  • 原则:每个sql尽量小,尽量不用大事务,
  • 尽量每条语句都要是用到索引
  • 语句中尽量少join
  • 在语句中少使用子查询,如果一定要,用join比子查询好;
  • 不用函数或在SQL中进行计算,包括类型转换
  • 少用or
  • union all 代替union
  • 少select *,而是需要什么查询什么
  • 尽早过滤
  • 尽量少排序
  • 优先优化数量多,性能没那么好的SQL;再优化频率低,杀伤力大的sql
  • 大量数据插入用load data,且放到低峰期执行
索引创建合理:
  • 索引数量不是越多越好,而是用得越多越好
  • 索引字段不要在sql中计算,可能无法使用索引
  • 尽量不用外键等约束,由程序来保障,避免高并发死锁

程序优化:
——————————————————— 
1.控制事务大小
2.充分利用应用服务器的资源
3.在应用端缓存,分布式、文件、本地都可以




Refer: