[pgpool-general: 8258] Re: Pg_stat_activity hung queries issue

Tatsuo Ishii ishii at sraoss.co.jp
Mon Jun 27 09:12:39 JST 2022


I am investigating this issue using pgproto with the data you
provided.  It seems there are lots of "Flush" messages in the data,
and I think the Flush message are causing troubles in pgpool. I guess
it will take some time before I find a solution.

> Any thoughts?
> On Wed, 22 Jun 2022 at 8:45 Avi Raboah <avi.raboah at gmail.com> wrote:
> 
>> Yea you right
>>
>> On Wed, 22 Jun 2022 at 8:45 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>>
>>> The default for backend_clustering_mode varies depending on the pgpool
>>> versions. Can I assume you are using 4.3?
>>>
>>> > All the configurations I didn’t provided are using the default.
>>> >
>>> > One more thing, the issue is occurring only if query cache enabled
>>> >
>>> > Thanks,
>>> >
>>> > Avi
>>> >
>>> > On Wed, 22 Jun 2022 at 4:10 Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>>> >
>>> >> What is backend_clustering_mode?
>>> >>
>>> >> > Pgpool.conf-
>>> >> >
>>> >> > listen_addresses = '*'
>>> >> >
>>> >> > port = '9999'
>>> >> >
>>> >> > socket_dir = '/var/run/pgpool'
>>> >> >
>>> >> > pcp_socket_dir = '/var/run/pgpool'
>>> >> >
>>> >> > wd_ipc_socket_dir = '/var/run/pgpool'
>>> >> >
>>> >> > pid_file_name = '/var/run/pgpool/pgpool.pid'
>>> >> >
>>> >> > backend_hostname0 = db_instance
>>> >> >
>>> >> > backend_port0 = '5432'
>>> >> >
>>> >> > backend_flag0 = 'ALLOW_TO_FAILOVER'
>>> >> >
>>> >> > backend_weight0 = '1'
>>> >> >
>>> >> > connection_cache = on
>>> >> >
>>> >> > num_init_children = 200
>>> >> >
>>> >> > child_life_time = 0
>>> >> >
>>> >> > max_pool = 4
>>> >> >
>>> >> > log_destination = 'stderr'
>>> >> >
>>> >> > log_line_prefix = '%m: %a pid %p: '
>>> >> >
>>> >> > log_connections = off
>>> >> >
>>> >> > log_disconnections = off
>>> >> >
>>> >> > log_statement = off
>>> >> >
>>> >> > log_client_messages = on
>>> >> >
>>> >> > client_min_messages = info
>>> >> >
>>> >> > log_min_messages = debug
>>> >> >
>>> >> > logging_collector = on
>>> >> >
>>> >> > log_directory = '/tmp/pgpool_logs'
>>> >> >
>>> >> > log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
>>> >> >
>>> >> > log_file_mode = 0600
>>> >> >
>>> >> > log_truncate_on_rotation = off
>>> >> >
>>> >> > log_rotation_age = 1d
>>> >> >
>>> >> > log_rotation_size = 200MB
>>> >> >
>>> >> > sr_check_user = 'user'
>>> >> >
>>> >> > sr_check_password = 'password'
>>> >> >
>>> >> > sr_check_period = '10'
>>> >> >
>>> >> > memory_cache_enabled = 'on'
>>> >> >
>>> >> > memqcache_total_size = 64MB
>>> >> >
>>> >> > memqcache_max_num_cache = 1000000
>>> >> >
>>> >> > memqcache_maxcache = 400kB
>>> >> >
>>> >> > memqcache_cache_block_size = 1MB
>>> >> >
>>> >> > memqcache_oiddir = '/var/log/pgpool/oiddir'
>>> >> >
>>> >> > cache_safe_memqcache_table_list = ''
>>> >> >
>>> >> > cache_unsafe_memqcache_table_list = ''
>>> >> >
>>> >> > check_unlogged_table = on
>>> >> >
>>> >> > health_check_period = '10'
>>> >> >
>>> >> > health_check_user = 'user'
>>> >> >
>>> >> > failover_on_backend_error = 'off'
>>> >> >
>>> >> >
>>> >> >
>>> >> >
>>> >> > On Tue, 21 Jun 2022 at 10:51 Tatsuo Ishii <ishii at sraoss.co.jp>
>>> wrote:
>>> >> >
>>> >> >> Please share the log with log_client_messages = on so that I can see
>>> >> >> what your client actually is sending.  Also I need pgpool.conf.
>>> >> >>
>>> >> >> > We are using asyncPg module in python in order to send our
>>> queries the
>>> >> >> > difference between that module to psycopg is that asyncpg using
>>> >> extended
>>> >> >> > queries.
>>> >> >> > And I can see that the issue is reproduced in case transaction
>>> aborted
>>> >> >> and
>>> >> >> > the catalog queries are launched.
>>> >> >> >
>>> >> >> > I checked in the code and found that
>>> check_transaction_state_and_abort
>>> >> >> > happens only in simpleQuery process.
>>> >> >> >
>>> >> >> > Thanks,
>>> >> >> >
>>> >> >> > Avi
>>> >> >> >
>>> >> >> > On Tue, 21 Jun 2022 at 6:43 Tatsuo Ishii <ishii at sraoss.co.jp>
>>> wrote:
>>> >> >> >
>>> >> >> >> Can you elaborate more?
>>> >> >> >>
>>> >> >> >> I tried following case and pgpool ignores all queries from client
>>> >> >> >> after a parse error occurs as expected.
>>> >> >> >>
>>> >> >> >> pgproto -d test -p 11000 -f ../pgproto.data
>>> >> >> >> FE=> Parse(stmt="", query="BEGIN")
>>> >> >> >> FE=> Bind(stmt="", portal="")
>>> >> >> >> FE=> Execute(portal="")
>>> >> >> >> FE=> Parse(stmt="", query="INSERT INTO non_existing_table
>>> values(1)")
>>> >> >> >> FE=> Bind(stmt="", portal="")
>>> >> >> >> FE=> Execute(portal="")
>>> >> >> >> FE=> Parse(stmt="", query="END")
>>> >> >> >> FE=> Bind(stmt="", portal="")
>>> >> >> >> FE=> Execute(portal="")
>>> >> >> >> FE=> Sync
>>> >> >> >> <= BE ParseComplete
>>> >> >> >> <= BE BindComplete
>>> >> >> >> <= BE CommandComplete(BEGIN)
>>> >> >> >> <= BE ErrorResponse(S ERROR V ERROR C 42P01 M relation
>>> >> >> >> "non_existing_table" does not exist P 13 F parse_relation.c L
>>> 1381 R
>>> >> >> >> parserOpenTable )
>>> >> >> >> <= BE ReadyForQuery(E)
>>> >> >> >> FE=> Terminate
>>> >> >> >>
>>> >> >> >> > Hi,
>>> >> >> >> >
>>> >> >> >> > After investigating the code I found that the patch you
>>> provided is
>>> >> >> >> fixing
>>> >> >> >> > the problem only for simple query. In extended query the issue
>>> >> still
>>> >> >> >> exist.
>>> >> >> >> >
>>> >> >> >> > Do you know where is the right place to add the transaction
>>> failure
>>> >> >> >> > verification in extended query process?
>>> >> >> >> > On Mon, 9 May 2022 at 16:17 Tatsuo Ishii <ishii at sraoss.co.jp>
>>> >> wrote:
>>> >> >> >> >
>>> >> >> >> >> The patch (plus more fix) has been committed to master and 4.3
>>> >> stable
>>> >> >> >> >> branches.
>>> >> >> >> >>
>>> >> >> >> >>
>>> >> >>
>>> https://www.pgpool.net/pipermail/pgpool-committers/2022-May/008592.html
>>> >> >> >> >>
>>> >> >>
>>> https://www.pgpool.net/pipermail/pgpool-committers/2022-May/008591.html
>>> >> >> >> >>
>>> >> >> >> >> The patches will appear in the next release (May 19, 2022).
>>> >> >> >> >>
>>> >> >> >> >> I have not pushed to other stable branches because the fix is
>>> not
>>> >> >> >> >> small and I wanted to minimize risks.
>>> >> >> >> >>
>>> >> >> >> >> > Thanks a lot for the patch have a great weekend
>>> >> >> >> >> >
>>> >> >> >> >> > On Sat, 7 May 2022 at 13:35 Tatsuo Ishii <
>>> ishii at sraoss.co.jp>
>>> >> >> wrote:
>>> >> >> >> >> >
>>> >> >> >> >> >> > Hi,
>>> >> >> >> >> >> >
>>> >> >> >> >> >> > Still thinking.
>>> >> >> >> >> >> >
>>> >> >> >> >> >> > Best reagards,
>>> >> >> >> >> >> > --
>>> >> >> >> >> >> > Tatsuo Ishii
>>> >> >> >> >> >> > SRA OSS, Inc. Japan
>>> >> >> >> >> >> > English: http://www.sraoss.co.jp/index_en.php
>>> >> >> >> >> >> > Japanese:http://www.sraoss.co.jp
>>> >> >> >> >> >> >
>>> >> >> >> >> >> >
>>> >> >> >> >> >> >> Hi,
>>> >> >> >> >> >> >>
>>> >> >> >> >> >> >> Any news about this thread?
>>> >> >> >> >> >> >>
>>> >> >> >> >> >> >> Thanks,
>>> >> >> >> >> >> >> Avi.
>>> >> >> >> >> >> >>
>>> >> >> >> >> >> >> On Mon, 25 Apr 2022 at 11:13 Avi Raboah <
>>> >> avi.raboah at gmail.com>
>>> >> >> >> >> wrote:
>>> >> >> >> >> >> >>
>>> >> >> >> >> >> >>> It will be perfect thanks a lot!
>>> >> >> >> >> >> >>>
>>> >> >> >> >> >> >>> On Mon, 25 Apr 2022 at 10:47 Tatsuo Ishii <
>>> >> ishii at sraoss.co.jp
>>> >> >> >
>>> >> >> >> >> wrote:
>>> >> >> >> >> >> >>>
>>> >> >> >> >> >> >>>> > No, it doesn't.
>>> >> >> >> >> >> >>>> > In that case when you make the select statement,
>>> piggy
>>> >> >> >> internal
>>> >> >> >> >> >> queries
>>> >> >> >> >> >> >>>> > failed and the session rebooted
>>> >> >> >> >> >> >>>>
>>> >> >> >> >> >> >>>> I see it now.
>>> >> >> >> >> >> >>>>
>>> >> >> >> >> >> >>>> test=# begin;
>>> >> >> >> >> >> >>>> BEGIN
>>> >> >> >> >> >> >>>> test=*# insert into ttt values(1);
>>> >> >> >> >> >> >>>> ERROR:  relation "ttt" does not exist
>>> >> >> >> >> >> >>>> LINE 1: insert into ttt values(1);
>>> >> >> >> >> >> >>>>                     ^
>>> >> >> >> >> >> >>>> test=!# select * from t1;
>>> >> >> >> >> >> >>>> FATAL:  Backend throw an error message
>>> >> >> >> >> >> >>>> DETAIL:  Exiting current session because of an error
>>> from
>>> >> >> >> backend
>>> >> >> >> >> >> >>>> HINT:  BACKEND Error: "current transaction is aborted,
>>> >> >> commands
>>> >> >> >> >> >> ignored
>>> >> >> >> >> >> >>>> until end of transaction block"
>>> >> >> >> >> >> >>>> server closed the connection unexpectedly
>>> >> >> >> >> >> >>>>         This probably means the server terminated
>>> >> abnormally
>>> >> >> >> >> >> >>>>         before or while processing the request.
>>> >> >> >> >> >> >>>> The connection to the server was lost. Attempting
>>> reset:
>>> >> >> >> Succeeded.
>>> >> >> >> >> >> >>>>
>>> >> >> >> >> >> >>>> I think pgpool could remember that current
>>> transaction is
>>> >> in
>>> >> >> >> abort
>>> >> >> >> >> >> >>>> status and does not issue the piggyback query when the
>>> >> selec
>>> >> >> is
>>> >> >> >> >> >> >>>> issued. Let me see what we can do for this.
>>> >> >> >> >> >>
>>> >> >> >> >> >> I have come up with a patch. In this patch pgpool checks if
>>> >> >> current
>>> >> >> >> >> >> transaction is in abort status. If so, it does not accept
>>> new
>>> >> >> query
>>> >> >> >> at
>>> >> >> >> >> >> all and just reply back frontend with the message:
>>> >> >> >> >> >>
>>> >> >> >> >> >> "current transaction is aborted, commands ignored until
>>> end of
>>> >> >> >> >> transaction
>>> >> >> >> >> >> block"
>>> >> >> >> >> >>
>>> >> >> >> >> >> until commit or abort command is sent from frontend. After
>>> >> >> applying
>>> >> >> >> >> >> the patch the example session above looks like this:
>>> >> >> >> >> >>
>>> >> >> >> >> >> test=# begin;
>>> >> >> >> >> >> BEGIN
>>> >> >> >> >> >> test=*# insert into ttt values(1);
>>> >> >> >> >> >> ERROR:  relation "ttt" does not exist
>>> >> >> >> >> >> LINE 1: insert into ttt values(1);
>>> >> >> >> >> >>                     ^
>>> >> >> >> >> >> test=!# select * from t1;
>>> >> >> >> >> >> ERROR:  current transaction is aborted, commands ignored
>>> until
>>> >> >> end of
>>> >> >> >> >> >> transaction block
>>> >> >> >> >> >>
>>> >> >> >> >> >> The patch was tested in master branch, but I believe it
>>> can be
>>> >> >> >> >> >> applied to 4.3 stable branch as well.
>>> >> >> >> >> >>
>>> >> >> >> >> >> Best reagards,
>>> >> >> >> >> >> --
>>> >> >> >> >> >> Tatsuo Ishii
>>> >> >> >> >> >> SRA OSS, Inc. Japan
>>> >> >> >> >> >> English: http://www.sraoss.co.jp/index_en.php
>>> >> >> >> >> >> Japanese:http://www.sraoss.co.jp
>>> >> >> >> >> >>
>>> >> >> >> >>
>>> >> >> >>
>>> >> >>
>>> >>
>>>
>>


More information about the pgpool-general mailing list