Search code examples
mysqldatabasemysqlcheck

MySQL: mysqlcheck taking too much disk space to perform operations

Every saturday at 8am I've the following sh script which makes a maintenance on all my databases. I run a mysqlcheck with --check, --optimize and --analyze.

This is the script:

# check: checks table for integrity errors
mysqlcheck -u root -h mydbendpoint.com -p'mypass' --check --all-databases

# optimize: reorganizes physical storage of table and index data
mysqlcheck -u root -h mydbendpoint.com -p'mypass' --optimize --all-databases

# analyze: rebuild and optimize the performance of indexes
mysqlcheck -u root -h -h mydbendpoint.com -p'mypass' --analyze --all-databases

The thing is that when this .sh is running it takes a lot of disk space. Here's a screenshot of my Amazon RDS free disk space:

enter image description here

Which of the three comands is taking so much disk space to make those mysqlchecks? --check, --optimize or --analyze? Or the three of them?

I can't find anything about this on the official documentation.

Thanks in advance.

Solution

  • It's the --optimize which uses all that space. It basically rebuilds all your tables to regain unused space (data that was deleted), if you've configured your server with innodb_file_per_table

    This is rarely necessary, you can skip that.

    That said, you really do this on all your databases? This should also not be necessary. I only have one database where I do a weekly check, and the purpose of this host is solely to verify that my backups work. Once a week a backup is restored on this host, then the mysqlcheck verifies that all tables work. And that's it. My boss would fire me, if I would do this on servers in production :)