pgbench的简单测试

红太狼 2022-02-24 02:08 264阅读 0赞

## 1.数据初始化 ##

[root@April ~]# /usr/pgsql-9.2/bin/pgbench -i mydb -U postgres
    NOTICE:  table "pgbench_branches" does not exist, skipping
    NOTICE:  table "pgbench_tellers" does not exist, skipping
    NOTICE:  table "pgbench_accounts" does not exist, skipping
    NOTICE:  table "pgbench_history" does not exist, skipping
    creating tables...
    10000 tuples done.
    20000 tuples done.
    30000 tuples done.
    40000 tuples done.
    50000 tuples done.
    60000 tuples done.
    70000 tuples done.
    80000 tuples done.
    90000 tuples done.
    100000 tuples done.
    set primary key...
    NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_branches_pkey" for table "pgbench_branches"
    NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_tellers_pkey" for table "pgbench_tellers"
    NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_accounts_pkey" for table "pgbench_accounts"
    vacuum...done.

## 2.登录数据库 ##

[root@April ~]#  psql -d mydb  -U postgres
    psql (9.2.24)
    Type "help" for help.

## 3.查询表信息 ##

### 3.1 相关表 ###

### 3.2 pgbench\_accounts ###

mydb=# select count(*) from pgbench_accounts;
     count  
    --------
     100000
    (1 row)

### 3.3 pgbench\_branches ###

mydb=# select count(*) from pgbench_branches;
     count 
    -------
         1
    (1 row

### 3.4 pgbench\_history ###

mydb=# select count(*) from pgbench_history;
     count 
    -------
         0
    (1 row)

### 3.5  pgbench\_tellers ###

mydb=# select count(*) from pgbench_tellers;
     count 
    -------
        10
    (1 row)

## 4.相关参数 ##

### 4.1初始化参数 ###

pgbench accepts the following command-line initialization arguments: 
    
    -F fillfactor
    Create the pgbench_accounts, pgbench_tellers and pgbench_branches tables with the given fillfactor. Default is 100. 
    
    -i
    Required to invoke initialization mode. 
    
    -s scale_factor
    Multiply the number of rows generated by the scale factor. For example, -s 100 will create 10,000,000 rows in the pgbench_accounts table. Default is 1.

### 4.2 压力测试参数 ###

pgbench accepts the following command-line benchmarking arguments: 
    
    -c clients
    Number of clients simulated, that is, number of concurrent database sessions. Default is 1. 
    
    -C
    Establish a new connection for each transaction, rather than doing it just once per client session. This is useful to measure the connection overhead. 
    
    -d
    Print debugging output. 
    
    -D varname=value
    Define a variable for use by a custom script (see below). Multiple -D options are allowed. 
    
    -f filename
    Read transaction script from filename. See below for details. -N, -S, and -f are mutually exclusive. 
    
    -j threads
    Number of worker threads within pgbench. Using more than one thread can be helpful on multi-CPU machines. The number of clients must be a multiple of the number of threads, since each thread is given the same number of client sessions to manage. Default is 1. 
    
    -l
    Write the time taken by each transaction to a log file. See below for details. 
    
    -M querymode
    Protocol to use for submitting queries to the server: 
    
    simple: use simple query protocol.
    
    extended: use extended query protocol.
    
    prepared: use extended query protocol with prepared statements.
    
    The default is simple query protocol. (See Chapter 46 for more information.) 
    
    -n
    Perform no vacuuming before running the test. This option is necessary if you are running a custom test scenario that does not include the standard tables pgbench_accounts, pgbench_branches, pgbench_history, and pgbench_tellers. 
    
    -N
    Do not update pgbench_tellers and pgbench_branches. This will avoid update contention on these tables, but it makes the test case even less like TPC-B. 
    
    -s scale_factor
    Report the specified scale factor in pgbench is output. With the built-in tests, this is not necessary; the correct scale factor will be detected by counting the number of rows in the pgbench_branches table. However, when testing custom benchmarks (-f option), the scale factor will be reported as 1 unless this option is used. 
    
    -S
    Perform select-only transactions instead of TPC-B-like test. 
    
    -t transactions
    Number of transactions each client runs. Default is 10. 
    
    -T seconds
    Run the test for this many seconds, rather than a fixed number of transactions per client. -t and -T are mutually exclusive. 
    
    -v
    Vacuum all four standard tables before running the test. With neither -n nor -v, pgbench will vacuum the pgbench_tellers and pgbench_branches tables, and will truncate pgbench_history.

## 5.默认测试脚本 ##

\set nbranches :scale
    \set ntellers 10 * :scale
    \set naccounts 100000 * :scale
    \setrandom delta -5000 5000
    \setrandom aid 1 :naccounts
    \setrandom bid 1 :nbranches
    \setrandom tid 1 :ntellers
    
    BEGIN;
    UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
    SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
    UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
    UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
    INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
    END;

上面是 pgbench 自带的测试脚本，用户也可以自己编写测试脚本。

## 6.测试 ##

[root@April ~]# /usr/pgsql-9.2/bin/pgbench -c 10 -t 100 mydb -U postgres
    starting vacuum...end.
    transaction type: TPC-B (sort of)
    scaling factor: 1
    query mode: simple
    number of clients: 10
    number of threads: 1
    number of transactions per client: 100
    number of transactions actually processed: 1000/1000
    tps = 188.343745 (including connections establishing)
    tps = 198.919984 (excluding connections establishing)

其中: -c 表示客户数目，也即并发的连接数目。 -t 表示每个客户启动的事务数目。-U 表示用户名。

tps 表示每秒处理的事务数，一个是包含网络开销(including)，另一个是不包含网络开销的(excluding)。