Page MenuHomePhabricator

examine the possibility of purging data from civicrm.civicrm_activity
Open, Needs TriagePublic

Description

We're looking at various ways to counter growth of the civicrm database, and civicrm.civicrm_activity is a >100GB table. We may be able to selectively purge data from this table.

Tests:

Get an order of magnitude on the impact of purging data from column 'details'

  • procedure:
    • create table civicrm_activity_test like civicrm_activity;
    • insert into civicrm_activity_test select * from civicrm_activity;
    • check size civicrm_activity_test.ibd (result: 104GB)
    • update civicrm_activity_test set details=null;
    • create table civicrm_activity_test2 like civicrm_activity_test;
    • insert into civicrm_activity_test2 select * from civicrm_activity_test;
    • check size of civicrm_activity_test2.ibd (result: 25GB)

Event Timeline

Jgreen created this task.Aug 26 2020, 8:53 PM
Jgreen updated the task description. (Show Details)Aug 26 2020, 8:56 PM
Jgreen updated the task description. (Show Details)Aug 26 2020, 9:01 PM
Jgreen updated the task description. (Show Details)Aug 26 2020, 10:34 PM
Jgreen added subscribers: Eileenmcnaughton, Ejegg.

@Eileenmcnaughton @Ejegg result of deleting all the civicrm.civicrm_activity 'details' column reduces the table from 104GB to 25GB, see task updated description for details.

@Jgreen so I'm going with this being worth more work :-) did log_civicrm_activity have an even larger change?

@Jgreen so I'm going with this being worth more work :-) did log_civicrm_activity have an even larger change?

log_civicrm_activity is a much smaller table, only 37GB before nulling that column