Various Ways to Retrieve Pgpool-II's Statistics


 

Pgpool-II is a PostgreSQL cluster management tool. In this post I will explain how to retrieve the cluster statistics from Pgpool-II.

There are 3 ways to retrieve cluster statistics:

         The SHOW commands can be issued inside a SQL session. They are not forwarded to PostgreSQL and only processed by Pgpool-II.           Since PCP commands can be executed via network, you are able to operate Pgpool-II from remote servers using PCP commands.
         Pgpool_adm is an extension to allow to access to PCP commands. The advantage of using pgpool_adm is that you can specify specific conditions to retrieve the required data. To use pgpool_adm you need to install pgpool-II-pg*-extensions package and execute "CREATE EXTENSION pgpool_adm" on PostgreSQL servers.
 
Next, let's see how to retrieve Pgpool-II's statistics.

Backend nodes statistics

To retrieve backend nodes statistics, you can execute the following commands.

SHOW command

$ psql -h localhost -p 11000 -c "SHOW POOL_NODES"

 node_id | hostname | port  | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | /tmp     | 11002 | up     | 0.500000  | primary | 4          | false             | 0                 |                   |                        | 2021-01-31 14:47:23
 1       | /tmp     | 11003 | up     | 0.500000  | standby | 0          | true              | 0                 | streaming         | async                  | 2021-01-31 14:47:23

PCP command 

$ pcp_node_info -h localhost -U pengbo -p 11001 -n 0
Password:
/tmp 11002 2 0.500000 up primary 0   2021-01-31 14:47:23

$ pcp_node_info -h localhost -U pengbo -p 11001 -n 1
Password:
/tmp 11003 2 0.500000 up standby 0 streaming async 2021-01-31 14:47:23

pgpool_adm 

$ psql -h localhost -p 11000

postgres=#  SELECT * FROM pcp_node_info(node_id => 0, host => 'localhost', port => 11001, username => 'pengbo', password => 'pengbo');
-[ RECORD 1 ]----------+--------------------
host                   | /tmp
port                   | 11002
status                 | Connection in use
weight                 | 0
role                   | Primary
replication_delay      | 0
replication_state      |
replication_sync_state |
last_status_change     | 2021-01-31 20:54:25

postgres=#  SELECT * FROM pcp_node_info(node_id => 1, host => 'localhost', port => 11001, username => 'pengbo', password => 'pengbo');
-[ RECORD 1 ]----------+--------------------
host                   | /tmp
port                   | 11003
status                 | Connection in use
weight                 | 0
role                   | Standby
replication_delay      | 0
replication_state      | streaming
replication_sync_state | async
last_status_change     | 2021-01-31 20:54:25
 

If you want to retrieve particular information, such as the role of the backend node, just specify the columns.

postgres=# SELECT host, role FROM pcp_node_info(node_id => 0, host => 'localhost', port => 11001, username => 'pengbo', password => 'pengbo');
-[ RECORD 1 ]-
host | /tmp
role | Primary

postgres=# SELECT host, role FROM pcp_node_info(node_id => 1, host => 'localhost', port => 11001, username => 'pengbo', password => 'pengbo');
-[ RECORD 1 ]-
host | /tmp
role | Standby

Pgpool-II's child processes

You can retrieve the information of Pgpool-II's child processes using SHOW POOL_PROCESSES and pcp_proc_info. You can format the results to retrieve the max concurrent connections, used connections and available connections of Pgpool-II.

SHOW command

postgres=# SHOW POOL_PROCESSES;
 pool_pid |     start_time      | database | username |     create_time     | pool_counter
----------+---------------------+----------+----------+---------------------+--------------
 25095    | 2021-01-31 20:03:29 |          |          |                     |
 25096    | 2021-01-31 20:03:29 |          |          |                     |
 25097    | 2021-01-31 20:03:29 | postgres | pengbo   | 2021-01-31 20:07:21 |

... 

PCP command

$  pcp_proc_info -h localhost -p 11001 -U pengbo --all
Password:
  2021-01-31 20:03:29  0 0 0 0 0 25095 0
  2021-01-31 20:03:29  0 0 0 0 0 25095 1
  2021-01-31 20:03:29  0 0 0 0 0 25095 0
  2021-01-31 20:03:29  0 0 0 0 0 25095 1
  2021-01-31 20:03:29  0 0 0 0 0 25096 0
  2021-01-31 20:03:29  0 0 0 0 0 25096 1
  2021-01-31 20:03:29  0 0 0 0 0 25096 0
  2021-01-31 20:03:29  0 0 0 0 0 25096 1
postgres pengbo 2021-01-31 20:03:29 2021-01-31 20:07:21 3 0 1 25261 1 25097 0
postgres pengbo 2021-01-31 20:03:29 2021-01-31 20:07:21 3 0 1 25262 1 25097 1
  2021-01-31 20:03:29  0 0 0 0 0 25097 0
  2021-01-31 20:03:29  0 0 0 0 0 25097 1

...

Watchdog information

To retrieve watchdog information, you can use pcp_watchdog_info command. So far, the pgpool_adm command has not been able to output this information.

$ pcp_watchdog_info -h localhost -U postgres

3 NO Linux_host1.localdomain_9991 host1

Linux_host1.localdomain_9991 host1 9991 9001 7 STANDBY
Linux_host2.localdomain_9992 host2 9992 9002 4 LEADER
Linux_host3.localdomain_9993 host3 9993 9003 7 STANDBY

Backend SQL command statistics

SHOW command

SHOW POOL_BACKEND_STATS command displays the SELECT/INSERT/UPDATE/DELETE/DDL/other query counts issued to each backend and the error messages returned from each backend.

$ psql -h localhost -p 11000
postgres=# SHOW POOL_BACKEND_STATS;
-[ RECORD 1 ]-------
node_id    | 0
hostname   | /tmp
port       | 11002
status     | up
role       | primary
select_cnt | 17
insert_cnt | 0
update_cnt | 0
delete_cnt | 0
ddl_cnt    | 0
other_cnt  | 18
panic_cnt  | 0
fatal_cnt  | 0
error_cnt  | 8
-[ RECORD 2 ]-------
node_id    | 1
hostname   | /tmp
port       | 11003
status     | up
role       | standby
select_cnt | 1
insert_cnt | 0
update_cnt | 0
delete_cnt | 0
ddl_cnt    | 0
other_cnt  | 11
panic_cnt  | 0
fatal_cnt  | 0
error_cnt  | 1

Configuration parameters

The following commands can be used to display the configuration parameters, values and the descriptions.

SHOW command

$ psql -h localhost -p 11000 postgres -c " SHOW POOL_STATUS"
         item        |    value   |           description                                   
---------------------+------------+--------------------------------------------------
 listen_addresses    | localhost  | host name(s) or IP address(es) to listen on
 port                | 11000      | pgpool accepting port number
 socket_dir          | /tmp       | pgpool socket directory
 pcp_listen_addresses| *          | host name(s) or IP address(es) for pcp process to listen on
 pcp_port            | 11001      | PCP port # to bind
 pcp_socket_dir      | /tmp       | PCP socket directory
 enable_pool_hba     | 0          | if true, use pool_hba.conf for client authentication

PCP command

$ pcp_pool_status -h localhost -p 11001
Password:
name : listen_addresses
value: localhost
desc : host name(s) or IP address(es) to listen on

name : port
value: 11000
desc : pgpool accepting port number

name : socket_dir
value: /tmp
desc : pgpool socket directory

pgpool_adm

As shown in the result, you can specify particular parameter using pgpool_adm.

$ psql -h localhost -p 11000 -c "SELECT * FROM pcp_pool_status(host => 'localhost', port => 11001, username => 'pengbo', password => 'pengbo') WHERE item = 'port';"
 item | value |         description          
------+-------+------------------------------
 port | 11000 | pgpool accepting port number


Comments

Popular posts from this blog

Connection Pooling in Pgpool-II

Monitoring PostgreSQL Cluster via Pgpool-II with Prometheus

Authentication in Pgpool-II