[pgpool-general: 8960] Java application occasionally gets "The connection attempt failed" errors
Ron
ronljohnsonjr at gmail.com
Sat Nov 11 05:03:56 JST 2023
PgPool 4.3.7 and PG 14.7
By "occasionally", I mean 1 or 2 times for every 500 "batches" of data we
process.
Attached is the error message text, the pgpool.conf file and postgresql.conf
file.
"ps | aux | grep <db application user name>" shows maybe a dozen connections
to PgPool.
We do not get these errors when connecting directly to the database on node "a".
It's a three node cluster (named "a", "b" and "c"). The VIP is attached to
node "c", and the PostgreSQL primary database is on node "a". PG streaming
replica is node "b".
Putting the VIP on node "a" does _not_ solve the problem (but it does make
things run slower).
"num_init_children = 350" on all three nodes, and "max_connections = 400" on
the two DB servers.
There's nothing in the error logs where PgPool "log_min_messages = notice"
and Postgresql log_min_error_statement and log_min_messages are both =
info, and client_min_messages = notice.
Where else should we look?
--
Born in Arizona, moved to Babylonia.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20231110/db031f65/attachment.htm>
-------------- next part --------------
Message:
An external error has occurred
com.powerdox.jcore2.dbproxy.DBPAbortedSQLException: No database connection available for the datasource 'cds' (e0) (s08001) due to The connection attempt failed.
at com.powerdox.jcore2.dbproxy.DBPPostgreSQLDataSource$DBPPostgreSQLDataSourceInner.verifySQLException(DBPPostgreSQLDataSource.java:112)
at com.powerdox.jcore2.dbproxy.DBPDataSource$DBPDataSourceInner.createConnection(DBPDataSource.java:384)
at com.powerdox.jcore2.dbproxy.DBPDataSource.createConnection(DBPDataSource.java:196)
at com.powerdox.jcore2.dbproxy.DBPConnectionPool.createConnection(DBPConnectionPool.java:163)
at com.powerdox.jcore2.dbproxy.DBPConnectionPool.getConnection(DBPConnectionPool.java:101)
at com.powerdox.jcore2.dbproxy.DBPPooledDataSource$DBPPooledDataSourceInner.getConnection(DBPPooledDataSource.java:55)
at com.powerdox.jcore2.dbproxy.DBPDataSource$DBPDataSourceInner.getConnection(DBPDataSource.java:362)
at com.powerdox.jcore2.dbproxy.DBPDataSource$DBPDataSourceInner.access$1300(DBPDataSource.java:231)
at com.powerdox.jcore2.dbproxy.DBPDataSource.getConnection(DBPDataSource.java:188)
at com.powerdox.jservice2.pos.db.DBManager$DBManagerInner.getConnection(DBManager.java:633)
at com.powerdox.jservice2.pos.db.DBManager.getConnection(DBManager.java:241)
at com.powerdox.jservice2.pos.db.DBTransactionProcess$DBTransactionProcessInner.getUpdateConnection(DBTransactionProcess.java:263)
at com.powerdox.jservice2.pos.db.DBTransactionProcess$DBTransactionProcessInner.getQueryConnection(DBTransactionProcess.java:252)
at com.powerdox.jservice2.pos.db.DBTransactionProcess.getQueryConnection(DBTransactionProcess.java:73)
at com.powerdox.jservice2.pos.db.DBTransactionHandler$DBTransactionHandlerInner.buildPrepareStatement(DBTransactionHandler.java:1806)
at com.powerdox.jservice2.pos.db.DBTransactionHandler$DBTransactionHandlerInner.prepareSearchStatement(DBTransactionHandler.java:1796)
at com.powerdox.jservice2.pos.db.DBTransactionHandler$DBTransactionHandlerInner.search(DBTransactionHandler.java:1315)
at com.powerdox.jservice2.pos.db.DBTransactionHandler$DBTransactionHandlerInner.search(DBTransactionHandler.java:1295)
at com.powerdox.jservice2.pos.db.DBTransactionHandler$DBTransactionHandlerInner.searchMax(DBTransactionHandler.java:1286)
at com.powerdox.jservice2.pos.db.DBTransactionHandler.searchMax(DBTransactionHandler.java:298)
at sun.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.powerdox.jservice2.session.JSTransactionProcess$JSTransactionProcessInner.processTransaction(JSTransactionProcess.java:465)
at com.powerdox.jservice2.pos.db.DBTransactionProcess$DBTransactionProcessInner.processTransaction(DBTransactionProcess.java:294)
at com.powerdox.jservice2.pos.db.DBTransactionProcess.processTransaction(DBTransactionProcess.java:93)
at com.powerdox.jservice2.session.JSTransactionHandler$JSTransactionHandlerInner.process(JSTransactionHandler.java:93)
at com.powerdox.jservice2.session.JSTransactionHandler.process(JSTransactionHandler.java:55)
at com.powerdox.jservice2.session.JSSession$JSSessionInner.sendTransaction(JSSession.java:348)
at com.powerdox.jservice2.session.JSSession.sendTransaction(JSSession.java:133)
at com.powerdox.lockbox.service.cds.DepositService.retrieveBatches(DepositService.java:1592)
at com.powerdox.lockbox.service.cds.DepositService.processX937Files(DepositService.java:323)
at com.powerdox.lockbox.service.cds.DepositX9Service.runService(DepositX9Service.java:36)
at com.powerdox.jservice2.service.AbstractService.runImpl(AbstractService.java:150)
at com.powerdox.jservice2.service.AbstractService.run(AbstractService.java:130)
at java.lang.Thread.run(Thread.java:748)
-------------- next part --------------
listen_addresses = '*'
port = 9999
socket_dir = '/var/run/postgresql'
backend_clustering_mode = 'streaming_replication'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/var/run/postgresql'
listen_backlog_multiplier = 2
serialize_accept = on
num_init_children = 350
max_pool = 2
reserved_connections = 3
client_idle_limit = 0
#client_idle_limit = 1200
#client_idle_limit = 60
load_balance_mode = off
backend_hostname0 = 'MYCLUSTERa'
backend_port0 = 5432
backend_weight0 = 2
backend_data_directory0 = '/var/lib/pgsql/15/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'server0'
backend_hostname1 = 'MYCLUSTERb'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/15/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server1'
enable_pool_hba = on
pool_passwd = '/etc/pgpool-II/pool_passwd'
log_destination = 'stderr'
log_line_prefix = '%m: %a pid %p: '
log_connections = on
log_hostname = on
#log_statement = on
log_statement = off
#log_per_node_statement = on
log_per_node_statement = off
log_client_messages = on
#log_min_messages = info
log_min_messages = notice
logging_collector = on
log_directory = '/var/log/pgpool2'
log_filename = 'pgpool-%F_%H.log'
log_truncate_on_rotation = on
log_rotation_age = 1h
log_rotation_size = 0
sr_check_user = 'pool_health_check'
sr_check_database = 'pool_health_check'
health_check_period = 5
health_check_user = 'pool_health_check'
health_check_database = 'pool_health_check'
health_check_max_retries = 10
health_check_retry_delay = 10
use_watchdog = on
wd_priority = 1
delegate_IP = '10.109.165.13'
hostname0 = 'MYCLUSTERa'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = 'MYCLUSTERb'
wd_port1 = 9000
pgpool_port1 = 9999
hostname2 = 'MYCLUSTERc'
wd_port2 = 9000
pgpool_port2 = 9999
wd_ipc_socket_dir = '/var/run/postgresql'
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/26 dev ens192 label ens192:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/26 dev ens192'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I ens192'
wd_escalation_command = '/etc/pgpool-II/escalation.sh'
wd_lifecheck_method = 'heartbeat'
heartbeat_hostname0 = 'MYCLUSTERa'
heartbeat_port0 = 9694
heartbeat_device0 = 'ens192'
heartbeat_hostname1 = 'MYCLUSTERb'
heartbeat_port1 = 9694
heartbeat_device1 = 'ens192'
heartbeat_hostname2 = 'MYCLUSTERc'
heartbeat_port2 = 9694
heartbeat_device2 = 'ens192'
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
memory_cache_enabled = off
#memory_cache_enabled = on
memqcache_oiddir = '/var/log/pgpool2/oiddir'
memqcache_total_size = 1024MB
memqcache_max_num_cache = 1048576
-------------- next part --------------
listen_addresses = '*'
max_connections = 400
shared_buffers = 32GB
#temp_buffers = 16MB
work_mem = 300MB
maintenance_work_mem = 12GB
dynamic_shared_memory_type = posix
effective_cache_size = 108GB
max_wal_size = 2GB
fsync = on
synchronous_commit = on
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_level = hot_standby
wal_log_hints = on
#archive_mode = on
archive_mode = on
archive_command = 'pgbackrest --stanza=localhost archive-push %p'
max_wal_senders = 12
max_wal_size = 12GB
password_encryption = scram-sha-256
default_statistics_target = 5000
log_destination = 'syslog, stderr'
logging_collector = on
#log_directory = '/var/lib/pgsql/pg_log'
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%F_%H.log'
log_truncate_on_rotation = off
log_rotation_age = 1h
log_rotation_size = 0
#client_min_messages = log
log_min_messages = info
log_min_error_statement = info
client_min_messages = notice
#log_min_messages = warning
#log_min_error_statement = warning
log_checkpoints = on
log_duration = off
log_error_verbosity = verbose
log_line_prefix = '%m\t%r\t%u\t%d\t%p\t%i\t%a\t%e\t'
log_lock_waits = on
#log_statement = 'mod'
log_statement = 'ddl'
#log_statement = 'all'
log_temp_files = 1kB
log_timezone = 'US/Eastern'
#track_activity_query_size = 10240
track_activity_query_size = 48kB
#log_statement_stats = off
autovacuum = on
#autovacuum_vacuum_threshold = 250
autovacuum_vacuum_cost_delay = 10ms
#autovacuum_vacuum_cost_limit = 1000
autovacuum_max_workers = 6
#autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.03
autovacuum_analyze_scale_factor = 0.03
datestyle = 'iso, mdy'
timezone = 'US/Eastern'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
max_locks_per_transaction = 15360
shared_preload_libraries = 'pgaudit'
pgaudit.role = 'auditor'
pgaudit.log_parameter = on
pgaudit.log_statement_once = on
More information about the pgpool-general
mailing list