Page MenuHomePhabricator

Remove partitions from revision table
Open, MediumPublic

Description

Following the discussions on T223151: Review special replica partitioning of certain tables by `xx_user` and given all the tests we have done with a non partitioned slave on s1 (enwiki) and s5, they have all been successful and we've not noticed any regression on serving queries for the following groups:

contributions
watchlist
recentchanges
logpager
recentchangeslinked

I would like to move forward and start removing the partitions from the revision table on those wikis where it is indeed partitioned which are:
s1: enwiki
s2: bgwiki cswiki enwikiquote enwiktionary fiwiki itwiki nlwiki nowiki plwiki ptwiki svwiki thwiki twiki zhwiki
s4: commonswiki
s5: dewiki
s6: frwiki jawiki ruwiki
s7: arwiki cawiki eswiki fawiki hewiki huwiki kowiki metawiki rowiki ukwiki viwiki
s8: wikidatawiki

I think we should start very slowly and keep monitoring things.
My proposal would be to start with dewiki, as it is big enough to see regressions (again, nothing has showed up during the tests done at T223151) and also it would only affect one wiki.

The PK for those slaves would need to change from:

PRIMARY KEY (`rev_id`,`rev_user`),

To:

PRIMARY KEY (`rev_id`),

The indexes would also need to change from:

KEY `rev_timestamp` (`rev_timestamp`,`rev_id`),
 KEY `page_timestamp` (`rev_page`,`rev_timestamp`,`rev_id`),
 KEY `user_timestamp` (`rev_user`,`rev_timestamp`,`rev_id`),
 KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`,`rev_id`),
 KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`,`rev_id`),
 KEY `rev_page_id` (`rev_page`,`rev_id`)

To:

KEY `rev_timestamp` (`rev_timestamp`),
KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`),
KEY `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`),
KEY `rev_page_id` (`rev_page`,`rev_id`)

The alter to run would be:

alter table revision remove partitioning;

alter table revision drop primary key, add primary key (rev_id),  drop index if exists rev_timestamp, drop index if exists page_timestamp, drop index if exists user_timestamp, drop index if exists usertext_timestamp, drop index if exists page_user_timestamp, drop index if exists rev_page_id;

alter table revision add index if not exists `rev_timestamp` (`rev_timestamp`), add index if not exists `page_timestamp` (`rev_page`,`rev_timestamp`), add index if not exists `user_timestamp` (`rev_user`,`rev_timestamp`), add index if not exists `usertext_timestamp` (`rev_user_text`,`rev_timestamp`), add index if not exists `page_user_timestamp` (`rev_page`,`rev_user`,`rev_timestamp`), add index if not exists `rev_page_id` (`rev_page`,`rev_id`);

Progress:

s1: enwiki

  •  db1099:3311
  •  db1105:3311
  •  db2085:3311
  •  db2088:3311

s2: bgwiki cswiki enwikiquote enwiktionary fiwiki itwiki nlwiki nowiki plwiki ptwiki svwiki thwiki trwiki zhwiki

  •  db1103:3312
  •  db1105:3312
  •  db2088:3312
  •  db2091:3312

s4: commonswiki

  •  db1097:3314
  •  db1103:3314
  •  db2084:3314
  •  db2091:3314

s5: dewiki

  •  db1096:3315
  •  db1097:3315
  •  db2084:3315
  •  db2089:3315

s6: frwiki jawiki ruwiki

  •  db1096:3316
  •  db1098:3316
  •  db2087:3316
  •  db2089:3316

s7: arwiki cawiki eswiki fawiki hewiki huwiki kowiki metawiki rowiki ukwiki viwiki

  •  db1098:3317
  •  db1101:3317
  •  db2086:3317
  •  db2087:3317

s8: wikidatawiki

  •  db1099:3318
  •  db1101:3318
  •  db2085:3318
  •  db2086:3318
  • Comment partitioning on *-pager.sql files

Details

Related Gerrit Patches:
operations/puppet : productiondb1097: Disable notifications
operations/puppet : productiondb1103: Enable notifications
operations/puppet : productiondb1103: Disable notifications

Related Objects

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
Restricted Application added subscribers: Strainu, jeblad, Cosine02 and 3 others. · View Herald TranscriptNov 29 2019, 7:10 AM
Marostegui triaged this task as Medium priority.Nov 29 2019, 7:10 AM
Marostegui moved this task from Triage to Next on the DBA board.
Marostegui updated the task description. (Show Details)Nov 29 2019, 8:34 AM

I have put the ALTERs in separate transactions, because I am finding something weird when testing it on my lab. I have reported it to MariaDB just in case: https://jira.mariadb.org/browse/MDEV-21176

Strainu removed a subscriber: Strainu.Nov 29 2019, 2:19 PM
Marostegui updated the task description. (Show Details)Dec 3 2019, 10:31 AM
Marostegui moved this task from Next to In progress on the DBA board.

Mentioned in SAL (#wikimedia-operations) [2020-01-02T06:26:51Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db2087:3316 - T239453', diff saved to https://phabricator.wikimedia.org/P10020 and previous config saved to /var/cache/conftool/dbconfig/20200102-062650-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-02T06:29:14Z] <marostegui> Remove revision partitions from db2087:3316 T239453

Marostegui updated the task description. (Show Details)Fri, Jan 3, 6:19 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-03T06:21:50Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repool db2087:3316 - T239453', diff saved to https://phabricator.wikimedia.org/P10028 and previous config saved to /var/cache/conftool/dbconfig/20200103-062148-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-03T06:22:43Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db2089:3316 - T239453', diff saved to https://phabricator.wikimedia.org/P10029 and previous config saved to /var/cache/conftool/dbconfig/20200103-062242-marostegui.json

Marostegui updated the task description. (Show Details)Tue, Jan 7, 6:48 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-07T06:48:47Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repool db2089:3316 - T239453', diff saved to https://phabricator.wikimedia.org/P10048 and previous config saved to /var/cache/conftool/dbconfig/20200107-064846-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-07T07:08:51Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1098:3316 - T239453', diff saved to https://phabricator.wikimedia.org/P10049 and previous config saved to /var/cache/conftool/dbconfig/20200107-070850-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-07T07:12:59Z] <marostegui> Remove partitions from revision table on s6: db1098 T239453

Mentioned in SAL (#wikimedia-operations) [2020-01-07T07:15:36Z] <marostegui> Remove partitions from s5: db2084:3315 T239453

Marostegui updated the task description. (Show Details)Tue, Jan 7, 5:22 PM

Mentioned in SAL (#wikimedia-operations) [2020-01-07T17:23:52Z] <marostegui> Remove partitions from dewiki.revision from db2089:3315 T239453

Marostegui updated the task description. (Show Details)Wed, Jan 8, 6:34 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-08T06:35:52Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repool db1098:3316 - T239453', diff saved to https://phabricator.wikimedia.org/P10077 and previous config saved to /var/cache/conftool/dbconfig/20200108-063550-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-08T06:41:45Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1096:3316 - T239453', diff saved to https://phabricator.wikimedia.org/P10078 and previous config saved to /var/cache/conftool/dbconfig/20200108-064144-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-08T06:42:27Z] <marostegui> Remove partitions from revision table on s6 for db1096:3316 - T239453

Mentioned in SAL (#wikimedia-operations) [2020-01-08T07:07:34Z] <marostegui> Remove partitions from dewiki.revision on db1097:3315 T239453

Marostegui updated the task description. (Show Details)Wed, Jan 8, 6:42 PM

Mentioned in SAL (#wikimedia-operations) [2020-01-08T18:46:08Z] <marostegui> Remove partitions from dewiki.revision on db1096:3315 T239453

Marostegui updated the task description. (Show Details)Thu, Jan 9, 6:18 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-09T06:21:59Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repool db1096:3315 db1096:3316 T239453', diff saved to https://phabricator.wikimedia.org/P10092 and previous config saved to /var/cache/conftool/dbconfig/20200109-062157-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-09T06:26:10Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db2088:3312 T239453', diff saved to https://phabricator.wikimedia.org/P10093 and previous config saved to /var/cache/conftool/dbconfig/20200109-062608-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-09T06:27:02Z] <marostegui> Remove revision partitions from db2088:3312 T239453

Marostegui updated the task description. (Show Details)Fri, Jan 10, 8:02 AM
Marostegui updated the task description. (Show Details)Fri, Jan 10, 8:57 AM
Marostegui added a parent task: Restricted Task.Fri, Jan 10, 3:04 PM
Marostegui updated the task description. (Show Details)Mon, Jan 13, 5:54 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-13T05:58:12Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1105:3312 - T239453', diff saved to https://phabricator.wikimedia.org/P10122 and previous config saved to /var/cache/conftool/dbconfig/20200113-055811-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-13T05:58:40Z] <marostegui> Remove partitions from db1105:3312 - T239453

Marostegui updated the task description. (Show Details)Tue, Jan 14, 5:58 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-14T06:01:17Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1103:3312 - T239453', diff saved to https://phabricator.wikimedia.org/P10141 and previous config saved to /var/cache/conftool/dbconfig/20200114-060116-marostegui.json

Marostegui updated the task description. (Show Details)Wed, Jan 15, 5:58 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-15T06:10:53Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repool db1103:3312 - T239453', diff saved to https://phabricator.wikimedia.org/P10150 and previous config saved to /var/cache/conftool/dbconfig/20200115-061052-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-15T06:16:57Z] <marostegui> Remove revision partitions from db2088:3311 - T239453

Marostegui updated the task description. (Show Details)Sat, Jan 18, 7:03 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-18T07:05:43Z] <marostegui> Remove partitions from enwiki.revision on db2085 T239453

Marostegui updated the task description. (Show Details)Sat, Jan 18, 7:08 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-20T07:15:14Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db2084 T239453', diff saved to https://phabricator.wikimedia.org/P10215 and previous config saved to /var/cache/conftool/dbconfig/20200120-071513-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-20T07:15:17Z] <marostegui> Remove partitions from revision on db2084:3314 T239453

Marostegui updated the task description. (Show Details)Tue, Jan 21, 5:49 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-21T05:50:24Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repool db2084:3314 T239453', diff saved to https://phabricator.wikimedia.org/P10230 and previous config saved to /var/cache/conftool/dbconfig/20200121-055023-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-21T05:51:51Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db2091:3314 T239453', diff saved to https://phabricator.wikimedia.org/P10231 and previous config saved to /var/cache/conftool/dbconfig/20200121-055149-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-21T05:52:18Z] <marostegui> Remove partitions from db2091:3314 - T239453

Marostegui updated the task description. (Show Details)Wed, Jan 22, 6:13 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-22T06:14:30Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repool db2091:3314 T239453', diff saved to https://phabricator.wikimedia.org/P10241 and previous config saved to /var/cache/conftool/dbconfig/20200122-061429-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-22T06:15:23Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1103:3314 T239453', diff saved to https://phabricator.wikimedia.org/P10242 and previous config saved to /var/cache/conftool/dbconfig/20200122-061522-marostegui.json

Change 566415 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] db1103: Disable notifications

https://gerrit.wikimedia.org/r/566415

Mentioned in SAL (#wikimedia-operations) [2020-01-22T06:16:19Z] <marostegui> Remove partitions from db1103:3314 - T239453

Change 566415 merged by Marostegui:
[operations/puppet@production] db1103: Disable notifications

https://gerrit.wikimedia.org/r/566415

Change 566649 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] db1103: Enable notifications

https://gerrit.wikimedia.org/r/566649

Marostegui updated the task description. (Show Details)Thu, Jan 23, 5:57 AM

Change 566649 merged by Marostegui:
[operations/puppet@production] db1103: Enable notifications

https://gerrit.wikimedia.org/r/566649

Mentioned in SAL (#wikimedia-operations) [2020-01-23T05:59:21Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repool db1103:3314 - T239453', diff saved to https://phabricator.wikimedia.org/P10247 and previous config saved to /var/cache/conftool/dbconfig/20200123-055919-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-23T06:03:10Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1097:3314 - T239453', diff saved to https://phabricator.wikimedia.org/P10248 and previous config saved to /var/cache/conftool/dbconfig/20200123-060308-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-23T06:05:10Z] <marostegui> Remove partitions from db1097:3314 - T239453

Change 566650 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] db1097: Disable notifications

https://gerrit.wikimedia.org/r/566650

Change 566650 merged by Marostegui:
[operations/puppet@production] db1097: Disable notifications

https://gerrit.wikimedia.org/r/566650