We have MariaDB in AWS RDS on r5.large instance. We have 4 instances and each instance has about 350GB of storage used. Each instance has almost 2000 different databases, the migration tries to run on all of them, but the alghoritm executes the migration one by one.
The table we are altering is actually quite small - it usually has hundreds of rows per database.
For some strange reason, when we run this migration the AWS RDS MariaDB consumes all the storage we have, as we have autoscaled storage the value of it jumped from 350GB to ~650GB and still it was not enough. Also the consumption of such enormous data was extremely quick (1-2 minutes based on monitoring). Then it was stopped just because AWS refused to give us more space such quickly and there was no storage left.
It happened on all 4 instances.
This is the alter table we used
ALTER TABLE `item_place`
ADD COLUMN IF NOT EXISTS `deleted_at` datetime NULL,
ALGORITHM=NOCOPY, LOCK=NONE;
ALTER TABLE `item_place`
ADD INDEX IF NOT EXISTS `deleted_at` (`deleted_at`),
ALGORITHM=NOCOPY, LOCK=NONE;
We did not see anything unusual. Also we have TEST databases with same migrations and nothing happened there. They are much smaller though (5GB), but we did not see even small drop in storage space when the migration was executed there.
We did not find anything special in logs.