192.168.16.112是pgpool的vip,192.168.16.110是节点1(主节点)的IP,192.168.16.111是备节点的IP。
[postgres@pgpool01 ~]$ psql -h 192.168.16.112 -p 9999 -d test
psql (9.5.6)
Type "help" for help.
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | pgbench_accounts | table | postgres
public | pgbench_branches | table | postgres
public | pgbench_history | table | postgres
public | pgbench_tellers | table | postgres
(4 rows)
重置当前数据库的所有统计计数器
test=# select pg_stat_reset();
pg_stat_reset
---------------
(1 row)
查询以下表被插入的行数,被更新的行数,被删除的行数
[postgres@pgpool01 ~]$ psql -p 5432 -d test
psql (9.5.6)
Type "help" for help.
test=# select relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables;
relname | n_tup_ins | n_tup_upd | n_tup_del
------------------+-----------+-----------+-----------
pgbench_tellers | 0 | 0 | 0
pgbench_accounts | 0 | 0 | 0
pgbench_history | 0 | 0 | 0
pgbench_branches | 0 | 0 | 0
insert_lock | 0 | 0 | 0
(5 rows)
[postgres@pgpool02 ~]$ psql -p 5433 -d test
psql (9.5.6)
Type "help" for help.
test=# select relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables;
relname | n_tup_ins | n_tup_upd | n_tup_del
------------------+-----------+-----------+-----------
pgbench_tellers | 0 | 0 | 0
pgbench_accounts | 0 | 0 | 0
pgbench_history | 0 | 0 | 0
pgbench_branches | 0 | 0 | 0
insert_lock | 0 | 0 | 0
(5 rows)
[postgres@pgpool01 ~]$ pgbench -h 192.168.16.112 -p 9999 -T 2 test
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 2 s
number of transactions actually processed: 202
latency average: 9.902 ms
tps = 100.991012 (including connections establishing)
tps = 102.044992 (excluding connections establishing)
[postgres@pgpool01 ~]$ psql -h 192.168.16.112 -p 9999 -d test
psql (9.5.6)
Type "help" for help.
test=# select relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables;
relname | n_tup_ins | n_tup_upd | n_tup_del
------------------+-----------+-----------+-----------
pgbench_tellers | 0 | 202 | 0
pgbench_accounts | 0 | 202 | 0
pgbench_history | 202 | 0 | 0
pgbench_branches | 0 | 202 | 0
insert_lock | 0 | 0 | 0
(5 rows)
[postgres@pgpool01 ~]$ psql -p 5432 -d test
psql (9.5.6)
Type "help" for help.
test=# select relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables;
relname | n_tup_ins | n_tup_upd | n_tup_del
------------------+-----------+-----------+-----------
pgbench_tellers | 0 | 202 | 0
pgbench_accounts | 0 | 202 | 0
pgbench_history | 202 | 0 | 0
pgbench_branches | 0 | 202 | 0
insert_lock | 0 | 0 | 0
(5 rows)
[postgres@pgpool02 ~]$ psql -p 5433 -d test
psql (9.5.6)
Type "help" for help.
test=# select relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables;
relname | n_tup_ins | n_tup_upd | n_tup_del
------------------+-----------+-----------+-----------
pgbench_tellers | 0 | 0 | 0
pgbench_accounts | 0 | 0 | 0
pgbench_history | 0 | 0 | 0
pgbench_branches | 0 | 0 | 0
insert_lock | 0 | 0 | 0
(5 rows)
重置当前数据库的所有统计计数器
[postgres@pgpool01 ~]$ psql -p 5432 -d test
psql (9.5.6)
Type "help" for help.
test=# select pg_stat_reset();
pg_stat_reset
---------------
(1 row)
[postgres@pgpool02 ~]$ pgbench -h 192.168.16.112 -p 9999 -T 2 test
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 2 s
number of transactions actually processed: 186
latency average: 10.787 ms
tps = 92.706306 (including connections establishing)
tps = 93.830588 (excluding connections establishing)
[postgres@pgpool01 ~]$ psql -p 5432 -d test
psql (9.5.6)
Type "help" for help.
test=# select relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables;
relname | n_tup_ins | n_tup_upd | n_tup_del
------------------+-----------+-----------+-----------
pgbench_tellers | 0 | 143 | 0
pgbench_accounts | 0 | 143 | 0
pgbench_history | 143 | 0 | 0
pgbench_branches | 0 | 143 | 0
insert_lock | 0 | 0 | 0
(5 rows)
[postgres@pgpool02 ~]$ psql -p 5433 -d test
psql (9.5.6)
Type "help" for help.
test=# select relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables;
relname | n_tup_ins | n_tup_upd | n_tup_del
------------------+-----------+-----------+-----------
pgbench_tellers | 0 | 0 | 0
pgbench_accounts | 0 | 0 | 0
pgbench_history | 0 | 0 | 0
pgbench_branches | 0 | 0 | 0
insert_lock | 0 | 0 | 0
(5 rows)