<html xmlns:v="urn:schemas-microsoft-com:vml" 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=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><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;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.EmailStyle18
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.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;}
/* List Definitions */
@list l0
{mso-list-id:1030034551;
mso-list-type:hybrid;
mso-list-template-ids:541498850 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l0:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal">Hi<o:p></o:p></p>
<p class="MsoNormal">PgPool gurus I need some help – probably lacking basic understanding.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I came to a project which was relying on pgpool for all three main features – replication, pooling and load_balancing.<o:p></o:p></p>
<p class="MsoNormal">Project was running on 1(master)+3(replica) cluster suffering from frequent inconsistency issues ( select issued right after insert/update returning not expected results ) as well as extremely
<o:p></o:p></p>
<p class="MsoNormal">unbalanced heavy load on master at times when system becomes busy due to multiple small queries …
<o:p></o:p></p>
<p class="MsoNormal">The former , I guess due to wrong / lack of transaction management in application , the later – due to multiple queries issues by pgpool itself against master to validate ability to redirect “selects” to replicas.<o:p></o:p></p>
<p class="MsoNormal">( this , in turn , might be influenced/explained by somewhat big data dictionary I have – hundreds of schemsa with 20-40 tables in each ? ) .<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Anyway , by simply setting load_balance_mode to “off” I was able to resolve both above issues and release 2 servers ( per environment ! ). Now running on 1 + 1 using replica for failover only.<o:p></o:p></p>
<p class="MsoNormal">Needless to say that transaction response time was closely monitored and remains same or lower after the change …<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">However , this “failover only” part , makes me unhappy as entire server still stays unutilized. Furthermore , I am trying to leverage internal knowledge that one ( biggest ) database on the cluster is essentially “read only”<o:p></o:p></p>
<p class="MsoNormal">( only controlled bulk loads few times an hour ) . Basically , want to have an ability to route queries for certain database to dedicated replica … I also want these connections to use large work_mem …<o:p></o:p></p>
<p class="MsoNormal">Unfortunately was not able to find a way to implement via pgpool. Answers for questions below could really help :
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Two questions :<o:p></o:p></p>
<p class="MsoListParagraph" style="text-indent:-.25in;mso-list:l0 level1 lfo2"><![if !supportLists]><span style="mso-list:Ignore">1.<span style="font:7.0pt "Times New Roman"">
</span></span><![endif]><span dir="LTR"></span>Tests I performed shows that pgpool is issuing queries on master with or without database_redirect_preference_list set ( in load_balance mode , of course ? )<o:p></o:p></p>
<p class="MsoListParagraph">Is it expected behavior ? Configurable , maybe ?<o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoListParagraph">The second question is somewhat related :<o:p></o:p></p>
<p class="MsoListParagraph" style="text-indent:-.25in;mso-list:l0 level1 lfo2"><![if !supportLists]><span style="mso-list:Ignore">2.<span style="font:7.0pt "Times New Roman"">
</span></span><![endif]><span dir="LTR"></span>With load_balance_mode=off I would expect no connections to be opened to replica. Am I right ?<o:p></o:p></p>
<p class="MsoListParagraph">One can tell that it is necessary for faster failover …<o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoListParagraph">Appreciate your help<o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoNormal">Versions involved :<o:p></o:p></p>
<p class="MsoNormal">Current prod versions<o:p></o:p></p>
<p class="MsoNormal">Postgres : PostgreSQL 9.3.3 ( scheduled upgrade for 9.5 already . 9.6 ? – maybe )<o:p></o:p></p>
<p class="MsoNormal">pgpool : pgpool-II version 3.4.7 (tataraboshi)<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Tests above performed on PostgreSQL 9.5.5 and pgpool-II version 3.5.4 (ekieboshi)<o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="492" style="width:368.85pt;margin-left:.15pt;border-collapse:collapse">
<tbody>
<tr style="height:27.5pt">
<td width="120" valign="top" style="width:89.85pt;padding:0in 5.4pt 0in 5.4pt;height:27.5pt">
<p class="MsoNormal"><img width="103" height="43" id="_x0000_i1026" src="cid:image001.png@01D28513.C94B3DF0" alt="download"><o:p></o:p></p>
</td>
<td width="306" valign="top" style="width:229.5pt;padding:0in 5.4pt 0in 5.4pt;height:27.5pt">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:#A6A6A6">Ilya Dorfman</span></b><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:#A6A6A6">
</span><span lang="FR-BE" style="font-size:10.0pt;font-family:"Arial",sans-serif;color:#A6A6A6"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="FR-BE" style="font-size:10.0pt;font-family:"Arial",sans-serif;color:#A6A6A6">StormRunner DBA<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial",sans-serif;color:#A6A6A6">Office +972-35398451 | Mobile +972-52-6815384
<o:p></o:p></span></p>
</td>
<td width="66" valign="top" style="width:49.5pt;padding:0in 5.4pt 0in 5.4pt;height:27.5pt">
<p class="MsoNormal"><a href="https://www.hp.com/go/srl"><span style="color:windowtext;text-decoration:none"><img border="0" width="50" height="50" id="Picture_x0020_2" src="cid:image002.png@01D28513.C94B3DF0" alt="cid:image002.png@01D12D20.35EFB3C0"></span></a><o:p></o:p></p>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>