GPCOPY performance variation seen when copying different schemas

Products

• VMware Tanzu Greenplum

Issue/Introduction

 

During comparison of multiple different executions of GPCOPY, a variation in performance is observed for different schemas: The transfer rate is not consistent with the amount of transferred data

 

Schema_1

Total elapsed time: 44m20.185485432s
Total transferred data 129.4TB, transfer rate 88.5TB/h

 

Schema_2

Total elapsed time: 3h5m13.473841275s
Total transferred data 9.7TB, transfer rate 100.2TB/h

 

Schema_3

Total elapsed time: 3h2m19.872100189s
Total transferred data 2.7TB, transfer rate 94.9TB/h

 

Taking one example view ("sample_vw") and focusing the comparison using GPCOPY log against the same view between schema_1 and schema_2, we can see "schema_2.sample_vw" unexplainably consumed significantly longer than "schema_1.sample_vw"

20240803:13:11:30 gpcopy:gpadmin:....:066553-[INFO]:-[Worker 5] Start copying table "sample_db"."schema_1"."sample_vw" => "sample_db"."schema_1"."sample_vw"
20240803:13:11:30 gpcopy:gpadmin:....:066553-[INFO]:-[Worker 5] [Progress: (0/1) DBs, (614/1274) tables done] Finished copying table "sample_db"."schema_1"."sample_vw" => "sample_db"."schema_1"."sample_vw"
20240803:15:14:19 gpcopy:gpadmin:....:050669-[INFO]:-[Worker 9] Start copying table "sample_db"."schema_2"."sample_vw" => "sample_db"."schema_2"."sample_vw"
20240803:15:17:35 gpcopy:gpadmin:....:050669-[INFO]:-[Worker 9] [Progress: (0/1) DBs, (620/1024) tables done] Finished copying table "sample_db"."schema_2"."sample_vw" => "sample_db"."schema_2"."sample_vw"

 

The following table summarizes what we should observe from the above log - the elapsed-time difference of the same view between the two schemas

database.schema	table	duration	start_time	finish_time
sample_db.schema_2	sample_vw	196.0	2024-08-03 15:14:19	2024-08-03 15:17:35
sample_db.schema_1	sample_vw	0.0	2024-08-03 13:11:30	2024-08-03 13:11:30

Cause

In taking the "schema_2.sample_vw" example above, analyzing pg_logs confirms the cause of the long elapsed time (196 second) is: DDL recreation is required on the target, hence execution of the COPY requires:

1. Running pg_dump on the source cluster.
2. Then re-creating the DDL on the target cluster using the generated Database dump.

 

 

For schema_1, "sample_vw" does not require creating DDL on the destination host, and so is the case for majority of the schema tables, resulting in less total elapsed time.

 

Resolution

This is a known disadvantage of gpcopy. If creating DDL is required on the destination, it will be time-consuming. Therefore, applying gpbackup to rebuild the DDL first, and then using GPCOPY to copy the data would be considered the best practice to optimize copy performance.