Page MenuHomePhabricator

Tables to compact on frdb hosts
Closed, ResolvedPublic

Description

Here are the tables that I have compressed on frdb2001. We should run this compaction on the other hosts (except the current master) in time. Then, when we are ready, we should do a master swap and run the compaction there.

One host we will want to watch the effects on is frdb2002. Since it is the middle master, we should see if the query passes on to frdb2001 (I believe it will). Mostly it's just to be aware of knock on effects.

Hosts to run compaction on:

  • frdb1001
  • frdb1002
  • frdb1003
  • frdev1001
  • frdb2001
  • frdb2002
# Tables to compress (increasing time to run)
# 
# 18s 5G
compact_innodb_table -d civicrm -t civicrm_entity_tag

# 2m 52G
compact_innodb_table -d drupal -t queue2civicrm_log

# 5m 2G
compact_innodb_table -d civicrm -t civicrm_email

# 15m 4G
compact_innodb_table -d civicrm -t civicrm_address

# 23m 7G
compact_innodb_table -d civicrm -t civicrm_activity_contact

# 40m 13G
compact_innodb_table -d civicrm -t civicrm_contact

# 84m 5G
compact_innodb_table -d civicrm -t civicrm_activity

# 129m 39G
compact_innodb_table -d civicrm -t civicrm_mailing_provider_data

Event Timeline

Dwisehaupt moved this task from Triage to Up Next on the fundraising-tech-ops board.
Dwisehaupt updated the task description. (Show Details)Mar 26 2020, 6:16 PM
Dwisehaupt updated the task description. (Show Details)

Starting on frdb2002 today.

Dwisehaupt updated the task description. (Show Details)Mar 27 2020, 10:33 PM

Finished up frdb202. Timings were similar in most cases, but some ran longer. Data reclaimed was also similar as expected. As a note, these queries did not propagate through replication to frdb2001.

Data from frdb2002:

# compact_innodb_table -d civicrm -t civicrm_entity_tag

Alter complete in: 20 seconds
  AKA: 0 minutes and 20 seconds

Change in sizes of tables from start to finish:
4,5c4,5
< 12    /srv/sqldata/civicrm/civicrm_entity_tag.frm
< 6017028       /srv/sqldata/civicrm/civicrm_entity_tag.ibd
---
> 4     /srv/sqldata/civicrm/civicrm_entity_tag.frm
> 131076        /srv/sqldata/civicrm/civicrm_entity_tag.ibd
7c7
< 6017056       total
---
> 131096        total


# compact_innodb_table -d drupal -t queue2civicrm_log

Alter complete in: 56 seconds
  AKA: 0 minutes and 56 seconds

Change in sizes of tables from start to finish:
1,3c1,3
< 12    /srv/sqldata/drupal/queue2civicrm_log.frm
< 55447560      /srv/sqldata/drupal/queue2civicrm_log.ibd
< 55447572      total
---
> 4     /srv/sqldata/drupal/queue2civicrm_log.frm
> 1507332       /srv/sqldata/drupal/queue2civicrm_log.ibd
> 1507336       total


# compact_innodb_table -d civicrm -t civicrm_email

Alter complete in: 376 seconds
  AKA: 6 minutes and 16 seconds

Change in sizes of tables from start to finish:
4,5c4,5
< 12    /srv/sqldata/civicrm/civicrm_email.frm
< 5533700       /srv/sqldata/civicrm/civicrm_email.ibd
---
> 8     /srv/sqldata/civicrm/civicrm_email.frm
> 3481604       /srv/sqldata/civicrm/civicrm_email.ibd
7c7
< 5533728       total
---
> 3481628       total


# compact_innodb_table -d civicrm -t civicrm_address

Alter complete in: 1022 seconds
  AKA: 17 minutes and 2 seconds

Change in sizes of tables from start to finish:
11c11
< 11833348      /srv/sqldata/civicrm/civicrm_address.ibd
---
> 7397380       /srv/sqldata/civicrm/civicrm_address.ibd
13c13
< 11833508      total
---
> 7397540       total


# compact_innodb_table -d civicrm -t civicrm_activity_contact

Alter complete in: 1514 seconds
  AKA: 25 minutes and 14 seconds

Change in sizes of tables from start to finish:
4,5c4,5
< 12    /srv/sqldata/civicrm/civicrm_activity_contact.frm
< 25014276      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
---
> 4     /srv/sqldata/civicrm/civicrm_activity_contact.frm
> 16461828      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
7c7
< 25014304      total
---
> 16461848      total


# compact_innodb_table -d civicrm -t civicrm_contact

Alter complete in: 2595 seconds
  AKA: 43 minutes and 15 seconds

Change in sizes of tables from start to finish:
6c6
< 33464324      /srv/sqldata/civicrm/civicrm_contact.ibd
---
> 20848644      /srv/sqldata/civicrm/civicrm_contact.ibd
26c26
< 33464836      total
---
> 20849156      total


# compact_innodb_table -d civicrm -t civicrm_activity

Alter complete in: 5461 seconds
  AKA: 91 minutes and 1 seconds

Change in sizes of tables from start to finish:
11c11
< 16580612      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
---
> 16670724      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
14c14
< 110505996     /srv/sqldata/civicrm/civicrm_activity.ibd
---
> 103694348     /srv/sqldata/civicrm/civicrm_activity.ibd
16c16
< 127086672     total
---
> 120365136     total


# compact_innodb_table -d civicrm -t civicrm_mailing_provider_data

Alter complete in: 8937 seconds
  AKA: 148 minutes and 57 seconds

Change in sizes of tables from start to finish:
2,3c2,3
< 99913744      /srv/sqldata/civicrm/civicrm_mailing_provider_data.ibd
< 99913752      total
---
> 58138636      /srv/sqldata/civicrm/civicrm_mailing_provider_data.ibd
> 58138644      total
DStrine moved this task from Triage to FR-Ops on the Fundraising-Backlog board.Mar 30 2020, 8:04 PM

Finished up frdb1001. Timings a bit slower in some cases.

Data from frdb1001

# compact_innodb_table -d civicrm -t civicrm_entity_tag

Alter complete in: 17 seconds
  AKA: 0 minutes and 17 seconds

Change in sizes of tables from start to finish:
4,5c4,5
< 12    /srv/sqldata/civicrm/civicrm_entity_tag.frm
< 6017024       /srv/sqldata/civicrm/civicrm_entity_tag.ibd
---
> 4     /srv/sqldata/civicrm/civicrm_entity_tag.frm
> 131072        /srv/sqldata/civicrm/civicrm_entity_tag.ibd
7c7
< 6017052       total
---
> 131092        total

# compact_innodb_table -d drupal -t queue2civicrm_log

Alter complete in: 44 seconds
  AKA: 0 minutes and 44 seconds

Change in sizes of tables from start to finish:
1,3c1,3
< 12    /srv/sqldata/drupal/queue2civicrm_log.frm
< 55431172      /srv/sqldata/drupal/queue2civicrm_log.ibd
< 55431184      total
---
> 4     /srv/sqldata/drupal/queue2civicrm_log.frm
> 1236996       /srv/sqldata/drupal/queue2civicrm_log.ibd
> 1237000       total

# compact_innodb_table -d civicrm -t civicrm_email

Alter complete in: 404 seconds
  AKA: 6 minutes and 44 seconds

Change in sizes of tables from start to finish:
4,5c4,5
< 12    /srv/sqldata/civicrm/civicrm_email.frm
< 5529604       /srv/sqldata/civicrm/civicrm_email.ibd
---
> 8     /srv/sqldata/civicrm/civicrm_email.frm
> 3485700       /srv/sqldata/civicrm/civicrm_email.ibd
7c7
< 5529632       total
---
> 3485724       total

# compact_innodb_table -d civicrm -t civicrm_address

Alter complete in: 1083 seconds
  AKA: 18 minutes and 3 seconds

Change in sizes of tables from start to finish:
11c11
< 11833352      /srv/sqldata/civicrm/civicrm_address.ibd
---
> 7405568       /srv/sqldata/civicrm/civicrm_address.ibd
13c13
< 11833512      total
---
> 7405728       total

# compact_innodb_table -d civicrm -t civicrm_activity_contact

Alter complete in: 1628 seconds
  AKA: 27 minutes and 8 seconds

Change in sizes of tables from start to finish:
4,5c4,5
< 12    /srv/sqldata/civicrm/civicrm_activity_contact.frm
< 25010196      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
---
> 4     /srv/sqldata/civicrm/civicrm_activity_contact.frm
> 16244740      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
7c7
< 25010224      total
---
> 16244760      total

# compact_innodb_table -d civicrm -t civicrm_contact

Alter complete in: 2733 seconds
  AKA: 45 minutes and 33 seconds

Change in sizes of tables from start to finish:
6c6
< 33464336      /srv/sqldata/civicrm/civicrm_contact.ibd
---
> 20758532      /srv/sqldata/civicrm/civicrm_contact.ibd
26c26
< 33464848      total
---
> 20759044      total

# compact_innodb_table -d civicrm -t civicrm_activity

Alter complete in: 5611 seconds
  AKA: 93 minutes and 31 seconds

Change in sizes of tables from start to finish:
11c11
< 16855044      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
---
> 16900100      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
14c14
< 110510140     /srv/sqldata/civicrm/civicrm_activity.ibd
---
> 103682096     /srv/sqldata/civicrm/civicrm_activity.ibd
16c16
< 127365248     total
---
> 120582260     total

# compact_innodb_table -d civicrm -t civicrm_mailing_provider_data

Alter complete in: 9034 seconds
  AKA: 150 minutes and 34 seconds

Change in sizes of tables from start to finish:
2,3c2,3
< 99934228      /srv/sqldata/civicrm/civicrm_mailing_provider_data.ibd
< 99934236      total
---
> 57950276      /srv/sqldata/civicrm/civicrm_mailing_provider_data.ibd
> 57950284      total
Dwisehaupt updated the task description. (Show Details)Mar 31 2020, 4:04 PM
Eileenmcnaughton added a comment.EditedApr 1 2020, 1:20 AM

So does this look right?

tableBeforeAftersaved
civicrm_activity110GB103GB7GB
civicrm_activity_contact25GB16GB9GB
civicrm_addresst12GB7GB5GB
civicrm_contact33GB20GB13GB
civicrm_email5GB3GB2GB
civicrm_entity_tag6GB.13GB6GB
civicrm_mailing_provider_data100GB58GB42GB
queue2civicrm_log55GB154GB

@Eileenmcnaughton That is pretty close. The numbers are a little bit off in a place or two, but I'm pretty sure it's due to converting to gigabytes using 1000 vs 1024 as they are only off by about 1G in general

One that is more off from my initial numbers is the civicrm_activity_contact. It looks like you may have duplicated the numbers from the civicrm_contact. I have civicrm_activity_contact as going from ~25G to ~16G.

frdb1003 is complete. Here are the details on it's compaction.

# compact_innodb_table -d civicrm -t civicrm_entity_tag

Alter complete in: 20 seconds
  AKA: 0 minutes and 20 seconds

Change in sizes of tables from start to finish:
5c5
< 2834436       /srv/sqldata/civicrm/civicrm_entity_tag.ibd
---
> 131076        /srv/sqldata/civicrm/civicrm_entity_tag.ibd
7c7
< 2834456       total
---
> 131096        total

# compact_innodb_table -d drupal -t queue2civicrm_log

Alter complete in: 42 seconds
  AKA: 0 minutes and 42 seconds

Change in sizes of tables from start to finish:
2,3c2,3
< 55242828      /srv/sqldata/drupal/queue2civicrm_log.ibd
< 55242832      total
---
> 1146884       /srv/sqldata/drupal/queue2civicrm_log.ibd
> 1146888       total

# compact_innodb_table -d civicrm -t civicrm_email

Alter complete in: 351 seconds
  AKA: 5 minutes and 51 seconds

Change in sizes of tables from start to finish:
5c5
< 4706308       /srv/sqldata/civicrm/civicrm_email.ibd
---
> 3502084       /srv/sqldata/civicrm/civicrm_email.ibd
7c7
< 4706332       total
---
> 3502108       total

# compact_innodb_table -d civicrm -t civicrm_address

Alter complete in: 988 seconds
  AKA: 16 minutes and 28 seconds

Change in sizes of tables from start to finish:
11c11
< 9908228       /srv/sqldata/civicrm/civicrm_address.ibd
---
> 7512068       /srv/sqldata/civicrm/civicrm_address.ibd
13c13
< 9908380       total
---
> 7512220       total

# compact_innodb_table -d civicrm -t civicrm_activity_contact

Alter complete in: 1644 seconds
  AKA: 27 minutes and 24 seconds

Change in sizes of tables from start to finish:
5c5
< 34807824      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
---
> 15675400      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
7c7
< 34807844      total
---
> 15675420      total

# compact_innodb_table -d civicrm -t civicrm_contact

Alter complete in: 2509 seconds
  AKA: 41 minutes and 49 seconds

Change in sizes of tables from start to finish:
6c6
< 30212112      /srv/sqldata/civicrm/civicrm_contact.ibd
---
> 21127172      /srv/sqldata/civicrm/civicrm_contact.ibd
26c26
< 30212616      total
---
> 21127676      total

# compact_innodb_table -d civicrm -t civicrm_activity

Alter complete in: 6375 seconds
  AKA: 106 minutes and 15 seconds

Change in sizes of tables from start to finish:
11c11
< 16158728      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
---
> 16257032      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
14c14
< 108576888     /srv/sqldata/civicrm/civicrm_activity.ibd
---
> 101277776     /srv/sqldata/civicrm/civicrm_activity.ibd
16c16
< 124735680     total
---
> 117534872     total

# compact_innodb_table -d civicrm -t civicrm_mailing_provider_data

Alter complete in: 8711 seconds
  AKA: 145 minutes and 11 seconds

Change in sizes of tables from start to finish:
2,3c2,3
< 112615488     /srv/sqldata/civicrm/civicrm_mailing_provider_data.ibd
< 112615496     total
---
> 61349900      /srv/sqldata/civicrm/civicrm_mailing_provider_data.ibd
> 61349908      total
Dwisehaupt updated the task description. (Show Details)Apr 1 2020, 8:50 PM

@Dwisehaupt it occurred to me that we could update the dev db on staging with a restore & then see if any of it's tables are smaller to see if we missed any we should have compressed

@Eileenmcnaughton That's a good point. I haven't started compaction there yet so I'll look into what it will take to do that test.

Main tables on frdev1001 are done. Will test the dump/restore option on the dev tables to see if there is more to be reclaimed there.

# sudo compact_innodb_table -d civicrm -t civicrm_entity_tag

Alter complete in: 23 seconds
  AKA: 0 minutes and 23 seconds

Change in sizes of tables from start to finish:
4,5c4,5
< 12    /srv/sqldata/civicrm/civicrm_entity_tag.frm
< 6017024       /srv/sqldata/civicrm/civicrm_entity_tag.ibd
---
> 4     /srv/sqldata/civicrm/civicrm_entity_tag.frm
> 131076        /srv/sqldata/civicrm/civicrm_entity_tag.ibd
7c7
< 6017052       total
---
> 131096        total


# sudo compact_innodb_table -d drupal -t queue2civicrm_log

Alter complete in: 53 seconds
  AKA: 0 minutes and 53 seconds

Change in sizes of tables from start to finish:
1,3c1,3
< 12    /srv/sqldata/drupal/queue2civicrm_log.frm
< 55431244      /srv/sqldata/drupal/queue2civicrm_log.ibd
< 55431256      total
---
> 4     /srv/sqldata/drupal/queue2civicrm_log.frm
> 1220612       /srv/sqldata/drupal/queue2civicrm_log.ibd
> 1220616       total


# sudo compact_innodb_table -d civicrm -t civicrm_email

Alter complete in: 474 seconds
  AKA: 7 minutes and 54 seconds

Change in sizes of tables from start to finish:
4,5c4,5
< 12    /srv/sqldata/civicrm/civicrm_email.frm
< 5529612       /srv/sqldata/civicrm/civicrm_email.ibd
---
> 8     /srv/sqldata/civicrm/civicrm_email.frm
> 3526656       /srv/sqldata/civicrm/civicrm_email.ibd
7c7
< 5529640       total
---
> 3526680       total


# sudo compact_innodb_table -d civicrm -t civicrm_address

Alter complete in: 1207 seconds
  AKA: 20 minutes and 7 seconds

Change in sizes of tables from start to finish:
11c11
< 11821076      /srv/sqldata/civicrm/civicrm_address.ibd
---
> 7487492       /srv/sqldata/civicrm/civicrm_address.ibd
13c13
< 11821236      total
---
> 7487652       total


# sudo compact_innodb_table -d civicrm -t civicrm_activity_contact

Alter complete in: 1793 seconds
  AKA: 29 minutes and 53 seconds

Change in sizes of tables from start to finish:
4,5c4,5
< 12    /srv/sqldata/civicrm/civicrm_activity_contact.frm
< 25006152      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
---
> 4     /srv/sqldata/civicrm/civicrm_activity_contact.frm
> 15663108      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
7c7
< 25006180      total
---
> 15663128      total


# sudo compact_innodb_table -d civicrm -t civicrm_contact

Alter complete in: 2931 seconds
  AKA: 48 minutes and 51 seconds

Change in sizes of tables from start to finish:
6c6
< 33448012      /srv/sqldata/civicrm/civicrm_contact.ibd
---
> 20582404      /srv/sqldata/civicrm/civicrm_contact.ibd
26c26
< 33448524      total
---
> 20582916      total


# sudo compact_innodb_table -d civicrm -t civicrm_activity

Alter complete in: 5343 seconds
  AKA: 89 minutes and 3 seconds

Change in sizes of tables from start to finish:
11c11
< 15863812      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
---
> 16330756      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
14c14
< 110788900     /srv/sqldata/civicrm/civicrm_activity.ibd
---
> 102138052     /srv/sqldata/civicrm/civicrm_activity.ibd
16c16
< 126652776     total
---
> 118468872     total


# sudo compact_innodb_table -d civicrm -t civicrm_mailing_provider_data

Alter complete in: 9715 seconds
  AKA: 161 minutes and 55 seconds

Change in sizes of tables from start to finish:
2,3c2,3
< 100184172     /srv/sqldata/civicrm/civicrm_mailing_provider_data.ibd
< 100184180     total
---
> 62984308      /srv/sqldata/civicrm/civicrm_mailing_provider_data.ibd
> 62984316      total
Dwisehaupt added a comment.EditedMay 26 2020, 10:46 PM

All complete on frdb1002. We are not going to do the dev dbs on frdev1001 as we rebuild them fairly regularly so they will get compacted at that point.

# compact_innodb_table -d civicrm -t civicrm_entity_tag

Alter complete in: 22 seconds
  AKA: 0 minutes and 22 seconds

Change in sizes of tables from start to finish:
4,5c4,5
< 12	/srv/sqldata/civicrm/civicrm_entity_tag.frm
< 6017024	/srv/sqldata/civicrm/civicrm_entity_tag.ibd
---
> 4	/srv/sqldata/civicrm/civicrm_entity_tag.frm
> 151552	/srv/sqldata/civicrm/civicrm_entity_tag.ibd
7c7
< 6017052	total
---
> 151572	total

# compact_innodb_table -d drupal -t queue2civicrm_log

Alter complete in: 50 seconds
  AKA: 0 minutes and 50 seconds

Change in sizes of tables from start to finish:
1,3c1,3
< 12    /srv/sqldata/drupal/queue2civicrm_log.frm
< 54870108      /srv/sqldata/drupal/queue2civicrm_log.ibd
< 54870120      total
---
> 4     /srv/sqldata/drupal/queue2civicrm_log.frm
> 1318916       /srv/sqldata/drupal/queue2civicrm_log.ibd
> 1318920       total

# compact_innodb_table -d civicrm -t civicrm_email

Alter complete in: 422 seconds
  AKA: 7 minutes and 2 seconds

Change in sizes of tables from start to finish:
4,5c4,5
< 12    /srv/sqldata/civicrm/civicrm_email.frm
< 5529608       /srv/sqldata/civicrm/civicrm_email.ibd
---
> 8     /srv/sqldata/civicrm/civicrm_email.frm
> 3547136       /srv/sqldata/civicrm/civicrm_email.ibd
7c7
< 5529636       total
---
> 3547160       total

# compact_innodb_table -d civicrm -t civicrm_address

Alter complete in: 1177 seconds
  AKA: 19 minutes and 37 seconds

Change in sizes of tables from start to finish:
11c11
< 11866128      /srv/sqldata/civicrm/civicrm_address.ibd
---
> 7548932       /srv/sqldata/civicrm/civicrm_address.ibd
13c13
< 11866288      total
---
> 7549092       total

# compact_innodb_table -d civicrm -t civicrm_activity_contact

Alter complete in: 1778 seconds
  AKA: 29 minutes and 38 seconds

Change in sizes of tables from start to finish:
4,5c4,5
< 12    /srv/sqldata/civicrm/civicrm_activity_contact.frm
< 24817736      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
---
> 4     /srv/sqldata/civicrm/civicrm_activity_contact.frm
> 15802372      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
7c7
< 24817764      total
---
> 15802392      total

# compact_innodb_table -d civicrm -t civicrm_contact

Alter complete in: 2608 seconds
  AKA: 43 minutes and 28 seconds

Change in sizes of tables from start to finish:
6c6
< 24686596      /srv/sqldata/civicrm/civicrm_contact.ibd
---
> 18960388      /srv/sqldata/civicrm/civicrm_contact.ibd
26c26
< 24687104      total
---
> 18960896      total

# compact_innodb_table -d civicrm -t civicrm_activity

Alter complete in: 5296 seconds
  AKA: 88 minutes and 16 seconds

Change in sizes of tables from start to finish:
11c11
< 16564228      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
---
> 16580612      /srv/sqldata/civicrm/civicrm_activity_contact.ibd
14c14
< 109822268     /srv/sqldata/civicrm/civicrm_activity.ibd
---
> 103116952     /srv/sqldata/civicrm/civicrm_activity.ibd
16c16
< 126386560     total
---
> 119697628     total

# compact_innodb_table -d civicrm -t civicrm_mailing_provider_data

Alter complete in: 9465 seconds
  AKA: 157 minutes and 45 seconds

Change in sizes of tables from start to finish:
2,3c2,3
< 101359792     /srv/sqldata/civicrm/civicrm_mailing_provider_data.ibd
< 101359800     total
---
> 59125884      /srv/sqldata/civicrm/civicrm_mailing_provider_data.ibd
> 59125892      total
Dwisehaupt updated the task description. (Show Details)May 26 2020, 10:46 PM

Minor update to the script that was used with this compacting so that it uses SSL connections by default.

[frack::puppet] 1fe1e45b Specify default value for ssl option
Dwisehaupt closed this task as Resolved.May 27 2020, 5:54 PM
Dwisehaupt moved this task from In Progress to Done on the fundraising-tech-ops board.