<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style>
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">Hi all, I know this is an edge case (and likely not supported) but I have a setup with a postgres streaming cluster running across 2 datacenters with the primary in on datacenter and standby in
the other. Postgres is managing the streaming replication. I insert pgpool between the application and the postgres nodes and set the pgpool weight for the remote datacenter node to 0, while setting the weight of the local (local to the application and pgpool)
postgres node to 1. This setup works great in that all local application 'selects' get sent to the local datacenter node and all writes go to the 'primary' node in the remote datacenter. And this setup solves the issue I am trying to address. </span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black"> </span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">However, I'm seeing a 2+ second latency in the application request response times under this setup, even though the node in the remote site is set to a weight of 0 and all 'selects' go to the database
node in the local datacenter. As a test, I've set the weight of both nodes to a weight of 1 and see selects going to both node and the 2+ second latency stays the same, which is something I would expect since some ‘selects’ are being sent to the remote datacenter.
If I shutdown the remote datacenter node the latency goes away. But if I detach the remote node from pgpool it does not. </span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black"> </span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">I've also brought the 2 nodes (primary and standby) into the same local datacenter and the latency doesn't show so it seems that the latency seen with the nodes in different datacenters has to
do with pgpool communications across the WAN. </span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black"> </span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">So now my question... Under the 2 datacenter model, with the remote node set to weight 0 and local node set to weight 1, why would there be a latency in response times through pgpool if none of
the select statements are being sent to the remote datacenter?</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black"> </span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">Also, I have confirmed from query logs that no select requests are hitting the node in the remote datacenter by the app. Only the sr and health_check requests are seen by that node coming from
the pgpool node. Below is my config. I've tried many variations of the config to try to eliminate the added latency but nothing seems to work. Any help would be greatly appreciated.</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black"> </span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black"> </span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">pgpool-II version 4.1.4 (karasukiboshi) running on RHEL7</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black"> </span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">My pgpool.conf</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">```</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">listen_addresses='*'</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">port=5342</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">socket_dir = '/var/run/pgpool-II-13'</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">pid_file_name = '/var/run/pgpool-II-13/pgpool.pid'</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">pcp_listen_addresses = '*'</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">pcp_port = 9898</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">pcp_socket_dir = '/var/run/pgpool-II-13'</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">pool_passwd = ''</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black"> </span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">backend_hostname0 = 'local-dc.dbnode.com'</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">backend_port0 = 5432</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">backend_weight0 = 1</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black"> </span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">backend_hostname1 = 'remote-dc.dbnode.com''</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">backend_port1 = 5432</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">backend_weight1 = 0</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black"> </span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">load_balance_mode = on</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black"> </span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">master_slave_mode = on</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">master_slave_sub_mode = 'stream'</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">replication_mode = off</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black"> </span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">sr_check_period = 10</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">sr_check_user = 'pgpool_monitor'</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">sr_check_password = 'xxxxxx'</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">sr_check_database = 'postgres'</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">delay_threshold = 10240</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black"> </span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">allow_clear_text_frontend_auth = on</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">ssl = 'on'</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">ssl_cert = '/etc/pki/tls/certs/internal.pem'</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">ssl_key = '/etc/pki/tls/private/internal.key'</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:12.0pt;color:black">```</span><span style="font-size:10.0pt;color:black"><o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>