3

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.

3

I don't think this is a solution you should employ without checking on a few things i.e. foreign keys, indexes, triggers etc. It's a risky way to do it. If you must find space and have cleared as much data as you can then the way to achieve the same results would be to backup and restore the database. This will ensure all the structures are maintained.

There are alternatives but it depends on the version of MySQL/MariaDB you're running so if you want to explore them then please do post the details.

5
  • Thanks for the caution Parvez. I contacted my hosting company with your suggestion to backup / restore the dbs. They indicated they have an automated monthly optimisation routine which should recover the cleared space. They will run it today so I'm hoping it will make a difference. I will report back.
    – Davy Ivins
    Jul 6 at 9:34
  • Sadly the optimisation had a negligible effect. Still need to find a safe solution to recover the space.
    – Davy Ivins
    Jul 6 at 19:13
  • My MariaDB is version 5.7.34 and my hosting provider (Omega8) provides a Chive Manager interface to manage the DB.
    – Davy Ivins
    Jul 6 at 19:29
  • 1
    So you could try a manual backup/restore? To be honest anything a hosting provider claims to be doing is very hard to quantify without them being 100% clear on the steps they take. I doubt they will be backup/restoring the DB. If you wanted to 'test' the theory then you could use a backup/export and import into a test/local and see how much space is being used. If you've truncated tables etc this is the only way space can be regained in the version your running, newer versions will do this when an optimize is run on the tables. Jul 6 at 20:28
  • Strongly agree w/ Parvez ... another issue that might be at play here is the mysql configuration "innodb_file_per_table" which used to default to 0. In that case, all the db data is in a big monster file and it tends to use up a lot of space and is harder to recover. I'd grab a copy of the database as a dump and do a few experiments on it to see what the real issue is. Or pay a bit more for more disk space.
    – Alan Dixon
    Jul 7 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.