Page MenuHomePhabricator
Create Task
Maniphest T174509

Drop now redundant indexes from pagelinks and templatelinks
Closed, ResolvedPublic
Actions

Description

So... After 1.30.0-wmf.17 is everywhere, our DBA can drop the other two primary indexes that were blocked on a code change

ALTER TABLE /*_*/pagelinks DROP INDEX /*i*/pl_from;
ALTER TABLE /*_*/templatelinks DROP INDEX /*i*/tl_from;

Index removal:

  • s1
  • s2
  • s3
  • s4
  • s5
  • s6
  • s7

Table defragmentation:

  • s1
  • db2016.codfw.wmnet
  • db2034.codfw.wmnet
  • db2042.codfw.wmnet
  • db2055.codfw.wmnet
  • db2062.codfw.wmnet
  • db2069.codfw.wmnet
  • db2070.codfw.wmnet
  • db2071.codfw.wmnet
  • db2072.codfw.wmnet
  • dbstore2002.codfw.wmnet
  • db2048.codfw.wmnet
  • labsdb1003.eqiad.wmnet (will not alter, see: T174509#3668778)
  • labsdb1001.eqiad.wmnet (will not alter, see: T174509#3668778)
  • labsdb1009.eqiad.wmnet
  • labsdb1010.eqiad.wmnet
  • labsdb1011.eqiad.wmnet
  • db1095.eqiad.wmnet
  • dbstore1001.eqiad.wmnet (not altered)
  • dbstore1002.eqiad.wmnet (not altered)
  • db1080.eqiad.wmnet
  • db1083.eqiad.wmnet
  • db1089.eqiad.wmnet
  • db1073.eqiad.wmnet
  • db1066.eqiad.wmnet
  • db1065.eqiad.wmnet
  • db1055.eqiad.wmnet
  • db1051.eqiad.wmnet
  • db1047.eqiad.wmnet (will not alter, at least until it gets replaced by a more powerful host)
  • db1067.eqiad.wmnet
  • db1052.eqiad.wmnet (will not be done: T174509#3743543)
  • s2
  • dbstore2002.codfw.wmnet
  • dbstore2001.codfw.wmnet
  • db2035.codfw.wmnet
  • db2041.codfw.wmnet
  • db2049.codfw.wmnet
  • db2056.codfw.wmnet
  • db2063.codfw.wmnet
  • db2064.codfw.wmnet
  • db2017.codfw.wmnet
  • labsdb1009.eqiad.wmnet
  • labsdb1010.eqiad.wmnet
  • labsdb1011.eqiad.wmnet
  • db1102.eqiad.wmnet
  • labsdb1003.eqiad.wmnet (will not alter, see: T174509#3668778)
  • labsdb1001.eqiad.wmnet (will not alter, see: T174509#3668778)
  • dbstore1002.eqiad.wmnet (not altered)
  • dbstore1001.eqiad.wmnet (not altered)
  • db1021.eqiad.wmnet
  • db1047.eqiad.wmnet (not altered)
  • db1060.eqiad.wmnet
  • db1074.eqiad.wmnet
  • db1076.eqiad.wmnet
  • db1090.eqiad.wmnet
  • db1101.eqiad.wmnet
  • db1054.eqiad.wmnet
  • s3 (probably not worth it)
  • s4
  • dbstore2002.codfw.wmnet
  • db2073.codfw.wmnet
  • db2065.codfw.wmnet
  • db2058.codfw.wmnet
  • db2044.codfw.wmnet
  • db2037.codfw.wmnet
  • db2019.codfw.wmnet
  • db2051.codfw.wmnet
  • labsdb1003.eqiad.wmnet (will not alter, see: T174509#3668778)
  • labsdb1001.eqiad.wmnet (will not alter, see: T174509#3668778)
  • db1102.eqiad.wmnet
  • labsdb1009.eqiad.wmnet
  • labsdb1010.eqiad.wmnet
  • labsdb1011.eqiad.wmnet
  • dbstore1001.eqiad.wmnet (not altered)
  • dbstore1002.eqiad.wmnet (not altered)
  • db1053.eqiad.wmnet
  • db1056.eqiad.wmnet
  • db1064.eqiad.wmnet
  • db1081.eqiad.wmnet
  • db1084.eqiad.wmnet
  • db1091.eqiad.wmnet
  • db1097.eqiad.wmnet
  • db1068.eqiad.wmnet (will not be done: T174509#3743543)
  • s5
  • dbstore2001.codfw.wmnet
  • db2079.codfw.wmnet
  • db2075.codfw.wmnet
  • db2038.codfw.wmnet
  • db2045.codfw.wmnet
  • db2052.codfw.wmnet
  • db2059.codfw.wmnet
  • db2066.codfw.wmnet
  • db2023.codfw.wmnet
  • labsdb1003.eqiad.wmnet (will not alter, see: T174509#3668778)
  • labsdb1001.eqiad.wmnet (will not alter, see: T174509#3668778)
  • labsdb1009.eqiad.wmnet
  • labsdb1010.eqiad.wmnet
  • labsdb1011.eqiad.wmnet
  • db1095.eqiad.wmnet
  • dbstore1001.eqiad.wmnet (not altered)
  • dbstore1002.eqiad.wmnet (not altered)
  • db1070.eqiad.wmnet
  • db1071.eqiad.wmnet
  • db1082.eqiad.wmnet
  • db1087.eqiad.wmnet
  • db1092.eqiad.wmnet
  • db1096.eqiad.wmnet
  • db1099.eqiad.wmnet
  • db1100.eqiad.wmnet
  • db1104.eqiad.wmnet
  • db1105.eqiad.wmnet
  • db1106.eqiad.wmnet
  • db1063.eqiad.wmnet
  • s6
  • dbstore2001.codfw.wmnet
  • db2076.codfw.wmnet
  • db2039.codfw.wmnet
  • db2046.codfw.wmnet
  • db2053.codfw.wmnet
  • db2060.codfw.wmnet
  • db2067.codfw.wmnet
  • db2028.codfw.wmnet
  • labsdb1009.eqiad.wmnet
  • labsdb1010.eqiad.wmnet
  • labsdb1011.eqiad.wmnet
  • db1102.eqiad.wmnet
  • labsdb1003.eqiad.wmnet (will not alter, see: T174509#3668778)
  • labsdb1001.eqiad.wmnet (will not alter, see: T174509#3668778)
  • dbstore1001.eqiad.wmnet (not altered)
  • dbstore1002.eqiad.wmnet (not altered)
  • db1030.eqiad.wmnet
  • db1050.eqiad.wmnet
  • db1085.eqiad.wmnet
  • db1088.eqiad.wmnet
  • db1093.eqiad.wmnet
  • db1098.eqiad.wmnet
  • db1061.eqiad.wmnet
  • s7
  • dbstore2001.codfw.wmnet
  • db2077.codfw.wmnet
  • db2040.codfw.wmnet
  • db2047.codfw.wmnet
  • db2054.codfw.wmnet
  • db2061.codfw.wmnet
  • db2068.codfw.wmnet
  • db2029.codfw.wmnet
  • labsdb1003.eqiad.wmnet (will not alter, see: T174509#3668778)
  • labsdb1001.eqiad.wmnet (will not alter, see: T174509#3668778)
  • labsdb1009.eqiad.wmnet
  • labsdb1010.eqiad.wmnet
  • labsdb1011.eqiad.wmnet
  • db1102.eqiad.wmnet
  • dbstore1001.eqiad.wmnet (not altered)
  • dbstore1002.eqiad.wmnet (not altered)
  • db1034.eqiad.wmnet
  • db1039.eqiad.wmnet
  • db1069.eqiad.wmnet
  • db1079.eqiad.wmnet
  • db1086.eqiad.wmnet
  • db1094.eqiad.wmnet
  • db1062.eqiad.wmnet

Details

SubjectRepoBranchLines +/-
db-eqiad.php: Depool db1060operations/mediawiki-configmaster+2 -2
db-eqiad.php: Depool db1101operations/mediawiki-configmaster+7 -7
db-eqiad.php: Depool db1065operations/mediawiki-configmaster+4 -4
db-eqiad.php: Depool db1051operations/mediawiki-configmaster+6 -6
db-eqiad.php: Depool db1055operations/mediawiki-configmaster+6 -6
db-eqiad.php: Depool db1056operations/mediawiki-configmaster+6 -6
db-eqiad.php: Depool db1098operations/mediawiki-configmaster+7 -7
db-eqiad.php: Depool db1034operations/mediawiki-configmaster+7 -7
db-eqiad.php: Depool db1067operations/mediawiki-configmaster+1 -1
db-eqiad.php: Depool db1081operations/mediawiki-configmaster+2 -2
db-eqiad.php: Depool db1085operations/mediawiki-configmaster+3 -3
db-eqiad.php: Depool db1084operations/mediawiki-configmaster+1 -1
db-eqiad.php: Depool db1094operations/mediawiki-configmaster+1 -1
db-eqiad.php: Depool db1088operations/mediawiki-configmaster+1 -1
db-eqiad.php: Depool db1074operations/mediawiki-configmaster+3 -3
db-eqiad.php: Depool db1070operations/mediawiki-configmaster+4 -4
db-eqiad.php: Depool db1093operations/mediawiki-configmaster+1 -1
mariadb: Depool db1098 temporarily for maintenaceoperations/mediawiki-configmaster+7 -7
db-eqiad.php: Depool db1086operations/mediawiki-configmaster+3 -3
db-eqiad.php: Depool db1087operations/mediawiki-configmaster+3 -3
db-eqiad.php: Depool db1082operations/mediawiki-configmaster+3 -3
db-eqiad.php: Depool db1079operations/mediawiki-configmaster+3 -3
db-eqiad.php: Depool db1080operations/mediawiki-configmaster+4 -4
db-eqiad.php: Depool db1076operations/mediawiki-configmaster+1 -1
db-eqiad.php: Depool db1071operations/mediawiki-configmaster+3 -3
db-eqiad.php: Depool db1091operations/mediawiki-configmaster+1 -1
db-eqiad.php: Depool db1083operations/mediawiki-configmaster+1 -1
db-eqiad.php: Depool db1092operations/mediawiki-configmaster+1 -1
db-eqiad.php: Depool db1090operations/mediawiki-configmaster+1 -1
db-eqiad.php: Depool db1096operations/mediawiki-configmaster+6 -6
db-eqiad.php: Depool db1053operations/mediawiki-configmaster+6 -6
db-eqiad.php: Depool db1089operations/mediawiki-configmaster+1 -1
db-eqiad.php: Depool db1099operations/mediawiki-configmaster+6 -6
db-codfw.php: Depool db2035 and db2056operations/mediawiki-configmaster+11 -11
db-eqiad.php: Depool db1097operations/mediawiki-configmaster+3 -3
db-codfw.php: Depool db2034, db2055 and db2062operations/mediawiki-configmaster+13 -13
db-codfw.php: Depool db2049 and db2041operations/mediawiki-configmaster+3 -3
db-codfw.php: Depool db2039operations/mediawiki-configmaster+7 -7
db-codfw: Depool db2070, db2069, db2042operations/mediawiki-configmaster+10 -10
db-codfw.php: Depool db2053 and db2046operations/mediawiki-configmaster+5 -5
db-codfw.php: Depool db2064 and db2063operations/mediawiki-configmaster+3 -3
db-codfw.php: Depool db2076, db2067, db2060operations/mediawiki-configmaster+5 -5
db-codfw.php: Depool db2072 and db2071operations/mediawiki-configmaster+3 -3
db-eqiad.php: Depool db1083operations/mediawiki-configmaster+1 -1
db-eqiad.php: Depool db1089operations/mediawiki-configmaster+1 -1
Show related patches Customize query in gerrit

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)Oct 17 2017, 7:08 AM
gerritbot added a comment.Oct 17 2017, 7:09 AM

Change 384656 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1067

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

Stashbot added a comment.Oct 17 2017, 7:11 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-17T07:11:08Z] <marostegui> Optimize templatelinks and pagelinks on db1067 - T174509

gerritbot added a comment.Oct 17 2017, 7:12 AM

Change 384656 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1067

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

Stashbot added a comment.Oct 17 2017, 7:13 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-17T07:13:52Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1067 - T174509 (duration: 00m 45s)

Stashbot added a comment.Oct 17 2017, 9:46 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-17T09:46:34Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1085 - T174509 T177772 (duration: 00m 46s)

• Marostegui updated the task description. (Show Details)Oct 17 2017, 11:45 AM
Stashbot added a comment.Oct 17 2017, 11:47 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-17T11:47:02Z] <marostegui> Optimize templatelinks and pagelinks on db1102 for s4 - T174509

Stashbot added a comment.Oct 17 2017, 11:47 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-17T11:47:44Z] <marostegui> Optimize recentchanges on db1102 for s4 - T174509

• Marostegui updated the task description. (Show Details)Oct 17 2017, 2:05 PM
Stashbot added a comment.Oct 17 2017, 2:09 PM

Mentioned in SAL (#wikimedia-operations) [2017-10-17T14:09:36Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1081 - T174509 T177772 (duration: 00m 45s)

• Marostegui updated the task description. (Show Details)Oct 18 2017, 5:19 AM
• Marostegui updated the task description. (Show Details)
• Marostegui updated the task description. (Show Details)Oct 18 2017, 5:22 AM
Stashbot added a comment.Oct 18 2017, 5:25 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-18T05:25:19Z] <marostegui> Optimize pagelinks and templatelinks on db1102 for s2 - T174509

Stashbot added a comment.Oct 18 2017, 5:27 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-18T05:27:38Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1067 - T174509 (duration: 00m 50s)

• Marostegui updated the task description. (Show Details)Oct 18 2017, 5:29 AM
gerritbot added a comment.Oct 18 2017, 5:33 AM

Change 384922 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1098

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

gerritbot added a comment.Oct 18 2017, 5:36 AM

Change 384922 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1098

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

Stashbot added a comment.Oct 18 2017, 5:36 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-18T05:36:41Z] <marostegui> Optimize pagelinks and templatelinks on db1098 - T174509

Stashbot added a comment.Oct 18 2017, 5:37 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-18T05:37:29Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1098 - T174509 (duration: 00m 49s)

gerritbot added a comment.Oct 18 2017, 5:39 AM

Change 384923 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1055

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

Stashbot added a comment.Oct 18 2017, 5:40 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-18T05:40:59Z] <marostegui> Optimize pagelinks and templatelinks on db1055 - T174509

gerritbot added a comment.Oct 18 2017, 5:42 AM

Change 384923 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1055

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

Stashbot added a comment.Oct 18 2017, 5:43 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-18T05:43:51Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1055 - T174509 (duration: 00m 50s)

• Marostegui updated the task description. (Show Details)Oct 18 2017, 7:40 AM
Stashbot added a comment.Oct 18 2017, 7:44 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-18T07:44:08Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1034 - T174509 (duration: 00m 59s)

gerritbot added a comment.Oct 18 2017, 7:44 AM

Change 384939 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1056

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

Stashbot added a comment.Oct 18 2017, 7:46 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-18T07:46:53Z] <marostegui> Optimize pagelinks and templatelinks on db1056 - T174509

gerritbot added a comment.Oct 18 2017, 7:48 AM

Change 384939 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1056

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

Stashbot added a comment.Oct 18 2017, 7:49 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-18T07:49:49Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1056 - T174509 (duration: 00m 49s)

• Marostegui updated the task description. (Show Details)Oct 18 2017, 9:42 AM
• Marostegui updated the task description. (Show Details)Oct 18 2017, 11:25 AM
Stashbot added a comment.Oct 18 2017, 11:26 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-18T11:26:46Z] <marostegui> Optimize pagelinks and templatelinks on db1102 s7 - T174509

• Marostegui updated the task description. (Show Details)Oct 19 2017, 5:27 AM
• Marostegui updated the task description. (Show Details)
• Marostegui updated the task description. (Show Details)
Stashbot added a comment.Oct 19 2017, 5:31 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-19T05:31:22Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1056 - T174509 (duration: 00m 50s)

Stashbot added a comment.Oct 19 2017, 5:34 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-19T05:34:54Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1055 - T174509 (duration: 00m 50s)

gerritbot added a comment.Oct 19 2017, 5:39 AM

Change 385134 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1051

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

Stashbot added a comment.Oct 19 2017, 5:41 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-19T05:41:54Z] <marostegui> Optimize pagelinks and templatelinks on db1051 - T174509

gerritbot added a comment.Oct 19 2017, 5:42 AM

Change 385134 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1051

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

Stashbot added a comment.Oct 19 2017, 5:44 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-19T05:44:28Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1051 - T174509 (duration: 00m 50s)

Stashbot added a comment.Oct 19 2017, 5:45 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-19T05:45:12Z] <marostegui> Optimize recentchanges, pagelinks and templatelinks on db1064 - T174509 T177772

Stashbot added a comment.Oct 19 2017, 5:46 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-19T05:46:43Z] <marostegui> Optimize recentchanges, pagelinks and templatelinks on db1102 for s6 - T174509 T177772

• Marostegui updated the task description. (Show Details)Oct 19 2017, 11:08 AM
• Marostegui updated the task description. (Show Details)Oct 20 2017, 5:36 AM
• Marostegui updated the task description. (Show Details)
Stashbot added a comment.Oct 20 2017, 5:41 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-20T05:41:33Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1051 - T174509 (duration: 00m 47s)

• Marostegui updated the task description. (Show Details)Oct 23 2017, 2:59 PM
• Marostegui updated the task description. (Show Details)Oct 24 2017, 6:09 AM
gerritbot added a comment.Oct 24 2017, 2:31 PM

Change 386197 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1065

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

Stashbot added a comment.Oct 24 2017, 2:32 PM

Mentioned in SAL (#wikimedia-operations) [2017-10-24T14:32:44Z] <marostegui> Optimize pagelinks and templatelinks on db1065 - T174509

gerritbot added a comment.Oct 24 2017, 2:35 PM

Change 386197 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1065

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

Stashbot added a comment.Oct 24 2017, 2:36 PM

Mentioned in SAL (#wikimedia-operations) [2017-10-24T14:36:53Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1065 - T174509 (duration: 00m 45s)

Stashbot added a comment.Oct 24 2017, 2:39 PM

Mentioned in SAL (#wikimedia-operations) [2017-10-24T14:39:15Z] <marostegui> Optimize pagelinks templatelinks and recentchanges on db1030 - T174509 https://phabricator.wikimedia.org/T177772

• Marostegui updated the task description. (Show Details)Oct 24 2017, 3:28 PM
• Marostegui updated the task description. (Show Details)Oct 25 2017, 5:37 AM
• Marostegui updated the task description. (Show Details)
• Marostegui updated the task description. (Show Details)Oct 25 2017, 5:40 AM
Stashbot added a comment.Oct 25 2017, 6:02 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-25T06:02:19Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1065 - T174509 (duration: 00m 50s)

• Marostegui updated the task description. (Show Details)Oct 25 2017, 7:04 AM
Stashbot added a comment.Oct 25 2017, 7:05 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-25T07:05:26Z] <marostegui> Optimize pagelinks and templatelinks on db1021 - T174509

• Marostegui updated the task description. (Show Details)Oct 25 2017, 3:38 PM
Stashbot added a comment.Oct 25 2017, 3:39 PM

Mentioned in SAL (#wikimedia-operations) [2017-10-25T15:39:59Z] <marostegui> Optimize pagelinks and templatelinks on labsdb1011 - T174509

• Marostegui updated the task description. (Show Details)Oct 26 2017, 5:08 AM
• Marostegui updated the task description. (Show Details)
gerritbot added a comment.Oct 26 2017, 5:09 AM

Change 386575 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1060

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

Stashbot added a comment.Oct 26 2017, 5:12 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-26T05:12:12Z] <marostegui> Optimize pagelinks and templatelinks on db1060 - T174509

gerritbot added a comment.Oct 26 2017, 5:12 AM

Change 386575 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1060

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

Stashbot added a comment.Oct 26 2017, 5:15 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-26T05:14:59Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1060 - T174509 (duration: 00m 50s)

Stashbot added a comment.Oct 26 2017, 6:55 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-26T06:55:58Z] <marostegui> Optimize pagelinks and templatelinks on db1095 s1 - T174509

• Marostegui updated the task description. (Show Details)Oct 27 2017, 5:41 AM
Stashbot added a comment.Oct 27 2017, 5:46 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-27T05:46:41Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1060 - T174509 (duration: 00m 50s)

Stashbot added a comment.Oct 27 2017, 8:11 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-27T08:11:08Z] <marostegui> Drop redundant indexes from pagelinks and templatelinks on s3 wikis only for dbstore2001 and dbstore2002 - T174509

• Marostegui added a comment.EditedOct 27 2017, 9:07 AM

Status:

  • Finished optimizing all the servers in s1,s2,s4,s5,s6 and s7 (pending master)
  • Dropping now the indexes on s3 - probably not worth running the optimize (maybe only on cebwiki.templatelinks which is quite insane now (this size is uncompressed): 414G Oct 27 09:06 templatelinks.ibd)
• Marostegui added a comment.Oct 27 2017, 11:03 AM

The index has been removed from all s3 hosts. The only pending one is the master (db1075) which I will do on Monday.
Also I have left an optimize table running for cebwiki.templatelinks on db1103 to see how much space we get.
Current state is: -rw-rw---- 1 mysql mysql 283G Oct 27 11:02 /srv/sqldata/cebwiki/templatelinks.ibd

I will also start running optimize tables for the other masters starting next week.

• Marostegui updated the task description. (Show Details)Oct 27 2017, 11:04 AM
Stashbot added a comment.Oct 27 2017, 11:04 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-27T11:04:30Z] <marostegui> Optimize cebwiki.templatelinks on db1103 - T174509

Stashbot added a comment.Oct 30 2017, 5:58 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-30T05:58:09Z] <marostegui> Deploy alter table on db1075 (s3 primary master) - T174509

• Marostegui added a comment.Oct 30 2017, 6:01 AM

s3 master - db1075 is done.
All the shards got the redundant indexes removed.

• Marostegui updated the task description. (Show Details)Oct 30 2017, 6:02 AM
• Marostegui added a comment.Oct 30 2017, 6:04 AM
In T174509#3715077, @Marostegui wrote:

The index has been removed from all s3 hosts. The only pending one is the master (db1075) which I will do on Monday.
Also I have left an optimize table running for cebwiki.templatelinks on db1103 to see how much space we get.
Current state is: -rw-rw---- 1 mysql mysql 283G Oct 27 11:02 /srv/sqldata/cebwiki/templatelinks.ibd

I will also start running optimize tables for the other masters starting next week.

This finished, and it is probably worth optimizing+compress across the fleet.
This is the result of the optimization:

-rw-rw---- 1 mysql mysql 209G Oct 30 06:02 templatelinks.ibd

That means that
table without compression and without optimization: -rw-rw---- 1 mysql mysql 414G Oct 30 06:03 /srv/sqldata/cebwiki/templatelinks.ibd and with compression+optimization: `-rw-rw---- 1 mysql mysql 209G Oct 30 06:02 templatelinks.ibd
`

I will probably create a ticket just to do it for cebwiki on all s3.

Stashbot added a comment.Oct 30 2017, 6:24 AM

Mentioned in SAL (#wikimedia-operations) [2017-10-30T06:24:50Z] <marostegui> Optimize dewiki.pagelinks and dewiki.templatelinks on db1063 (s5 master) - T174509

• Marostegui added a comment.Oct 30 2017, 8:30 AM

Optimized s5 master:
dewiki.pagelinks (10G)
dewiki.templatelinks (30G)
wikidatawiki.templatelinks (300M)
pending: wikidatawiki.pagelinks (around 90G) - will do it tomorrow morning.

No delays happened as this is an online operation

Stashbot added a comment.Nov 2 2017, 6:27 AM

Mentioned in SAL (#wikimedia-operations) [2017-11-02T06:27:29Z] <marostegui> Deploy optimize table wikidatawiki.pagelinks on s5 master (db1063) - T174509

• Marostegui added a comment.Nov 2 2017, 10:14 AM
In T174509#3718640, @Marostegui wrote:

Optimized s5 master:
dewiki.pagelinks (10G)
dewiki.templatelinks (30G)
wikidatawiki.templatelinks (300M)
pending: wikidatawiki.pagelinks (around 90G) - will do it tomorrow morning.

No delays happened as this is an online operation

s5 master is fully done.

-rw-rw---- 1 mysql mysql 46G Nov  2 10:14 /srv/sqldata/wikidatawiki/pagelinks.ibd
• Marostegui updated the task description. (Show Details)Nov 2 2017, 10:14 AM
• Marostegui moved this task from Backlog to In progress on the Schema-change-in-production board.Nov 3 2017, 1:17 PM
Stashbot added a comment.Nov 6 2017, 6:19 AM

Mentioned in SAL (#wikimedia-operations) [2017-11-06T06:19:25Z] <marostegui> Optimize pagelinks and templatelinks on s6 master (db1061) - T174509

• Marostegui updated the task description. (Show Details)Nov 6 2017, 12:29 PM
Stashbot added a comment.Nov 6 2017, 4:51 PM

Mentioned in SAL (#wikimedia-operations) [2017-11-06T16:51:11Z] <marostegui> Optimize pagelinks and templatelinks on s2 master - db1054 - T174509

• Marostegui updated the task description. (Show Details)Nov 7 2017, 8:47 AM
Stashbot added a comment.Nov 7 2017, 8:48 AM

Mentioned in SAL (#wikimedia-operations) [2017-11-07T08:48:19Z] <marostegui> Optimize pagelinks and templatelinks on s7 master - db1062 - T174509

• Marostegui updated the task description. (Show Details)Nov 8 2017, 6:26 AM
• Marostegui closed this task as Resolved.Nov 8 2017, 6:35 AM

The only two pending hosts to optimize are db1052 (s1 master) and db1068 (s4 master).
The tables there are:

root@db1068:/srv/sqldata/commonswiki# ls -lh pagelinks.ibd templatelinks.ibd
-rw-rw---- 1 mysql mysql  90G Nov  8 06:32 pagelinks.ibd
-rw-rw---- 1 mysql mysql 285G Nov  8 06:32 templatelinks.ibd

root@db1052:/srv/sqldata/enwiki# ls -lh pagelinks.ibd templatelinks.ibd
-rw-rw---- 1 mysql mysql 271G Nov  8 06:32 pagelinks.ibd
-rw-rw---- 1 mysql mysql 162G Nov  8 06:32 templatelinks.ibd

The tables once optimized would be like:

s1

root@db1089:/srv/sqldata/enwiki# ls -lh templatelinks.ibd pagelinks.ibd
-rw-rw---- 1 mysql mysql 193G Nov  8 06:33 pagelinks.ibd
-rw-rw---- 1 mysql mysql 109G Nov  8 06:33 templatelinks.ibd

s4

root@db1097:/srv/sqldata/commonswiki# ls -lh templatelinks.ibd pagelinks.ibd
-rw-rw---- 1 mysql mysql  65G Nov  8 06:31 pagelinks.ibd
-rw-rw---- 1 mysql mysql 202G Nov  8 06:31 templatelinks.ibd

There is some gain but not a huge one. Considering how big the tables are, how busy those two masters are, and that the gain isn't life-changing and that both masters have no disk constraint issues at the moment I am not going to risk them because of this optimize - we can always revisit it and do it during the next DC switchover:

root@db1052:/srv/sqldata/enwiki# df -hT /srv
Filesystem            Type  Size  Used Avail Use% Mounted on
/dev/mapper/tank-data xfs   3.3T  1.4T  1.9T  42% /srv

root@db1068:~# df -hT /srv
Filesystem            Type  Size  Used Avail Use% Mounted on
/dev/mapper/tank-data xfs   3.3T  1.7T  1.6T  51% /srv

So closing this as resolved until the next DC switch.

• Marostegui updated the task description. (Show Details)Nov 8 2017, 6:37 AM
Ladsgroup mentioned this in T252126: tl_from index on templatelinks is lingering in production.May 7 2020, 3:43 PM
Ladsgroup mentioned this in T256684: pl_from index still lingers in random hosts.Jun 29 2020, 8:27 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL