论坛徽章:: 0

电梯直达

1楼 [收藏(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');

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

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');

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

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

(1 row)

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

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

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;