并行运行多个飞行路线迁移的最佳策略 [英] Best strategy to run multiple flyway migration in parallel

问题描述

我想在单个mysql实例上运行的旧系统上升级多个架构.

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

在开发中，我有〜10个架构，而在生产中，我有〜100个架构.

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

在开发中，我使用一个简单的bash循环为每个模式启动flyway migrate:

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

此策略在开发人员中效果很好.在生产中，我迅速使运行flyway migrate的gitlab运行器的内存最大化.

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?

推荐答案

您似乎需要限制并行运行的进程数.当前，您将运行与架构一样多的进程，在生产中，您有100个进程，因此将耗尽所有内存.有许多方法可以做到这一点，包括pexec，parallel甚至xargs.我假设您有权访问xargs其他人需要安装的软件.

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 写了很好的答案，并提供有关如何在-P选项中使用xargs的示例:

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

尝试使用-P后更新示例.

Updating with example after experimenting with -P.

此命令演示-P:

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

在flyway上尝试使用此命令:

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'

这篇关于并行运行多个飞行路线迁移的最佳策略的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！