Page MenuHomePhabricator

Remove partitions from revision table
Closed, ResolvedPublic

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

Details

Related Gerrit Patches:
operations/puppet : productiondb1101: Disable notifications
operations/software : mastersX-pager.sql: Remove partitions from revision tables.
operations/puppet : productiondb1099: Disable notifications
operations/puppet : productiondb2085: Disable notifications
operations/puppet : productiondb1101: Enable notifications
operations/puppet : productiondb1105: Enable notifications
operations/puppet : productiondb1101: Disable notifications
operations/puppet : productiondb1098: Disable notifications
operations/puppet : productiondb2086: Enable notifications
operations/puppet : productiondb1105,db2086: Disable notifications
operations/puppet : productiondb2087: Disable notifications
operations/puppet : productiondb1097: Enable notifications
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
Marostegui updated the task description. (Show Details)Feb 6 2020, 6:50 AM

Mentioned in SAL (#wikimedia-operations) [2020-02-06T06:52:39Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repool db1098:3317 - T239453', diff saved to https://phabricator.wikimedia.org/P10318 and previous config saved to /var/cache/conftool/dbconfig/20200206-065238-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-06T06:59:07Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1101:3317 - T239453', diff saved to https://phabricator.wikimedia.org/P10319 and previous config saved to /var/cache/conftool/dbconfig/20200206-065906-marostegui.json

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

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

Change 570525 merged by Marostegui:
[operations/puppet@production] db1101: Disable notifications

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

Marostegui updated the task description. (Show Details)Feb 6 2020, 3:41 PM

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

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

Change 570789 merged by Marostegui:
[operations/puppet@production] db1105: Enable notifications

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

Mentioned in SAL (#wikimedia-operations) [2020-02-07T06:20:43Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Slowly repool db1105:3311 T239453', diff saved to https://phabricator.wikimedia.org/P10328 and previous config saved to /var/cache/conftool/dbconfig/20200207-062043-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-07T06:23:45Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Slowly repool db1105:3311 T239453', diff saved to https://phabricator.wikimedia.org/P10329 and previous config saved to /var/cache/conftool/dbconfig/20200207-062345-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-07T06:27:32Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Slowly repool db1105:3311 T239453', diff saved to https://phabricator.wikimedia.org/P10331 and previous config saved to /var/cache/conftool/dbconfig/20200207-062731-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-07T06:34:02Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Fully repool db1105:3311 T239453', diff saved to https://phabricator.wikimedia.org/P10332 and previous config saved to /var/cache/conftool/dbconfig/20200207-063402-marostegui.json

Marostegui updated the task description. (Show Details)Feb 7 2020, 6:38 AM

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

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

Change 570793 merged by Marostegui:
[operations/puppet@production] db1101: Enable notifications

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

Mentioned in SAL (#wikimedia-operations) [2020-02-07T07:31:30Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Fully repool db1101:3317 T239453', diff saved to https://phabricator.wikimedia.org/P10335 and previous config saved to /var/cache/conftool/dbconfig/20200207-073130-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-07T07:42:58Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Slowly repool db1101:3317 T239453', diff saved to https://phabricator.wikimedia.org/P10336 and previous config saved to /var/cache/conftool/dbconfig/20200207-074258-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-07T07:44:08Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db2085:3318 T239453', diff saved to https://phabricator.wikimedia.org/P10337 and previous config saved to /var/cache/conftool/dbconfig/20200207-074407-marostegui.json

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

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

Change 570810 merged by Marostegui:
[operations/puppet@production] db2085: Disable notifications

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

Mentioned in SAL (#wikimedia-operations) [2020-02-07T07:48:31Z] <marostegui> Remove revision partitions from db2085:3318 T239453

Mentioned in SAL (#wikimedia-operations) [2020-02-07T07:52:35Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Slowly repool db1101:3317 T239453', diff saved to https://phabricator.wikimedia.org/P10339 and previous config saved to /var/cache/conftool/dbconfig/20200207-075234-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-07T08:44:47Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Fully repool db1101:3317 T239453', diff saved to https://phabricator.wikimedia.org/P10341 and previous config saved to /var/cache/conftool/dbconfig/20200207-084447-marostegui.json

Marostegui updated the task description. (Show Details)Feb 10 2020, 6:08 AM

Mentioned in SAL (#wikimedia-operations) [2020-02-10T06:18:22Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool repool db1099:3311 T239453', diff saved to https://phabricator.wikimedia.org/P10356 and previous config saved to /var/cache/conftool/dbconfig/20200210-061822-marostegui.json

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

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

Mentioned in SAL (#wikimedia-operations) [2020-02-10T06:21:14Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1099:3318 T239453', diff saved to https://phabricator.wikimedia.org/P10357 and previous config saved to /var/cache/conftool/dbconfig/20200210-062112-marostegui.json

Change 571115 merged by Marostegui:
[operations/puppet@production] db1099: Disable notifications

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

Mentioned in SAL (#wikimedia-operations) [2020-02-10T06:23:08Z] <marostegui> Remove partitions from db1099:3311, db1099:3318 T239453

Mentioned in SAL (#wikimedia-operations) [2020-02-11T07:16:40Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repool db2085:3318 T239453', diff saved to https://phabricator.wikimedia.org/P10372 and previous config saved to /var/cache/conftool/dbconfig/20200211-071639-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-11T07:19:37Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db2086:3318 T239453', diff saved to https://phabricator.wikimedia.org/P10373 and previous config saved to /var/cache/conftool/dbconfig/20200211-071936-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-11T07:21:24Z] <marostegui> Remove partitions from db2086:3318 - T239453

Marostegui updated the task description. (Show Details)Feb 12 2020, 1:20 PM
Marostegui updated the task description. (Show Details)Feb 13 2020, 6:11 AM

Mentioned in SAL (#wikimedia-operations) [2020-02-13T06:12:19Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Slowly repool db1099:3318, db1099:3311 T239453', diff saved to https://phabricator.wikimedia.org/P10396 and previous config saved to /var/cache/conftool/dbconfig/20200213-061219-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-13T06:21:48Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Slowly repool db1099:3318, db1099:3311 T239453', diff saved to https://phabricator.wikimedia.org/P10397 and previous config saved to /var/cache/conftool/dbconfig/20200213-062148-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-13T06:26:43Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Slowly repool db1099:3318, db1099:3311 T239453', diff saved to https://phabricator.wikimedia.org/P10398 and previous config saved to /var/cache/conftool/dbconfig/20200213-062642-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-13T06:32:07Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Fully repool db1099:3318, db1099:3311 T239453', diff saved to https://phabricator.wikimedia.org/P10399 and previous config saved to /var/cache/conftool/dbconfig/20200213-063207-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-13T06:33:35Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Pool db1099:3318 into vslow for s8 T239453', diff saved to https://phabricator.wikimedia.org/P10400 and previous config saved to /var/cache/conftool/dbconfig/20200213-063334-marostegui.json

Marostegui updated the task description. (Show Details)Feb 14 2020, 5:37 AM

Mentioned in SAL (#wikimedia-operations) [2020-02-14T15:54:43Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repool db2086:3318 T239453', diff saved to https://phabricator.wikimedia.org/P10414 and previous config saved to /var/cache/conftool/dbconfig/20200214-155443-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-20T06:09:15Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1099:3318 to remove revision partitions - T239453', diff saved to https://phabricator.wikimedia.org/P10463 and previous config saved to /var/cache/conftool/dbconfig/20200220-060914-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-20T06:10:20Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repool db1099:3318 this host already had the partitions removed - T239453', diff saved to https://phabricator.wikimedia.org/P10464 and previous config saved to /var/cache/conftool/dbconfig/20200220-061019-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-20T06:12:14Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1101:3318 to remove revision partitions - T239453', diff saved to https://phabricator.wikimedia.org/P10465 and previous config saved to /var/cache/conftool/dbconfig/20200220-061213-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-20T06:12:40Z] <marostegui> Remove partitions from db1101:3318 - T239453

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

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

Change 573437 merged by Marostegui:
[operations/puppet@production] db1101: Disable notifications

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

Marostegui updated the task description. (Show Details)Feb 24 2020, 6:00 AM

Mentioned in SAL (#wikimedia-operations) [2020-02-24T06:01:18Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Slowly repool db1101:3318 after removing partitions - T239453', diff saved to https://phabricator.wikimedia.org/P10483 and previous config saved to /var/cache/conftool/dbconfig/20200224-060118-marostegui.json

Change 574271 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/software@master] sX-pager.sql: Remove partitions from revision tables.

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

Change 574271 merged by Marostegui:
[operations/software@master] sX-pager.sql: Remove partitions from revision tables.

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

Marostegui updated the task description. (Show Details)Feb 24 2020, 6:22 AM

Mentioned in SAL (#wikimedia-operations) [2020-02-24T06:22:26Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Slowly repool db1101:3318 after removing partitions - T239453', diff saved to https://phabricator.wikimedia.org/P10484 and previous config saved to /var/cache/conftool/dbconfig/20200224-062226-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-24T06:32:58Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Slowly repool db1101:3318 after removing partitions - T239453', diff saved to https://phabricator.wikimedia.org/P10485 and previous config saved to /var/cache/conftool/dbconfig/20200224-063258-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-02-24T06:40:44Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Fully repool db1101:3318 after removing partitions - T239453', diff saved to https://phabricator.wikimedia.org/P10486 and previous config saved to /var/cache/conftool/dbconfig/20200224-064044-marostegui.json

Marostegui closed this task as Resolved.Feb 24 2020, 6:40 AM

This is all done! Partitions are no longer in production.
This open new possibilities, like removing entirely the logpager, watchlist, contributions,recentchanges etc sections and let the traffic balance between all the hosts. I will create a new task to discuss those possibilities.
For now let's leave them how they are at the moment.

jcrespo added a subscriber: jcrespo.

Do we have a new task already to track/research logging partition removal?

That was already done :-) T233625