I need to reduce the size of my Civi database.
I've done a bunch of reading and have found what looks like a solution. Please will someone step in to advise if this solution (below) is the way to go, and if I can just run it on each of the db tables.
Identifying the problem tables
Mailing records are my primary issue and this extension worked well to remove old records. https://github.com/adixon/nz.co.fuzion.deleteoldbulkmailings
But as mentioned in Recommendations or Experiences on Archiving Mailing Data this has not freed-up as much the space as I'd hoped.
Using this helped identify the culprits:
SELECT table_schema AS `Database`, table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = 'dbname' ORDER BY (data_length + index_length) DESC LIMIT 20;
SO, IS THIS THE SOLUTION?
This link offers a solution to recover the disk space: https://dba.stackexchange.com/questions/48740/recover-the-disk-space-after-deleting-rows-from-table
The accepted solution in the post is to run this on the tables that have had rows removed:
USE mydb DROP TABLE IF EXISTS mytablenew; CREATE TABLE mytablenew LIKE mytable; ALTER TABLE mytable RENAME mytableold; INSERT INTO mytablenew SELECT * FROM mytableold; ANALYZE TABLE mytablenew; ALTER TABLE mytablenew RENAME mytable; DROP TABLE mytableold;
I'm running the latest Drupal and Civi versions.