使用sysbench测试postgresql和造数据

已经安装好sysbench

查看安装情况

scutech@infokist:~$ dpkg -l|grep sysbench
ii  sysbench                               1.0.20-1                                   amd64        Scriptable database and system performance benchmark
$ sysbench --help
......
pgsql options:
  --pgsql-host=STRING     PostgreSQL server host [localhost]
  --pgsql-port=N          PostgreSQL server port [5432]
  --pgsql-user=STRING     PostgreSQL user [sbtest]
  --pgsql-password=STRING PostgreSQL password []
  --pgsql-db=STRING       PostgreSQL database name [sbtest]
....

造数据

造数据

scutech@infokist:~$ sysbench  --db-driver=pgsql  --pgsql-host=192.168.17.40  --pgsql-user=scutech  --pgsql-password=dingjia \
> --pgsql-db=postgres  --oltp-table-size=10000  --rand-init=on  --threads=10  --time=120  --events=0 \
>  --report-interval=10  --percentile=99  /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua  prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating secondary indexes on 'sbtest1'...

清除数据

sysbench  --db-driver=pgsql  --pgsql-host=192.168.17.40  --pgsql-user=scutech  --pgsql-password=dingjia \
--pgsql-db=postgres  --oltp-table-size=10000  --rand-init=on  --threads=10  --time=120  --events=0 \
 --report-interval=10  --percentile=99  /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua  cleanup

检查表

 psql -U scutech -h 192.168.17.40 -d postgres
 postgres=# select * FROM pg_tables where schemaname='public';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | sbtest1   | scutech    |            | t          | f        | f           | f
(1 row)

postgres=# 
postgres=# select count(*) from sbtest1;
 count 
-------
 10000
(1 row)

测试

scutech@infokist:~$ sysbench  --db-driver=pgsql  --pgsql-host=192.168.17.40  --pgsql-port=5432  --pgsql-user=scutech  --pgsql-password=dingjia  --pgsql-db=mydb  --oltp-table-size=10000  --threads=10  --time=120  --events=0  --report-interval=10  --percentile=99  /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua  run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 10
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 10 tps: 300.40 qps: 6099.34 (r/w/o: 4278.15/1203.70/617.49) lat (ms,99%): 64.47 err/s: 4.40 reconn/s: 0.00
[ 20s ] thds: 10 tps: 256.33 qps: 5200.16 (r/w/o: 3646.09/1028.81/525.26) lat (ms,99%): 70.55 err/s: 4.00 reconn/s: 0.00
[ 30s ] thds: 10 tps: 245.79 qps: 4950.20 (r/w/o: 3467.29/980.74/502.17) lat (ms,99%): 669.89 err/s: 2.20 reconn/s: 0.00
[ 40s ] thds: 10 tps: 328.60 qps: 6646.79 (r/w/o: 4658.09/1315.00/673.70) lat (ms,99%): 57.87 err/s: 4.10 reconn/s: 0.00
[ 50s ] thds: 10 tps: 327.60 qps: 6634.91 (r/w/o: 4651.11/1312.30/671.50) lat (ms,99%): 57.87 err/s: 4.60 reconn/s: 0.00
[ 60s ] thds: 10 tps: 268.20 qps: 5438.79 (r/w/o: 3814.29/1074.10/550.40) lat (ms,99%): 116.80 err/s: 4.10 reconn/s: 0.00
[ 70s ] thds: 10 tps: 296.52 qps: 6004.58 (r/w/o: 4207.86/1190.07/606.64) lat (ms,99%): 61.08 err/s: 4.10 reconn/s: 0.00
[ 80s ] thds: 10 tps: 323.58 qps: 6563.00 (r/w/o: 4601.42/1297.02/664.56) lat (ms,99%): 59.99 err/s: 5.20 reconn/s: 0.00
[ 90s ] thds: 10 tps: 330.20 qps: 6679.35 (r/w/o: 4681.27/1321.39/676.70) lat (ms,99%): 57.87 err/s: 4.20 reconn/s: 0.00
[ 100s ] thds: 10 tps: 273.20 qps: 5515.64 (r/w/o: 3865.03/1091.11/559.50) lat (ms,99%): 64.47 err/s: 3.00 reconn/s: 0.00
[ 110s ] thds: 10 tps: 244.12 qps: 4950.41 (r/w/o: 3471.22/978.76/500.43) lat (ms,99%): 80.03 err/s: 3.60 reconn/s: 0.00
[ 120s ] thds: 10 tps: 234.88 qps: 4772.88 (r/w/o: 3347.70/942.52/482.66) lat (ms,99%): 87.56 err/s: 4.30 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            486976
        write:                           137398
        other:                           70325
        total:                           694699
    transactions:                        34306  (285.77 per sec.)
    queries:                             694699 (5786.92 per sec.)
    ignored errors:                      478    (3.98 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0410s
    total number of events:              34306

Latency (ms):
         min:                                   18.69
         avg:                                   34.98
         max:                                 3026.66
         99th percentile:                       63.32
         sum:                              1200039.52

Threads fairness:
    events (avg/stddev):           3430.6000/22.74
    execution time (avg/stddev):   120.0040/0.01

scutech@infokist:~$ 

文章下方是我的微信，欢迎加我。👇