2

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.

Avión
  • 7,963
  • 11
  • 64
  • 105
  • --flush Flush each table after check. This is useful if you don't want to have the checked tables take up space in the caches after the check. | https://mariadb.com/kb/en/library/mysqlcheck/ – unixmiah Sep 12 '18 at 13:08
  • @unixmiah That's MariaDB, I don't see the `flush` option on the MySQL documentation: https://dev.mysql.com/doc/refman/8.0/en/mysqlcheck.html – Avión Sep 12 '18 at 13:23
  • Why exactly is the correct syntax highlighting bad? I makes the comments look like comments! – dessert Nov 18 '19 at 19:26

1 Answers1

3

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 :)

fancyPants
  • 50,732
  • 33
  • 89
  • 96