Answers.Body: <p>Writing "code" could be as simple as a cron script that runs daily and does <code>mysql -u user -ppassword -e "DELETE FROM log_civicrm_group WHERE log_date &lt; DATE_SUB(NOW(), INTERVAL 2 MONTH)" databasename</code>, which deletes records older than 2 months.</p>
  
  <p>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.</p>
  
  <p><code>SHOW TRIGGERS LIKE '%civicrm_group%';
  DROP TRIGGER civicrm_group_after_insert;
  DROP TRIGGER ...</code></p>
  
  <p>Repeat and replace civicrm_group_after_insert with the various triggers.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-03-18T12:35:20.313Z
Answers.Text: 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.
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-03-18T13:49:11.593Z
Answers.Text: I'm not completely sure what you mean by "tidy", but I've updated the answer.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-03-18T15:08:38.197Z
Answers.Text: By 'tidy' I mean to housekeep and stop it growing too large
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-03-18T15:14:55.147Z
Answers.Text: 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.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-03-16T19:16:08.890Z
Answers.LastActivityDate: 2019-03-18T13:48:51.257Z
Answers.LastEditDate: 2019-03-18T13:48:51.257Z
Answers.UserDisplayName: Demerit
Body: <p>I've successfully improved the performance of the 'Change Log' by taking Eileen's advice <a href="https://civicrm.org/blog/eileen/who-did-what-when" rel="nofollow noreferrer">here</a>, 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.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-03-19T10:13:23.940Z
Comments.Text: 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 https://lab.civicrm.org/dev/core/issues/449
Comments.UserDisplayName: Thomas Schüttler
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-03-19T10:31:07.727Z
Comments.Text: Thanks, Thomas. This is exactly what I noticed.
Comments.UserDisplayName: Andy Clark
Tags: <mysql><logging><db-logging>
Title: Is there a process to tidy the tables created by logging?
ThreadText: # Is there a process to tidy the tables created by logging?
  
  (Asked by: username_0 on 2019-03-16)
  
  I've successfully improved the performance of the 'Change Log' by taking Eileen's advice [here](https://civicrm.org/blog/eileen/who-did-what-when), 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.
  
  **Tags:** mysql, logging, db-logging
  
  > **Comments:**
  > **username_1**: 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 https://lab.civicrm.org/dev/core/issues/449  
  > **username_0**: Thanks, Thomas. This is exactly what I noticed.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2019-03-16. Score: 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.
  
  > **Comments:**
  > **username_0**: 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.  
  > **username_2**: I'm not completely sure what you mean by "tidy", but I've updated the answer.  
  > **username_0**: By 'tidy' I mean to housekeep and stop it growing too large  
  > **username_2**: 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.  
  
  ---
  
  