View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0000582Pgpool-IIBugpublic2020-02-09 00:222020-04-16 11:12
Reporterpostgann2020 Assigned Tohoshiai  
PriorityhighSeveritymajorReproducibilityhave not tried
Status closedResolutionfixed 
PlatformRHEL-64OSLinuxOS Version7.2
Product Version3.7.11 
Summary0000582: 'idle in transaction' connections in pgpool server and not able see the same in postgres servers
DescriptionHi Team,

Thanks for your support.

Hi all,
We need support.
I am getting too many 'idle in transaction' connections in pgpool server and the related PIDs are missing in postgres servers i.e connections are getting closed in postgres servers( not sure what is happening ).

what may be the problem?


Environment Details : Two pgpool servers( 3.7.11 ) with [ one master and three slave Postgres servers (9.5) ]
                      Application side : Java with struts framework and deploying in Wildfly servers(9).
                      
1.Can not reproduce the issue every time.

2. Pgpool.conf params :
    connection_cache = on
                                   # Activate connection pools
                                   # (change requires restart)
                                   # Semicolon separated list of queries
                                   # to be issued at the end of a session
                                   # The default is for 8.3 and later
    reset_query_list = 'ABORT; DISCARD ALL'
                                       # The following one is for 8.2 and before
    #reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
    num_init_children = 1000
                                       # Number of concurrent sessions allowed
                                       # (change requires restart)
    max_pool = 1
                                       # Number of connection pool caches per connection
                                       # (change requires restart)
    # - Life time -
    child_life_time = 300
                                       # Pool exits after being idle for this many seconds
    child_max_connections = 0
                                       # Pool exits after receiving that many connections
                                       # 0 means no exit
    connection_life_time = 0
                                       # Connection to backend closes after being idle for this many seconds
                                       # 0 means no close
    client_idle_limit = 0
    connect_timeout = 100000
    client_idle_limit_in_recovery = 0
    
3. pids which got struck in pgpool servers and disappear in postgres servers

    1). From the pgpool servers:

    (i) In Pgpool :
    root 30659 6239 0 13:17 ? 00:00:00 pgpool: postgres postgres 10.19.61.122 idle in transaction
    root 32688 6239 0 13:29 ? 00:00:00 pgpool: postgres postgres 10.19.61.122 idle in transaction
    
    (ii) show pool_pools;
    30659 | 2020-02-08 13:17:26 | 0 | 0 | postgres | postgres | 2020-02-08 13:47:53 | 3 | 0 | 1 | 378276 | 1
    30659 | 2020-02-08 13:17:26 | 0 | 1 | postgres | postgres | 2020-02-08 13:47:53 | 3 | 0 | 1 | 446378 | 1
    30659 | 2020-02-08 13:17:26 | 0 | 2 | postgres | postgres | 2020-02-08 13:47:53 | 3 | 0 | 1 | 26020 | 1
    30659 | 2020-02-08 13:17:26 | 0 | 3 | postgres | postgres | 2020-02-08 13:47:53 | 3 | 0 | 1 | 346430 | 1

    32688 | 2020-02-08 13:29:14 | 0 | 0 | postgres | postgres | 2020-02-08 13:52:05 | 3 | 0 | 1 | 378814 | 1
    32688 | 2020-02-08 13:29:14 | 0 | 1 | postgres | postgres | 2020-02-08 13:52:05 | 3 | 0 | 1 | 447031 | 1
    32688 | 2020-02-08 13:29:14 | 0 | 2 | postgres | postgres | 2020-02-08 13:52:05 | 3 | 0 | 1 | 26544 | 1
    32688 | 2020-02-08 13:29:14 | 0 | 3 | postgres | postgres | 2020-02-08 13:52:05 | 3 | 0 | 1 | 346873 | 1
    
    (iii) In Postgres :

    ps -ef| grep 378814
    397874 393866 0 16:26 pts/0 00:00:00 grep --color=auto 378814
    ps -ef| grep 378276
    397995 393866 0 16:27 pts/0 00:00:00 grep --color=auto 378276
    
    iv: Not able to see in pg_stat_activity

Please help us to find the issues:

1. Why we are not able to see the pgpool "idle in transaction" connections in backend DB's(Using pg_stat_activity and ps -eaf| grep pid).
2. how to check what exactly the "idle in transaction" connections are doing in backends?. and how to see those connections?.
3. How to avoid these kind of issues and what parameters at pgpool and db level will help us to release these "idle in transaction" connections?.

Your support will be much appreciate.
Thanks for your support.

Regards,
Bingo.
TagsNo tags attached.

Activities

siva

2020-02-10 11:26

reporter   ~0003163

Hi Team,

Could someone please respond on this request, this issues is entirely blocking all the activities in production.

Thanks.

postgann2020

2020-02-11 14:54

reporter   ~0003169

Hi hoshiai,

Good Morning,

Just for information.

We are seeing below messages in log:

1. pgpool[12047]: [87052-1] 2020-02-10 19:25:05: pid 12047: FATAL: connection was terminated due to conflict with recovery.
2. pgpool[26352]: [869029-2] 2020-02-10 18:52:26: pid 26352: DETAIL: kind mismatch among backends. Possible last query was: "COMMIT" kind details are: 0[2] 3[E: terminating connection due to conflict with recovery].

Please help us for below queries as well.
1. Can we ignore "connection was terminated due to conflict with recovery" ?.
2. Please suggest, how to avoid the event ?.

Thanks & Regards,
postgann.

hoshiai

2020-02-12 13:21

developer   ~0003182

Last edited: 2020-02-12 13:24

 In the above 'Description', I understand that pgpool use its connection and postgres(node:0) connection isn't maybe existed.
We can't know Why backend process don't exist that based on just this information.

If possible, replay a situation, log_connections and log_disconnections are enabled by postgresql, and pgpool set log_min_messages to 'debug1'.

> (iii) In Postgres :
> ps -ef| grep 378814
> 397874 393866 0 16:26 pts/0 00:00:00 grep --color=auto 378814
> ps -ef| grep 378276
> 397995 393866 0 16:27 pts/0 00:00:00 grep --color=auto 378276
Then, was this executed by postgres of node0 ?

hoshiai

2020-02-12 13:35

developer   ~0003183

And Could you share pgpool.conf? If necessary, you masked data(address, password ,etc ..).
If only above description, I can't understand some settings such as pgpool's mode.

hoshiai

2020-02-12 13:43

developer   ~0003184

Last edited: 2020-02-12 13:50

 I'm sorry many times... Please share postgresql.conf, postgresql.log and pgpool.log too.

hoshiai

2020-04-14 15:41

developer   ~0003321

It's been more than a month since I replay to you ... Is your problem solved?
If you don't have any problems, I would like to close this issue.

postgann2020

2020-04-15 16:48

reporter   ~0003326

Thanks for the support. Please close this request.

hoshiai

2020-04-16 11:12

developer   ~0003328

OK, I closed tihis ticket.

Issue History

Date Modified Username Field Change
2020-02-09 00:22 postgann2020 New Issue
2020-02-10 11:26 siva Note Added: 0003163
2020-02-10 17:16 hoshiai Assigned To => hoshiai
2020-02-10 17:16 hoshiai Status new => assigned
2020-02-11 14:54 postgann2020 Note Added: 0003169
2020-02-12 13:21 hoshiai Note Added: 0003182
2020-02-12 13:24 hoshiai Note Edited: 0003182
2020-02-12 13:35 hoshiai Status assigned => feedback
2020-02-12 13:35 hoshiai Note Added: 0003183
2020-02-12 13:43 hoshiai Note Added: 0003184
2020-02-12 13:50 hoshiai Note Edited: 0003184
2020-04-14 15:41 hoshiai Note Added: 0003321
2020-04-15 16:48 postgann2020 Note Added: 0003326
2020-04-15 16:48 postgann2020 Status feedback => assigned
2020-04-16 11:12 hoshiai Status assigned => closed
2020-04-16 11:12 hoshiai Resolution open => fixed
2020-04-16 11:12 hoshiai Note Added: 0003328