MySQL, PostgreSQL ベンチマーク(sysbench, pgbench)
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