<html><body><div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px"><br><div id="yui_3_16_0_1_1438532868409_4313" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"><div id="yui_3_16_0_1_1438532868409_4312" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"><div id="yui_3_16_0_1_1438532868409_4315" class="y_msg_container"><br><div id="yiv0977138448"><div id="yui_3_16_0_1_1438532868409_4326"><div id="yui_3_16_0_1_1438532868409_4325" style="color:#000;background-color:#fff;font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;"><div id="yiv0977138448yui_3_16_0_1_1438322534381_3052"><span>Hi All,</span></div><div id="yiv0977138448yui_3_16_0_1_1438322534381_3205"><br clear="none"><span></span></div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_3206">I have a pgpool-II version 3.3.4 (tokakiboshi) Master-Slave setup and two Postgres 9.3.3 servers configured also as Master-Slave with Streaming Replication.</div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_3433">On a application side i have a JPA(Hibernate) app running on a glassfish 4.1 server.</div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_3605"><br clear="none"></div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_3514">My nodes are defined as:</div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_3646"><br clear="none"></div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_3647">backend_hostname0 = '192.168.1.31'<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3649" clear="none">backend_port0 = 5432<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3651" clear="none">backend_weight0 = 1<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3653" clear="none">backend_data_directory0 = '/var/lib/postgresql/9.3/data'<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3655" clear="none">backend_flag0= 'ALLOW_TO_FAILOVER'<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3657" clear="none"><br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3659" clear="none">backend_hostname1 = '192.168.1.32'<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3661" clear="none">backend_port1 = 5432<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3663" clear="none">backend_weight1 = 0<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3665" clear="none">backend_data_directory1 = '/var/lib/postgresql/9.3/data'<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3667" clear="none">backend_flag1= 'ALLOW_TO_FAILOVER'<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3669" clear="none"></div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_3710"><br clear="none"></div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_3711"><br clear="none"></div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_3712">on each postgresql node I have : <br clear="none"></div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_4226"><br clear="none"></div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_3824"># - Memory -<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3799" clear="none">shared_buffers = 2560MB # min 128kB<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3805" clear="none"><br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3811" clear="none">max_prepared_transactions = 512 # zero disables the feature<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3813" clear="none"> # (change requires restart)<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3815" clear="none"># Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3817" clear="none"># per transaction slot, plus lock space (see max_locks_per_transaction).<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3819" clear="none"># It is not advisable to set max_prepared_transactions nonzero unless you<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3821" clear="none"># actively intend to use prepared transactions.<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3823" clear="none"><br clear="none"></div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_3713"><br clear="none"></div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_4223">The problem is that when Node 1 (192.168.1.32) is the master (Node0 1.31 slave, after a failover) if something happens with a transaction and need to be rolledback I get : <br clear="none"></div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_3951"><br clear="none"></div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_3952">JTS5068: Unexpected error occurred in rollback<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3954" clear="none">org.postgresql.xa.PGXAException: Error rolling back prepared transaction<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3956" clear="none"> at org.postgresql.xa.PGXAConnection.rollback(PGXAConnection.java:420)<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3958" clear="none"> at com.sun.gjc.spi.XAResourceImpl.rollback(XAResourceImpl.java:195)<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3960" clear="none"> at com.sun.jts.jta.TransactionState._rollback(TransactionState.java:212)<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3962" clear="none"> at com.sun.jts.jta.TransactionState.rollback(TransactionState.java:180)<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3964" clear="none"> at com.sun.jts.jtsxa.OTSResourceImpl.rollback(OTSResourceImpl.java:333)<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3966" clear="none"> at com.sun.jts.CosTransactions.RegisteredResources.distributeRollback(RegisteredResources.java:1040)<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3968" clear="none"> at com.sun.jts.CosTransactions.TopCoordinator.rollback(TopCoordinator.java:2291)<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3970" clear="none"> at com.sun.jts.CosTransactions.CoordinatorTerm.rollback(CoordinatorTerm.java:530)<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3972" clear="none"> at com.sun.jts.CosTransactions.TerminatorImpl.rollback(TerminatorImpl.java:286)<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3974" clear="none"> at com.sun.jts.CosTransactions.CurrentImpl.rollback(CurrentImpl.java:767)<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3976" clear="none"> at com.sun.jts.jta.TransactionManagerImpl.rollback(TransactionManagerImpl.java:372)<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_3978" clear="none"> ...............................................................................................<br class="yiv0977138448" id="yiv0977138448yui_3_16_0_1_1438322534381_4004" clear="none"></div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_4046"><br clear="none"></div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_4045">And glassfish server needs to be restarted to kill all connections and open new ones.</div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_4096"><br clear="none"></div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_4097">But, when Node 0 is the master this issue is not present. I have check and postgresql conf seems to be identical, at least in what concerns XA transactions... <br clear="none"></div><div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_4271">Any idea what could be the issue here ?<br clear="none"><span></span></div><div id="yiv0977138448yui_3_16_0_1_1438322534381_3207"><span></span></div><div id="yiv0977138448yui_3_16_0_1_1438322534381_3037"> </div><div id="yiv0977138448yui_3_16_0_1_1438322534381_2999"><div id="yiv0977138448yui_3_16_0_1_1438322534381_2998">Regards,<br clear="none">Adrian Videanu</div></div><br clear="none"> <div class="qtdSeparateBR"><br><br></div><div class="yiv0977138448yqt9154269826" id="yiv0977138448yqt01008"><div id="yiv0977138448yui_3_16_0_1_1438322534381_3105" style="font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;"> <div id="yiv0977138448yui_3_16_0_1_1438322534381_3104" style="font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;"> <div dir="ltr" id="yiv0977138448yui_3_16_0_1_1438322534381_3103"> <hr id="yiv0977138448yui_3_16_0_1_1438322534381_3135" size="1"> <font id="yiv0977138448yui_3_16_0_1_1438322534381_3797" face="Arial" size="2"> <b><span style="font-weight:bold;">From:</span></b> Tatsuo Ishii <ishii@postgresql.org><br clear="none"> <b><span style="font-weight:bold;">To:</span></b> liujinfei@xiangrikui.com <br clear="none"><b><span style="font-weight:bold;">Cc:</span></b> pgpool-general@pgpool.net <br clear="none"> <b><span style="font-weight:bold;">Sent:</span></b> Friday, July 31, 2015 6:40 AM<br clear="none"> <b><span style="font-weight:bold;">Subject:</span></b> [pgpool-general: 3906] Re: pgpool num_init_children with 10000 Concurrent connections<br clear="none"> </font> </div> <div class="yiv0977138448y_msg_container" id="yiv0977138448yui_3_16_0_1_1438322534381_3216"><br clear="none">> Yesterday I have test pgpool with pgbench :<br clear="none">> pgbench -c 30 -T 20 -r pgbench -p9999 -h192.168.8.28<br clear="none">> Concurrent connections is 30, pgpool default num_init_children is 32.<br clear="none">> So, when I set -c 33 ,test will blocked unless I break out.<br clear="none">> My question is :<br clear="none">> If my concurrent connections online is 10000, should I set num_init_children=10000?<br clear="none">> It is terrible that num_init_children=10000 means pgpool start with 10000 process.<br clear="none">> Is there something wrong ?<br clear="none">> How can I config pgpool with 10000 concurrent connections?<br clear="none"><br clear="none">That depends how your app behaves. For example if you want to start<br clear="none">10000 psql sessions, you need to set num_init_children=10000. On the<br clear="none">other hand, if your app is someting like web app, which connects to DB<br clear="none">and disconnects very frequently (and each session is very short), you<br clear="none">could set lower value of num_init_children.<br clear="none"><br clear="none">Note that pgbench is psql type app.<br clear="none"><br clear="none">Best regards,<br clear="none">--<br clear="none">Tatsuo Ishii<br clear="none">SRA OSS, Inc. Japan<br clear="none">English: <a rel="nofollow" shape="rect" target="_blank" href="http://www.sraoss.co.jp/index_en.php">http://www.sraoss.co.jp/index_en.php</a><br clear="none">Japanese:<a rel="nofollow" shape="rect" target="_blank" href="http://www.sraoss.co.jp/">http://www.sraoss.co.jp</a><div class="yiv0977138448qtdSeparateBR"><br clear="none"><br clear="none"></div><div class="yiv0977138448yqt0335269169" id="yiv0977138448yqtfd26018"><br clear="none">_______________________________________________<br clear="none">pgpool-general mailing list<br clear="none"><a rel="nofollow" shape="rect" ymailto="mailto:pgpool-general@pgpool.net" target="_blank" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br clear="none"><a rel="nofollow" shape="rect" target="_blank" href="http://www.pgpool.net/mailman/listinfo/pgpool-general">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br clear="none"></div><br clear="none"><br clear="none"></div> </div> </div></div> </div></div></div><br><br></div> </div> </div> </div></body></html>