MySQL on CentOS 6.9 に sysbench でベンチマークをかけてみる

ファイルシステムの初期化

• ファイルシステムを初期化する

# mkdir /nvme0n1 /e32000
# fdisk -l
# mkfs.ext4 /dev/nvme0n1
# mkfs.ext4 /dev/xvdb
# mount /dev/nvme0n1 /nvme01
# mount /dev/xvdb /e32000

インストール

• MySQL の yum リポジトリを追加する

# yum -y install http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm

• MySQL Community Server をインストールする

# yum -y install mysql-community-server

• sysbench をインストールする

# curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
# sudo yum -y install sysbench

• strace をインストールする

# yum -y install strace 

• MySQLにログインする

# mysql -u root

• データベースを作成する

mysql> create database mydb;

• テーブルを作成してデータをロードする

$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=100000 \
 --mysql-user=root \
 --mysql-db=mydb \
 --db-ps-mode=disable \
 prepare

• datadir 配下のファイルをコピーしておく

# service mysqld stop
# cp -pr /var/lib/mysql /nvme01/
# cp -pr /var/lib/mysql /dev/shm/
# service mysqld start

• ベンチマークをかける

$ sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=100000 \
 --mysql-db=mydb \
 --mysql-user=root \
 --time=30 \
 --db-ps-mode=disable \
 --threads=1 \
 run

結果

• E300

sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            101892
        write:                           29112
        other:                           14556
        total:                           145560
    transactions:                        7278   (242.57 per sec.)
    queries:                             145560 (★4851.45 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0013s
    total number of events:              7278

Latency (ms):
         min:                                    3.78
         avg:                                    4.12
         max:                                   22.54
         95th percentile:                        4.33
         sum:                                29979.87

Threads fairness:
    events (avg/stddev):           7278.0000/0.00
    execution time (avg/stddev):   29.9799/0.00

• E32000

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            99582
        write:                           28452
        other:                           14226
        total:                           142260
    transactions:                        7113   (237.08 per sec.)
    queries:                             142260 (4741.55 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0007s
    total number of events:              7113

Latency (ms):
         min:                                    3.88
         avg:                                    4.21
         max:                                    7.83
         95th percentile:                        4.41
         sum:                                29979.78

Threads fairness:
    events (avg/stddev):           7113.0000/0.00
    execution time (avg/stddev):   29.9798/0.00

• NVMe

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            152558
        write:                           43588
        other:                           21794
        total:                           217940
    transactions:                        10897  (363.17 per sec.)
    queries:                             217940 (★7263.49 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0027s
    total number of events:              10897

Latency (ms):
         min:                                    2.49
         avg:                                    2.75
         max:                                    4.85
         95th percentile:                        2.81
         sum:                                29972.98

Threads fairness:
    events (avg/stddev):           10897.0000/0.00
    execution time (avg/stddev):   29.9730/0.00

• tmpfs

sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            209902
        write:                           59972
        other:                           29986
        total:                           299860
    transactions:                        14993  (499.73 per sec.)
    queries:                             299860 (★9994.51 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0003s
    total number of events:              14993

Latency (ms):
         min:                                    1.89
         avg:                                    2.00
         max:                                    4.16
         95th percentile:                        2.07
         sum:                                29982.17

Threads fairness:
    events (avg/stddev):           14993.0000/0.00
    execution time (avg/stddev):   29.9822/0.00

システムコール（fsyncの所要時間）

• コマンド

# strace -yy -fe fsync -Tttp <PID>

• E300

[pid 28833] 15:16:59.322025 fsync(9</var/lib/mysql/ib_logfile1>) = 0 <0.001616>
[pid 28833] 15:16:59.340313 fsync(9</var/lib/mysql/ib_logfile1>) = 0 <0.001362>
[pid 28833] 15:16:59.358445 fsync(9</var/lib/mysql/ib_logfile1>) = 0 <0.001512>

• NVMe

[pid 28510] 15:14:48.225248 fsync(8</nvme01/mysql/ib_logfile0>) = 0 <0.000204>
[pid 28510] 15:14:48.241499 fsync(8</nvme01/mysql/ib_logfile0>) = 0 <0.000194>
[pid 28510] 15:14:48.257621 fsync(8</nvme01/mysql/ib_logfile0>) = 0 <0.000224>

• tmpfs

[pid  8264] 15:12:02.394681 fsync(9</dev/shm/mysql/ib_logfile1>) = 0 <0.000029>
[pid  8264] 15:12:02.410769 fsync(9</dev/shm/mysql/ib_logfile1>) = 0 <0.000029>
[pid  8264] 15:12:02.426758 fsync(9</dev/shm/mysql/ib_logfile1>) = 0 <0.000030>
[pid  8264] 15:12:02.442635 fsync(9</dev/shm/mysql/ib_logfile1>) = 0 <0.000030>

システムコール（集計）

• コマンド

# strace -fcp <PID>

• E300

% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 55.57   51.989330       53487       972           io_getevents
 32.08   30.015424        3314      9057      1740 futex
 11.11   10.398417      142444        73           select
  1.01    0.945853      157642         6         5 restart_syscall
  0.15    0.142054          84      1686           fsync
  0.03    0.027272           0    199846           gettimeofday
  0.02    0.014865           0    103448           clock_gettime
  0.01    0.008399           0     65686         2 recvfrom
  0.01    0.007503           0     32842           sendto
  0.00    0.002924           0      8466           madvise
  0.00    0.001104           0      6570           sched_yield
  0.00    0.000734           0      1681           pwrite
  0.00    0.000233           1       339           pread
  0.00    0.000042           0       401           io_submit
  0.00    0.000020           3         6           read
  0.00    0.000000           0         6           open
  0.00    0.000000           0         5           close
  0.00    0.000000           0         1           stat
  0.00    0.000000           0         6           lstat
  0.00    0.000000           0         2           poll
  0.00    0.000000           0         9           lseek
  0.00    0.000000           0         3           mmap
  0.00    0.000000           0        22           mprotect
  0.00    0.000000           0         1           munmap
  0.00    0.000000           0         2         1 access
  0.00    0.000000           0         1           accept
  0.00    0.000000           0         1           shutdown
  0.00    0.000000           0         2         1 setsockopt
  0.00    0.000000           0         5           fcntl
  0.00    0.000000           0         6           getcwd
------ ----------- ----------- --------- --------- ----------------
100.00   93.554174                431151      1749 total

• NVMe

% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 56.05   54.565652       37710      1447           io_getevents
 32.70   31.833832        3075     10353      1931 futex
 11.14   10.849351      139094        78           select
  0.03    0.027523           0    223938           gettimeofday
  0.02    0.022996        3833         6         5 restart_syscall
  0.02    0.016611           9      1892           fsync
  0.01    0.014254           0    115670           clock_gettime
  0.01    0.010720           0     73445         1 recvfrom
  0.01    0.007561           0     36722           sendto
  0.00    0.003592           0      9214           madvise
  0.00    0.002005           0      7359           sched_yield
  0.00    0.000726           0      1883           pwrite
  0.00    0.000205           0       447           pread
  0.00    0.000163           0       888           io_submit
  0.00    0.000000           0         6           read
  0.00    0.000000           0         6           open
  0.00    0.000000           0         5           close
  0.00    0.000000           0         1           stat
  0.00    0.000000           0         6           lstat
  0.00    0.000000           0         1           poll
  0.00    0.000000           0         9           lseek
  0.00    0.000000           0         3           mmap
  0.00    0.000000           0        22           mprotect
  0.00    0.000000           0         1           munmap
  0.00    0.000000           0         2         1 access
  0.00    0.000000           0         1           accept
  0.00    0.000000           0         1           shutdown
  0.00    0.000000           0         2         1 setsockopt
  0.00    0.000000           0         5           fcntl
  0.00    0.000000           0         6           getcwd
------ ----------- ----------- --------- --------- ----------------
100.00   97.355191                483419      1939 total

• tmpfs

% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 55.82   41.885262       11926      3512           io_getevents
 33.08   24.819955        1427     17391      2800 futex
 11.02    8.271746      114885        72           select
  0.03    0.023314           0    230171           gettimeofday
  0.02    0.011618           0    117623           clock_gettime
  0.01    0.007673           0     74685         1 recvfrom
  0.01    0.006094           0     37342           sendto
  0.00    0.002555           0      8772           madvise
  0.00    0.001471           0      7508           sched_yield
  0.00    0.001067           0      3799           io_submit
  0.00    0.001000         167         6         5 restart_syscall
  0.00    0.000930           0      2012           pwrite
  0.00    0.000315           0      2100           fsync
  0.00    0.000000           0         1           close
  0.00    0.000000           0         1           poll
  0.00    0.000000           0         3           pread
  0.00    0.000000           0         1           access
  0.00    0.000000           0         1           accept
  0.00    0.000000           0         1           shutdown
  0.00    0.000000           0         2         1 setsockopt
  0.00    0.000000           0         3           fcntl
------ ----------- ----------- --------- --------- ----------------
100.00   75.033000                505006      2807 total

起動と停止

• 停止

# service mysqld stop

• datadir の変更

# vi /etc/my.conf
#datadir=/var/lib/mysql
#datadir=/e32000/mysql
#datadir=/nvme01/mysql
datadir=/dev/shm/mysql

• 起動

# service mysqld start

環境

• 16vCPU
• 122 GB Memory
• CentOS 6.9 (2.6.32-696.16.1.el6.x86_64)
• 5.6.40 MySQL Community Server (GPL)

参考

• https://techblog.sbcloud.co.jp/2017/11/28/ephemeral/
• MySQLのCOMMIT時におけるfsyncの実行タイミング - 技術メモ置き場