1

I foolishly tried to add a column to a table that I did not have enough space on disk to copy and had to kill it and expand my RDS instance's storage capacity to avert a site crash. I would like to do it again (this time with enough disk space) but I can't seem to get back to my pre-query free storage levels. My query was to create a table like a giant table, add a column and then insert the entire contents of the old table together with null into the new table. I tried CALL mysql.rds_rotate_slow_log; and CALL mysql.rds_rotate_general_log; but judging by my AWS Cloudwatch panel, I'm still down ~10GB from my pre-query levels. No lines were successfully inserted into the new table. Is there some "clear hdd cache" command or something like that? Since it's RDS, I don't have access to the instance that's running it but I do have master user and RDS CLI access.

EDIT: It seems my problem may be related to giant ibdata files but since I don't have root access, I can't really execute the solutions mentioned in How to shrink/purge ibdata1 file in MySQL

Community
  • 1
  • 1
mmdanziger
  • 4,466
  • 2
  • 31
  • 47

1 Answers1

3

The solution was to drop the new table. I didn't think that anything was stored in the new table because select count(*) from new_table; returned 0 but I guess the temporary data was tied in to the new table anyway. I'm not sure how exactly this works from a database structural point of view but fortunately it did what I wanted.

Bottom line: killed inserts still use storage space.

If somebody can explain why this is the case, it would be helpful for the future.

mmdanziger
  • 4,466
  • 2
  • 31
  • 47