RDBMS Benchmark Get Started

RDBMS Benchmark Get Started

pgbench

pgbench がインストールされてることの確認

$ pgbench --version
pgbench (PostgreSQL) 9.6.8

PostgreSQLに対してpgbenchの準備

$ pgbench -U hayashier -h aurorapgtest.c5x5yhkaqvsm.us-west-2.rds.amazonaws.com -i --unlogged-tables test
Password: 
NOTICE:  table pgbench_history does not exist, skipping
NOTICE:  table pgbench_tellers does not exist, skipping
NOTICE:  table pgbench_accounts does not exist, skipping
NOTICE:  table pgbench_branches does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.03 s, remaining 0.00 s)
vacuum...
set primary keys...
done.

pgbench実行

$ pgbench -v -U hayashier -h aurorapgtest.c5x5yhkaqvsm.us-west-2.rds.amazonaws.com -c 3 -t 1000 test
Password: 
starting vacuum...end.
starting vacuum pgbench_accounts...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 3
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 3000/3000
latency average = 12.392 ms
tps = 242.091731 (including connections establishing)
tps = 242.335592 (excluding connections establishing)

sysbench

インストール

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

インストールされたか確認

$ sysbench --version
sysbench 1.0.15

PostgreSQL の場合

test=> CREATE DATABASE sysbenchdb;
CREATE DATABASE

環境変数のエクスポート

$ export PASS=xxxxxxxx

sysbench 準備

$ sysbench oltp_common \
    --mysql-dry-run=off \
    --db-driver=pgsql \
    --pgsql-host=postgres-test.c5x5yhkaqvsm.us-west-2.rds.amazonaws.com \
    --pgsql-user=hayashier \
    --pgsql-password=$PASS \
    --pgsql-db=sysbenchdb \
    --table_size=1000 \
    --tables=20 \
    prepare
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...
Inserting 1000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 1000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
:
:

実行

$ sysbench oltp_read_write \
    --mysql-dry-run=off \
    --db-driver=pgsql \
    --pgsql-host=postgres-test.c5x5yhkaqvsm.us-west-2.rds.amazonaws.com \
    --pgsql-user=hayashier \
    --pgsql-password=$PASS \
    --pgsql-db=sysbenchdb \
    --table_size=1000 \
    --tables=20 \
    --threads=2 \
    --time=30 \
    run
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

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


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            69874
        write:                           19956
        other:                           9986
        total:                           99816
    transactions:                        4989   (166.21 per sec.)
    queries:                             99816  (3325.44 per sec.)
    ignored errors:                      2      (0.07 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0138s
    total number of events:              4989

Latency (ms):
         min:                                   10.71
         avg:                                   12.03
         max:                                   56.74
         95th percentile:                       13.95
         sum:                                60006.14

Threads fairness:
    events (avg/stddev):           2494.5000/0.50
    execution time (avg/stddev):   30.0031/0.00

MySQL の場合

mysql> create database sbtest;
Query OK, 1 row affected (0.16 sec)

mysql> grant all on sbtest.* to root@'*';
Query OK, 0 rows affected (0.05 sec)

mysql> use sample;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE `sbtest1` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `k` int(11) NOT NULL DEFAULT '0',
    ->   `c` char(120) NOT NULL DEFAULT '',
    ->   `pad` char(60) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`id`),
    ->   KEY `k_1` (`k`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.11 sec)

環境変数エクスポート

$ export PASS=XXXXXXXX

実行

$ sysbench --db-driver=mysql \
    --mysql-host=hayashier-test.c5x5yhkaqvsm.us-west-2.rds.amazonaws.com \
    --mysql-user=hayashier \
    --mysql-password=$PASS \
    --mysql-db=sample \
    oltp_read_write \
    run
sysbench 1.0.15 (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:                            7042
        write:                           1432
        other:                           1586
        total:                           10060
    transactions:                        503    (50.22 per sec.)
    queries:                             10060  (1004.45 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0133s
    total number of events:              503

Latency (ms):
         min:                                   17.70
         avg:                                   19.90
         max:                                   49.93
         95th percentile:                       24.38
         sum:                                10010.23

Threads fairness:
    events (avg/stddev):           503.0000/0.00
    execution time (avg/stddev):   10.0102/0.00

References

• sysbench

  • URL: https://github.com/akopytov/sysbench

• PostgreSQL: Documentation: 9.6: pgbench」

  • URL: https://www.postgresql.org/docs/9.6/static/pgbench.html