sysbench覚書

sysbenchを仕様する際の覚書を記載する

# インストール
aws EC2 linuxへのインストール

```
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
```
# バージョン確認

```
$ sysbench --version
sysbench 1.0.17
```

# CPUテスト例

```
$ sysbench --test=cpu run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

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


Prime numbers limit: 10000

Initializing worker threads...

Threads started!

CPU speed:
    events per second:   856.08

General statistics:
    total time:                          10.0007s
    total number of events:              8563

Latency (ms):
         min:                                    1.03
         avg:                                    1.17
         max:                                    3.82
         95th percentile:                        1.23
         sum:                                 9990.39

Threads fairness:
    events (avg/stddev):           8563.0000/0.00
    execution time (avg/stddev):   9.9904/0.00
```

# memoryテスト例

```
$ sysbench --test=memory run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

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


Running memory speed test with the following options:
  block size: 1KiB
  total size: 102400MiB
  operation: write
  scope: global

Initializing worker threads...

Threads started!

Total operations: 7954482 (795281.88 per second)

7768.05 MiB transferred (776.64 MiB/sec)


General statistics:
    total time:                          10.0001s
    total number of events:              7954482

Latency (ms):
         min:                                    0.00
         avg:                                    0.00
         max:                                    1.87
         95th percentile:                        0.00
         sum:                                 3579.54

Threads fairness:
    events (avg/stddev):           7954482.0000/0.00
    execution time (avg/stddev):   3.5795/0.00
```

# mysqlテスト例

## 準備
DB・ユーザー作成

```
$ mysql -u root -p
```
```
mysql> CREATE DATABASE sbtest;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE USER sbtest;                                                                                                                                                                               
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON sbtest.* TO 'sbtest'@'localhost' IDENTIFIED BY 'sbtest';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET PASSWORD FOR sbtest = 'sbtest';                                                                                                                                                               
Query OK, 0 rows affected (0.00 sec)
```

## prepare実行
```
$ sysbench --db-driver=mysql \                                                                                                                                                                             
    --mysql-user=sbtest \                                                                                                                                                                                
    --mysql-password=sbtest \                                                                                                                                                                            
    --mysql-db=sbtest \                                                                                                                                                                                  
    --range_size=100 \                                                                                                                                                                                   
    --table_size=10000 \                                                                                                                                                                                 
    --tables=2 \                                                                                                                                                                                         
    --threads=1 \                                                                                                                                                                                        
    --events=0 \                                                                                                                                                                                         
    --time=60 \                                                                                                                                                                                          
    --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua prepare
```

## 実行
```
$ sysbench --db-driver=mysql \                                                                                                                                                                             
    --mysql-user=sbtest \                                                                                                                                                                                
    --mysql-password=sbtest \                                                                                                                                                                            
    --mysql-db=sbtest \                                                                                                                                                                                  
    --range_size=100 \                                                                                                                                                                                   
    --table_size=10000 \                                                                                                                                                                                 
    --tables=2 \                                                                                                                                                                                         
    --threads=1 \                                                                                                                                                                                        
    --events=0 \                                                                                                                                                                                         
    --time=60 \                                                                                                                                                                                          
    --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua run
sysbench 1.0.17 (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:                            631302
        write:                           0
        other:                           90186
        total:                           721488
    transactions:                        45093  (751.51 per sec.)
    queries:                             721488 (12024.10 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0015s
    total number of events:              45093

Latency (ms):
         min:                                    1.08
         avg:                                    1.33
         max:                                    7.98
         95th percentile:                        1.39
         sum:                                59878.85

Threads fairness:
    events (avg/stddev):           45093.0000/0.00
    execution time (avg/stddev):   59.8789/0.00
```