pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: r.zharkov@postgrespro.ru
To: Andres Freund <andres@anarazel.de>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15727: PANIC: cannot abort transaction 295144144, it was already committed
Date: Wed, 03 Apr 2019 15:38:16 +0700
Message-ID: <0ebe78ca6c44528cffdb8a0f466abc4d@postgrespro.ru> (raw )
In-Reply-To: <20190402171152.lmdbcnlhyakexujl@alap3.anarazel.de >
References: <30650.1554219866@sss.pgh.pa.us >
<20190402155108.GA17787@alvherre.pgsql >
<20190402161320.o5wkbp7evuzqe4c2@alap3.anarazel.de >
<b5d89910ec01a657530d1a49ff0e05dc@postgrespro.ru >
<20190402171152.lmdbcnlhyakexujl@alap3.anarazel.de >
On 2019-04-03 00:11, Andres Freund wrote:
> Hi,
>
> On 2019-04-03 00:04:15 +0700, r.zharkov@postgrespro.ru wrote:
>> pgbench connects to the database through the connection pooller called
>> Odyssey.
>> We use pool of 16 or 32 backends.
>> We run four series of pgbench tests with different connections count (
>> 100,
>> 150, 200, ... , 1000 )
>> Three series with pool size 16 runs normally.
>> But fourth series fails with 200-300 connections. It took about five
>> hours
>> to reproduce the error this time.
>>
>> Here is the little piece of the pgbench log:
>>
>> Bench start
>> Tue Apr 2 14:43:12 UTC 2019
>> Timestamp 1554216192
>> transaction type: multiple scripts
>> scaling factor: 1
>
> Is this the actual scaling factor? Did you create the pgbench database
> with foreign keys?
>
> Greetings,
>
> Andres Freund
Hi,
Yes, 1 is the actual scaling factor.
My script to create database:
/db/zharkov/.vanila/bin/pg_ctl -D /db/zharkov/vanila -l
/db/zharkov/vanila/log.log stop
rm -rf /db/zharkov/vanila/*
/db/zharkov/.vanila/bin/initdb -D /db/zharkov/vanila --data-checksums
#/db/zharkov/.vanila/bin/initdb -D /db/zharkov/vanila
cp postgresql.conf /db/zharkov/vanila/
ulimit -c unlimited
/db/zharkov/.vanila/bin/pg_ctl -D /db/zharkov/vanila -c -l
/db/zharkov/vanila/log.log start
/db/zharkov/.vanila/bin/psql postgres -c 'create database test'
/db/zharkov/.vanila/bin/psql test -c 'create extension pg_prewarm;'
/db/zharkov/.vanila/bin/pgbench -i test -s 1
#/db/zharkov/.vanila/bin/pgbench -i test -s 1000
/db/zharkov/.vanila/bin/psql test -c "select pg_prewarm(
'pgbench_accounts' );"
/db/zharkov/.vanila/bin/psql test -c "select pg_prewarm(
'pgbench_branches' );"
/db/zharkov/.vanila/bin/psql test -c "select pg_prewarm(
'pgbench_history' );"
/db/zharkov/.vanila/bin/psql test -c "select pg_prewarm(
'pgbench_tellers' );"
The dump of the server settings is in the attachment.
--
regards,
Roman Zharkov
allow_system_table_mods = off
application_name = psql
archive_cleanup_command =
archive_command = (disabled)
archive_mode = off
archive_timeout = 0
array_nulls = on
authentication_timeout = 60
autovacuum = off
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 = 60
autovacuum_vacuum_cost_delay = 2
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 = 200
bgwriter_flush_after = 64
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2
block_size = 8192
bonjour = off
bonjour_name =
bytea_output = hex
check_function_bodies = on
checkpoint_completion_target = 0.9
checkpoint_flush_after = 32
checkpoint_timeout = 1500
checkpoint_warning = 30
client_encoding = UTF8
client_min_messages = notice
cluster_name =
commit_delay = 0
commit_siblings = 5
config_file = /db/zharkov/vanila/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 = on
data_directory = /db/zharkov/vanila
data_directory_mode = 0700
data_sync_retry = off
DateStyle = ISO, DMY
db_user_namespace = off
deadlock_timeout = 1000
debug_assertions = on
debug_pretty_print = on
debug_print_parse = off
debug_print_plan = off
debug_print_rewritten = off
default_statistics_target = 100
default_table_access_method = heap
default_tablespace =
default_text_search_config = pg_catalog.russian
default_transaction_deferrable = off
default_transaction_isolation = read committed
default_transaction_read_only = off
dynamic_library_path = $libdir
dynamic_shared_memory_type = posix
effective_cache_size = 524288
effective_io_concurrency = 1
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_parallel_append = on
enable_parallel_hash = on
enable_partition_pruning = on
enable_partitionwise_aggregate = off
enable_partitionwise_join = off
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 = 1
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 = 4096
hba_file = /db/zharkov/vanila/pg_hba.conf
hot_standby = on
hot_standby_feedback = off
huge_pages = try
ident_file = /db/zharkov/vanila/pg_ident.conf
idle_in_transaction_session_timeout = 0
ignore_checksum_failure = off
ignore_system_indexes = off
integer_datetimes = on
IntervalStyle = postgres
jit = on
jit_above_cost = 100000
jit_debugging_support = off
jit_dump_bitcode = off
jit_expressions = on
jit_inline_above_cost = 500000
jit_optimize_above_cost = 500000
jit_profiling_support = off
jit_provider = llvmjit
jit_tuple_deforming = on
join_collapse_limit = 8
krb_caseins_users = off
krb_server_keyfile =
lc_collate = en_US.UTF-8
lc_ctype = en_US.UTF-8
lc_messages = ru_RU.UTF-8
lc_monetary = ru_RU.UTF-8
lc_numeric = ru_RU.UTF-8
lc_time = ru_RU.UTF-8
listen_addresses = localhost
local_preload_libraries =
lock_timeout = 0
lo_compat_privileges = off
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 = 0600
log_filename = postgresql-%Y-%m-%d_%H%M%S.log
logging_collector = off
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 = info
log_parser_stats = off
log_planner_stats = off
log_replication_commands = off
log_rotation_age = 1440
log_rotation_size = 10240
log_statement = none
log_statement_sample_rate = 1
log_statement_stats = off
log_temp_files = -1
log_timezone = Asia/Novosibirsk
log_truncate_on_rotation = off
maintenance_work_mem = 65536
max_connections = 1010
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_maintenance_workers = 2
max_parallel_workers = 8
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 = 2048
max_standby_archive_delay = 30000
max_standby_streaming_delay = 30000
max_sync_workers_per_subscription = 2
max_wal_senders = 10
max_wal_size = 20480
max_worker_processes = 8
min_parallel_index_scan_size = 64
min_parallel_table_scan_size = 1024
min_wal_size = 80
old_snapshot_threshold = -1
operator_precedence_warning = off
parallel_leader_participation = on
parallel_setup_cost = 1000
parallel_tuple_cost = 0.1
password_encryption = md5
pg_prewarm.autoprewarm_interval = 300
plan_cache_mode = auto
port = 5432
post_auth_delay = 0
pre_auth_delay = 0
primary_conninfo =
primary_slot_name =
promote_trigger_file =
quote_all_identifiers = off
random_page_cost = 4
recovery_end_command =
recovery_min_apply_delay = 0
recovery_target =
recovery_target_action = pause
recovery_target_inclusive = on
recovery_target_lsn =
recovery_target_name =
recovery_target_time =
recovery_target_timeline = latest
recovery_target_xid =
restart_after_crash = off
restore_command =
row_security = on
search_path = "$user", public
segment_size = 131072
seq_page_cost = 1
server_encoding = UTF8
server_version = 12devel-master/bb76134
server_version_num = 120000
session_preload_libraries =
session_replication_role = origin
shared_buffers = 2097152
shared_memory_type = mmap
shared_preload_libraries =
ssl = off
ssl_ca_file =
ssl_cert_file = server.crt
ssl_ciphers = none
ssl_crl_file =
ssl_dh_params_file =
ssl_ecdh_curve = none
ssl_key_file = server.key
ssl_library =
ssl_max_protocol_version =
ssl_min_protocol_version = TLSv1
ssl_passphrase_command =
ssl_passphrase_command_supports_reload = off
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 = on
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 = 1024
temp_file_limit = -1
temp_tablespaces =
TimeZone = Asia/Novosibirsk
timezone_abbreviations = Default
trace_notify = off
trace_recovery_messages = log
trace_sort = off
track_activities = on
track_activity_query_size = 1024
track_commit_timestamp = off
track_counts = on
track_functions = none
track_io_timing = off
transaction_deferrable = off
transaction_isolation = read committed
transaction_read_only = off
transform_null_equals = off
unix_socket_directories = /tmp
unix_socket_group =
unix_socket_permissions = 0777
update_process_title = on
vacuum_cleanup_index_scale_factor = 0.1
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 = 2048
wal_compression = off
wal_consistency_checking =
wal_keep_segments = 0
wal_level = replica
wal_log_hints = off
wal_receiver_status_interval = 10
wal_receiver_timeout = 60000
wal_retrieve_retry_interval = 5000
wal_segment_size = 16777216
wal_sender_timeout = 60000
wal_sync_method = fdatasync
wal_writer_delay = 200
wal_writer_flush_after = 128
work_mem = 4096
xmlbinary = base64
xmloption = content
zero_damaged_pages = off
Attachments:
[text/plain] settings.txt (8.0K, ../0ebe78ca6c44528cffdb8a0f466abc4d@postgrespro.ru/2-settings.txt)
download | inline:
allow_system_table_mods = off
application_name = psql
archive_cleanup_command =
archive_command = (disabled)
archive_mode = off
archive_timeout = 0
array_nulls = on
authentication_timeout = 60
autovacuum = off
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 = 60
autovacuum_vacuum_cost_delay = 2
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 = 200
bgwriter_flush_after = 64
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2
block_size = 8192
bonjour = off
bonjour_name =
bytea_output = hex
check_function_bodies = on
checkpoint_completion_target = 0.9
checkpoint_flush_after = 32
checkpoint_timeout = 1500
checkpoint_warning = 30
client_encoding = UTF8
client_min_messages = notice
cluster_name =
commit_delay = 0
commit_siblings = 5
config_file = /db/zharkov/vanila/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 = on
data_directory = /db/zharkov/vanila
data_directory_mode = 0700
data_sync_retry = off
DateStyle = ISO, DMY
db_user_namespace = off
deadlock_timeout = 1000
debug_assertions = on
debug_pretty_print = on
debug_print_parse = off
debug_print_plan = off
debug_print_rewritten = off
default_statistics_target = 100
default_table_access_method = heap
default_tablespace =
default_text_search_config = pg_catalog.russian
default_transaction_deferrable = off
default_transaction_isolation = read committed
default_transaction_read_only = off
dynamic_library_path = $libdir
dynamic_shared_memory_type = posix
effective_cache_size = 524288
effective_io_concurrency = 1
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_parallel_append = on
enable_parallel_hash = on
enable_partition_pruning = on
enable_partitionwise_aggregate = off
enable_partitionwise_join = off
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 = 1
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 = 4096
hba_file = /db/zharkov/vanila/pg_hba.conf
hot_standby = on
hot_standby_feedback = off
huge_pages = try
ident_file = /db/zharkov/vanila/pg_ident.conf
idle_in_transaction_session_timeout = 0
ignore_checksum_failure = off
ignore_system_indexes = off
integer_datetimes = on
IntervalStyle = postgres
jit = on
jit_above_cost = 100000
jit_debugging_support = off
jit_dump_bitcode = off
jit_expressions = on
jit_inline_above_cost = 500000
jit_optimize_above_cost = 500000
jit_profiling_support = off
jit_provider = llvmjit
jit_tuple_deforming = on
join_collapse_limit = 8
krb_caseins_users = off
krb_server_keyfile =
lc_collate = en_US.UTF-8
lc_ctype = en_US.UTF-8
lc_messages = ru_RU.UTF-8
lc_monetary = ru_RU.UTF-8
lc_numeric = ru_RU.UTF-8
lc_time = ru_RU.UTF-8
listen_addresses = localhost
local_preload_libraries =
lock_timeout = 0
lo_compat_privileges = off
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 = 0600
log_filename = postgresql-%Y-%m-%d_%H%M%S.log
logging_collector = off
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 = info
log_parser_stats = off
log_planner_stats = off
log_replication_commands = off
log_rotation_age = 1440
log_rotation_size = 10240
log_statement = none
log_statement_sample_rate = 1
log_statement_stats = off
log_temp_files = -1
log_timezone = Asia/Novosibirsk
log_truncate_on_rotation = off
maintenance_work_mem = 65536
max_connections = 1010
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_maintenance_workers = 2
max_parallel_workers = 8
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 = 2048
max_standby_archive_delay = 30000
max_standby_streaming_delay = 30000
max_sync_workers_per_subscription = 2
max_wal_senders = 10
max_wal_size = 20480
max_worker_processes = 8
min_parallel_index_scan_size = 64
min_parallel_table_scan_size = 1024
min_wal_size = 80
old_snapshot_threshold = -1
operator_precedence_warning = off
parallel_leader_participation = on
parallel_setup_cost = 1000
parallel_tuple_cost = 0.1
password_encryption = md5
pg_prewarm.autoprewarm_interval = 300
plan_cache_mode = auto
port = 5432
post_auth_delay = 0
pre_auth_delay = 0
primary_conninfo =
primary_slot_name =
promote_trigger_file =
quote_all_identifiers = off
random_page_cost = 4
recovery_end_command =
recovery_min_apply_delay = 0
recovery_target =
recovery_target_action = pause
recovery_target_inclusive = on
recovery_target_lsn =
recovery_target_name =
recovery_target_time =
recovery_target_timeline = latest
recovery_target_xid =
restart_after_crash = off
restore_command =
row_security = on
search_path = "$user", public
segment_size = 131072
seq_page_cost = 1
server_encoding = UTF8
server_version = 12devel-master/bb76134
server_version_num = 120000
session_preload_libraries =
session_replication_role = origin
shared_buffers = 2097152
shared_memory_type = mmap
shared_preload_libraries =
ssl = off
ssl_ca_file =
ssl_cert_file = server.crt
ssl_ciphers = none
ssl_crl_file =
ssl_dh_params_file =
ssl_ecdh_curve = none
ssl_key_file = server.key
ssl_library =
ssl_max_protocol_version =
ssl_min_protocol_version = TLSv1
ssl_passphrase_command =
ssl_passphrase_command_supports_reload = off
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 = on
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 = 1024
temp_file_limit = -1
temp_tablespaces =
TimeZone = Asia/Novosibirsk
timezone_abbreviations = Default
trace_notify = off
trace_recovery_messages = log
trace_sort = off
track_activities = on
track_activity_query_size = 1024
track_commit_timestamp = off
track_counts = on
track_functions = none
track_io_timing = off
transaction_deferrable = off
transaction_isolation = read committed
transaction_read_only = off
transform_null_equals = off
unix_socket_directories = /tmp
unix_socket_group =
unix_socket_permissions = 0777
update_process_title = on
vacuum_cleanup_index_scale_factor = 0.1
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 = 2048
wal_compression = off
wal_consistency_checking =
wal_keep_segments = 0
wal_level = replica
wal_log_hints = off
wal_receiver_status_interval = 10
wal_receiver_timeout = 60000
wal_retrieve_retry_interval = 5000
wal_segment_size = 16777216
wal_sender_timeout = 60000
wal_sync_method = fdatasync
wal_writer_delay = 200
wal_writer_flush_after = 128
work_mem = 4096
xmlbinary = base64
xmloption = content
zero_damaged_pages = off
view thread (46+ messages) latest in thread
Message-ID: <0ebe78ca6c44528cffdb8a0f466abc4d@postgrespro.ru>
Permalink: ../0ebe78ca6c44528cffdb8a0f466abc4d@postgrespro.ru/
Also on: postgresql.org/message-id/0ebe78ca6c44528cffdb8a0f466abc4d@postgrespro.ru
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: r.zharkov@postgrespro.ru, andres@anarazel.de, alvherre@2ndquadrant.com, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15727: PANIC: cannot abort transaction 295144144, it was already committed
In-Reply-To: <0ebe78ca6c44528cffdb8a0f466abc4d@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox