I am getting Storage Full on one of my staging RDS tables and trying to figure out which DB/Tables is large ... but found that they are actually very small:
SELECT
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;
SELECT table_schema "DB Name",
Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema;
Largest DB is only 16.7 MB so whats filling up my 10GB RDS?
From the pattern I am suspecting some cron ... which indeed there is a cron inserting data ... but as you can see from the table/db sizes its not that large ... what else can be filling up my RDS? Does backup/logs etc count as storage?
UPDATE:
I notice that on 26 July there is is spike in DB free storage and I tried looking into General Query log to determine what happened. I notice theres
PURGE BINARY LOGS TO 'mysql-bin-changelog.097019'
Wonder if it fails to purge logs subsequently?
An Amazon RDS DB instance in the storage-full status doesn't have enough available space to perform basic operations, such as connecting to or restarting the instance. To resolve this issue, do the following: Confirm that the DB instance status is storage-full. Increase the allocated storage of your DB instance.
Search for the FreeStorageSpace metric. Choose RDS, and then choose Per-Database metrics. For the instance that you want to monitor, choose the DB instance identifier FreeStorageSpace metric. Set the statistic to Minimum, and set the period to 1 minute.
SHOW BINARY LOGS;
to see how many, and of what size, your logs. Then we can discuss purging some of them.
SHOW VARIABLES LIKE '%log%'
Chase down the files that are mentioned; show us the output so we can discuss sizes.
The "general log" can grow very fast; turn it off when not using it. And get rid of its log(s).
The "slow log" is very good for debugging performance problems. Use pt-query-digest
to summarize it, then toss it.
The "error log" should not be big. If it is, then you probably have worse problems.
What is the current setting for expire_logs_days
?
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