Bug 1614023

Summary: Migrations linking jobs and miq_tasks could take long time when upgrading to 5.9
Product: Red Hat CloudForms Management Engine Reporter: Yuri Rudman <yrudman>
Component: ApplianceAssignee: Yuri Rudman <yrudman>
Status: CLOSED CURRENTRELEASE QA Contact: Jaroslav Henner <jhenner>
Severity: high Docs Contact:
Priority: unspecified    
Version: 5.9.0CC: abellott, dmetzger, jkrocil, obarenbo, simaishi, smallamp, yrudman
Target Milestone: GAKeywords: TestOnly, ZStream
Target Release: 5.10.0   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: 5.10.0.11 Doc Type: If docs needed, set a value
Doc Text:
Story Points: ---
Clone Of:
: 1618808 (view as bug list) Environment:
Last Closed: 2019-02-12 16:53:05 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Bug Depends On:    
Bug Blocks: 1618808    
Attachments:
Description Flags
db with 1M jobs
none
db_dump none

Description Yuri Rudman 2018-08-08 20:22:29 UTC 
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.
Comment 2 Yuri Rudman 2018-08-08 20:28:18 UTC 
PR to optimize migration: https://bugzilla.redhat.com/show_bug.cgi?id=1614023
PR to purge MiqTasks: https://github.com/ManageIQ/manageiq/pull/17727
Comment 3 Yuri Rudman 2018-08-10 13:37:30 UTC 
Correct link to PR opytimizing migration: https://github.com/ManageIQ/manageiq-schema/pull/201
Comment 5 Jaroslav Henner 2018-10-04 09:38:57 UTC 
How to reproduce?
Comment 6 Yuri Rudman 2018-10-04 13:36:23 UTC 
This optimization applicable only to CFME version 5.7 and below.
Database with pretty big jobs table should be used for db migration
Comment 9 Jaroslav Henner 2018-10-24 12:40:45 UTC 
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
Comment 10 Jaroslav Henner 2019-01-22 13:52:20 UTC 
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