Re: Need help identifying a periodic performance issue.

On Nov 17, 2021, at 12:00 PM, Justin Pryzby <pryzby(at)telsasoft(dot)com<mailto:pryzby(at)telsasoft(dot)com>> wrote:

This message originated outside your organization.

On Wed, Nov 17, 2021 at 05:51:05PM +0000, Robert Creager wrote:
postgres`HeapTupleSatisfiesVisibility+0x42
postgres`heapgetpage+0x237
postgres`heapgettup_pagemode+0x5ad
postgres`heap_getnextslot+0x52
postgres`SeqNext+0x71
postgres`ExecScan+0xc9
postgres`ExecLockRows+0x7b
postgres`standard_ExecutorRun+0x10a
postgres`_SPI_execute_plan+0x524
postgres`SPI_execute_snapshot+0x116
postgres`ri_PerformCheck+0x29e
postgres`RI_FKey_check+0x5d3
postgres`RI_FKey_check_ins+0x21
postgres`ExecCallTriggerFunc+0x105
postgres`afterTriggerInvokeEvents+0x605
postgres`AfterTriggerEndQuery+0x7a
postgres`CopyFrom+0xaca
postgres`DoCopy+0x553
postgres`standard_ProcessUtility+0x5f9
postgres`ProcessUtility+0x28
55

It shows that the process is running FK triggers.
Would you show \d for the table which is the destination of COPY, and for other
tables to which it has FK constraints.

Two tables being copied into. I chased the first FK tables from the job_entry. I can do the entire thing if you want. There are bunches...

tapesystem=# \d ds3.job_entry
Table "ds3.job_entry"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
blob_id | uuid | | not null |
chunk_id | uuid | | not null |
id | uuid | | not null |
job_id | uuid | | not null |
order_index | integer | | not null |
Indexes:
"job_entry_pkey" PRIMARY KEY, btree (id)
"job_entry_blob_id_idx" btree (blob_id)
"job_entry_chunk_id_idx" btree (chunk_id)
"job_entry_job_id_blob_id_key" UNIQUE CONSTRAINT, btree (job_id, blob_id)
"job_entry_job_id_idx" btree (job_id)
"job_entry_order_index_chunk_id_key" UNIQUE CONSTRAINT, btree (order_index, chunk_id)
Foreign-key constraints:
"job_entry_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
"job_entry_chunk_id_fkey" FOREIGN KEY (chunk_id) REFERENCES ds3.job_chunk(id) ON UPDATE CASCADE ON DELETE CASCADE
"job_entry_job_id_fkey" FOREIGN KEY (job_id) REFERENCES ds3.job(id) ON UPDATE CASCADE ON DELETE CASCADE

tapesystem=# \d ds3.job_chunk
Table "ds3.job_chunk"
Column | Type | Collation | Nullable | Default
---------------------------+--------------------------------+-----------+----------+---------
blob_store_state | ds3.job_chunk_blob_store_state | | not null |
chunk_number | integer | | not null |
id | uuid | | not null |
job_id | uuid | | not null |
node_id | uuid | | |
pending_target_commit | boolean | | not null |
read_from_azure_target_id | uuid | | |
read_from_ds3_target_id | uuid | | |
read_from_pool_id | uuid | | |
read_from_s3_target_id | uuid | | |
read_from_tape_id | uuid | | |
Indexes:
"job_chunk_pkey" PRIMARY KEY, btree (id)
"job_chunk_blob_store_state_idx" btree (blob_store_state)
"job_chunk_chunk_number_job_id_key" UNIQUE CONSTRAINT, btree (chunk_number, job_id)
"job_chunk_job_id_idx" btree (job_id)
"job_chunk_node_id_idx" btree (node_id)
"job_chunk_read_from_azure_target_id_idx" btree (read_from_azure_target_id)
"job_chunk_read_from_ds3_target_id_idx" btree (read_from_ds3_target_id)
"job_chunk_read_from_pool_id_idx" btree (read_from_pool_id)
"job_chunk_read_from_s3_target_id_idx" btree (read_from_s3_target_id)
"job_chunk_read_from_tape_id_idx" btree (read_from_tape_id)
Foreign-key constraints:
"job_chunk_job_id_fkey" FOREIGN KEY (job_id) REFERENCES ds3.job(id) ON UPDATE CASCADE ON DELETE CASCADE
"job_chunk_node_id_fkey" FOREIGN KEY (node_id) REFERENCES ds3.node(id) ON UPDATE CASCADE ON DELETE SET NULL
"job_chunk_read_from_azure_target_id_fkey" FOREIGN KEY (read_from_azure_target_id) REFERENCES target.azure_target(id) ON UPDATE CASCADE ON DELETE SET NULL
"job_chunk_read_from_ds3_target_id_fkey" FOREIGN KEY (read_from_ds3_target_id) REFERENCES target.ds3_target(id) ON UPDATE CASCADE ON DELETE SET NULL
"job_chunk_read_from_pool_id_fkey" FOREIGN KEY (read_from_pool_id) REFERENCES pool.pool(id) ON UPDATE CASCADE ON DELETE SET NULL
"job_chunk_read_from_s3_target_id_fkey" FOREIGN KEY (read_from_s3_target_id) REFERENCES target.s3_target(id) ON UPDATE CASCADE ON DELETE SET NULL
"job_chunk_read_from_tape_id_fkey" FOREIGN KEY (read_from_tape_id) REFERENCES tape.tape(id) ON UPDATE CASCADE ON DELETE SET NULL
Referenced by:
TABLE "ds3.job_chunk_azure_target" CONSTRAINT "job_chunk_azure_target_chunk_id_fkey" FOREIGN KEY (chunk_id) REFERENCES ds3.job_chunk(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ds3.job_chunk_ds3_target" CONSTRAINT "job_chunk_ds3_target_chunk_id_fkey" FOREIGN KEY (chunk_id) REFERENCES ds3.job_chunk(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ds3.job_chunk_persistence_target" CONSTRAINT "job_chunk_persistence_target_chunk_id_fkey" FOREIGN KEY (chunk_id) REFERENCES ds3.job_chunk(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ds3.job_chunk_s3_target" CONSTRAINT "job_chunk_s3_target_chunk_id_fkey" FOREIGN KEY (chunk_id) REFERENCES ds3.job_chunk(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ds3.job_entry" CONSTRAINT "job_entry_chunk_id_fkey" FOREIGN KEY (chunk_id) REFERENCES ds3.job_chunk(id) ON UPDATE CASCADE ON DELETE CASCADE

tapesystem=# \d ds3.job
Table "ds3.job"
Column | Type | Collation | Nullable | Default
-----------------------------------------+-------------------------------------------------+-----------+----------+---------
bucket_id | uuid | | not null |
cached_size_in_bytes | bigint | | not null |
chunk_client_processing_order_guarantee | ds3.job_chunk_client_processing_order_guarantee | | not null |
completed_size_in_bytes | bigint | | not null |
created_at | timestamp without time zone | | not null |
id | uuid | | not null |
original_size_in_bytes | bigint | | not null |
priority | ds3.blob_store_task_priority | | not null |
request_type | ds3.job_request_type | | not null |
user_id | uuid | | not null |
truncated | boolean | | not null |
rechunked | timestamp without time zone | | |
error_message | character varying | | |
naked | boolean | | not null |
name | character varying | | not null |
aggregating | boolean | | not null |
minimize_spanning_across_media | boolean | | not null |
truncated_due_to_timeout | boolean | | not null |
implicit_job_id_resolution | boolean | | not null |
verify_after_write | boolean | | not null |
replicating | boolean | | not null |
dead_job_cleanup_allowed | boolean | | not null |
restore | ds3.job_restore | | not null |
Indexes:
"job_pkey" PRIMARY KEY, btree (id)
"ds3_job__bucket_id" btree (bucket_id)
"ds3_job__created_at" btree (created_at)
"ds3_job__name" btree (name)
"ds3_job__user_id" btree (user_id)
Foreign-key constraints:
"job_bucket_id_fkey" FOREIGN KEY (bucket_id) REFERENCES ds3.bucket(id) ON UPDATE CASCADE ON DELETE CASCADE
"job_user_id_fkey" FOREIGN KEY (user_id) REFERENCES ds3."user"(id) ON UPDATE CASCADE
Referenced by:
TABLE "ds3.data_migration" CONSTRAINT "data_migration_get_job_id_fkey" FOREIGN KEY (get_job_id) REFERENCES ds3.job(id) ON UPDATE CASCADE ON DELETE SET NULL
TABLE "ds3.data_migration" CONSTRAINT "data_migration_put_job_id_fkey" FOREIGN KEY (put_job_id) REFERENCES ds3.job(id) ON UPDATE CASCADE ON DELETE SET NULL
TABLE "ds3.job_chunk" CONSTRAINT "job_chunk_job_id_fkey" FOREIGN KEY (job_id) REFERENCES ds3.job(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "notification.job_completed_notification_registration" CONSTRAINT "job_completed_notification_registration_job_id_fkey" FOREIGN KEY (job_id) REFERENCES ds3.job(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ds3.job_entry" CONSTRAINT "job_entry_job_id_fkey" FOREIGN KEY (job_id) REFERENCES ds3.job(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "notification.s3_object_cached_notification_registration" CONSTRAINT "s3_object_cached_notification_registration_job_id_fkey" FOREIGN KEY (job_id) REFERENCES ds3.job(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "notification.s3_object_persisted_notification_registration" CONSTRAINT "s3_object_persisted_notification_registration_job_id_fkey" FOREIGN KEY (job_id) REFERENCES ds3.job(id) ON UPDATE CASCADE ON DELETE CASCADE

tapesystem=# \d ds3.blob
Table "ds3.blob"
Column | Type | Collation | Nullable | Default
---------------+------------------------+-----------+----------+---------
byte_offset | bigint | | not null |
checksum | character varying | | |
checksum_type | security.checksum_type | | |
id | uuid | | not null |
length | bigint | | not null |
object_id | uuid | | not null |
Indexes:
"blob_pkey" PRIMARY KEY, btree (id)
"blob_byte_offset_object_id_key" UNIQUE CONSTRAINT, btree (byte_offset, object_id)
"ds3_blob__object_id" btree (object_id)
Foreign-key constraints:
"blob_object_id_fkey" FOREIGN KEY (object_id) REFERENCES ds3.s3_object(id) ON UPDATE CASCADE
Referenced by:
TABLE "target.blob_azure_target" CONSTRAINT "blob_azure_target_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "temp.blob_azure_target_to_verify" CONSTRAINT "blob_azure_target_to_verify_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "pool.blob_pool" CONSTRAINT "blob_pool_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "target.blob_s3_target" CONSTRAINT "blob_s3_target_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "temp.blob_s3_target_to_verify" CONSTRAINT "blob_s3_target_to_verify_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "tape.blob_tape" CONSTRAINT "blob_tape_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "target.blob_ds3_target" CONSTRAINT "blob_target_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ds3.degraded_blob" CONSTRAINT "degraded_blob_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ds3.job_entry" CONSTRAINT "job_entry_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ds3.multi_part_upload_part" CONSTRAINT "multi_part_upload_part_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "ds3.multi_part_upload" CONSTRAINT "multi_part_upload_placeholder_blob_id_fkey" FOREIGN KEY (placeholder_blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "pool.obsolete_blob_pool" CONSTRAINT "obsolete_blob_pool_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "tape.obsolete_blob_tape" CONSTRAINT "obsolete_blob_tape_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "target.suspect_blob_azure_target" CONSTRAINT "suspect_blob_azure_target_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "pool.suspect_blob_pool" CONSTRAINT "suspect_blob_pool_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "target.suspect_blob_s3_target" CONSTRAINT "suspect_blob_s3_target_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "tape.suspect_blob_tape" CONSTRAINT "suspect_blob_tape_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "target.suspect_blob_ds3_target" CONSTRAINT "suspect_blob_target_blob_id_fkey" FOREIGN KEY (blob_id) REFERENCES ds3.blob(id) ON UPDATE CASCADE ON DELETE CASCADE

Also, do you have any long-running transactions ?

Not at the time this is happening.

In your first message, you showed no other queries except "idle" ones (not
idle-in-transaction) but I figured I'd ask anyway.

Does your COPY job run in a transaction block ?

Auto-commit is enabled for that connection, so each COPY should be in its own transaction.

You're running pg13.2, so it would be interesting to know if the problem exists
under 13.5.

I’d have to see what it would take to get to 13.5

--
Justin