• 一、性能诊断方法论

• 1、性能问题

• 2、解决方案

• 2.1 测量任务所花费的时间

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

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

• 3.1 影响因素

• 3.2 工具

• 4、剖析MySQL查询

• 4.1 剖析服务器负载

• 4.2 剖析单条查询

• 4.3 使用性能剖析

• 5、诊断间歇性问题

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

• 5.2 捕获诊断数据

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

• 5.2.2 收集什么样的数据

• 5.2.3 解释结果数据

• 6、其他剖析工具

• 二、优化方向

• 1. 服务器及OS优化

• 1.1 文件系统

• 1.2 内核参数

• 1.3 硬件提升

• 2. MySQL参数调整

• 2.1 内存参数

• 2.2 事务日志相关

• 2.3 IO参数

• 2.4 其他参数

• 3. SQL优化

• 4. 表架构优化

• 4.1 选择优化的数据类型

• 4.1.1 浮点类型

• 4.1.2 VARCHAR和CHAR类型

• 4.1.3 BLOB和TEXT类型

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

• 4.2 表设计原则

• 5. 索引优化

一、性能诊断方法论

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 解释结果数据

1. 检查问题是否真的发生了，避免误报

2. 是否有非常明显的跳跃性变化

3. 将Percona Toolkit中pt-mysql-summary和pt-summary的输出结果打包，用pt-sift快速检查收集到的样本数据

4. 什么导致资源性能低下

• 资源过度使用，余量不足以正常工作

• 资源没有被正确配置

• 资源已经损坏或者失灵

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

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

• XFS

• EXT4

  echo '/dev/sda1/ext4 native,nodiratime,data=writeback 1 1' >> /etc/fstab
  复制

1.2 内核参数

可以参考Oracle的内核参数的调整
修改/etc/sysctl.conf

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

1.3 硬件提升

• CPU

• 非计算密集型 - 多核

• 计算密集型 - 高频

• 内存

• 磁盘

• RAID 10

• SSD & PCIE卡

• 网络

• 万兆网卡

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

3. SQL优化

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

4. 表架构优化

4.1 选择优化的数据类型

4.1.1 浮点类型

• 精确

• DECIMAL

• MySQL自身实现，运算较慢

• 不精确

• DOUBLE、FLOAT

• CPU直接支持，运算较快

• 提升效率方法

• 在数据量较大时，使用BIGINT代替DECIMAL。乘以相应倍数即可。

•  

4.1.2 VARCHAR和CHAR类型

• VARCHAR

• 字符串列的最大长度比平均长度大很多

• 列的更新很少，所以碎片不是问题

• 使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储

• CHAR

• 存储很短的字符串

• 经常变更

4.1.3 BLOB和TEXT类型

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

• 优点

• ENUM和ENUM关联会很快

• 缺点

• 避免使用数字作为枚举常量，双重性容易导致混乱

• 字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE

4.2 表设计原则

• 更小的通常更好

• 简单就好

• 尽量避免NULL

• 查询越频繁的表应该设计越简单

• 查询越频繁的关联表应该多考虑冗余

5. 索引优化

复合索引：最常用的放在最前面，无where顺序无关
索引是不可更改的，想更改必须删除重新建