MySQL的性能测试

常用性能测试工具

  • sysbench
  • tpcc-mysql
  • mysqlslap

性能衡量指标

  • 服务吞吐量(TPS\QPS)
    tps是每秒内的事务数,比如执行了dml操作,那么相应的tps会增加;
    qps是指每秒内查询次数,比如执行了select操作,相应的qps会增加。
  • 服务响应时间
  • 服务的并发性

sysbench

  • 业界知名
  • 支持磁盘、cpu和数据库
  • 支持多种数据库

测试准备

$ sysbench --test=oltp --oltp-table-size=1000000 --mysql-host=127.0.0.1 --mysql-user=sujx --mysql-db=sujx --mysql-password=****  prepare

执行测试

$ sysbench --num-threads=16 --max-requests=100000  --test=oltp --oltp-table-size=1000000 --oltp-read-only --mysql-host=127.0.0.1 --mysql-user=sujx --mysql-db=sujx --mysql-password=**** run

测试结果

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 16

Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 100000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            1400028
        write:                           0
        other:                           200004
        total:                           1600032
    transactions:                        100002 (1936.18 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1400028 (27106.57 per sec.)
    other operations:                    200004 (3872.37 per sec.)

Test execution summary:
    total time:                          51.6490s
    total number of events:              100002
    total time taken by event execution: 825.9599
    per-request statistics:
         min:                                  2.09ms
         avg:                                  8.26ms
         max:                                 20.08ms
         approx.  95 percentile:              11.48ms

Threads fairness:
    events (avg/stddev):           6250.1250/10.94
    execution time (avg/stddev):   51.6225/0.00

Tpcc-mysql

  1. Ubuntu下执行安装
$ sudo apt install -y git mysql-server libmysqlclient-dev  
$ git clone https://github.com/Percona-Lab/tpcc-mysql.git
$ cd tpcc-mysql/src
$ make 
$ export LD_LIBRARY_PATH=$MYSQL_HOME/lib
export C_INCLUDE_PATH=$MYSQL_HOME/include
export PATH=$MYSQL_HOME/bin:$PATH
mysql>create database tpcc;
mysql>source create_table.sql
mysql>source add_fkey_idx.sql
$ ./tpcc_load 127.0.0.1 sujx sujx ***** 5
./tpcc_start -h 127.0.0.1 -d sujx -u sujx -p ***** -w 5 -c 16 -r 1 -l 10 -i 1 -f report-mysql-tpcc.txt

输出结果

STOPPING THREADS................

<Raw Results>
  [0] sc:3243  lt:0  rt:0  fl:0 
  [1] sc:3235  lt:0  rt:0  fl:0 
  [2] sc:325  lt:0  rt:0  fl:0 
  [3] sc:323  lt:0  rt:0  fl:0 
  [4] sc:325  lt:0  rt:0  fl:0 
 in 10 sec.

<Raw Results2(sum ver.)>
  [0] sc:3243  lt:0  rt:0  fl:0 
  [1] sc:3242  lt:0  rt:0  fl:0 
  [2] sc:325  lt:0  rt:0  fl:0 
  [3] sc:323  lt:0  rt:0  fl:0 
  [4] sc:325  lt:0  rt:0  fl:0 

<Constraint Check> (all must be [OK])
 [transaction percentage]
        Payment: 43.42% (>=43.0%) [OK]
   Order-Status: 4.36% (>= 4.0%) [OK]
       Delivery: 4.33% (>= 4.0%) [OK]
    Stock-Level: 4.36% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 100.00%  [OK]
        Payment: 100.00%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 100.00%  [OK]

<TpmC>                 19458.000 TpmC

总结

  • IO测试要远大于内存、CPU测试要小于内存;
  • 测试时间应当大于60分钟;
  • sysbench倾向于测试MySQL性能,TPCC更接近业务;
  • 运行测试程序需要同时监控机器负载、MySQL各项监控指标

tips:
- tpcc的详细介绍和使用tpcc
- Sysbench的介绍和具体使用sysbench

MySQL的恢复

什么时候需要恢复数据

  • 硬件故障(如磁盘损坏、raid丢失)
  • 人为删除(如误删数据、黑客入侵)
  • 业务回滚(如游戏bug需要回滚)
  • 正常需求(部署镜像库、查看历史某时刻数据)

恢复条件

  • 有效备份
  • 完整日志
  • row格式的binlog(反转SQL)

恢复工具与命令

  • source
  • innodbackupex
  • mysql

案例

恢复某几条误删数据
恢复误删除表、库
将数据库恢复到指定时间点

MySQL的备份

基本知识

  1. 数据灾备
    应对硬件故障、数据丢失
    应对人为或者程序bug导致的数据误删除
  2. 制作数据库以供服务
    需要将数据库迁移、统计分析等用处;
    需要为线上数据建立一个镜像;

备份内容

  1. 数据
    数据文件或者文本格式数据
  2. 操作日志(binlog)
    数据库变更日志

类型

  1. 冷备份
    关闭数据库服务,完整拷贝数据文件
  2. 热备份
    在不影响数据库读写服务的情况下备份数据库
  3. 物理备份
    以数据页形式拷贝数据文件,速度快
  4. 逻辑备份
    导出为裸数据或者SQL(insert)语句,恢复快
  5. 本地备份
    在数据库服务器本地进行备份
  6. 远程连接数据库进行备份
  7. 全量备份
    备份完整的数据库
  8. 增量备份
    只备份上一次备份发生之后的数据;

备份周期

考虑因素:
+ 数据库的大小
+ 恢复速度要求(快速或者慢速)
+ 备份方式(全量or增量)

常用工具

mysqldump——逻辑备份,单线程,热备
xtrabackup——物理备份,热备
snapshot
mysqldumper
cp

MySQLdump的使用

sujx@x200:~$ mysqldump -help
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

备份整个数据库

mysqldump -usujx -p12345 sujx > sujx.sql

备份数据库中某个表

mysqldump -usujx -p12345 sujx food > food.sql

备份远程数据库

mysqldump -usujx -p12345 -h192.168.31.25 sujx food > food.bak.sql

xtrabackup的使用

安装
sudo apt install xtrabackup -y
OR
download:http://www.percona.com/downloads/xtrabackup

特点

  • 开源,可以在线备份InnoDB表;
  • 支持限速备份,避免对业务造成影响;
  • 支持流备;
  • 支持增量备份;
  • 支持备份文件压缩与加密;
  • 支持并行备份与恢复,速度快

原理

  • 基于innodb的crash-recovery功能;
  • 备份期间允许用户读写,写请求产生redo日志;
  • 从磁盘上拷贝数据文件;
  • 从InnoDB redo log file实时拷贝走备份期间产生的所有redo日志;
  • 恢复数据的时候,数据文件+redo日志=一致性数据

实用脚本

innobackupex

如何制定备份策略

考虑因素
+ 数据库是不是都是InnoDB引擎表
+ 数据量的大小
+ 数据库本地空间是否充足;
+ 需要多快恢复;

MySQL性能优化

性能优化是通过某些有效的方法提高MySQL数据库的性能。性能优化的目的是为了使MySQL数据库运行速度更快、占用的磁盘空间更小。主要包括优化查询速度、优化更新速度和优化MySQL服务器等。

数据库管理员可以使用'show status like 'value';'来查看数据库的性能:

mysql> show status like 'Connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 6     |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 19052 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 71    |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 1     |
+---------------+-------+
1 row in set (0.01 sec)

优化查询

通过对查询语句的分析,可以了解查询语句的执行情况。MySQL中,可使用explain和edscribe来分析语句。

mysql> explain select * from employee\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> create index index_name on employee(name);
Query OK, 0 rows affected (0.02 sec)
mysql> explain select name from employee where name='tom'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: ref
possible_keys: index_name
          key: index_name
      key_len: 62
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

使用索引查询时,一定要注意索引的使用情况。例如,like关键字配置的字符创不能以’%‘开头;使用多列索引时,查询条件必须要使用这个索引的第一个字段;使用or关键字时,or关键字所链接的所有条件都必须使用索引。

优化数据库结构

  1. 将字段分解为多个表;
  2. 增加中间表;
  3. 增加冗余字段;
  4. 优化插入记录的速度;
  5. 分析表、检查表和优化表

优化服务器

  1. 优化服务器硬件;
  2. 优化MySQL的参数。