Обсуждение: pgpool not load balancing via foreign table

All;


I created a db node with 2 standby's

then I setup pgpool and set it up with hosts talking to the 3 backend 
nodes above

then I created a stand alone db cluster, and setup a foreign server 
pointing to the pgpool node, then I used the IMPORT FOREIGN SCHEMA to 
import the tables via the pgpool node.

all this worked great


however no matter how many selects I run via the foreign tables a "show 
pool_nodes" on the pgpool server shows  a select_cnt of 0 for all 3 nodes.

If I connect to the pgpool server/cluster and run a number of select 
statements then a "show pool_nodes"  shows increasing values for all 3 
backend nodes for select_cnt


It appears that a foreign table / FDW connection to a pgpool node does 
not trigger the collection of any load balancing stats


Anyone have any ideas of how to get these stats?


Thanks in advance

however no matter how many selects I run via the foreign tables a "show pool_nodes" on the pgpool server shows  a select_cnt of 0 for all 3 nodes.

If I connect to the pgpool server/cluster and run a number of select statements then a "show pool_nodes"  shows increasing values for all 3 backend nodes for select_cnt


It appears that a foreign table / FDW connection to a pgpool node does not trigger the collection of any load balancing stats

On Friday, June 21, 2024, Sbob <sbob@quadratum-braccas.com> wrote:

however no matter how many selects I run via the foreign tables a "show pool_nodes" on the pgpool server shows  a select_cnt of 0 for all 3 nodes.

If I connect to the pgpool server/cluster and run a number of select statements then a "show pool_nodes"  shows increasing values for all 3 backend nodes for select_cnt


It appears that a foreign table / FDW connection to a pgpool node does not trigger the collection of any load balancing stats

Skimming over the project and the extension (for the first time ever…) it seems pgpool only does stats in response to Execute messages while FDW is using the simple query protocol.  That pgpool doesn’t do stats for Query as well seems like an oversight.

On Friday, June 21, 2024, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Friday, June 21, 2024, Sbob <sbob@quadratum-braccas.com> wrote:

however no matter how many selects I run via the foreign tables a "show pool_nodes" on the pgpool server shows  a select_cnt of 0 for all 3 nodes.

If I connect to the pgpool server/cluster and run a number of select statements then a "show pool_nodes"  shows increasing values for all 3 backend nodes for select_cnt


It appears that a foreign table / FDW connection to a pgpool node does not trigger the collection of any load balancing stats

Skimming over the project and the extension (for the first time ever…) it seems pgpool only does stats in response to Execute messages while FDW is using the simple query protocol.  That pgpool doesn’t do stats for Query as well seems like an oversight.

Nevermind…or at least while possible further reading of the pgpool code doesn’t support it - the simple query case is present.

David J.

 

Im not sure what the 'simple query protocol' is, is this an option when setting up a FDW/Foreign Table? Or is this something that pgpool just does not support?

I also ran a test where I set all 3 backend nodes to have 'log_min_duration_statement = 0' and I ran a tail on the logs for all 3 backend data nodes while I ran a series of selects via a new connection to the FDW database for each select. And I only see selects logged in the logfile of the master/primary backend db.

Is there anything I can do to force the load balancing?