pgbench test PostgreSQL performace

测试思路参考一位朋友，具体操作为本人实测。

测试环境：

PC 2台 4G RAM E5200 CPU OpenSUSE 11.4 64bit 

Postgresql 9.0.4 建立流复制

准备测试数据，可以使用pgbench自带的测试模板

初始化数据

pgbench -i -F 10 -s 20 kyle 

初始化的时候scale使用了20，也就是创建了200W条account记录.

创建4个表，大小分别如下

pgbench_accounts

2000000

pgbench_branches

20

pgbench_tellers

200

pgbench_history

0

表结构如下：

kyle=# \d pgbench_accounts

   Table "public.pgbench_accounts"

  Column  |     Type      | Modifiers 

----------+---------------+-----------

 aid      | integer       | not null

 bid      | integer       | 

 abalance | integer       | 

 filler   | character(84) | 

Indexes:

    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

kyle=# \d pgbench_branches 

   Table "public.pgbench_branches"

  Column  |     Type      | Modifiers 

----------+---------------+-----------

 bid      | integer       | not null

 bbalance | integer       | 

 filler   | character(88) | 

Indexes:

    "pgbench_branches_pkey" PRIMARY KEY, btree (bid)

kyle=# \d pgbench_tellers

    Table "public.pgbench_tellers"

  Column  |     Type      | Modifiers 

----------+---------------+-----------

 tid      | integer       | not null

 bid      | integer       | 

 tbalance | integer       | 

 filler   | character(84) | 

Indexes:

    "pgbench_tellers_pkey" PRIMARY KEY, btree (tid)

kyle=# \d pgbench_history

          Table "public.pgbench_history"

 Column |            Type             | Modifiers 

--------+-----------------------------+-----------

 tid    | integer                     | 

 bid    | integer                     | 

 aid    | integer                     | 

 delta  | integer                     | 

 mtime  | timestamp without time zone | 

 filler | character(22)               | 

DB大小

kyle=# SELECT pg_database_size('kyle')/1024/1024/1024||'GB';

 ?column? 

----------

 2GB

(1 row)

测试前使用pgfincore将除历史表以外的表放入内存。（pgfincore的function需要单独安装，下载）

kyle=# SELECT * from pgfincore('pgbench_accounts');

      relpath       | segment | os_page_size | rel_os_pages | pages_mem | group_mem | os_pages_free | databit 

--------------------+---------+--------------+--------------+-----------+-----------+---------------+---------

 base/24913/24926   |       0 |         4096 |       262144 |    262086 |        28 |          5779 | 

 base/24913/24926.1 |       1 |         4096 |       262144 |    262144 |         1 |          5779 | 

 base/24913/24926.2 |       2 |         4096 |       142380 |    140268 |         1 |          5779 | 

(3 rows)

kyle=# select * from pgfincore('pgbench_branches');

     relpath      | segment | os_page_size | rel_os_pages | pages_mem | group_mem | os_pages_free | databit 

------------------+---------+--------------+--------------+-----------+-----------+---------------+---------

 base/24913/24914 |       0 |         4096 |            2 |         2 |         1 |          5591 | 

(1 row)

kyle=# select * from pgfincore('pgbench_tellers');

     relpath      | segment | os_page_size | rel_os_pages | pages_mem | group_mem | os_pages_free | databit 

------------------+---------+--------------+--------------+-----------+-----------+---------------+---------

 base/24913/24917 |       0 |         4096 |           24 |        25 |         1 |          5562 | 

(1 row)

详细测试步骤：

首先清空SQL统计信息,方便后面统计

kyle=# select pg_stat_statements_reset();

此函数只有在你的数据库添加了pg_stat_statements.sql 后才能使用。

而此函数的添加你需要到DB源码包中 make；make install。

并且开启SQL跟踪（在配置文件中）

shared_preload_libraries = 'pg_stat_statements'

custom_variable_classes = 'pg_stat_statements' # list of custom variable class names

pg_stat_statements.max = 10000

pg_stat_statements.track = all

只读测试

脚本：

cat pgbench.sql

\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
SELECT abalance FROM pgbench_accounts WHERE aid = aid;

$ pgbench -c 10 -j 5 -M prepared -n -s 20 -T 60 -f ./pgbench.sql -p 2011 -U postgres kyle

transaction type: Custom query

scaling factor: 20

query mode: prepared

number of clients: 10

number of threads: 5

duration: 60 s

number of transactions actually processed: 30

tps = 0.376122 (including connections establishing)

tps = 0.376199 (excluding connections establishing)

读写测试

建立测试function：

create or replace function pgbench(i_aid int,i_bid int,i_tid int,i_delta int) returns setof int as $BODY$
declare
begin
UPDATE pgbench_accounts SET abalance = abalance + i_delta WHERE aid = i_aid;
UPDATE pgbench_tellers SET tbalance = tbalance + i_delta WHERE tid = i_tid;
UPDATE pgbench_branches SET bbalance = bbalance + i_delta WHERE bid = i_bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (i_tid, i_bid, i_aid, i_delta, CURRENT_TIMESTAMP);
return query SELECT abalance FROM pgbench_accounts WHERE aid = i_aid;
end;
$BODY$ language plpgsql;

脚本:

\set nbranches :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
select pgbench(:aid,:bid,:tid,:delta);

$ pgbench -c 10 -j 5 -M prepared -n -s 20 -T 60 -f ./pgbenchrw.sql -p 2011 -U postgres kyle

transaction type: Custom query

scaling factor: 20

query mode: prepared

number of clients: 10

number of threads: 5

duration: 60 s

number of transactions actually processed: 24648

tps = 407.736850 (including connections establishing)

tps = 407.877426 (excluding connections establishing)

[pgbench官方参看]