If you’re finding that your MySQL is taking up a large amount of disk space on your servers, and you’re not running any particularly large websites, it’s likely that this is due to how InnoDB works.
What may be happening is your sites are storing a lot of temporary data inside the database. Each InnoDB table is stored in it’s own file, and InnoDB files can grow, but not shrink on their own. So even though you might have a huge InnoDB file, it may only have 200Mb of real data inside it.
There are 2 ways to fix this: –
- Use a database optimization plugin to delete the unnecessary extra data from individual websites.
- Use WP CLI
The WP CLI Method
To run a database optimization with WP CLI, first connect to your server using SSH. Here are our articles on how to do this: –
Generate SSH Key on Windows with Putty
Generate SSH Key on Windows with Windows Subsystem for Linux
Connect to a vCanopy server by SSH as Root user
When logged into your server, copy and paste the following command and hit enter (“domain.com” should be the domain you wish to optimize): –
gp -wp domain.com db optimize
Prevention
To prevent this from happening in the future, Redis object caching should help reduce this kind of behavior by a wide margin. Here is an overview of Redis object caching and how to use it on your website: