A while back the server running my instance had 20GB of EBS storage. It then started getting disk storage errors, so I increased it to 40GB. Then again, storage missing errors so I increased again to 60GB. (So this is a 60GB RDS Instance)
You can see here the Free Storage Space (MB) chart. Each time it shoots up I add more storage space..
If I run this query..
SELECT CONCAT(table_schema, '.', table_name),
CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
ROUND(index_length / data_length, 2) idxfrac
FROM information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 10;
I get the following response...
Nothing stands out as taking up a huge amount of space.
If I then run
select table_schema, CONCAT(ROUND( sum((data_length+index_length)/1024/1024)/1024, 2), 'G') AS MB from information_schema.tables group by 1;
I can see that the largest table has about 10GB. (This includes data_length and index_length )
My next thought is that the slow low in storage is general_log or slow query logs writing to disk...
If I check the parameter groups on my RDS instance I can see that logging is disabled.
Does anyone have any idea why my RDS server is slowly leaking storage?
UPDATE:
I got some help from the kind folk on #mysql
After running
show global variables like 'log_bin';
It was clear that binary logs were enabled.
I then ran
show binary logs
and had 41674+ logs.
Scrolling down through my logs I could see one of the file size was 2064636
I then attempted to delete all the binary logs up to that changelog file.
purge binary logs to "mysql-bin-changelog.152193"
However RDS does not provide File_priv
or Super_priv
to the master user.
I would like to think that this is where the diskspace has gone.. however, 2064636 is only about 2Mb... So back to the drawing board?