上篇 blog 介绍了搭建简单的基于表的 Londiste 复制，接下来使用 pgbench 测试下性能，看看在较大 update 压力下，复制是否依然正常。

关于 pgbench

这里使用 pgbench 做压力测试，当然也可以选择其它工具，关于 pgbench 的使用可以参考之前的 blog: 使用 pgbench 进行数据库压力测试

Londiste 压力测试

2.1 创建测试表并插入测试数据 (源库和目标库)

skytf=> create table test_lond2(id serial primary key ,amount int8,create_time timestamp(0) without time zone);
NOTICE: CREATE TABLE will create implicit sequence "test_lond2_id_seq" for serial column "test_lond2.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_lond2_pkey" for table "test_lond2"
CREATE TABLE
  
skytf=> /d test_Lond2
                    Table "skytf.test_lond2"
 Column  |       Type       |            Modifiers            
-------------+--------------------------------+---------------------------------------------------------
id     | integer            | not null default nextval('test_lond2_id_seq'::regclass)
amount   | bigint             |
create_time | timestamp(0) without time zone |
Indexes:
  "test_lond2_pkey" PRIMARY KEY, btree (id)

skytf=> insert into test_lond2 (amount) select 0 from generate_series(1,10000);
INSERT 0 10000
  
skytf=> select * from test_lond2 limit 3;
id | amount | create_time
----+--------+-------------
 1 |   0 |
 2 |   0 |
 3 |   0 |
(3 rows)

2.2 添加表到 Londsite 复制列表（源库）

1 2	[pg92@redhatB londiste]$ londiste3 p_skytf.ini add-table skytf.test_lond2 2013-03-30 10:53:19,964 29596 INFO Table added: skytf.test_lond2

2.3 添加表到 Londsite 复制列表（目标库）

1 2	[pg91@redhat6 londiste]$ londiste3 s_skytf.ini add-table skytf.test_lond2 2013-03-30 10:54:04,596 8629 INFO Table added: skytf.test_lond2

2.4 检查源库和目标库表同步状态

[pg92@redhatB londiste]$ londiste3 p_skytf.ini tables
Tables on node
table_name    merge_state   table_attrs
---------------- --------------- ---------------
skytf.test_lond1 ok       
skytf.test_lond2 ok

备注：两节点 merge_state 状态都显示为 ok 时表示完成同步，尤其是目标节点，merge_state 状态变化为以下： none -> in-copy -> catching-up -> ok 。

2.5 编写压力测试 SQL 脚本 update_1.sql

1
2
3

setrandom v_amount 1 10000
  
update skytf.test_lond2 set amount=repeat(:v_amount::text,3)::int8,create_time=clock_timestamp() where id=:v_amount;

2.6 pgbench 压力测试(源库)

nohup pgbench -c 10 -T 30 -j 5 -n -d skytf -U skytf -f update_1.sql > update_1.out &
  
pghost: pgport: 1921 nclients: 10 duration: 30 dbName: skytf
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 5
duration: 30 s
number of transactions actually processed: 37403
tps = 1246.423194 (including connections establishing)
tps = 1251.730479 (excluding connections establishing)

2.7 源库数据验证

[pg92@redhatB ~]$ psql skytf skytf
psql (9.2.1)
skytf=> select * from test_lond2 where id > 100 and id < 106 order by id;
id | amount  |   create_time  
-----+-----------+---------------------
101 | 101101101 | 2013-03-30 11:16:34
102 | 102102102 | 2013-03-30 11:16:30
103 |  103103 | 2013-03-30 11:13:59
104 | 104104104 | 2013-03-30 11:16:36
105 | 105105105 | 2013-03-30 11:16:35
(5 rows)

2.8 目标库数据验证

[pg91@redhat6 ~]$ psql skytf skytf
psql (9.1.2)
Type "help" for help.
  
skytf=> select * from test_lond2 where id > 100 and id < 106 order by id;
id | amount  |   create_time  
-----+-----------+---------------------
101 | 101101101 | 2013-03-30 11:16:34
102 | 102102102 | 2013-03-30 11:16:30
103 |  103103 | 2013-03-30 11:13:59
104 | 104104104 | 2013-03-30 11:16:36
105 | 105105105 | 2013-03-30 11:16:35
(5 rows)

备注：可见数据已经同步过来了。

2.9 compare 比较记录条数

[pg91@redhat6 londiste]$ londiste3 s_skytf.ini compare
2013-03-30 11:23:07,946 9164 INFO Checking if node_p can be used for copy
2013-03-30 11:23:07,950 9164 INFO Node node_p seems good source, using it
2013-03-30 11:23:07,951 9164 INFO skytf.test_lond1: Using node node_p as provider
2013-03-30 11:23:08,024 9164 INFO Provider: node_p (root)
2013-03-30 11:23:08,045 9164 INFO Locking skytf.test_lond1
2013-03-30 11:23:08,052 9164 INFO Syncing skytf.test_lond1
2013-03-30 11:23:10,623 9164 INFO Counting skytf.test_lond1
2013-03-30 11:23:10,769 9164 INFO srcdb: 701 rows, checksum=455541632
2013-03-30 11:23:10,783 9164 INFO dstdb: 701 rows, checksum=455541632
2013-03-30 11:23:10,855 9164 INFO Checking if node_p can be used for copy
2013-03-30 11:23:10,866 9164 INFO Node node_p seems good source, using it
2013-03-30 11:23:10,867 9164 INFO skytf.test_lond2: Using node node_p as provider
2013-03-30 11:23:10,953 9164 INFO Provider: node_p (root)
2013-03-30 11:23:10,972 9164 INFO Locking skytf.test_lond2
2013-03-30 11:23:10,979 9164 INFO Syncing skytf.test_lond2
2013-03-30 11:23:13,564 9164 INFO Counting skytf.test_lond2
2013-03-30 11:23:13,690 9164 INFO srcdb: 10000 rows, checksum=-93227414953
2013-03-30 11:23:13,754 9164 INFO dstdb: 10000 rows, checksum=-93227414953

参考

原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/237949.html

Londiste3：压力测试

关于 pgbench

Londiste 压力测试

参考

发表回复

Londiste3：压力测试

关于 pgbench

Londiste 压力测试

参考

相关推荐

发表回复