I've successfully improved the performance of the 'Change Log' by taking Eileen's advice here, converting 5 tables to InnoDB and adding indexes. In so doing, I almost blew the space limit on the host site as one table log_civicrm_group had almost 4 million rows. (The site has only 3.500 contacts but 1.3 million activities) I solved the problem by emptying this table, with what appears to be no ill effect (!). The upside of better performance having the advantage over not being able to look at the change log at all. So the question arises - as Civi is used year on year these logging tables must grow and grow - so is there a process to keep them tidied, and stop them blowing a space limit? A parameter or setting somewhere? Or is there a need to write some code to remove rows over a certain age? I guess the same question applies to activities but I'm interested in logging for now.
2
-
As a sidenote: There has been an issue, where the group log table is growing way to fast, which is fixed for the upcoming 5.12.0 lab.civicrm.org/dev/core/issues/449– Thomas SchüttlerCommented Mar 19, 2019 at 10:13
-
Thanks, Thomas. This is exactly what I noticed.– Andy ClarkCommented Mar 19, 2019 at 10:31
Add a comment
|
1 Answer
Writing "code" could be as simple as a cron script that runs daily and does mysql -u user -ppassword -e "DELETE FROM log_civicrm_group WHERE log_date < DATE_SUB(NOW(), INTERVAL 2 MONTH)" databasename, which deletes records older than 2 months.
EDIT: To address your comment, if you want to completely disable logging but just for this table, you could drop the associated triggers. This might get turned back on during an upgrade if there's a change to the trigger, but can deal with that then. e.g.
SHOW TRIGGERS LIKE '%civicrm_group%';
DROP TRIGGER civicrm_group_after_insert;
DROP TRIGGER ...
Repeat and replace civicrm_group_after_insert with the various triggers.
-
This particular logging table gets added to every time the smart group cache tidy cron job is run, so grows rapidly. Not sure if the data is any use though. Commented Mar 18, 2019 at 12:35
-
I'm not completely sure what you mean by "tidy", but I've updated the answer.– DemeritCommented Mar 18, 2019 at 13:49
-
By 'tidy' I mean to housekeep and stop it growing too large Commented Mar 18, 2019 at 15:08
-
I don't think that's what the smart group cache cron job does, if we're talking about the same job - it rebuilds the cache to keep it current, so it wouldn't be unusual for that job to add rows.– DemeritCommented Mar 18, 2019 at 15:14