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

Post a Comment

Popular posts from this blog

Installing Pgpool-II on Debian/Ubuntu

-
First of all, many thanks to the PostgreSQL Global Development Group (PGDG) for creating Pgpool-II packages for Debian and Ubuntu. This post shows you step by step how to install Pgpool-II using official APT repository provided by PGDG. Prerequisites This blog assumes you have already installed two PostgreSQL 14 servers and setup streaming replication between the PostgreSQL servers.   For testing purpose,we set trust authentication in pg_hba.conf . In a production environment, Please follow the PostgreSQL and Pgpool-II documentation to configure proper authentication settings.  Install Pgpool-II To use the PostgreSQL apt repository, follow the steps below. Create the repository configuration file: # echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list Import the repository signing key: # wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Update the package lists: #
Read more

Query Load Balancing in Pgpool-II

-
Image
Pgpool-II is a PostgreSQL cluster management tool. The major features of Pgpool-II are: Connection pooling Query load balancing Automated failover Watchdog (High availability of Pgpool-II) Replication In memory query cache In this post, I will describe the query load balancing mechanism in Pgpool-II and the relevant configuration parameters. Why use query load balancing? Query load balancing can distribute database server workloads across multiple PostgreSQL servers. Nowadays, most database systems use multiple replicated database servers to achieve high availability for PostgreSQL. Pgpool-II takes the advantage of the replication feature in order to distribute the workloads across multiple PostgreSQL servers. If there is a PostgreSQL cluster with multiple PostgreSQL servers, Pgpool-II is able to distribute READ queries across those PostgreSQL servers. The benefit of READ queries load balancing is, improve the system's throughput reduce the load on each PostgreSQL server optimize
Read more

Connection Pooling in Pgpool-II

-
Image
Pgpool-II is a cluster management tool for PostgreSQL that can cache connections to PostgreSQL servers. This blog introduces Pgpool-II connection pooling feature and shows how to configure connection pooling in Pgpool-II. What is connection pooling? Establishing and maintaining Database connections are expensive. The reason is that you have to establish a network connection, perform authentication and so on. Database connection pooling is a cache of database connections to keep database connections open so that the connection can be reused when a connection is required by a future request. Reusing an active connection rather than establishing a new connection each time a connection is requested can improve performance and save system resources. How does connection pooling work in Pgpool-II? Pgpool-II caches established connections to the PostgreSQL servers and reuses them whenever a new request with the same properties (i.e. user name, database, protocol version, and other con
Read more