View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000535 | Pgpool-II | General | public | 2019-08-08 03:22 | 2019-08-27 10:58 |
| Reporter | ravi4000 | Assigned To | pengbo | ||
| Priority | high | Severity | major | Reproducibility | always |
| Status | closed | Resolution | open | ||
| Product Version | 3.4.3 | ||||
| Summary | 0000535: Frequently seeing "unable to flush data to frontend" errors and timeout errors in application servers | ||||
| Description | PgPool 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 Reproduce | while 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 | ||||
| Tags | No tags attached. | ||||
|
|
Any help on this is really appreciated. We are seeing this error more frequently in our production system and its impacting the users. |
|
|
This seems the application disconnects the socket to pgpool-II. Is there setting of connection timeout in your application? |
|
|
No. There is no timeout setting in application. Development team is using Postgres9.4 psql library to connect to pgpool server. |
|
|
There are no timeout settings at firewall level as well. |
|
|
> 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. |
|
|
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. |
| 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 |
