View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0000535Pgpool-IIGeneralpublic2019-08-08 03:222019-08-27 10:58
Reporterravi4000 Assigned Topengbo  
PriorityhighSeveritymajorReproducibilityalways
Status closedResolutionopen 
Product Version3.4.3 
Summary0000535: Frequently seeing "unable to flush data to frontend" errors and timeout errors in application servers
DescriptionPgPool Version: pgpool-II version 3.4.3 (tataraboshi)
OS Version: CentOS Linux release 7.5.1804 (Core)
Postgres Version: PostgreSQL 9.4.14
I have posted pgpool.conf details in additional info field.

We are seeing the following errors in pgpool log and application is receiving timeout errors. These are happening very frequently for queries those are taking more than 10 or 15mins. We did not see any issues with short running queries, those are running less than 2 or 5 mins.

====>>> PgPool Log
Aug 7 00:18:43 smoermspgplp07p pgpool: 2019-08-07 00:18:43 psql CHILD[30009] fi13982pgadmin@fi13982: ERROR: unable to flush data to frontend
Aug 7 00:18:43 smoermspgplp07p pgpool: 2019-08-07 00:18:43 psql CHILD[30009] fi13982pgadmin@fi13982: LOG: statement: DISCARD ALL
Aug 7 00:18:43 smoermspgplp07p pgpool: 2019-08-07 00:18:43 psql CHILD[30009] fi13982pgadmin@fi13982: LOG: DB node id: 0 backend pid: 18400 statement: DISCARD ALL
Aug 7 00:18:43 smoermspgplp07p pgpool[30009]: [48828-1] 2019-08-07 00:18:43 psql CHILD[30009] fi13982pgadmin@fi13982: ERROR: unable to flush data to frontend
Aug 7 00:18:43 smoermspgplp07p pgpool[30009]: [48829-1] 2019-08-07 00:18:43 psql CHILD[30009] fi13982pgadmin@fi13982: LOG: statement: DISCARD ALL
Aug 7 00:18:43 smoermspgplp07p pgpool[30009]: [48830-1] 2019-08-07 00:18:43 psql CHILD[30009] fi13982pgadmin@fi13982: LOG: DB node id: 0 backend pid: 18400 statement: DISCARD ALL
====>>> PgPool Log
Aug 7 03:07:21 smoermspgplp07p pgpool[16531]: [57294-1] 2019-08-07 03:07:21 psql CHILD[16531] fi13982pgadmin@fi13982: ERROR: unable to flush data to frontend
Aug 7 03:07:21 smoermspgplp07p pgpool: 2019-08-07 03:07:21 psql CHILD[16531] fi13982pgadmin@fi13982: ERROR: unable to flush data to frontend
Aug 7 03:07:21 smoermspgplp07p pgpool: 2019-08-07 03:07:21 psql CHILD[16531] fi13982pgadmin@fi13982: LOG: statement: DISCARD ALL
Aug 7 03:07:21 smoermspgplp07p pgpool: 2019-08-07 03:07:21 psql CHILD[16531] fi13982pgadmin@fi13982: LOG: DB node id: 0 backend pid: 18713 statement: DISCARD ALL
Aug 7 03:07:21 smoermspgplp07p pgpool[16531]: [57295-1] 2019-08-07 03:07:21 psql CHILD[16531] fi13982pgadmin@fi13982: LOG: statement: DISCARD ALL
Aug 7 03:07:21 smoermspgplp07p pgpool[16531]: [57296-1] 2019-08-07 03:07:21 psql CHILD[16531] fi13982pgadmin@fi13982: LOG: DB node id: 0 backend pid: 18713 statement: DISCARD ALL



====>>> PG DB log
2019-08-07 00:18:43 CDT 10.222.126.76(44636) fi13982pgadmin@fi13982 18400 sql_error_code = 00000LOG: duration: 929867.396 ms statement: select * from erms_stage.depaccthist where _id >= 18592 and _delflag = false limit 10000;

====>>> PG DB log
2019-08-07 02:49:38 CDT 10.222.126.76(41878) fi13982pgadmin@fi13982 18713 sql_error_code = 00000LOG: connection authorized: user=fi13982pgadmin database=fi13982
2019-08-07 03:07:21 CDT 10.222.126.76(41878) fi13982pgadmin@fi13982 18713 sql_error_code = 00000LOG: duration: 929397.507 ms statement: select * from fcfprep.fsp_account_dim where account_key >= 16584 limit 10000;

Steps To Reproducewhile running heavy jobs
Additional Information=== pgpool.conf file
listen_addresses = '*'
port = 5432
socket_dir = '/var/run/pgpool'
enable_pool_hbd = true
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/var/run/pgpool'

## Postgres database vip details
backend_hostname0 = 'xx.xxx.xxx.49'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/9.4/main'
backend_flag0 = 'DISALLOW_TO_FAILOVER'

## Both pgpool server details
use_watchdog = on
trusted_servers = 'xx.xxx.xxx.76, xx.xxx.xxx.78'

## First pgpool server IP address
wd_hostname = 'xx.xxx.xxx.76'
wd_port = 9000
wd_priority = 2
wd_ipc_socket_dir = '/var/run/pgpool'
## pgpool VIP IP
delegate_IP = 'xx.xxx.xxx.70'

wd_lifecheck_method = 'heartbeat'
wd_interval = 3
wd_heartbeat_port = 9694

## Second pgpool server IP
heartbeat_destination0 = 'xx.xxx.xxx.78'
heartbeat_destination_port0 = 9694

## Second pgpool server IP
other_pgpool_hostname0 = 'xx.xxx.xxx.78'
other_pgpool_port0 = 5432
other_wd_port0 = 9000

ifconfig_path = '/sbin'
ifconfig_path = '/sbin'
if_up_cmd = 'ifconfig ens192:0 inet $_IP_$ netmask 255.255.255.0'
if_down_cmd = 'ifconfig ens192:0 down'
arping_path = '/sbin'
arping_cmd = 'arping -U $_IP_$ -w 1'

num_init_children = 2500
max_pool = 1
#child_life_time = 120
##connections_life_time = 120
## turn on logging
load_balance_mode = false
fail_over_on_backend_error = off
#search_primary_node_timeout = 10
log_destination = 'syslog'
print_timestamp = on
log_connections = on
log_hostname = on
log_statement = on
log_per_node_statement = on
log_standby_delay = 'none'
#log_min_messages = debug1
log_min_messages = info
#log_min_messages = warning
client_min_messages = notice
debug_level = 1
# - Syslog specific -
syslog_facility = 'local1'
syslog_ident = 'pgpool'
logdir = '/var/log/pgpool-II'
log_line_prefix = '%t %a %P[%p] %u@%d: '
child_life_time = 0
connection_life_time = 0
client_idle_limit = 0
TagsNo tags attached.

Activities

ravi4000

2019-08-08 23:50

reporter   ~0002758

Any help on this is really appreciated. We are seeing this error more frequently in our production system and its impacting the users.

pengbo

2019-08-09 11:47

developer   ~0002760

This seems the application disconnects the socket to pgpool-II.
Is there setting of connection timeout in your application?

ravi4000

2019-08-09 13:32

reporter   ~0002762

No. There is no timeout setting in application. Development team is using Postgres9.4 psql library to connect to pgpool server.

ravi4000

2019-08-09 13:33

reporter   ~0002763

There are no timeout settings at firewall level as well.

pengbo

2019-08-10 18:16

developer   ~0002767

> ERROR: unable to flush data to frontend

This error means that the connection is disconnected when pgpool sends data to frontend.
Pgpool-II doesn't disconnect the connection.
 
I think it is due to the setting of application side.
Which program language do you use? How to connect to pgpool?
Do you use connection pooling between application and pgpool?
If you use connection pooling between application and pgpool, when the connection is timeout, this error will occur.

ravi4000

2019-08-26 23:52

reporter   ~0002800

We have resolved the issue by upgrading the pgpool version to 3.6.6.

We are not using any connection pooling between application and pgpool. Multiple threads are connecting to pgpool by using psql command. We are using different languages c++, JAVA and python.

Please go ahead and close the ticket.

Issue History

Date Modified Username Field Change
2019-08-08 03:22 ravi4000 New Issue
2019-08-08 23:50 ravi4000 Note Added: 0002758
2019-08-09 11:47 pengbo Note Added: 0002760
2019-08-09 11:48 pengbo Assigned To => pengbo
2019-08-09 11:48 pengbo Status new => feedback
2019-08-09 13:32 ravi4000 Note Added: 0002762
2019-08-09 13:32 ravi4000 Status feedback => assigned
2019-08-09 13:33 ravi4000 Note Added: 0002763
2019-08-10 18:16 pengbo Note Added: 0002767
2019-08-26 09:55 pengbo Status assigned => feedback
2019-08-26 23:52 ravi4000 Note Added: 0002800
2019-08-26 23:52 ravi4000 Status feedback => assigned
2019-08-27 10:58 pengbo Status assigned => closed