Testing migration from 6.7 to 7.8 on a huge dataset, we have detected the following hotspots.
Total duration of the migration: 22H
- Migration #1907 PopulateLiveMeasures: 16.5H
- Migration #1908 DeletePersonAndFileMeasures: 4H
- Migration #2504 DeleteModuleAndFolderMeasures: 1H
In addition to the performance issue, there is a functional problem with migration PopulateLiveMeasures: in case of failure during the migration (which is more likely to happen since the migration is very long), the migration will restart from scratch.
- try to optimize migrations to decrease duration
- make PopulateLiveMeasures incremental to not restart from scratch in case of issue
- Split DeletePersonAndFileMeasures in two and delete person measures before PopulateLiveMeasures to decrease size of the table and simplify the query (remove condition pm.person_id is null).
- Decrease the volume of data selected/transmitted by PopulateLiveMeasures by only returning measures ids in the SELECT, and using a SELECT in the INSERT step to copy measures data on DB side:
INSERT INTO live_measures (uuid, component_uuid, project_uuid, metric_id, value, text_value, variation, measure_data, created_at, updated_at) SELECT ?, ?, ?, pm.metric_id, pm.value, pm.text_value, pm.variation_value_1, pm.measure_data, ?, ? FROM project_measures pm WHERE pm.id = ?
- Remove some indexes before doing the DELETE (and readd indexes only at the end)
- Merge #1908 and #2504