sysbenchでMySQLの性能測定してみる

今回はMySQLでのパフォーマンス測定のやり方を調べてみます
MySQLの構築はMySQL8.0構築を自動化してみる – Swingbyの技術ブログを参考にしてください

パフォーマンス測定

パフォーマンスの測定はなぜ必要？

MySQLのバージョンやOSなどが変わると性能が変化します
性能が上がる分には問題ないですが、下がってしまうと今まで問題なかったクエリが性能要件を満たせなくなったり、出来ていたことが出来なくなってしまうのが問題になします

ベンチマークツール

MySQLのパフォーマンスを測定するベンチマークツールはいくつかありますがsysbenchを利用してみます
sysbenchはデータベースの他にファイルシステムやCPU,メモリなどのシステムのベンチマークも可能となっており、OSなどの変更があった場合にファイルシステムの性能測定→データベースの性能測定と2つのレイヤーでベンチマークが可能になります
※今回はデータベースの測定を行い次回ファイルシステムの測定をやります

sysbenchの使い方は下記にまとまっています
https://github.com/akopytov/sysbench

インストール

CentOSでは下記コマンドで入れられます
他のOSの入れ方もgithubに記載されていますので、他のOSを利用の方は確認して入れて下さい

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

使い方

ドキュメントに記載されているものから特に使いそうなものを抜き出してまとめます

一般的に下記の使い方をします

sysbench [options]... [testname] [command] 

オプション

オプション名	詳細	デフォルト
–threads	同時実行するスレッド数	1
–events	実行する合計イベント（トランザクション）数	0(無制限)
–time	合計実行時間、秒で指定 0は無制限	10
–rate	全てのスレッドが1秒間に実行するイベント（トランザクション）数	0（無制限）

テストパターン

コマンド名	説明
prepare	テスト用のデータを作成
run	テストを実行します
cleanup	テスト用のデータを削除します

コマンド

コマンドのオプション

オプション	詳細	デフォルト
–table_size	１つのテストテーブルに挿入されるテストデータのレコード数	10000
–tables	テストテーブルの数を指定	1

よく使うコマンドは上記になるかと思います
sysbench testname helpで各コマンドのオプションを確認出来ます
oltp_read_writeの実行例

sysbench oltp_read_write help
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

oltp_read_write options:
  --auto_inc[=on|off]           Use AUTO_INCREMENT column as Primary Key (for MySQL), or its alternatives in other DBMS. When disabled, use client-generated IDs [on]
  --create_secondary[=on|off]   Create a secondary index in addition to the PRIMARY KEY [on]
  --delete_inserts=N            Number of DELETE/INSERT combinations per transaction [1]
  --distinct_ranges=N           Number of SELECT DISTINCT queries per transaction [1]
  --index_updates=N             Number of UPDATE index queries per transaction [1]
  --mysql_storage_engine=STRING Storage engine, if MySQL is used [innodb]
  --non_index_updates=N         Number of UPDATE non-index queries per transaction [1]
  --order_ranges=N              Number of SELECT ORDER BY queries per transaction [1]
  --pgsql_variant=STRING        Use this PostgreSQL variant when running with the PostgreSQL driver. The only currently supported variant is 'redshift'. When enabled, create_secondary is automatically disabled, and delete_inserts is set to 0
  --point_selects=N             Number of point SELECT queries per transaction [10]
  --range_selects[=on|off]      Enable/disable all range SELECT queries [on]
  --range_size=N                Range size for range SELECT queries [100]
  --secondary[=on|off]          Use a secondary index in place of the PRIMARY KEY [off]
  --simple_ranges=N             Number of simple range SELECT queries per transaction [1]
  --skip_trx[=on|off]           Don't start explicit transactions and execute all queries in the AUTOCOMMIT mode [off]
  --sum_ranges=N                Number of SELECT SUM() queries per transaction [1]
  --table_size=N                Number of rows per table [10000]
  --tables=N                    Number of tables [1]

実行

実際にMySQLをのベンチマークを実行してみます

//テスト用のデーターベースを作成します
create database sbtest;

//テストデータの準備
sysbench oltp_read_write 
　　　--db-driver=mysql \
      --mysql-host=<host> \
      --mysql-user=<user> \
      --mysql-password=password \
      --mysql-db=sbtest \

　　　prepare

Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...

//テストを実行
sysbench oltp_read_write \
      --db-driver=mysql \
      --mysql-host=<host> \
      --mysql-user=<user> \
      --mysql-password=<password> \
      --mysql-db=sbtest \
      --threads=10 \
      --time=120 \
      run

//実行結果
Number of threads: 10
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            345590
        write:                           98709
        other:                           49358
        total:                           493657
    transactions:                        24673  (205.55 per sec.)
    queries:                             493657 (4112.58 per sec.)
    ignored errors:                      12     (0.10 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0344s
    total number of events:              24673

Latency (ms):
         min:                                   10.99
         avg:                                   48.64
         max:                                  946.46
         95th percentile:                       90.78
         sum:                              1200133.84

Threads fairness:
    events (avg/stddev):           2467.3000/18.20
    execution time (avg/stddev):   120.0134/0.01

実行結果では色々な情報が取れますが、クエリとトランザクションの秒間実行数で比較するのが簡単で良いかと思います

次回はファイルシステムのベンチマークを測って見ようと思います