2

I want to upgrade multiple schemas on a legacy system running on a single mysql instance.

In development I have ~10 schemas, while in production I have ~100 schemas.

In development I was using a simple bash loop to start a flyway migrate for each schema:

schemas=$(echo "SET SESSION group_concat_max_len=8192; select GROUP_CONCAT(SCHEMA_NAME SEPARATOR ' ') from information_schema.SCHEMATA where SCHEMA_NAME like 'FOO_%'" | mysql -h$DB_URL -P$DB_PORT -u$DB_USER -p$DB_PASSWORD -sN)
for schema in $schemas; do
    echo "Starting Migration for :  $schema"
    flyway -configFile=src/flyway.conf -user=$DB_USER -password=$DB_PASSWORD -url="jdbc:mysql://$DB_URL:$DB_PORT" -schemas=$schema -locations=filesystem:src/schema/ migrate 2>&1 | tee $schema.log &
done

This strategy was working fine in dev. In production I quickly max out the ram of the gitlab runner that runs the flyway migrate.

In your opinion what would be the best way to acheive the database migration as fast as possible without maxing out the ram?

vinni_f
  • 622
  • 3
  • 15
  • 25

1 Answers1

1

It looks like you need to limit the number of processes run in parallel. Currently you will run as many processes as schemas, in prod you have 100 so that uses up all the ram. There are many ways of achieving this including pexec, parallel and even xargs. I'll assume you have access to xargs the others need software to be installed.

mklement0 wrote a great answer with examples on how to use xargs with the -P option:

    -P, --max-procs=MAX-PROCS    Run up to max-procs processes at a time

EDIT: Updating with example after experimenting with -P.

This command demonstrates -P:

echo -e "a\nb\nc\nd\n" | xargs -i -P 2 sh -c 'touch {}.log; sleep 3;'
ls --full-time

Try this command with flyway:

$(echo "SET SESSION group_concat_max_len=8192; select GROUP_CONCAT(SCHEMA_NAME SEPARATOR ' ') from information_schema.SCHEMATA where SCHEMA_NAME like 'FOO_%'" | mysql -h$DB_URL -P$DB_PORT -u$DB_USER -p$DB_PASSWORD -sN) | xargs -i -P 10 sh -c 'echo "Starting Migration for :  {}"; flyway -configFile=src/flyway.conf -user=$DB_USER -password=$DB_PASSWORD -url="jdbc:mysql://$DB_URL:$DB_PORT" -schemas={} -locations=filesystem:src/schema/ migrate 2>&1 | tee {}.log'
Hamish Carpenter
  • 831
  • 4
  • 9