Page MenuHomePhabricator
Create Task
Maniphest T179793

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

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

Details

SubjectRepoBranchLines +/-
Dropping the "wb_items_per_site.wb_ips_site_page" indexmediawiki/extensions/Wikibasemaster+8 -1
Customize query in gerrit

Related Objects

Event Timeline

hoo created this task.Nov 5 2017, 7:37 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 5 2017, 7:37 PM
hoo mentioned this in T114904: Migrate wb_items_per_site to using prefixed entity IDs instead of numeric IDs.Nov 5 2017, 7:39 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.
Marostegui added a comment.Nov 6 2017, 8:36 AM

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

Marostegui claimed this task.Nov 10 2017, 9:26 AM
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 Medium priority.Nov 10 2017, 9:28 AM
Stashbot added a subscriber: Stashbot.Nov 13 2017, 6:44 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 closed this task as Resolved.Nov 13 2017, 7:23 AM
Marostegui updated the task description. (Show Details)

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
Marostegui mentioned this in T177223: Determine schema differences between labsdb1001 and labsdb1009.Jan 10 2018, 6:25 AM
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.

Apparently, the repo was never updated: https://phabricator.wikimedia.org/diffusion/EWBA/browse/master/repo/sql/Wikibase.sql

jcrespo moved this task from In progress to Done on the DBA board.Mar 2 2018, 6:33 PM
jcrespo added a comment.Mar 2 2018, 6:36 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.

Ladsgroup added a comment.Mar 2 2018, 11:47 PM

Will do it ASAP.

gerritbot added a project: Patch-For-Review.Mar 3 2018, 9:51 PM
Dispenser mentioned this in T181401: wikidatawiki_p.wb_items_per_site query is slower than expected on Wiki Replicas.Mar 4 2018, 10:06 PM
thiemowmde added a project: Wikidata-Sprint-2018-02-28.Mar 5 2018, 7:58 AM
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.
thiemowmde moved this task from Review to Done on the Wikidata-Sprint-2018-02-28 board.Mar 6 2018, 10:25 AM
ReleaseTaggerBot added a project: MW-1.31-release-notes (WMF-deploy-2018-03-06 (1.31.0-wmf.24)).Mar 6 2018, 3:01 PM
Marostegui added a comment.Mar 23 2018, 7:15 AM

Was this deployed?

Marostegui closed this task as Resolved.Mar 23 2018, 8:35 AM
In T179793#4074970, @Marostegui wrote:

Was this deployed?

Looks like it is gone indeed: https://phabricator.wikimedia.org/diffusion/EWBA/browse/master/repo/sql/Wikibase.sql

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