Menu
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.
900
RDS DB instances require pages in the RAM only when the pages are currently accessed, such as when running queries. Other pages that are brought into the RAM by previously run queries are flushed to swap space if they weren't used recently.
RDS does not allow you to reduce the amount of storage allocated to a database instance, only increase it. To move your database to less storage you would have to create a new RDS instance with your desired storage space, then use something like pg_dump/pg_restore to move the data from the old database to the new one.
To check the size of each table for a particular database (in your DB instance), run the following query: mysql> SELECT table_schema "DB Name", table_name,(data_length + index_length)/1024/1024/1024 AS "TableSizeinGB" from information_schema. tables where table_schema='database_name';
To scale your read operations, you horizontally scale your database through read replicas. When you create a read replica, Amazon RDS creates read-only copies of your database and manages the asynchronous replication from the primary database.
we had the same problem. Upgrade MariaDB version from 10.3.8 to 10.5.8 solved the issue.
164
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
