Consider dropping the "wb_items_per_site.wb_ips_site_page" index
Closed, ResolvedPublic

Description

From db1070:

KEY `wb_ips_site_page` (`ips_site_page`),

This is useful for queries where we want to find a given linked page by title (like "Berlin"), but don't know the site id (like "enwiki"). We don't do these kinds of queries within the software and I can barely imagine a purpose for this.

The only way to use this via Wikibase is SiteLinkLookup::getLinks which allows for such queries to be crafted (but no one does this currently). The method states:

Note: if the conditions are not very selective the result set can be very big.
	 * Thus the caller is responsible for not executing too expensive queries in its context.

If we want, we could make the implementations of getLinks throw if $siteIds is not set, but I'm not sure that's even needed here (as per the above comment).

Progress:

eqiad:

  • labsdb1001.eqiad.wmnet (read only host: T179464)
  • labsdb1003.eqiad.wmnet
  • labsdb1009.eqiad.wmnet
  • labsdb1010.eqiad.wmnet
  • labsdb1011.eqiad.wmnet
  • db1095.eqiad.wmnet
  • dbstore1001.eqiad.wmnet
  • dbstore1002.eqiad.wmnet
  • 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
  • db1106.eqiad.wmnet
  • db1063.eqiad.wmnet
  • codfw
hoo created this task.Nov 5 2017, 7:37 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 5 2017, 7:37 PM
Marostegui moved this task from Triage to Backlog on the DBA board.Nov 6 2017, 8:34 AM
Marostegui added a subscriber: Marostegui.
This comment was removed by Marostegui.

Should be done together with T179106 as that task requires depooling servers most likely.

Marostegui moved this task from Backlog to In progress on the DBA board.

I will start working on T179106 so I will also get this done while depooling servers.

Marostegui triaged this task as Normal priority.Nov 10 2017, 9:28 AM

Mentioned in SAL (#wikimedia-operations) [2017-11-13T06:44:49Z] <marostegui> Deploy alter table directly on codfw s5 master (db2023), this will generate lag on codfw - T179793

Marostegui updated the task description. (Show Details)Nov 13 2017, 6:49 AM

index dropped from codfw:

root@neodymium:/home/marostegui/git/software/dbtools# cat s5.hosts | grep codfw | while read host port; do echo $host:$port; mysql --skip-ssl wikidatawiki -h$host -P$port -e "show create table wb_items_per_site\G" | grep wb_ips_site_page; done
dbstore2001.codfw.wmnet:3315
db2089.codfw.wmnet:3315
db2086.codfw.wmnet:3315
db2085.codfw.wmnet:3315
db2084.codfw.wmnet:3315
db2083.codfw.wmnet:3306
db2082.codfw.wmnet:3306
db2081.codfw.wmnet:3306
db2080.codfw.wmnet:3306
db2079.codfw.wmnet:3306
db2075.codfw.wmnet:3306
db2038.codfw.wmnet:3306
db2045.codfw.wmnet:3306
db2052.codfw.wmnet:3306
db2059.codfw.wmnet:3306
db2066.codfw.wmnet:3306
db2023.codfw.wmnet:3306
Marostegui updated the task description. (Show Details)Nov 13 2017, 7:04 AM
Marostegui updated the task description. (Show Details)
Marostegui updated the task description. (Show Details)Nov 13 2017, 7:10 AM
Marostegui updated the task description. (Show Details)Nov 13 2017, 7:14 AM
Marostegui updated the task description. (Show Details)Nov 13 2017, 7:16 AM
Marostegui updated the task description. (Show Details)Nov 13 2017, 7:23 AM
Marostegui closed this task as Resolved.

This is all done:

root@neodymium:/home/marostegui/git/software/dbtools# cat s5.hosts | grep -v "labs" | while read host port; do echo $host:$port; mysql --skip-ssl wikidatawiki -h$host -P$port -e "show create table wb_items_per_site\G" | grep wb_ips_site_page; done
dbstore2001.codfw.wmnet:3315
db2089.codfw.wmnet:3315
db2086.codfw.wmnet:3315
db2085.codfw.wmnet:3315
db2084.codfw.wmnet:3315
db2083.codfw.wmnet:3306
db2082.codfw.wmnet:3306
db2081.codfw.wmnet:3306
db2080.codfw.wmnet:3306
db2079.codfw.wmnet:3306
db2075.codfw.wmnet:3306
db2038.codfw.wmnet:3306
db2045.codfw.wmnet:3306
db2052.codfw.wmnet:3306
db2059.codfw.wmnet:3306
db2066.codfw.wmnet:3306
db2023.codfw.wmnet:3306
db1095.eqiad.wmnet:3306
dbstore1001.eqiad.wmnet:3306
dbstore1002.eqiad.wmnet:3306
db1070.eqiad.wmnet:3306
db1071.eqiad.wmnet:3306
db1082.eqiad.wmnet:3306
db1087.eqiad.wmnet:3306
db1092.eqiad.wmnet:3306
db1096.eqiad.wmnet:3306
db1099.eqiad.wmnet:3306
db1100.eqiad.wmnet:3306
db1104.eqiad.wmnet:3306
db1106.eqiad.wmnet:3306
db1063.eqiad.wmnet:3306
jcrespo reopened this task as Open.Mar 2 2018, 6:32 PM
jcrespo reassigned this task from Marostegui to hoo.
jcrespo added a subscriber: jcrespo.
jcrespo moved this task from In progress to Done on the DBA board.Mar 2 2018, 6:33 PM

Adding @Ladsgroup because he seems to be our link with wikidata- this should be a 1-line commit (I haven't checked)- right now there seems to be a difference between code deployment and db deployment.

Will do it ASAP.

thiemowmde moved this task from incoming to in progress on the Wikidata board.
thiemowmde moved this task from Backlog to Review on the Wikidata-Sprint-2018-02-28 board.

Was this deployed?