Description of problem: In 5.9 we added several migrations which are linking jobs and miq_tasks. On one of client's db (I think it was CU) Job.count = 1.3 millions. Timing on job related migrations for that client : CreateTaskForEachJobAndTransferAttributes - 4863 seconds CopyAgentIdToMiqServerIdInJobsTable - 168 seconds CopyServerIdFromJobsToMiqTask - 1645 seconds Version-Release number of selected component (if applicable): 5.9.* Expected results: DB migration linking jobs and tasks should not take almost 2 hours, Additional info: - We did not implemented purging for jobs table before and this table could be quite big.
PR to optimize migration: https://bugzilla.redhat.com/show_bug.cgi?id=1614023 PR to purge MiqTasks: https://github.com/ManageIQ/manageiq/pull/17727
Correct link to PR opytimizing migration: https://github.com/ManageIQ/manageiq-schema/pull/201
How to reproduce?
This optimization applicable only to CFME version 5.7 and below. Database with pretty big jobs table should be used for db migration
Created attachment 1497026 [details] db with 1M jobs I am unable to test this right now. I cannot update to 5.10 without problems and then do the migration. I am attaching the db with 1M jobs that I created on 5.7.4.3-1
Created attachment 1522423 [details] db_dump With this db (~1e6 jobs), created on CFME 5.8, the db:migrate took real 0m14.290s user 0m9.926s sys 0m1.716s