1

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

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. – Andy Clark Mar 18 at 12:35
  • I'm not completely sure what you mean by "tidy", but I've updated the answer. – Demerit Mar 18 at 13:49
  • By 'tidy' I mean to housekeep and stop it growing too large – Andy Clark Mar 18 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. – Demerit Mar 18 at 15:14

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.