<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted1 ContentPasted2 ContentPasted3 ContentPasted4">
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.<br>
</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
<br>
</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted5">
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).<br>
</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted0">
<a href="https://www.hammerdb.com/blog/uncategorized/hammerdb-v4-0-new-features-pt4-connect-pooling-for-clusters/" id="LPNoLPOWALinkPreview">https://www.hammerdb.com/blog/uncategorized/hammerdb-v4-0-new-features-pt4-connect-pooling-for-clusters/</a></div>
<div class="_Entity _EType_OWALinkPreview _EId_OWALinkPreview _EReadonly_1"></div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">
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:</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted6">
read_only_function_list = 'slev,ostat'</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted6 ContentPasted7">
statement_level_load_balance = on</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted6 ContentPasted7">
<br>
</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted6 ContentPasted7">
I would have thought, that having the above, would get slev and ostat to go to the secondary nodes.</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted6 ContentPasted7">
<br>
</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted6 ContentPasted7">
What am I misunderstanding about pgpool?</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted6 ContentPasted7">
I'm running:</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted6 ContentPasted7">
<br>
</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted6 ContentPasted7">
pgpoolii version 4.3.3.</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted6 ContentPasted7">
Postgres 14.5</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted6 ContentPasted7">
HammerDB 4.5<br>
</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted6 ContentPasted7">
<br>
</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted6 ContentPasted7">
<br>
</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted6 ContentPasted7">
Thanks,</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted6 ContentPasted7">
Julio S<br>
</div>
<div style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof ContentPasted0">
<br>
</div>
</body>
</html>