<div dir="ltr">No. All clients use persistent connections using c3p0 pool, user 'myuser' and database 'mydb'. Most of transactions use SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY; or SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE; but on pgpool1 I see that those transactions are executed on backend 0.</div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Aug 23, 2016 at 10:52 PM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">>> Can you identify the query which executed on the same time on pgpool2?<br>
> Well this is interesting. In the logs I see queries executed as:<br>
> 1. [local] - Queries sent by Zabbix agent<br>
<br>
</span>So not related to Pgpool-II.<br>
<span class=""><br>
> 2. Health checks from pgpool1, which is primary, using user postgres and<br>
> database template1<br>
<br>
</span>Ridiculous. From your pgpool.conf:<br>
<br>
health_check_user = 'healthcheck'<br>
health_check_database = 'template1'<br>
<span class=""><br>
> 3. SR checks using user postgres and database mydb.<br>
<br>
</span>Ridiculous too.<br>
<br>
sr_check_user = 'postgres'<br>
sr_check_database = 'template1'<br>
<span class=""><br>
> The processes in the list, however, show that username used for those<br>
> connections is 'myuser' and database is 'mydb'. I found no entries with<br>
> these values in PostgreSQL logfile.<br>
<br>
</span>One possibilty is, a user connected as 'myser' and 'mydb' then only<br>
issued some Pgppoll-II specific show commands like "show<br>
pool_nodes". Can this ever happen?<br>
<div class="HOEnZb"><div class="h5"><br>
> I also checked pgpool-II log on pgpool1, the primary node, and found no<br>
> queries sent to backend id 1, all of them are sent to backend 0, which is<br>
> pgpool1.<br>
><br>
> Regards,<br>
> Vlad<br>
><br>
><br>
> On Tue, Aug 23, 2016 at 10:16 PM, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br>
><br>
>> Ok.<br>
>><br>
>> > Start time : 2016-08-23 21:23:54<br>
>><br>
>> This is the time when the pgpool process started. Since pgpool uses<br>
>> "pre-fork" method, in which all pgpool process are forked when pgpool<br>
>> starts. In summary, this is normal.<br>
>><br>
>> > Creation time: 2016-08-23 23:01:21<br>
>><br>
>> This is the time when pgpool received a query from frontend and<br>
>> forwarded it to PostgreSQL (which I do not suppose to happen). Can you<br>
>> identify the query which executed on the same time on pgpool2?<br>
>><br>
>> Best regards,<br>
>> --<br>
>> Tatsuo Ishii<br>
>> SRA OSS, Inc. Japan<br>
>> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_<wbr>en.php</a><br>
>> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.<wbr>jp</a><br>
>><br>
>> > They are relevant:<br>
>> ><br>
>> > [vlad@pgpool2 ~]$ ps ax | grep postgres<br>
>> ><br>
>> > ...<br>
>> ><br>
>> > *62230* ? Ss 0:00 postgres: myuser mydb 192.168.241.101(39856)<br>
>> > idle<br>
>> ><br>
>> > ...<br>
>> ><br>
>> > [vlad@pgpool2 ~]$ pcp_proc_info -U postgres -w -h pgpool1 -v | grep -B7<br>
>> -A1<br>
>> > 62230<br>
>> ><br>
>> > Database : mydb<br>
>> ><br>
>> > Username : myuser<br>
>> ><br>
>> > Start time : 2016-08-23 21:23:54<br>
>> ><br>
>> > Creation time: 2016-08-23 23:01:21<br>
>> ><br>
>> > Major : 3<br>
>> ><br>
>> > Minor : 0<br>
>> ><br>
>> > Counter : 2<br>
>> ><br>
>> > Backend PID : *62230*<br>
>> ><br>
>> > Connected : 1<br>
>> ><br>
>> ><br>
>> >>From what I understand, connected stands for the backend id (which is 1<br>
>> in<br>
>> > this case).<br>
>> ><br>
>> > On Tue, Aug 23, 2016 at 9:35 PM, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>><br>
>> wrote:<br>
>> ><br>
>> >> You can use pcp_proc_info command to check if the PostgreSQL process<br>
>> >> are relevant to Pgpool-II or not.<br>
>> >><br>
>> >> <a href="http://www.pgpool.net/docs/latest/pgpool-en.html#pcp_proc_info" rel="noreferrer" target="_blank">http://www.pgpool.net/docs/<wbr>latest/pgpool-en.html#pcp_<wbr>proc_info</a><br>
>> >><br>
>> >> Best regards,<br>
>> >> --<br>
>> >> Tatsuo Ishii<br>
>> >> SRA OSS, Inc. Japan<br>
>> >> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_<wbr>en.php</a><br>
>> >> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.<wbr>jp</a><br>
>> >><br>
>> >> > Interesting thing, though, is that I see postgres processes being<br>
>> forked<br>
>> >> on<br>
>> >> > pgpool2 eve though in the logs I see only the queries listed above.<br>
>> >> > ...<br>
>> >> ><br>
>> >> > 65032 ? Ss 0:00 *postgres*: myuser mydb<br>
>> 192.168.241.101(40278)<br>
>> >> > idle<br>
>> >> ><br>
>> >> > 65033 ? Ss 0:00 *postgres*: myuser mydb<br>
>> 192.168.241.101(40280)<br>
>> >> > idle<br>
>> >> ><br>
>> >> > 65034 ? Ss 0:00 *postgres*: myuser mydb<br>
>> 192.168.241.101(40282)<br>
>> >> > idle<br>
>> >> ><br>
>> >> > 65035 ? Ss 0:00 *postgres*: myuser mydb<br>
>> 192.168.241.101(40288)<br>
>> >> > idle<br>
>> >> ><br>
>> >> > 65036 ? Ss 0:00 *postgres*: myuser mydb<br>
>> 192.168.241.101(40290)<br>
>> >> > idle<br>
>> >> ><br>
>> >> > [vlad@pgpool2 ~]$ ps ax | grep postgres | grep myuser | wc -l<br>
>> >> ><br>
>> >> > 317<br>
>> >> ><br>
>> >> > Any ideas why can those processes be forked? It feels like pgpool<br>
>> creates<br>
>> >> > slots just in case it needs to failover, but it's not documented<br>
>> anywhere<br>
>> >> > and that's why it looks weird.<br>
>> >> ><br>
>> >> > On Tue, Aug 23, 2016 at 9:30 AM, Vlad Novikov <<br>
>> <a href="mailto:xou.slackware@gmail.com">xou.slackware@gmail.com</a>><br>
>> >> > wrote:<br>
>> >> ><br>
>> >> >> Ok, looks like the connections I see on pgpool2 are just healthchecks<br>
>> >> and<br>
>> >> >> standard db SELECT calls:<br>
>> >> >><br>
>> >> >> SELECT 1;<br>
>> >> >><br>
>> >> >> SELECT class.relname, blocking_activity.client_addr,<br>
>> >> >> blocking_activity.query FROM pg_catalog.pg_locks blocked JOIN<br>
>> >> >> pg_catalog.pg_stat_activity a ON a.pid=blocked.pid JOIN<br>
>> >> pg_catalog.pg_locks<br>
>> >> >> locks ON locks.locktype = blocked.locktype AND locks.DATABASE IS NOT<br>
>> >> >> DISTINCT FROM blocked.DATABASE AND locks.relation IS NOT DISTINCT<br>
>> FROM<br>
>> >> >> blocked.relation AND locks.page IS NOT DISTINCT FROM blocked.page AND<br>
>> >> >> locks.tuple IS NOT DISTINCT FROM blocked.tuple AND locks.virtualxid<br>
>> IS<br>
>> >> NOT<br>
>> >> >> DISTINCT FROM blocked.virtualxid AND locks.transactionid IS NOT<br>
>> DISTINCT<br>
>> >> >> FROM blocked.transactionid AND locks.classid IS NOT DISTINCT FROM<br>
>> >> >> blocked.classid AND locks.objid IS NOT DISTINCT FROM blocked.objid<br>
>> AND<br>
>> >> >> locks.objsubid IS NOT DISTINCT FROM blocked.objsubid AND locks.pid !=<br>
>> >> >> blocked.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON<br>
>> >> >> blocking_activity.pid=locks.<wbr>pid JOIN pg_catalog.pg_class class ON<br>
>> >> >> class.oid=locks.relation WHERE NOT blocked.GRANTED;<br>
>> >> >><br>
>> >> >> I think we can consider this solved. Thank you, guys, for helping<br>
>> with<br>
>> >> >> this.<br>
>> >> >><br>
>> >> >> Regards,<br>
>> >> >><br>
>> >> >> Vlad<br>
>> >> >><br>
>> >> >> On Mon, Aug 22, 2016 at 3:41 PM, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>><br>
>> >> wrote:<br>
>> >> >><br>
>> >> >>> > Hello,<br>
>> >> >>> ><br>
>> >> >>> > I attach node only when I start the failover node (PostgreSQL)<br>
>> after<br>
>> >> >>> > primary is up and running. E.g. I start PostgreSQL on pgpool1,<br>
>> then I<br>
>> >> >>> start<br>
>> >> >>> > popool-II and it detects the backend.<br>
>> >> >>><br>
>> >> >>> Probably at this point a fail over happens because PostgreSQL<br>
>> pgpool2<br>
>> >> >>> is not running. So far so good.<br>
>> >> >>><br>
>> >> >>> > Then, when I start pgpool2, I need to<br>
>> >> >>> > manually attach it, so pgpool-II would know that the backend is<br>
>> >> online,<br>
>> >> >>> > right?<br>
>> >> >>><br>
>> >> >>> Correct.<br>
>> >> >>><br>
>> >> >>> > Now nodes status. At this moment I see following nodes status:<br>
>> >> >>> > node_id hostname port status lb_weight role<br>
>> select_cnt<br>
>> >> >>> > 0 pgpool1 5432 2 0.500000 primary 66133198<br>
>> >> >>> > 1 pgpool2 5432 2 0.500000 standby 0<br>
>> >> >>><br>
>> >> >>> I don't know which node you executed "show pool_nodes" but as long<br>
>> as<br>
>> >> >>> the result of the command are same, it is ok.<br>
>> >> >>><br>
>> >> >>> So you sometimes see write queries are sent to PostgreSQL on<br>
>> pgpool2?<br>
>> >> >>><br>
>> >> >>> > I have a sneaky feeling that lb_weight has something to do about<br>
>> >> what is<br>
>> >> >>> > going on. However, if you take a look at the pgpool.conf attached<br>
>> to<br>
>> >> the<br>
>> >> >>> > initial message, you'll find the backends configured like this:<br>
>> >> >>> > backend_hostname0 = 'pgpool1'<br>
>> >> >>> > backend_port0 = 5432<br>
>> >> >>> > backend_weight0 = 1<br>
>> >> >>> > backend_data_directory0 = '/var/lib/pgsql/9.4/data'<br>
>> >> >>> > backend_flag0 = 'ALLOW_TO_FAILOVER'<br>
>> >> >>> ><br>
>> >> >>> > backend_hostname1 = 'pgpool2'<br>
>> >> >>> > backend_port1 = 5432<br>
>> >> >>> > backend_weight1 = 1<br>
>> >> >>> > backend_data_directory1 = '/var/lib/pgsql/9.4/data'<br>
>> >> >>> > backend_flag1 = 'ALLOW_TO_FAILOVER'<br>
>> >> >>> ><br>
>> >> >>> > In particular, backend_weight is set to 1 on both nodes.<br>
>> >> >>><br>
>> >> >>> Don't worry. The load balance weight is always "normalized" so that<br>
>> >> >>> the tatal weight is 1.0.<br>
>> >> >>><br>
>> >> >>> > Also, as far as I<br>
>> >> >>> > understand, this setting should not matter when<br>
>> >> load_balance_mode=off.<br>
>> >> >>><br>
>> >> >>> Correct.<br>
>> >> >>><br>
>> >> >>> > Regards,<br>
>> >> >>> > Vlad<br>
>> >> >>> ><br>
>> >> >>> ><br>
>> >> >>> > On Sat, Aug 20, 2016 at 4:37 PM, Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a><br>
>> ><br>
>> >> >>> wrote:<br>
>> >> >>> ><br>
>> >> >>> >> > Hi Lucas,<br>
>> >> >>> >> ><br>
>> >> >>> >> > I checked the log and found no failover entries. Here's how<br>
>> that<br>
>> >> >>> >> happens. I<br>
>> >> >>> >> > start two postgres backends (master-slave streaming<br>
>> replication)<br>
>> >> and<br>
>> >> >>> >> > pgpool-II instance. Then I attach both of the nodes and<br>
>> initially<br>
>> >> >>> all the<br>
>> >> >>> >> > clients get connected to master only.<br>
>> >> >>> >><br>
>> >> >>> >> Why do you need attach the backends? Pgpool-II automatically<br>
>> attach<br>
>> >> >>> >> all backends valid in pgpool.conf.<br>
>> >> >>> >><br>
>> >> >>> >> > I see that with ps ax | grep<br>
>> >> >>> >> > postgres. After some time new clients start getting connected<br>
>> to<br>
>> >> the<br>
>> >> >>> hot<br>
>> >> >>> >> > standby node while older clients are still connected to master.<br>
>> >> >>> Again, I<br>
>> >> >>> >> > see that with ps ax | grep postgres. In that case both master<br>
>> and<br>
>> >> hot<br>
>> >> >>> >> > standby have pgpool-II connected. That's what concerns me the<br>
>> >> most.<br>
>> >> >>> If<br>
>> >> >>> >> > there was a failover event, master would've been detached and<br>
>> >> there<br>
>> >> >>> would<br>
>> >> >>> >> > be no pgpool-II connections there.<br>
>> >> >>> >><br>
>> >> >>> >> Can you connect to pgool using psql then issue "show pool_nodes"<br>
>> >> when<br>
>> >> >>> >> pgpool starts to behaves like this? This should show which is the<br>
>> >> >>> >> primary node (role) and which node should be the node the query<br>
>> >> routes<br>
>> >> >>> >> to (load_balance_node).<br>
>> >> >>> >><br>
>> >> >>> >> Best regards,<br>
>> >> >>> >> --<br>
>> >> >>> >> Tatsuo Ishii<br>
>> >> >>> >> SRA OSS, Inc. Japan<br>
>> >> >>> >> English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_<wbr>en.php</a><br>
>> >> >>> >> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.<wbr>jp</a><br>
>> >> >>> >><br>
>> >> >>> >> > Vlad<br>
>> >> >>> >> ><br>
>> >> >>> >> > On Sat, Aug 20, 2016 at 1:25 AM, Lucas Luengas <<br>
>> >> >>> <a href="mailto:lucasluengas@gmail.com">lucasluengas@gmail.com</a>><br>
>> >> >>> >> > wrote:<br>
>> >> >>> >> ><br>
>> >> >>> >> >> Hello.<br>
>> >> >>> >> >><br>
>> >> >>> >> >> Have you checked pgpool log file? Maybe a failover happened?<br>
>> >> >>> >> >><br>
>> >> >>> >> >> On Fri, Aug 19, 2016 at 10:48 PM, Vlad Novikov <<br>
>> >> >>> <a href="mailto:xou.slackware@gmail.com">xou.slackware@gmail.com</a><br>
>> >> >>> >> ><br>
>> >> >>> >> >> wrote:<br>
>> >> >>> >> >><br>
>> >> >>> >> >>> Hello,<br>
>> >> >>> >> >>><br>
>> >> >>> >> >>> I'm have setup pgpool-II with 2 backends in streaming mode<br>
>> (see<br>
>> >> >>> >> >>> configuration file attached). In particular, I have<br>
>> >> >>> load_balance_mode<br>
>> >> >>> >> = off<br>
>> >> >>> >> >>> to make sure that in this pool all connections will be<br>
>> >> established<br>
>> >> >>> to<br>
>> >> >>> >> >>> streaming master only. However, over some time I see<br>
>> pgpool-II<br>
>> >> >>> >> establishing<br>
>> >> >>> >> >>> connections to hot standby server. As a result client<br>
>> >> applications<br>
>> >> >>> >> start<br>
>> >> >>> >> >>> failing as they cannot write to the database they're<br>
>> connected<br>
>> >> to.<br>
>> >> >>> So<br>
>> >> >>> >> far<br>
>> >> >>> >> >>> the only solution for me is to keep the hot standby detached<br>
>> >> >>> (which is<br>
>> >> >>> >> not<br>
>> >> >>> >> >>> a good idea in terms of automated failover).<br>
>> >> >>> >> >>> Pgpool-II starts behaving like this at about 100 clients<br>
>> >> connected.<br>
>> >> >>> >> >>> PostgreSQL max_connections is set to 900 and with hot standby<br>
>> >> >>> detached<br>
>> >> >>> >> >>> there are no connection issues reported (all clients can<br>
>> connect<br>
>> >> >>> to the<br>
>> >> >>> >> >>> backend with no issues).<br>
>> >> >>> >> >>> I use pgpool-II and PostgreSQL provided by PostgreSQL<br>
>> official<br>
>> >> >>> >> repository.<br>
>> >> >>> >> >>> PostgreSQL 9.4.9<br>
>> >> >>> >> >>> pgpool-II 3.5.3<br>
>> >> >>> >> >>> OS: CentOS 7.2<br>
>> >> >>> >> >>><br>
>> >> >>> >> >>> Is there anything I need to change in the configuration file<br>
>> to<br>
>> >> >>> make<br>
>> >> >>> >> all<br>
>> >> >>> >> >>> clients connect to master only when both backends are<br>
>> attached?<br>
>> >> >>> From<br>
>> >> >>> >> what I<br>
>> >> >>> >> >>> understand that is expected with load_balance_mode = off.<br>
>> >> >>> >> >>><br>
>> >> >>> >> >>> Regards,<br>
>> >> >>> >> >>> Vlad<br>
>> >> >>> >> >>><br>
>> >> >>> >> >>> ______________________________<wbr>_________________<br>
>> >> >>> >> >>> pgpool-general mailing list<br>
>> >> >>> >> >>> <a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
>> >> >>> >> >>> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/<wbr>listinfo/pgpool-general</a><br>
>> >> >>> >> >>><br>
>> >> >>> >> >>><br>
>> >> >>> >> >><br>
>> >> >>> >><br>
>> >> >>><br>
>> >> >><br>
>> >> >><br>
>> >><br>
>><br>
</div></div></blockquote></div><br></div>