Mysql performance test on localdisk and rbd

Posted on | In | |

概述

在我们的应用中，发现数据库上跑rbd的性能比较差，为此我对比测试了mysql的benchmark在本地 sata，ssd和rbd上的性能，并进行了分析比较。

测试工具sysbench

sysbench是一个模块化的、跨平台、多线程基准测试工具，主要用于评估测试各种不同系统参数下的数据库负载情况。关于这个项目的详细介绍请看：https://github.com/akopytov/sysbench

sysbench的数据库OLTP测试支持MySQL、PostgreSQL、Oracle，目前主要用于Linux操作系统，开源社区已经将sysbench移植到了Windows，并支持SQL Server的基准测试。

所以这里选用sysbench测试mysql性能，mysql table engine选择innodb。

sysbench安装

从 https://github.com/akopytov/sysbench 上下载sysbench源码，然后编译安装即可。

1
2
3
4
5
# yum install -y gcc gcc-c++ automake make  libtool mysql-devel
# ./autogen.sh
# ./configure
# make
# make install

对比测试

测试命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
#!/bin/bash

host=$1
port=3306
user=$2
passwd=$3
if [ $# -eq 5 ]; then
    port=$5
fi

db_name=sbtest
db_size=5000000
thread_count=32
duration=600

#print a log and then exit
function EXIT() {
    [ $# -ne 0 ] && [ "$1" != "" ] && printf "$1\n"
    exit 1
}

#create database
mysql -u$user -p$passwd -h $host -P $port -e "create database if not exists $db_name;"
[ $? -eq 0 ] || EXIT "Create database FAILED!"

# prepare data
time sysbench --oltp-table-size=$db_size --test=/root/mike/sysbench-master/tests/include/oltp_legacy/oltp.lua --mysql-table-engine=innodb --mysql-host=$host --mysql-port=$port --mysql-user=$user --mysql-password=$passwd --mysql-db=$db_name prepare
[ $? -eq 0 ] || EXIT "Prepare data FAILED!"

# start benchmark
if [ "$4" == "ro" ]; then
    time sysbench --max-time=$duration --max-requests=0 --num-threads=$thread_count --oltp-table-size=$db_size --test=/root/mike/sysbench-master/tests/include/oltp_legacy/oltp.lua --mysql-table-engine=innodb --mysql-host=$host --mysql-port=$port --mysql-user=$user --mysql-password=$passwd --mysql-db=$db_name --oltp-read-only=on run
else
    time sysbench --max-time=$duration --max-requests=0 --num-threads=$thread_count --oltp-table-size=$db_size --test=/root/mike/sysbench-master/tests/include/oltp_legacy/oltp.lua --mysql-table-engine=innodb --mysql-host=$host --mysql-port=$port --mysql-user=$user --mysql-password=$passwd --mysql-db=$db_name run
fi
[ $? -eq 0 ] || EXIT "Start benchmark FAILED!"

# cleanup environment
sysbench --num-threads=$thread_count --oltp-table-size=$db_size --test=/root/mike/sysbench-master/tests/include/oltp_legacy/oltp.lua --mysql-table-engine=innodb --mysql-host=$host --mysql-port=$port --mysql-user=$user --mysql-password=$passwd --mysql-db=$db_name cleanup
[ $? -eq 0 ] || EXIT "Cleanup FAILED!"

本地磁盘

sata盘和ssd盘上的性能类似，结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SQL statistics:
    queries performed:
        read:                            40007562
        write:                           11430732
        other:                           5715366
        total:                           57153660
    transactions:                        2857683 (4762.67 per sec.)
    queries:                             57153660 (95253.36 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          600.0172s
    total number of events:              2857683

Latency (ms):
         min:                                  1.37
         avg:                                  6.72
         max:                                100.65
         95th percentile:                     17.01
         sum:                            19193736.29

Threads fairness:
    events (avg/stddev):           89302.5938/256.06
    execution time (avg/stddev):   599.8043/0.00


real	10m0.030s
user	11m12.564s
sys	9m3.083s

rbd设备

准备rbd设备命令：

1
2
# rbd create -p tstpool foxvol --size 30720 --image-format 1
# rbd map tstpool/foxvol

针对应用这里选择的image format为1，后来测试了image format为2的volume，性能一样

在设备/dev/rbd0上测试结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SQL statistics:
    queries performed:
        read:                            14509516
        write:                           4145576
        other:                           2072788
        total:                           20727880
    transactions:                        1036394 (1727.28 per sec.)
    queries:                             20727880 (34545.51 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          600.0166s
    total number of events:              1036394

Latency (ms):
         min:                                  2.02
         avg:                                 18.52
         max:                                460.01
         95th percentile:                     63.32
         sum:                            19197876.44

Threads fairness:
    events (avg/stddev):           32387.3125/109.50
    execution time (avg/stddev):   599.9336/0.01


real	10m0.028s
user	3m52.788s
sys	3m17.306s

分析

raid卡

查看本地盘的配置，本地盘的raid卡配置的写是writeback模式，缓存有2G大小。
测试机器上总共有两种raid卡：

raid卡1

1
2
3
4
5
6
7
8
9
10
11
# hpssacli ctrl all show config detail
...
Cache Board Present: True
Cache Status: OK
Cache Ratio: 10% Read / 90% Write
Drive Write Cache: Disabled
Total Cache Size: 2.0 GB
Total Cache Memory Available: 1.8 GB
No-Battery Write Cache: Disabled
SSD Caching RAID5 WriteBack Enabled: True
SSD Caching Version: 2

raid卡2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# /opt/MegaRAID/MegaCli/MegaCli64 -cfgdsply -aALL
 
==============================================================================
Adapter: 0
Product Name: PERC H730P Mini
Memory: 2048MB
BBU: Present
Serial No: 57E00PQ
==============================================================================
...
Default Cache Policy: WriteBack, ReadAhead, Direct, No Write Cache if Bad BBU
Current Cache Policy: WriteBack, ReadAhead, Direct, No Write Cache if Bad BBU
Default Access Policy: Read/Write
Current Access Policy: Read/Write
Disk Cache Policy   : Disk's Default

性能测试写IOPS

dd命令测试写IOPS性能

测试本地sata盘，发现raid卡能提升本地盘的IOPS到 20000 多；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# dd if=/dev/zero of=tstfile bs=4k count=1024000 oflag=direct &
# iostat -kx 2 sdk
...
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.16    0.00    0.71    2.37    0.00   96.77
 
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdk               0.00     0.00    0.00 26587.00     0.00 106348.00     8.00     0.85    0.03    0.00    0.03   0.03  85.35
 
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.14    0.00    0.72    2.37    0.00   96.77
 
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdk               0.00     0.00    0.00 26706.50     0.00 106826.00     8.00     0.84    0.03    0.00    0.03   0.03  84.35
...

测试rbd设备，IOPS只有1000多点；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# dd if=/dev/zero of=/dev/rbd1 bs=4k count=1024000 oflag=direct &
#iostat -kx 2 rbd1
...
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.19    0.00    0.50    2.94    0.00   96.37
 
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
rbd1              0.00     0.00    0.00 1135.50     0.00  4542.00     8.00     0.97    0.86    0.00    0.86   0.86  97.45
 
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.86    0.00    0.63    2.93    0.00   95.58
 
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
rbd1              0.00     0.00    0.00 1193.50     0.00  4774.00     8.00     0.98    0.82    0.00    0.82   0.82  97.85
...

分析：
rbd设备的所有IO是要走网络和ceph IO栈的，IO的平均时延都是ms级别的，所以每秒的IOPS也就1000多点。。。

查看网络时延

1
2
3
4
5
6
7
[root@tstserver yangguanjun]# ping 10.10.1.5
PING 10.10.1.5 (10.10.1.5) 56(84) bytes of data.
64 bytes from 10.10.1.5: icmp_seq=1 ttl=63 time=0.191 ms
64 bytes from 10.10.1.5: icmp_seq=2 ttl=63 time=0.157 ms
64 bytes from 10.10.1.5: icmp_seq=3 ttl=63 time=0.151 ms
64 bytes from 10.10.1.5: icmp_seq=4 ttl=63 time=0.147 ms
64 bytes from 10.10.1.5: icmp_seq=5 ttl=63 time=0.164 ms

网络的时延也是ms级别的，就算没有ceph io的时延，这个iops也只能是5k多，所以针对mysql这样的应用，rbd的性能会比较低；

支持原创