pgpool 通过统计信息查看数据插入

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)