摘要

MySQL 凭借着开源社区和 Oracle 公司的支持,快速迭代变为很多公司关系型数据库的首选。

本文着眼于 MySQL 的性能优化, 系统的介绍了性能优化的各个方面,从系统的选择,安装使用到性能测量,优化,末尾介绍了文档平台数据库优化过程中几个有代表性的例子。

由于数据库优化设计内容众多,本文仅列出大纲,具体内容分布在对应的文章中。

安排

2016-05-24T15:30:00 +0800 - 2016-05-24T18:20:00 +0800

目录

性能

  • 不要只关注平均情况
  • 结合变化趋势(daily, weekly, monthly)
  • 为将来性能考虑

指标及测量

性能优化虑顾

  • 稳定性
  • 可扩展性
    • 负载
    • 数据集大小
    • 架构
  • 效率(Efficiency)

重点

性能优化一般聚焦于:

  • 使 Query 执行更快
  • 使用更少的资源
  • 更好的扩展性

分析

系统

系统负载

MySQL Server

内部状态

工作负载

Query

Explain

Slow Query

优化

步骤

  1. 硬件
  2. 服务器
    • 操作系统
    • 网络
    • 文件系统
  3. MySQL 服务器
    • Query 优化
    • Database schema

操作系统优化

一般而言,操作系统厂商对于操作系统的优化使面向公共系统负载的。但是在生产环境中,基本上每个服务器的负载都是各有偏重的。例如数据库,应用和负载均衡代理等各自为一个系统,而每个对各种资源的需求都是各有特色的。

工具

  • PT-Query-Digest from Percona Toolkit
  • Mysql Utilities
  • Mysql Performance Schemas

内存

I/O

网络

延迟

压缩

系统配置

MySQL 配置

SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;

存储引擎

MySQL 优化

线程

内存

日志

InnoDB 特定优化

MySQL Replication

数据库优化

第三范式

表优化

压缩

索引优化

InnoDB 特定优化

事务优化

优化事物的慢查询

Query 优化

Query Cache

Prepared Statements

Query Rewrites

案例分析

  1. protocol compression
  2. table compression
  3. add index
  4. query rewrite
  5. query cache

Differences Between System and MySQL Versions

select
@@version,
@@max_connections,
@@log_bin,
@@table_open_cache,
@@table_definition_cache,
@@open_files_limit,
@@innodb_buffer_pool_size,
@@innodb_log_file_size,
@@innodb_flush_log_at_trx_commit,
@@innodb_flush_method;

Ubuntu 14.04 LTS

+-------------------------+-------------------+-----------+--------------------+--------------------------+--------------------+---------------------------+------------------------+----------------------------------+-----------------------+
| @@version               | @@max_connections | @@log_bin | @@table_open_cache | @@table_definition_cache | @@open_files_limit | @@innodb_buffer_pool_size | @@innodb_log_file_size | @@innodb_flush_log_at_trx_commit | @@innodb_flush_method |
+-------------------------+-------------------+-----------+--------------------+--------------------------+--------------------+---------------------------+------------------------+----------------------------------+-----------------------+
| 5.5.47-0ubuntu0.14.04.1 |               151 |         0 |                400 |                      400 |               1024 |                 134217728 |                5242880 |                                1 | NULL                  |
+-------------------------+-------------------+-----------+--------------------+--------------------------+--------------------+---------------------------+------------------------+----------------------------------+-----------------------+
1 row in set (0.00 sec)

Red Hat Enterprise Linux 7.2

+----------------+-------------------+-----------+--------------------+--------------------------+--------------------+---------------------------+------------------------+----------------------------------+-----------------------+
| @@version      | @@max_connections | @@log_bin | @@table_open_cache | @@table_definition_cache | @@open_files_limit | @@innodb_buffer_pool_size | @@innodb_log_file_size | @@innodb_flush_log_at_trx_commit | @@innodb_flush_method |
+----------------+-------------------+-----------+--------------------+--------------------------+--------------------+---------------------------+------------------------+----------------------------------+-----------------------+
| 5.5.47-MariaDB |               151 |         0 |                400 |                      400 |               1024 |                 134217728 |                5242880 |                                1 | NULL                  |
+----------------+-------------------+-----------+--------------------+--------------------------+--------------------+---------------------------+------------------------+----------------------------------+-----------------------+
1 row in set (0.00 sec)

CentOS 6.3

+------------+-------------------+-----------+--------------------+--------------------------+--------------------+---------------------------+------------------------+----------------------------------+-----------------------+
| @@version  | @@max_connections | @@log_bin | @@table_open_cache | @@table_definition_cache | @@open_files_limit | @@innodb_buffer_pool_size | @@innodb_log_file_size | @@innodb_flush_log_at_trx_commit | @@innodb_flush_method |
+------------+-------------------+-----------+--------------------+--------------------------+--------------------+---------------------------+------------------------+----------------------------------+-----------------------+
| 5.5.35-log |              3000 |         1 |               1024 |                      400 |              65536 |               34359738368 |             1992294400 |                                2 | NULL                  |
+------------+-------------------+-----------+--------------------+--------------------------+--------------------+---------------------------+------------------------+----------------------------------+-----------------------+
1 row in set (0.00 sec)
  1. Diagnose

    $ sudo apt-get install percona-toolkit
    $ pt-query-digest slow-pre.log > report-post.txt
    
  2. Sort Optimization

    mysql>  show global variables like '%sort%';
    +---------------------------+-------------+
    | Variable_name             | Value       |
    +---------------------------+-------------+
    | max_length_for_sort_data  | 1024        |
    | max_sort_length           | 1024        |
    | myisam_max_sort_file_size | 10737418240 |
    | myisam_sort_buffer_size   | 67108864    |
    | sort_buffer_size          | 2097152     |
    +---------------------------+-------------+
    5 rows in set (0.00 sec)
    mysql> show global status like '%sort%';
    +-------------------+-----------+
    | Variable_name     | Value     |
    +-------------------+-----------+
    | Sort_merge_passes | 11759     |
    | Sort_range        | 86795644  |
    | Sort_rows         | 909752450 |
    | Sort_scan         | 97771     |
    +-------------------+-----------+
    4 rows in set (0.01 sec)
    
set global sort_buffer_size = 8M;
set global sort_buffer_size = 32M;
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| Sort_merge_passes | 24 |
| Sort_range | 1 |
| Sort_rows | 287159 |
| Sort_scan | 0 |
+-------------------+--------+

set global query_cache_min_res_unit = 4096*2
set global query_cache_min_res_unit = 1024;
flush query cache;
set global query_cache_min_res_unit = 2048;
set global query_cache_limit = 2*1024*1024;
flush query cache;

结论

  • 以应用性能为重心
  • 真对不同的工作,选择合适的工具
  • 查看负载的查询
  • 减少查询次数
  • 减少返回结果大小
  • 看看如何可以减少工作(See how they can do less work)
  • 提高工作效率(Do that work more efficiently)

注意

  • 不要使用默认配置
  • 不要沉迷于性能调优

Q&A