Re: ETL - sql orchestrator is stuck when there is not sleep() between queries

Hello,

> Is it true that the SELECTs have no joins in them ?

Yes there is a lot of LEFT JOIN.

> When it doesn't work, you could check SELECT * FROM pg_stat_activity, and
>SELECT pg_blocking_pids(pid), * FROM pg_locks, to see what's going on.

I can't see any blocking queries blocking pg_locks, pg_blocking_pids.

> It'd be very useful to get "explain analyze" for a working query and for a
> stuck query. It sound like the stuck query never finishes, so maybe the
second
> part is impossible (?)

We run an explain analysis and we see some very interesting stuff going on.
It seems without explicitly adding a `ANALYZE`, the query has a cost of
over billions, so the query is not stuck but took forever.
When I run the same scripts with an ANALYZE right before running the query,
the query is exec is 50secondes and the cost is normal

Explain analyze WITHOUT ANALYZE https://explain.depesz.com/s/RaSr
Explain analyze same query WITH ANALYZE BEFORE
https://explain.depesz.com/s/tYVl

The configuration is tuned by aws aurora, but this issue happens also with
a default config.

allow_system_table_mods,off
application_name,DataGrip 2021.1.3
archive_command,(disabled)
archive_mode,off
archive_timeout,5min
array_nulls,on
authentication_timeout,1min
autovacuum,on
autovacuum_analyze_scale_factor,0.05
autovacuum_analyze_threshold,50
autovacuum_freeze_max_age,200000000
autovacuum_max_workers,12
autovacuum_multixact_freeze_max_age,400000000
autovacuum_naptime,5s
autovacuum_vacuum_cost_delay,1ms
autovacuum_vacuum_cost_limit,1200
autovacuum_vacuum_scale_factor,0.1
autovacuum_vacuum_threshold,50
autovacuum_work_mem,-1
backend_flush_after,0
backslash_quote,safe_encoding
bgwriter_delay,200ms
bgwriter_flush_after,0
bgwriter_lru_maxpages,100
bgwriter_lru_multiplier,2
bonjour,off
bytea_output,hex
check_function_bodies,on
checkpoint_completion_target,0.9
checkpoint_flush_after,0
checkpoint_timeout,15min
checkpoint_warning,30s
client_encoding,UTF8
client_min_messages,notice
commit_delay,0
commit_siblings,5
constraint_exclusion,partition
cpu_index_tuple_cost,0.005
cpu_operator_cost,0.0025
cpu_tuple_cost,0.01
cursor_tuple_fraction,0.1
DateStyle,"ISO, MDY"
db_user_namespace,off
deadlock_timeout,1s
debug_pretty_print,on
debug_print_parse,off
debug_print_plan,off
debug_print_rewritten,off
default_statistics_target,500
default_text_search_config,pg_catalog.simple
default_transaction_deferrable,off
default_transaction_isolation,read committed
default_transaction_read_only,off
dynamic_library_path,$libdir
effective_cache_size,4GB
effective_io_concurrency,600
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
extra_float_digits,3
force_parallel_mode,off
from_collapse_limit,8
fsync,off
full_page_writes,off
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
hot_standby,off
hot_standby_feedback,on
huge_pages,try
idle_in_transaction_session_timeout,25min
ignore_checksum_failure,off
ignore_system_indexes,off
IntervalStyle,postgres
jit,off
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
lc_monetary,C
lc_numeric,C
lc_time,C
listen_addresses,*
lock_timeout,0
lo_compat_privileges,off
maintenance_work_mem,2GB
max_connections,100
max_files_per_process,1000
max_locks_per_transaction,256
max_logical_replication_workers,4
max_parallel_maintenance_workers,12
max_parallel_workers,12
max_parallel_workers_per_gather,6
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,6MB
max_standby_archive_delay,30s
max_standby_streaming_delay,14s
max_sync_workers_per_subscription,2
max_wal_senders,0
max_wal_size,8GB
max_worker_processes,12
min_parallel_index_scan_size,512kB
min_parallel_table_scan_size,8MB
min_wal_size,2GB
old_snapshot_threshold,-1
operator_precedence_warning,off
parallel_leader_participation,off
parallel_setup_cost,1000
parallel_tuple_cost,0.1
password_encryption,md5
port,5432
post_auth_delay,0
pre_auth_delay,0
quote_all_identifiers,off
random_page_cost,1.1
restart_after_crash,on
row_security,on
search_path,public
seq_page_cost,1
session_replication_role,origin
shared_buffers,1GB
standard_conforming_strings,on
statement_timeout,0
superuser_reserved_connections,3
synchronize_seqscans,on
synchronous_commit,on
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,8MB
temp_file_limit,-1
TimeZone,UTC
trace_notify,off
trace_recovery_messages,log
trace_sort,off
track_activities,on
track_activity_query_size,4kB
track_commit_timestamp,off
track_counts,on
track_functions,none
track_io_timing,off
transform_null_equals,off
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,0
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_buffers,16MB
wal_compression,off
wal_level,minimal
wal_log_hints,off
wal_receiver_status_interval,10s
wal_receiver_timeout,30s
wal_retrieve_retry_interval,5s
wal_sender_timeout,1min
wal_sync_method,fdatasync
wal_writer_delay,200ms
wal_writer_flush_after,1MB
work_mem,2GB
xmlbinary,base64
xmloption,content
zero_damaged_pages,off

On Thu, Jul 8, 2021 at 2:33 PM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

> On Thu, Jul 08, 2021 at 01:00:28AM +0200, Allan Barrielle wrote:
> > All is fine, and can work great.
> > But sometimes, some queries that used to take about 20 secs to complete
> can
> > suddenly end in 5mins.
> > Important all queries have the same shape -> CREATE TABLE SELECT AS *(a
> bit
> > of transform) FROM TABLE). No update, nothing, it’s dead simple.
> >
> > Nothing works, it’s very random, some query won’t simply work ( even
> after
> > hours ).
>
> When it doesn't work, you could check SELECT * FROM pg_stat_activity, and
> SELECT pg_blocking_pids(pid), * FROM pg_locks, to see what's going on.
>
> > Important all queries have the same shape -> CREATE TABLE SELECT AS *(a
> bit
> > of transform) FROM TABLE). No update, nothing, it’s dead simple.
> > We are just trying to copy a table from schema1, to schema2, to schema3
> and
> > finally schema3. That’s it.
>
> Is it true that the SELECTs have no joins in them ?
>
> Did this ever work better or differently under different versions of
> postgres ?
>
> > Why does the query never end even after hours ? Why there is no log about
> > where the query is stuck.
>
> Please send your nondefault config.
> https://wiki.postgresql.org/wiki/Server_Configuration
>
> Also enable logging (I just added this to the wiki).
> https://wiki.postgresql.org/wiki/Slow_Query_Questions#Enable_Logging
>
> It'd be very useful to get "explain analyze" for a working query and for a
> stuck query. It sound like the stuck query never finishes, so maybe the
> second
> part is impossible (?)
>
> But it'd be good to get at least "explain" output. You'd have to edit
> your sql
> script to run an "explain" before each query, and run it, logging the
> ouput,
> until you capture the plan for a stuck query. Save the output and send
> here,
> along with the query plan for a working query.
>
> --
> Justin
>