Project Export in import/export and direct transfer - PG::QueryCanceled: ERROR: canceling statement due to statement timeout

Some projects fail to export due to the error PG::QueryCanceled: ERROR: canceling statement due to statement timeout. This issue may arise from inadequate table indexes during the export process rather than from the batch size mentioned in #441426 (closed).

The Kibana filter displays the project details along with the SQL query that encountered the error. With this information, we can assess potential performance issues of the SQL queries using Database Lab. When utilizing Database Lab, it's essential to use the project_id of the project that failed to examine SQL performance, especially when exporting a large number of records.

added Category:Importers Importer:Direct Transfer Importer:Project Export/Import backend devopsfoundations groupimport and integrate sectioncore platform typemaintenance labels

Review - Query 1

SELECT notes.id
FROM   notes
WHERE  notes.project_id = PROJECT_ID
       AND notes.noteable_type = 'MergeRequest'
ORDER  BY notes.id ASC
LIMIT  100

Review - Query 2

SELECT merge_requests.id
FROM   merge_requests
WHERE  merge_requests.target_project_id = PROJECT_ID
ORDER  BY merge_requests.id ASC
LIMIT  100

Review - Query 3

SELECT notes.id
FROM   notes
WHERE  notes.project_id = PROJECT_ID
       AND notes.noteable_type = PROJECT_ID
       AND notes.id > NOTE_ID
ORDER  BY notes.id ASC
LIMIT  100

added customer label

mentioned in issue #442465

@wortschi @m_frankiewicz, I created this to fix PG::QueryCanceled: ERROR errors that happen when exporting large projects. The issue impacts DT and Project Export.

Fixing the problem should allow us to remove the OPS FF: export_reduce_relation_batch_size

Thank you! @wortschi should get to refinement and in 17.8? I guess something to be done before DT GA.

@rodrigo.tomonari I wonder if we can close Project imported by file export/direct transfer... (#458367) in favor of this issue?

@wortschi, unfortunately, we can't close. This issue is related to export and #458367 is related to import, so they aren't related.

Got it, thanks for confirming.

@wortschi I'd like to add them both to 17.10, to make next milestone focus of bringing DT to GA.

added gitlab-org#11398 as parent epic

changed milestone to %17.10

added ready for next refinement label

marked this issue as related to #442465

mentioned in issue #509325

mentioned in issue gitlab-org/foundations/general-discussion#17694

mentioned in issue #514910 (closed)

We've logged 3 other queries that have been failing over the past 7 days besides the ones on merge_requests and notes (logs).

SELECT "labels"."id" FROM "labels" WHERE "labels"."type" = $1 AND "labels"."project_id" = $2 ORDER BY "labels"."id" ASC LIMIT $3

SELECT "uploads".* FROM "uploads" WHERE "uploads"."model_id" = $1 AND "uploads"."model_type" = $2 ORDER BY "uploads"."id" ASC LIMIT $3

SELECT "p_ci_pipelines"."id" FROM "p_ci_pipelines" WHERE "p_ci_pipelines"."project_id" = $1 AND ("p_ci_pipelines"."source" IN ($2, $3, $4, $5, $6, $7, $8, $9, $10, $11) OR "p_ci_pipelines"."source" IS NULL) ORDER BY "p_ci_pipelines"."id" ASC LIMIT $12

changed title from Project Export - PG::QueryCanceled: ERROR: canceling statement due to statement timeout to Project Export in import/export and direct transfer - PG::QueryCanceled: ERROR: canceling statement due to statement timeout

set weight to 3

removed ready for next refinement label

added workflowready for development label

added milestonep3 label

mentioned in incident gitlab-com/gl-infra/production#19364 (closed)

This just came up in a recent severity3 incident: gitlab-com/gl-infra/production#19364 (closed)

@rodrigo.tomonari @.luke I've been wondering if we should take a look at the incident logs while they are still available and add any information to this issue that would help in getting a better understanding of the root cause. Or do we already know what needs to be done here - WDYT?

@wortschi, The incident error is related to #458367, a slightly different problem that occurs during import rather than export.

I copied the failed SQL, stack trace, and the impacted project from the logs and added a comment in the other issue. All the information to fix the problem.