Memory Leaks on Linux with Delphi

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Posts: 11
Joined: Fri 06 May 2016 01:22

Memory Leaks on Linux with Delphi

Post by hsvandrew » Tue 22 May 2018 03:07

The following code demonstrates huge issues with the Postgres components compiled under Delphi for Linux

For some reason the components are never free'd at the end of the procedure which is one issue
DisposeOf reduces the memory leaks, but there is still many megabytes of leaking (over a loop of the size indicated below) from within the component as far as I can tell.
When using free, it doesn't seem to do anything which means the connection isn't even dropped, meaning the Postgres server just gets more and more connections hitting it until it refuses to accept anymore.

I assume this behaviour is caused by ARC, but it appears to be affecting the internals of your controls as well.

Code: Select all

program PgMemLeak2;


{$R *.res}


procedure newTask( value: integer );
pgConnection: TPgConnection;
pgQuery: TPgQuery;
        pgConnection := TPgConnection.Create(nil);
        pgConnection.Server := server;
        pgConnection.Username := user;
        pgConnection.Password := password;
        pgConnection.Database := Database;
        pgConnection.Pooling := false;
        pgQuery := TPgQuery.Create(nil);
        pgQuery.Connection := pgConnection;
        pgQuery.SQL.Text := 'select * from ' + TableName;

i: integer;


    for i := 0 to 1000 do
      newTask( i );


    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);

Posts: 11
Joined: Fri 06 May 2016 01:22

Re: Memory Leaks on Linux with Delphi

Post by hsvandrew » Tue 22 May 2018 09:50

Based on what I can determine, could it be that you've missed some weak references or some of your 'free' statements aren't working as expected ... 3#27926153

This still leaks memory...

Code: Select all

...     pgQuery := TPgQuery.Create(nil);
        pgQuery.Connection := pgConnection;
        pgQuery.SQL.Text := 'select * from ' + TableName;
        pgQuery := nil;
        pgQuery := nil;

Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Memory Leaks on Linux with Delphi

Post by azyk » Tue 22 May 2018 14:09

Thank you for the information. We reproduced the issue and are investigating it now. We will inform you about the obtained results.

Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Memory Leaks on Linux with Delphi

Post by azyk » Wed 27 Jun 2018 08:53

We fixed the issue. This fix will be included in the next UniDAC build.

Posts: 11
Joined: Fri 06 May 2016 01:22

Re: Memory Leaks on Linux with Delphi

Post by hsvandrew » Sat 30 Jun 2018 03:33


Solution: this problem seems to have been caused by having pg_stat_statements enabled


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)
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
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
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_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
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
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	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_replication_role	origin
shared_buffers	16GB
shared_preload_libraries	pg_stat_statements
ssl	off
ssl_cert_file	server.crt
ssl_ciphers	HIGH:MEDIUM:+3DES:!aNULL
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
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
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_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_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

Post Reply