USE SysBench test Mysql and PostgreSQL - 1

上一篇介绍了编译sysbench支持mysql和postgresql.

本文将简单的介绍一下sysbench测试mysql和postgresql.

首先初始化数据 : 

MySQL :

[root@db-172-16-3-33 bin]# ./sysbench --test=oltp --oltp-table-size=10000000 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test --db-driver=mysql prepare
sysbench 0.4.12:  multi-threaded system evaluation benchmark
Creating table 'sbtest'...
Creating 10000000 records in table 'sbtest'...

PostgreSQL : 

[root@db-172-16-3-33 bin]# ./sysbench --test=oltp --oltp-table-size=10000000 --pgsql-host=127.0.0.1 --pgsql-port=1999 --pgsql-user=postgres --pgsql-password=postgres --pgsql-db=postgres --db-driver=pgsql prepare

实际执行的SQL是

CREATE TABLE sbtest (id SERIAL  NOT NULL , k integer  DEFAULT '0' NOT NULL, c char(120) DEFAULT '' NOT NULL, pad char(60) DEFAULT '' NOT NULL, PRIMARY KEY  (id) );

然后调用1000W次以下SQL.

INSERT INTO sbtest(k, c, pad) VALUES (0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt');

插入速度较慢, 可以自己插入.

停掉sysbench prepare进程, 然后在psql shell中执行.

postgres=# truncate sbtest ;
TRUNCATE TABLE
postgres=# INSERT INTO sbtest(k, c, pad) select 0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt' from generate_series(1,10000000);
INSERT 0 10000000

[simple read测试]

MySQL :

[root@db-172-16-3-33 bin]# ./sysbench --max-requests=0 --max-time=60 --num-threads=16 --test=oltp --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test --db-driver=mysql --oltp-reconnect-mode=session --oltp-test-mode=simple run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16

Doing OLTP test.
Running simple OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 15 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            5589652
        write:                           0
        other:                           0
        total:                           5589652
    transactions:                        5589652 (93160.39 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 5589652 (93160.39 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          60.0003s
    total number of events:              5589652
    total time taken by event execution: 940.6337
    per-request statistics:
         min:                                  0.06ms
         avg:                                  0.17ms
         max:                                 96.02ms
         approx.  95 percentile:               0.17ms

Threads fairness:
    events (avg/stddev):           349353.2500/4024.92
    execution time (avg/stddev):   58.7896/0.05

PostgreSQL : 

[root@db-172-16-3-33 bin]# ./sysbench --max-requests=0 --max-time=60 --num-threads=16 --test=oltp --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-port=1999 --pgsql-user=postgres --pgsql-password=postgres --pgsql-db=postgres --oltp-test-mode=simple --oltp-reconnect-mode=session run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16

Doing OLTP test.
Running simple OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 15 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            7961615
        write:                           0
        other:                           0
        total:                           7961615
    transactions:                        7961615 (132692.94 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 7961615 (132692.94 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          60.0003s
    total number of events:              7961615
    total time taken by event execution: 821.8844
    per-request statistics:
         min:                                  0.05ms
         avg:                                  0.10ms
         max:                                570.73ms
         approx.  95 percentile:               0.12ms

Threads fairness:
    events (avg/stddev):           497600.9375/34503.98
    execution time (avg/stddev):   51.3678/1.36

PostgreSQL tps : 132692.94 

MySQL tps : 93160.39 

下一篇将介绍comple场景测试.

[参考]

1. Install SysBench support MySQL and PostgreSQL

http://blog.163.com/digoal@126/blog/static/16387704020134142151769/