一、性能诊断方法论

1、性能问题

  • 如何确认服务器是否达到了性能最佳状态
  • 找出某条语句为什么执行不够快
  • 卡死等某些间歇性疑难故障
  • 周期性变化还是偶尔
  • 检查mysql的io和cpu利用比例
效果    ------------------------->
硬件、系统配置、数据库表结构、索引
<-------------------------    成本

2、解决方案

2.1 测量任务所花费的时间

  • 执行时间
    • 服务器需要做大量的工作，从而导致大量消耗CPU
    • 可以参考 Percona Toolkit中的pt-collect
  • 等待时间
    • 在等待某些资源被释放
    • GDB的堆栈跟踪
    • pt-pmp剖析器

      2.2 对结果进行统计和排序，将重要的任务排到前面

      3、对应用程序进行性能剖析

      3.1 影响因素

  • 外部资源，比如调用了外部的Web服务或搜索引擎
  • 应用需要处理大量的数据，比如分析一个超大的XML文件
  • 在循环中执行昂贵操作，比如滥用正则
  • 使用了低效算法，比如暴力搜索算法

    3.2 工具

    New Relic的软件即服务(software-as-a-service)产品

    4、剖析MySQL查询

    4.1 剖析服务器负载

  • 捕获查询到日志文件中
  • 分析查询日志

    4.2 剖析单条查询

  • 使用 show profile测量耗费时间和查询执行状态变更相关数据
  • 使用慢查询日志
  • 使用Performance Schema
  • 使用 show status

    4.3 使用性能剖析

    5、诊断间歇性问题

    5.1 单条查询问题还是服务器问题

  • 使用show global status
  • 使用show processlist
  • 使用查询日志

    5.2 捕获诊断数据

    5.2.1 一个可靠且实时的触发器，就是什么时候问题会出现

    可以使用工具 Percona Toolkit的pt-stalk

    5.2.2 收集什么样的数据

  • 系统状态
  • CPU利用率
  • 磁盘使用率和可用空间
  • ps的输出采样
  • 内存利用率

    5.2.3 解释结果数据

  • 检查问题是否真的发生了，避免误报
  • 是否有非常明显的跳跃性变化
  • 将Percona Toolkit中pt-mysql-summary和pt-summary的输出结果打包，用pt-sift快速检查收集到的样本数据
  • 什么导致资源性能低下
    • 资源过度使用，余量不足以正常工作
    • 资源没有被正确配置
    • 资源已经损坏或者失灵

      6、其他剖析工具

  • 使用USER_STATISTICS表
    • 可以查找使用得最多或者使用得最少的表和索引
    • 可以查找出从未使用的索引，可以考虑删除之
    • 可以看看复制用户的CONNECTED_TIME和BUSY_TIME，以确认复制是否会很难跟上主库的进度
  • 使用strace
    • 调查系统调用情况

二、优化方向

1. 服务器及OS优化

1.1 文件系统

  • 文件数限制: /etc/security/limit.conf
    • soft nofile 65535
    • hard nofile 65535
  • 磁盘调度策略: /sys/block/devname/queue/scheduler

  • 使用deadline/noop这两种I/O调度器，千万别用cfq（它不适合跑DB类服务）；

      echo deadline > /sys/block/devname/queue/scheduler

  • 使用xfs文件系统，千万别用ext3；ext4勉强可用，但业务量很大的话，则一定要用xfs；

    • ext4
        echo '/dev/sda1/ext4 native,nodiratime,data=writeback 1 1' >> /etc/fstab
  • 文件系统mount参数中增加：noatime, nodiratime, nobarrier几个选项（nobarrier是xfs文件系统特有的）；

1.2 内核参数

修改/etc/sysctl.conf

  • 增加tcp支持的队列数
    • net.ipv4.tcp_max_syn_backlog = 65535
  • 减少断开连接时，资源回收
    • net.ipv4.tcp_max_tw_backets = 8000
    • net.ipv4.tcp_tw_reuse = 1
    • net.ipv4.tcp_tw_recycle = 1
    • net.ipv4.tcp_fin_timeout = 10
  • 将vm.swappiness设置为5-10左右即可，甚至设置为0（RHEL 7以上则慎重设置为0，除非你允许OOM kill发生），以降低使用SWAP的机会；
  • vm.dirty_background_ratio设置为5-10，将vm.dirty_ratio设置为它的两倍左右，以确保能持续将脏数据刷新到磁盘，避免瞬间I/O写，产生严重等待（和MySQL中的innodb_max_dirty_pages_pct类似）；
  • 将net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse都设置为1，减少TIME_WAIT，提高TCP效率；

另外，可以参考Oracle的内核参数的调整

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
#kernel.shmmax = 4398046511104  //一般设置为系统内存75%单位是字节
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

1.3 硬件提升

  • BIOS的调整
    • 选择Performance Per Watt Optimized(DAPC)模式，发挥CPU最大性能，跑DB这种通常需要高运算量的服务就不要考虑节电了；
    • 关闭C1E和C States等选项，目的也是为了提升CPU效率；
    • Memory Frequency（内存频率）选择Maximum Performance（最佳性能）；
    • 内存设置菜单中，启用Node Interleaving，避免NUMA问题；
  • CPU
    • 非计算密集型 - 多核
    • 计算密集型 - 高频
    • 如何选择CPU
      • MySQL 有一些工作只能使用到单核CPU
      • MySQL 对CPU多核的支持并不是核数越多就越快
  • 内存
  • 磁盘
    • 使用SSD或者PCIe SSD设备，至少获得数百倍甚至万倍的IOPS提升；
    • 购置阵列卡同时配备CACHE及BBU模块，可明显提升IOPS（主要是指机械盘，SSD或PCIe SSD除外。同时需要定期检查CACHE及BBU模块的健康状况，确保意外时不至于丢失数据）；
    • 有阵列卡时，设置阵列写策略为WB，甚至FORCE WB（若有双电保护，或对数据安全性要求不是特别高的话），严禁使用WT策略。并且闭阵列预读策略，基本上是鸡肋，用处不大；
    • 尽可能选用RAID-10，而非RAID-5；
    • 使用机械盘的话，尽可能选择高转速的，例如选用15KRPM，而不是7.2KRPM的盘，不差几个钱的；
    • 常用RAID级别简介
      • RAID0: 也称为条带，就是把多个磁盘链接为一个磁盘使用，这个级别IO最好
      • RAID1: 也称为镜像，要求至少两个硬盘，第个磁盘的数据都是一样的
      • RAID5: 也是把多个磁盘当作一个磁盘使用，至少3块硬盘，数据读写时会建立奇偶校验信息，并且奇偶检验信息和相对应的数据分别存储于不同的磁盘上，当RAID5的一个磁盘数据发生损坏时，利用剩下的数据和相应的奇偶检验信息去恢复被损坏的数据是完全没有问题的
      • 推荐使用RAID1+0：就是RAID1和RAID0的结合，同时具备两个级别的优缺点。一般建议数据库使用这个级别
  • 网络
    • 万兆网卡

1.4 my.cnf启动顺序

注意：如果多个位置存在 配置文件，则后面的会覆盖前面的

[root@nazeebodan ~]# mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

2. MySQL参数调整

2.1 内存参数

  • 线程独享
    • sort_buffer_size
    • join_buffer_size
    • read_buffer_size
    • read_rnd_buffer_size
  • 线程共享
    • innodb_buffer_pool_size
    • key_buffer_size
    • tmp_table_size
    • max_head_table_size

2.2 事务日志相关

  • innodb_log_file_size
  • innodb_log_files_in_group
  • innodb_log_buffer_size
  • innodb_flush_log_at_trx_commit

2.3 IO参数

  • innodb_flush_method = O_DIRECT
  • innodb_file_per_table = 1
  • innodb_doublewrite = 1
  • delay_key_write
  • innodb_read_io_threads
  • innodb_read_io_threads
  • innodb_io_capacity
  • innodb_flush_neighbors
  • sync_binlog

2.4 其他参数

  • expire_logs_days
  • max_allowed_packet
  • skip_name_resolve
  • read_only
  • skip_slave_start
  • sql_mode
  • max_connections

2.5 最重要的参数选项调整建议

  • 选择Percona或MariaDB版本的话，强烈建议启用thread pool特性，可使得在高并发的情况下，性能不会发生大幅下降。此外，还有extra_port功能，非常实用， 关键时刻能救命的。还有另外一个重要特色是 QUERY_RESPONSE_TIME 功能，也能使我们对整体的SQL响应时间分布有直观感受；
  • 设置default-storage-engine=InnoDB，也就是默认采用InnoDB引擎，强烈建议不要再使用MyISAM引擎了，InnoDB引擎绝对可以满足99%以上的业务场景；
  • 调整innodb_buffer_pool_size大小，如果是单实例且绝大多数是InnoDB引擎表的话，可考虑设置为物理内存的50% ~ 90%左右；
  • 根据实际需要设置innodb_flush_log_at_trx_commit、sync_binlog的值。如果要求数据不能丢失，那么两个都设为1。如果允许丢失一点数据，则可分别设为2和10。而如果完全不用care数据是否丢失的话（例如在slave上，反正大不了重做一次），则可都设为0。这三种设置值导致数据库的性能受到影响程度分别是：高、中、低，也就是第一个会另数据库最慢，最后一个则相反；
  • 设置innodb_file_per_table = 1，使用独立表空间，我实在是想不出来用共享表空间有什么好处了；
  • 设置innodb_data_file_path = ibdata1:1G:autoextend，千万不要用默认的10M，否则在有高并发事务时，会受到不小的影响；
  • 设置innodb_log_file_size=256M，设置innodb_log_files_in_group=2，基本可满足90%以上的场景；
  • 设置long_query_time = 1，而在5.5版本以上，已经可以设置为小于1了，建议设置为0.05（50毫秒），记录那些执行较慢的SQL，用于后续的分析排查；
  • 根据业务实际需要，适当调整max_connection（最大连接数）、max_connection_error（最大错误数，建议设置为10万以上，而open_files_limit、innodb_open_files、table_open_cache、table_definition_cache这几个参数则可设为约10倍于max_connection的大小；
  • 常见的误区是把tmp_table_size和max_heap_table_size设置的比较大，曾经见过设置为1G的，这2个选项是每个连接会话都会分配的，因此不要设置过大，否则容易导致OOM发生；其他的一些连接会话级选项例如：sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等，也需要注意不能设置过大；
  • 由于已经建议不再使用MyISAM引擎了，因此可以把key_buffer_size设置为32M左右，并且强烈建议关闭query cache功能；

3. SQL优化

SQL优化内容较多，单独一章

4. 表架构优化

4.1 选择优化的数据类型

数据类型的选择重点在于合适二字，如何确定选择的数据类型是否合适？

  • 使用可以存下你的数据的最小的数据类型
  • 使用简单的数据类型。int要比varchar类型处理更加简单
  • 尽可能的使用not null定义字段
  • 尽量少使用text类型，非用不可时最好考虑分表

4.1.1 浮点类型

  • 精确
    • DECIMAL
    • MySQL自身实现，运算较慢
  • 不精确
    • DOUBLE、FLOAT
    • CPU直接支持，运算较快
  • 提升效率方法
    • 在数据量较大时，使用BIGINT代替DECIMAL。乘以相应倍数即可。 *

      4.1.2 VARCHAR和CHAR类型

  • VARCHAR
    • 字符串列的最大长度比平均长度大很多
    • 列的更新很少，所以碎片不是问题
    • 使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储
  • CHAR
    • 存储很短的字符串
    • 经常变更

      4.1.3 BLOB和TEXT类型

存储类型 是否有排序规则和字符集
BLOB 二进制 否
TEXT 字符 是

4.1.4 使用枚举代替字符串类型

  • 优点
    • ENUM和ENUM关联会很快
  • 缺点
    • 避免使用数字作为枚举常量，双重性容易导致混乱
    • 字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE

4.2 表设计原则

  • 更小的通常更好
    • 字段长度满足需求前提下，尽可能选择长度小的。此外，字段属性尽量都加上NOT NULL约束，可一定程度提高性能
  • 简单就好
    • 尽可能不使用TEXT/BLOB类型，确实需要的话，建议拆分到子表中，不要和主表放在一起，避免SELECT * 的时候读性能太差。
    • 读取数据时，只选取所需要的列，不要每次都SELECT *，避免产生严重的随机读问题，尤其是读到一些TEXT/BLOB列；
  • 尽量避免NULL
  • 查询越频繁的表应该设计越简单
  • 查询越频繁的关联表应该多考虑冗余

4.3 表的范式优化

  • 范式化是指数据库设计的规范，目前说到范式化一般就是指第三设计模式，要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖规则符合第三范式

4.4 表的反范式优化

  • 反范式化是指为了查询效率的考虑把原本符合第三范式的表适当的增加冗余，以达到优化查询效率的目的，反范式化是一种以空间换时间的操作

4.5 数据库表的垂直拆分

  • 所谓垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。
  • 通常垂直拆分可以按以下几个原则进行：
    • 把不常用的字段单独放到一个表中
    • 把大字段独立存入到一个表中
    • 把经常一起使用的字段放到一起

4.6 数据库表的水平拆分

5. 索引优化

5.1 如何选择合适的列建立索引

  • 在where从句，group by从句，on 从句中出现的列
  • 索引字段越小越好
  • 离散度大的列放到联合索引的前面

5.2 索引优化SQL的方法

重复及冗余的索引

重复索引是指相同的列以相同顺序建立的同类型的索引，如下表中primary key和ID列上的索引就是重复索引

create table test(
    id int not null primary key,
    name varchar(10) not null,
    title varchar(50) not null,
    unique(id)
) engine = innodb;

推荐使用工具：pt-duplicate-key-checker 使用方法

pt-duplicate-key-checker -uroot -p123456 -p3306 -hnazeebo

复合索引

最常用的放在最前面，与where顺序无关

5.3 索引维护的方法

  • 索引是不可更改的，想更改必须删除重新建
  • 目前MySQL官方还没有记录索引使用情况的功能，但是在PerconMySQL和MariaDB中可以通过INDEX_STATISTICS表查看那些索引没有被使用，但是在MySQL中目前只能通过慢查询日志配合pt-index-usage工具来进行索引使用情况的分析
pt-index-usage -uroot -p'passwd' mysql-slow.log

6.管理维护方向的优化

  • 通常地，单表物理大小不超过10GB，单表行数不超过1亿条，行平均长度不超过8KB，如果机器性能足够，这些数据量MySQL是完全能处理的过来的，不用担心性能问题，这么建议主要是考虑ONLINE DDL的代价较高；
  • 不用太担心mysqld进程占用太多内存，只要不发生OOM kill和用到大量的SWAP都还好；
  • 在以往，单机上跑多实例的目的是能最大化利用计算资源，如果单实例已经能耗尽大部分计算资源的话，就没必要再跑多实例了；
  • 定期使用pt-duplicate-key-checker检查并删除重复的索引
  • 定期使用pt-index-usage工具检查并删除使用频率很低的索引；
  • 定期采集slow query log，用pt-query-digest工具进行分析，可结合Anemometer系统进行slow query管理以便分析slow query并进行后续优化工作；
  • 可使用pt-kill杀掉超长时间的SQL请求，Percona版本中有个选项 innodb_kill_idle_transaction也可实现该功能；
  • 使用pt-online-schema-change来完成大表的ONLINE DDL需求；
  • 定期使用pt-table-checksum、pt-table-sync来检查并修复mysql主从复制的数据差异；
Copyright © suredandan 2018 all right reserved，powered by GitbookUpdateTime: 2020-04-09 16:35:45

results matching ""

    No results matching ""