[pgpool-general: 8424] Re: pgpool with HammerDB

[Tatsuo Ishii]

> I'm having some trouble getting load balancing to run with HammerDB. I have 3 nodes of postgres in streamin replication mode. One primary (RW), two secondary (RO). HammerDB has 5 procedures (with BEGIN-END), two of which I know are read only. The 5 procedures are, new order (defined as neword), payment (defined as payment), delivery (defined as delivery), order status (defined as ostat), and stock level (defined as slev)/ The two that are read only are ostat, and slev. I figured these to procedures should be load balanced, while all others go to the primary node.
> 
> Basically, I'm trying to create the scenario described in the following HammerDB blog. However, rather than configuring HammerDB to understand that I have a primary and two secondaries. I'd rather front the cluster with pgpool, and point HammerDB to pgpool like it's a single RW node (and pgpool handles the rest).
> https://www.hammerdb.com/blog/uncategorized/hammerdb-v4-0-new-features-pt4-connect-pooling-for-clusters/
> 
> This setup runs, but I see 100% of procedures going to the primary (RW) node only. This is even when I set the following configs:
> read_only_function_list = 'slev,ostat'
> statement_level_load_balance = on
> 
> I would have thought, that having the above, would get slev and ostat to go to the secondary nodes.
> 
> What am I misunderstanding about pgpool?
> I'm running:
> 
> pgpoolii version 4.3.3.
> Postgres 14.5
> HammerDB 4.5

I am not familiar with HammerDB but there are number of reasons why
pgpool does not do load balancing.

https://www.pgpool.net/docs/latest/en/html/runtime-config-load-balancing.html

If you read this and still you are not sure why, please enable
"log_client_messages" and share pgpool log.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp