Page MenuHomePhabricator

Compress new Wikibase tables
Open, NormalPublic

Description

This and maybe other newly created tables are in innodb uncompressed format, and probably will benefit a lot from it:

| wbt_item_terms           | InnoDB |      10 | Compact    |   79850814 |             38 |   3068133376 |               
| wbt_property_terms       | InnoDB |      10 | Compact    |     261980 |             34 |      8929280 |               
| wbt_term_in_lang         | InnoDB |      10 | Compact    |   45801912 |             35 |   1633681408 |               
| wbt_text                 | InnoDB |      10 | Compact    |   17176409 |             60 |   1044381696 |               
| wbt_text_in_lang         | InnoDB |      10 | Compact    |   45872021 |             36 |   1680867328 |               
| wbt_type                 | InnoDB |      10 | Compact    |          2 |           8192 |        16384 |

Progress:

  • db1109
  • db1087
  • db1092
  • db1099:3318
  • db1101:3318
  • db1104
  • db1116:3318
  • db1124:3318
  • db1126
  • db2079
  • db2080
  • db2081
  • db2082
  • db2083
  • db2085:3318
  • db2086:3318
  • db2094:3318
  • db2100:3318
  • dbstore1005:3318
  • labsdb1009
  • labsdb1010
  • labsdb1011
  • labsdb1012

Event Timeline

jcrespo created this task.Sep 10 2019, 9:18 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptSep 10 2019, 9:18 AM
jcrespo claimed this task.Sep 10 2019, 9:34 AM
jcrespo moved this task from Triage to In progress on the DBA board.

I will do a test run on db2100:3318, to see how much time it may take currently.

jcrespo triaged this task as Normal priority.Sep 10 2019, 9:34 AM
jcrespo updated the task description. (Show Details)Sep 10 2019, 10:39 AM

It took one hour, but the compression was worth it:

-rw-rw---- 1 mysql mysql 8.5G Sep 10 09:38 wbt_item_terms.ibd
-rw-rw---- 1 mysql mysql  36M Sep 10 08:13 wbt_property_terms.ibd
-rw-rw---- 1 mysql mysql 4.2G Sep 10 09:37 wbt_term_in_lang.ibd
-rw-rw---- 1 mysql mysql 2.1G Sep 10 09:38 wbt_text.ibd
-rw-rw---- 1 mysql mysql 4.2G Sep 10 09:37 wbt_text_in_lang.ibd
-rw-rw---- 1 mysql mysql 112K Jun 26 13:25 wbt_type.ibd

-rw-rw---- 1 mysql mysql 3.4G Sep 10 10:34 wbt_item_terms.ibd
-rw-rw---- 1 mysql mysql  15M Sep 10 10:20 wbt_property_terms.ibd
-rw-rw---- 1 mysql mysql 1.7G Sep 10 10:32 wbt_term_in_lang.ibd
-rw-rw---- 1 mysql mysql 952M Sep 10 10:34 wbt_text.ibd
-rw-rw---- 1 mysql mysql 1.8G Sep 10 10:39 wbt_text_in_lang.ibd
-rw-rw---- 1 mysql mysql  64K Sep 10 10:30 wbt_type.ibd

Compressing db1116:s8, however that may take a while as it has other uncompressed tables that are also being converted.

jcrespo updated the task description. (Show Details)Sep 12 2019, 8:20 AM

Mentioned in SAL (#wikimedia-operations) [2019-09-12T09:42:15Z] <jynus> compressing tables on labsdb1012 T232446

s8 backups on eqiad had shrinked considerably:

mysql> select name, total_size FROM backups WHERE type = 'snapshot' and section = 's8' and status = 'finished' and source like '%eqiad%' ORDER BY id desc;
+----------------------------------+---------------+
| name                             | total_size    |
+----------------------------------+---------------+
| snapshot.s8.2019-09-12--08-29-04 | 1199474663181 |
| snapshot.s8.2019-09-08--19-00-01 | 1650038824688 |
| snapshot.s8.2019-09-05--19-00-01 | 1639380508400 |
| snapshot.s8.2019-09-03--19-00-01 | 1627220696816 |
| snapshot.s8.2019-09-01--19-00-02 | 1625729689716 |
jcrespo updated the task description. (Show Details)Sep 12 2019, 2:12 PM

Mentioned in SAL (#wikimedia-operations) [2019-10-11T06:08:15Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db2085:3318 for compression - T232446', diff saved to https://phabricator.wikimedia.org/P9311 and previous config saved to /var/cache/conftool/dbconfig/20191011-060814-marostegui.json

I am going to start compressing rc slaves, as there are many tables there that need compression, due to all the recent schema changes., I have seen that more special slaves across different sections need re-compression. Given that we have this ticket already, I will start with hosts I am currently working on
Example db2085:3318

root@db2085:~# df -hT /srv
Filesystem            Type  Size  Used Avail Use% Mounted on
/dev/mapper/tank-data xfs   3.6T  2.9T  777G  79% /srv
+--------------------------------------+
| table_name                           |
+--------------------------------------+
| wikidatawiki.actor                   |
| wikidatawiki.archive                 |
| wikidatawiki.change_tag              |
| wikidatawiki.change_tag_def          |
| wikidatawiki.comment                 |
| wikidatawiki.content                 |
| wikidatawiki.content_models          |
| wikidatawiki.entityschema_id_counter |
| wikidatawiki.ipblocks_restrictions   |
| wikidatawiki.revision_actor_temp     |
| wikidatawiki.revision_comment_temp   |
| wikidatawiki.slot_roles              |
| wikidatawiki.slots                   |
| wikidatawiki.translate_groupstats    |
| wikidatawiki.wb_items_per_site       |
| wikidatawiki.wbt_item_terms          |
| wikidatawiki.wbt_property_terms      |
| wikidatawiki.wbt_term_in_lang        |
| wikidatawiki.wbt_text                |
| wikidatawiki.wbt_text_in_lang        |
| wikidatawiki.wbt_type                |
+--------------------------------------+

Mentioned in SAL (#wikimedia-operations) [2019-10-11T06:13:20Z] <marostegui> Compress tables on db2085:3318 - T232446

Marostegui updated the task description. (Show Details)Fri, Oct 11, 6:13 AM
Marostegui updated the task description. (Show Details)Mon, Oct 14, 8:00 AM

Mentioned in SAL (#wikimedia-operations) [2019-10-15T06:44:20Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db2086:3318 T232446', diff saved to https://phabricator.wikimedia.org/P9344 and previous config saved to /var/cache/conftool/dbconfig/20191015-064419-marostegui.json

Marostegui updated the task description. (Show Details)Thu, Oct 17, 12:48 PM
Reedy renamed this task from Compress new Wikibase tables to Compress new Wikibase tables.Thu, Oct 17, 12:56 PM
jcrespo removed jcrespo as the assignee of this task.Mon, Oct 21, 10:16 AM