*** PLEASE IGNORE THIS POST, I WILL LEAVE IT HERE TO HELP OTHERS**
Solution: this problem seems to have been caused by having pg_stat_statements enabled
Problem:
Using Windows 64bit target under Delphi Tokyo 10.2.3 using your controls in a 40 thread pool (with your pooling turned off ) after around 2-3 million transactions my postgres database server crashes and refuses to serve any further users reporting 'out of memory'. The pool appears to be working correctly as the 'sessions' list remains at the 40 connection mark and appears to be reusing the same session as expected throughout the process, and the application itself is not leaking any memory.
Can you do a test with TPgQuery that inserts a single record in each transaction, running 40 transactions at a time in threads, using Postgres 10, insert 10 million transactions and see if your server also reports an out of memory error.
Connection Setup
fConnection.Pooling := false;
fConnection.LoginPrompt := False;
fConnection.Options.ApplicationName := ExtractFileName(paramstr(0));
fConnection.Options.UseUnicode := true;
fConnection.Options.EnableComposites := true;
fConnection.Options.DefaultSortType := stCaseInsensitive;
Query Setup
thisQuery := TPgQuery.Create(nil);
thisQuery.Connection := fConnection;
thisQuery.readonly := true;
thisQuery.cachedUpdates := true;
thisQuery.FetchAll := true;
thisQuery.Options.LocalMasterDetail := true;
thisQuery.Options.StrictUpdate := false;
thisQuery.Connection.Database := DatabaseName;
Sample Table
CREATE TABLE web.ip2location
(
id bigint NOT NULL DEFAULT nextval('web.ip2location_id_seq'::regclass),
ip_from bigint,
ip_to bigint,
country_code character varying(2) COLLATE pg_catalog."default",
country_name character varying(64) COLLATE pg_catalog."default",
region character varying(128) COLLATE pg_catalog."default",
city character varying(128) COLLATE pg_catalog."default",
latitude double precision,
longitude double precision,
zipcode character varying(30) COLLATE pg_catalog."default",
CONSTRAINT ip2location_pkey PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
Postgres Server Config
Code: Select all
allow_system_table_mods off
application_name pgAdmin 4 - CONN:6023645
archive_command (disabled)
archive_mode off
archive_timeout 0
array_nulls on
authentication_timeout 1min
autovacuum on
autovacuum_analyze_scale_factor 0.1
autovacuum_analyze_threshold 50
autovacuum_freeze_max_age 200000000
autovacuum_max_workers 3
autovacuum_multixact_freeze_max_age 400000000
autovacuum_naptime 1min
autovacuum_vacuum_cost_delay 20ms
autovacuum_vacuum_cost_limit -1
autovacuum_vacuum_scale_factor 0.2
autovacuum_vacuum_threshold 50
autovacuum_work_mem -1
backend_flush_after 0
backslash_quote safe_encoding
bgwriter_delay 200ms
bgwriter_flush_after 512kB
bgwriter_lru_maxpages 100
bgwriter_lru_multiplier 2
block_size 8192
bonjour off
bonjour_name
bytea_output escape
check_function_bodies on
checkpoint_completion_target 0.7
checkpoint_flush_after 256kB
checkpoint_timeout 5min
checkpoint_warning 30s
client_encoding UNICODE
client_min_messages notice
cluster_name
commit_delay 0
commit_siblings 5
config_file /var/lib/pgsql/10/data/postgresql.conf
constraint_exclusion partition
cpu_index_tuple_cost 0.005
cpu_operator_cost 0.0025
cpu_tuple_cost 0.01
cursor_tuple_fraction 0.1
data_checksums off
data_directory /var/lib/pgsql/10/data
DateStyle ISO, DMY
db_user_namespace off
deadlock_timeout 1s
debug_assertions off
debug_pretty_print on
debug_print_parse off
debug_print_plan off
debug_print_rewritten off
default_statistics_target 100
default_tablespace
default_text_search_config pg_catalog.english
default_transaction_deferrable off
default_transaction_isolation read committed
default_transaction_read_only off
default_with_oids off
dynamic_library_path $libdir
dynamic_shared_memory_type posix
effective_cache_size 48GB
effective_io_concurrency 200
enable_bitmapscan on
enable_gathermerge on
enable_hashagg on
enable_hashjoin on
enable_indexonlyscan on
enable_indexscan on
enable_material on
enable_mergejoin on
enable_nestloop on
enable_seqscan on
enable_sort on
enable_tidscan on
escape_string_warning on
event_source PostgreSQL
exit_on_error off
external_pid_file
extra_float_digits 0
force_parallel_mode off
from_collapse_limit 8
fsync on
full_page_writes on
geqo on
geqo_effort 5
geqo_generations 0
geqo_pool_size 0
geqo_seed 0
geqo_selection_bias 2
geqo_threshold 12
gin_fuzzy_search_limit 0
gin_pending_list_limit 4MB
hba_file /var/lib/pgsql/10/data/pg_hba.conf
hot_standby on
hot_standby_feedback off
huge_pages try
ident_file /var/lib/pgsql/10/data/pg_ident.conf
idle_in_transaction_session_timeout 0
ignore_checksum_failure off
ignore_system_indexes off
integer_datetimes on
IntervalStyle postgres
join_collapse_limit 8
krb_caseins_users off
krb_server_keyfile FILE:/etc/sysconfig/pgsql/krb5.keytab
lc_collate en_US.UTF-8
lc_ctype en_US.UTF-8
lc_messages en_AU.UTF-8
lc_monetary en_AU.UTF-8
lc_numeric en_AU.UTF-8
lc_time en_AU.UTF-8
listen_addresses *
lo_compat_privileges off
local_preload_libraries
lock_timeout 0
log_autovacuum_min_duration -1
log_checkpoints off
log_connections off
log_destination stderr
log_directory log
log_disconnections off
log_duration off
log_error_verbosity default
log_executor_stats off
log_file_mode 600
log_filename postgresql-%a.log
log_hostname off
log_line_prefix %m [%p]
log_lock_waits off
log_min_duration_statement -1
log_min_error_statement error
log_min_messages warning
log_parser_stats off
log_planner_stats off
log_replication_commands off
log_rotation_age 1d
log_rotation_size 0
log_statement none
log_statement_stats off
log_temp_files -1
log_timezone Australia/Victoria
log_truncate_on_rotation on
logging_collector on
maintenance_work_mem 2GB
max_connections 100000
max_files_per_process 1000
max_function_args 100
max_identifier_length 63
max_index_keys 32
max_locks_per_transaction 64
max_logical_replication_workers 4
max_parallel_workers 16
max_parallel_workers_per_gather 2
max_pred_locks_per_page 2
max_pred_locks_per_relation -2
max_pred_locks_per_transaction 64
max_prepared_transactions 0
max_replication_slots 10
max_stack_depth 2MB
max_standby_archive_delay 30s
max_standby_streaming_delay 30s
max_sync_workers_per_subscription 2
max_wal_senders 10
max_wal_size 2GB
max_worker_processes 8
min_parallel_index_scan_size 512kB
min_parallel_table_scan_size 8MB
min_wal_size 1GB
old_snapshot_threshold -1
operator_precedence_warning off
parallel_setup_cost 1000
parallel_tuple_cost 0.1
password_encryption md5
pg_stat_statements.max 5000
pg_stat_statements.save on
pg_stat_statements.track all
pg_stat_statements.track_utility on
port 5432
post_auth_delay 0
pre_auth_delay 0
quote_all_identifiers off
random_page_cost 1.1
replacement_sort_tuples 150000
restart_after_crash on
row_security on
search_path $user", public"
segment_size 1GB
seq_page_cost 1
server_encoding UTF8
server_version 10.4
server_version_num 100004
session_preload_libraries
session_replication_role origin
shared_buffers 16GB
shared_preload_libraries pg_stat_statements
ssl off
ssl_ca_file
ssl_cert_file server.crt
ssl_ciphers HIGH:MEDIUM:+3DES:!aNULL
ssl_crl_file
ssl_dh_params_file
ssl_ecdh_curve prime256v1
ssl_key_file server.key
ssl_prefer_server_ciphers on
standard_conforming_strings on
statement_timeout 0
stats_temp_directory pg_stat_tmp
superuser_reserved_connections 3
synchronize_seqscans on
synchronous_commit off
synchronous_standby_names
syslog_facility local0
syslog_ident postgres
syslog_sequence_numbers on
syslog_split_messages on
tcp_keepalives_count 9
tcp_keepalives_idle 7200
tcp_keepalives_interval 75
temp_buffers 28MB
temp_file_limit -1
temp_tablespaces
TimeZone Australia/Victoria
timezone_abbreviations Default
trace_notify off
trace_recovery_messages log
trace_sort off
track_activities on
track_activity_query_size 2024
track_commit_timestamp off
track_counts on
track_functions all
track_io_timing on
transaction_deferrable off
transaction_isolation read committed
transaction_read_only off
transform_null_equals off
unix_socket_directories /var/run/postgresql, /tmp
unix_socket_group
unix_socket_permissions 777
update_process_title on
vacuum_cost_delay 0
vacuum_cost_limit 200
vacuum_cost_page_dirty 20
vacuum_cost_page_hit 1
vacuum_cost_page_miss 10
vacuum_defer_cleanup_age 0
vacuum_freeze_min_age 50000000
vacuum_freeze_table_age 150000000
vacuum_multixact_freeze_min_age 5000000
vacuum_multixact_freeze_table_age 150000000
wal_block_size 8192
wal_buffers 16MB
wal_compression off
wal_consistency_checking
wal_keep_segments 0
wal_level replica
wal_log_hints off
wal_receiver_status_interval 10s
wal_receiver_timeout 1min
wal_retrieve_retry_interval 5s
wal_segment_size 16MB
wal_sender_timeout 1min
wal_sync_method fdatasync
wal_writer_delay 2s
wal_writer_flush_after 10MB
work_mem 1677kB
xmlbinary base64
xmloption content
zero_damaged_pages off