免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 1901 | 回复: 0

pgbench test PostgreSQL performace [复制链接]

论坛徽章:
0
发表于 2011-12-22 08:53 |显示全部楼层
测试思路参考一位朋友,具体操作为本人实测。

测试环境:
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
  1. \set naccounts 100000 * :scale
  2. \setrandom aid 1 :naccounts
  3. 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:
  1. create or replace function pgbench(i_aid int,i_bid int,i_tid int,i_delta int) returns setof int as $BODY$
  2. declare
  3. begin
  4. UPDATE pgbench_accounts SET abalance = abalance + i_delta WHERE aid = i_aid;
  5. UPDATE pgbench_tellers SET tbalance = tbalance + i_delta WHERE tid = i_tid;
  6. UPDATE pgbench_branches SET bbalance = bbalance + i_delta WHERE bid = i_bid;
  7. INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (i_tid, i_bid, i_aid, i_delta, CURRENT_TIMESTAMP);
  8. return query SELECT abalance FROM pgbench_accounts WHERE aid = i_aid;
  9. end;
  10. $BODY$ language plpgsql;

脚本:
  1. \set nbranches :scale
  2. \set ntellers 10 * :scale
  3. \set naccounts 100000 * :scale
  4. \setrandom aid 1 :naccounts
  5. \setrandom bid 1 :nbranches
  6. \setrandom tid 1 :ntellers
  7. \setrandom delta -5000 5000
  8. 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官方参看]






您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP