db_cleanup
Overview
Db Cleanup is a module that provides drush commands in order to monitor the space used by your database and try to reduce it if the "data_free" of some tables have grown too much.
Features
If your website uses MariaDB/MySQL and the innodb_file_per_table parameter is on, the space used by some tables like the watchdog table can grow significantly and doesn't shrink even if a lot of rows are deleted.
With this module you can check if there are tables that would benefit from being rebuilt in order to get some space back.
You can also check what are the biggest tables in your database, to see if other actions might be needed to reduce the space used, like deleting some revisions.
Usage
Drush commands
drush db:check-fragmentation
Display tables that can be rebuilt in order to save some space. Fragmentation ratio is calculated based on the space of the current data of the table compared to the "data_free" that can potentially be reclaimed.
drush db:cleanup-tables --threshold=20
drush db:cleanup-tables --dry-runMake sure to back up your database before running this command in case anything goes wrong.
Cleanup tables based on a threshold of fragmentation ratio (default to 10 so only tables with more than 10% fragmentation ratio would be rebuilt).
Use the dry-run option to see which tables would be rebuilt without actually running the cleanup.
drush db:check-db-size --number=20
Display the biggest tables in your database and the total size of the database.
Number option to choose the number of tables to display (default to 30).
drush db:check-node-revisions --limit=20
Display the nodes with the most revisions and the average number of revisions per node.
Limit option to choose the number of nodes to display (default to 10).
Similar projects
Db Cleanup focuses on reclaiming space in your database and uses ALTER TABLE directly to rebuild the tables because OPTIMIZE TABLE doesn't reclaim the data_free space in some configurations.
It also provides other drush commands to check the space usage of your database. Reclaiming the space in this module is done manually rather than automatically via cron job.
Compatibility
Compatible with MySQL and MariaDB.
The drush command for reclaiming space can only work if innodb_file_per_table is ON.