Ibdata1 filling up disk space

Had the same issue a lot of people have had, though this one filled up very quickly so I think there is something wrong. I tried deleting everything out of the cel table and then optimizing said table, but that caused the database to double in size. What are my options? Is there a way to delete the temporary table it built while trying to optimize?

I’m assuming it’s the CEL table, but dont know for sure.

It’s grown another couple hundred mb since posting this.

image

The bad news is that the file cannot be shrunk. But it can be recreated and will be smaller.

Delete any data you don’t want from the CDR and CEL tables using mysql CLI or other tool.

Then use steps like these: How to Shrink MySQL ibdata1 Size using innodb_file_per_table (or search for similar instructions on Google using search terms “shrink ibdata1”)

In short, the process is to use mysqldump to backup your databases, drop the databases, delete the large ibdata file, and then restore the databases.

Thanks. Will need to practice my sql. Can I optimize each of these to create a copy?

image

Or do I need to go into each database and optimize each table? Hoping not.

Optimize won’t do anything for you. Don’t bother. See MySQL :: MySQL 8.0 Reference Manual :: 13.7.3.4 OPTIMIZE TABLE Statement . None of the conditions listed there apply.

Just dump your Asterisk and asteriskcdrdb databases. As @billsimon mentioned, it’s going to take less time if you remove old entries first.
Drop both databases and then stop the mysqld service, remove the ibdata1 files + log files. Start the service back and restore the backed up databases.

And then do the same in a few months when the cel database grows like Topsy :-)’

(sounds like a cronjob to me)

Appreciate it. Dumping the asteriskcdrdb database after having deleted CEL data older than 6 months was only 740 mb. If it grows really quick again I’ll look into storing it remotely.