# 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: 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: * PostgreSQL: Documentation: 9.6: pgbench」 * URL: --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://hayashier.gitbook.io/article/rdbms/rdbms-benchmark-get-started.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.