[pgpool-general: 4115] Re: Terminating Long Running Query Triggers Failover

[Tatsuo Ishii]

How did you find the long running queries? If you used
pg_stat_activity, that could be the case already discussed in the
FAQ.

http://pgpool.net/mediawiki/index.php/FAQ#When_I_check_pg_stat_activity_view.2C_I_see_a_query_like_.22SELECT_count.28.2A.29_FROM_pg_catalog.pg_class_AS_c_WHERE_c.oid_.3D_pgpool_regclass.28.27pgbench_accounts.27.29_AND_c.relpersistence_.3D_.27u.27.22_in_active_state_for_very_long_time._Why.3F

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Hi everyone. We have some master/slave clusters using pgpool 3.2.4 connected to 9.4 instances. Yesterday, I found some long running queries using a script I have to find them, and found some very long running queries on our clusters that have a status of unlogged in pg_class. I noticed in the where clause pgpool_regclass was referenced and the query had been active for 26 days. I also found some on other clusters that had been running for 14 days. I terminated them on our clusters, and two of our clusters showed as a failover being triggered in pgpool. The master databases never went down. I executed a pcp_attach on the pool servers for each of the clusters reporting the failover, and the connection status using pcp_node_info returned the master node to a status of, "2". Everything seemed fine afterward. An example is below.
> 
> SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = pgpool_regclass('mytable') AND c.relpersistence = 'u'|active|26 days
> 
> A couple of questions:
> 
>   1.  Is the pgpool failover related to terminating long running queries like the one above?
>   2.  Should this have triggered the failover?
>   3.  What can I do to prevent pgpool from seeing it as a failed instance when it never actually went down? I don't want to "DISALLOW_FAILOVER" in the pool config, however, I don't want it reacting to any sort of false positive.
>   4.  What is the correlation to pgpool in the query example above? Is this a select query that was issued by pool and got hung in an active state for some reason?
> 
> Any help trying to figure this out would be greatly appreciated. I'm not new to Postgres, however, I am somewhat new and green with Pg Pool.
> 
> Thanks!
> Daniel
> 
> 
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general