RDS server instance storage breakdown

Hi,

2 things first come to mind:

1. you must regularly vacuum your db to collect the space of dead tuples. See https://www.postgresql.org/docs/current/sql-vacuum.html
2. you must regularly do a snapshot in order to be able to delete the db journals. See https://dbsnapper.com/blog/how-to-create-a-postgresql-database-snapshot in general and https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateSnapshot.html for AWS RDS specific aspects

Best,

Didier

You should be able to check the size for the database using: SELECT SUM(data_length + index_length) / 1024 / 1024 AS total_size_mb FROM information_schema.tables;

Check the transaction log size (binlog for mysql) using the following:

• In the AWS Management Console, go to CloudWatch.
• Navigate to Metrics > RDS > Per-Database Metrics.
• Find your DB instance and look for the "BinLogDiskUsage" metric. This metric shows the amount of disk space occupied by binary logs.

You can also get overall storage information from the RDS console: Go to the RDS console and select your instance. In the "Configuration" tab, you'll see "Allocated storage" and "Free storage space".

If you are comfortable using AWS CLI: aws rds describe-db-instances --db-instance-identifier your-instance-id --query 'DBInstances[*].[DBInstanceIdentifier,AllocatedStorage,FreeStorageSpace]'

While these methods give you good insights into your storage usage, they don't provide real-time, to-the-byte accuracy. For critical operations, always ensure you have some buffer in your storage allocation.

Please remember that the transaction log size in RDS MySQL is managed automatically. If you're concerned about log space usage, you might want to review your backup retention period and consider more frequent log exports if needed.

When facing a low available storage warning on your RDS instance despite having allocated more storage than your database size, it's important to investigate the detailed storage breakdown. Here are some steps you can take to identify the cause of this issue:

1. Monitor the FreeStorageSpace metric: Use Amazon CloudWatch to track the FreeStorageSpace metric for your RDS instance. A rapid drop in this metric can indicate that your database has increased its usage and might be approaching the storage-full state.

2. Check for autoextended tablespaces: If you're using Oracle, review any autoextended tablespaces that might be consuming more space than expected.

3. Examine Oracle file size quotas: Ensure that Oracle file size quotas are not causing unexpected storage consumption.

4. Manage large trace files and Data Pump directories: Large trace files or Data Pump directories can consume significant storage space. Consider cleaning up or archiving these files if they're no longer needed.

5. Review archive log retention: Check your archive log retention policies, as excessive log retention can quickly consume storage space.

6. Analyze database fragmentation: Database fragmentation can lead to inefficient storage usage. Consider analyzing and addressing any fragmentation issues.

7. Use AWS tools to query DB instance metrics: Utilize AWS CLI commands or the RDS console to retrieve detailed information about your instance's storage usage.

8. Check for temporary or unmanaged files: Look for any temporary files, backups, or other unmanaged files that might be consuming space on your RDS instance.

9. Monitor storage autoscaling: If you have storage autoscaling enabled, check if it's functioning correctly and adjust the settings if necessary.

10. Create CloudWatch alarms: Set up CloudWatch alarms to notify you when the FreeStorageSpace metric reaches a specified threshold, allowing you to take proactive measures.

By systematically investigating these areas, you should be able to identify the cause of your storage issue and take appropriate action to resolve it. If the problem persists, you may need to consider increasing your allocated storage or optimizing your database usage.

Sources
Resolve issues that occur when Amazon RDS runs out of storage | AWS re:Post
Troubleshooting for Amazon RDS - Amazon Relational Database Service
Use best practices to manage RDS for Oracle storage | AWS re:Post