MODINVSTOR-844 Investigate and choose a strategy for publicationPeriod Migration

Owned by Oleksii Kuzminov
Last updated: Jan 11, 2022 by Natalia Zaitseva
2 min read

MODINVSTOR-844 - Getting issue details... STATUS

Purpose

When migrating the mod-inventory-storage, it was found that the update script is being executed for more than 2 hours on a large amount of data. This downtime is unacceptable for the FSE team. It is necessary to analyze and prepare possible solutions to the problem.

Investigation results

PTF env

During the investigation, the Falcon and PTF team conducted several tests. The next approaches were tested:

  • Non-blocking Java migration
  • Script enhancement
  • Create temp table and create from select
  • Delete indexes before the update

Non-blocking Java migration

Using a basic approach from PR, it was found the next results by the PTF team.

DB instance type: db.r6g.8xlarge

Record amount: 10M records database.

Time: 9 hours 10 minutes. 

transactionRateRun2.png

CPU Usage.png

First tests on performance rancher env show more than 40 hours to finish the migration.

Script enhancement

PTF env

Adding indexes and disabling triggers didn't show any results. According to the PTF team tests we have the next results.

DB instance type: db.r6g.8xlarge

Record amount: 10M records database.

Time: 9 hours 15 minutes. 

Perf rancher env

Doing the same tests on performance rancher env we achieved close results:

DB instance type: db.r6g.8xlarge

Record amount: 8M records database.

Time: 8 hours 35 minutes.

Another interesting point is that after preparing DB and running VACUUM FULL, the migration finished faster, but the vacuum took more than 3 hours

DB instance type: db.r6g.8xlarge

Record amount: 8M records database.

Time: 1 hour 34 minutes.

Create temp table and create from select and recreation of indexes

By request from Mikhail we created a script for testing possible approaches with the temp table and testing migration after deleting all indexes.

Using 

create table temp_table
as 
select

we found next results

DB instance type: db.r6g.8xlarge

Record amount: 8M records database.

Time: 27 minutes.

But recreating all indexes took more than 5 hours

Preferable solution

After analyzing all results and conversations with the architect's team, it looks like Java migration is a more preferable solution. It allows to run the migration in a non-blocking way and run an ES reindex in the same way for particular records.

User stories created:

  • MODINVSTOR-862 - Getting issue details... STATUS - Implement non-blocking Java migration for publicationPeriod as a separate process
  • PERF-212 - Getting issue details... STATUS - Test non-blocking Java migration for publicationPeriod as a separate process