====== 52 MySQLQ sysbench 1.0 ======

新しいsysbenchはちょっとオプションが違った。
===== インストール =====

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

<code>
apt -y install sysbench
</code>

<code>
yum -y install sysbench
</code>

===== 準備 =====
==== DB作成 ====

<code>
mysql> create database sbtest charset=utf8;
</code>

==== テーブル用意 ====

<code>
sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=1000000 \
 --mysql-host=localhost \
 --mysql-user=root \
 --mysql-password=[password] \
 --mysql-db=sbtest \
 --db-ps-mode=disable \
 prepare
</code>

===== 実行 =====
<code>

sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=100000 \
 --mysql-host=localhost \
 --mysql-user=root \
 --mysql-password=[password] \
 --mysql-db=benchmark \
 --time=300 \
 --db-ps-mode=disable \
 --threads=16 \
 run
</code>


<code>
SQL statistics:
    queries performed:
        read:                            13620712
        write:                           3811897
        other:                           2025506
        total:                           19458115
    transactions:                        972895 (3242.90 per sec.)
    queries:                             19458115 (64858.62 per sec.)
    ignored errors:                      13     (0.04 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0066s
    total number of events:              972895

Latency (ms):
         min:                                    2.36
         avg:                                    4.93
         max:                                  273.59
         95th percentile:                       10.09
         sum:                              4797901.95

Threads fairness:
    events (avg/stddev):           60805.9375/188.35
    execution time (avg/stddev):   299.8689/0.00
</code>


===== CPU/Memory/Fileio =====

==== CPU ====
<code>
# sysbench cpu run
sysbench 1.0.18 (using system LuaJIT 2.1.0-beta3)

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:   920.02

General statistics:
    total time:                          10.0009s
    total number of events:              9203

Latency (ms):
         min:                                    1.08
         avg:                                    1.09
         max:                                    1.39
         95th percentile:                        1.10
         sum:                                 9996.71

Threads fairness:
    events (avg/stddev):           9203.0000/0.00
    execution time (avg/stddev):   9.9967/0.00
</code>

==== Memory ====
<code>
# sysbench memory run
sysbench 1.0.18 (using system LuaJIT 2.1.0-beta3)

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: 47201506 (4719062.27 per second)

46095.22 MiB transferred (4608.46 MiB/sec)


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

Latency (ms):
         min:                                    0.00
         avg:                                    0.00
         max:                                    0.26
         95th percentile:                        0.00
         sum:                                 4803.32

Threads fairness:
    events (avg/stddev):           47201506.0000/0.00
    execution time (avg/stddev):   4.8033/0.00
</code>

==== Fileio ====
<code>
sysbench fileio --file-total-size=1G --file-test-mode=rndrw --time=300 --max-requests=0 prepare
sysbench fileio --file-total-size=1G --file-test-mode=rndrw --time=300 --max-requests=0 run
sysbench fileio --file-total-size=1G --file-test-mode=rndrw --time=300 --max-requests=0 cleanup
</code>

<code>
# sysbench fileio --file-total-size=1G --file-test-mode=rndrw --time=300 --max-requests=0 --file-io-mode=async --file-extra-flags=direct  --file-num=1 --file-rw-ratio=1 --file-fsync-freq=0 run
sysbench 1.0.18 (using system LuaJIT 2.1.0-beta3)

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


Extra file open flags: directio
1 files, 1GiB each
1GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.00
Calling fsync() at the end of test, Enabled.
Using asynchronous I/O mode
Doing random r/w test
Initializing worker threads...

Threads started!


File operations:
    reads/s:                      33768.16
    writes/s:                     33768.21
    fsyncs/s:                     0.00

Throughput:
    read, MiB/s:                  527.63
    written, MiB/s:               527.63

General statistics:
    total time:                          300.0012s
    total number of events:              20261233

Latency (ms):
         min:                                    0.01
         avg:                                    0.01
         max:                                   25.74
         95th percentile:                        0.03
         sum:                               279588.50

Threads fairness:
    events (avg/stddev):           20261233.0000/0.00
    execution time (avg/stddev):   279.5885/0.00
</code>
{{tag>mysql}}