Re: Need help identifying a periodic performance issue.

Robert Creager <robertc@xxxxxxxxxxxxxxxx> · Wed, 17 Nov 2021 19:51:49 +0000

On Nov 17, 2021, at 12:00 PM, Justin Pryzby <pryzby@xxxxxxxxxxxxx> 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