Page MenuHomePhabricator

Add statistics table to information_schema_p
Open, MediumPublic

Description

On Toolforge, I would like to easily find out what indexes are available on a given table and given schema. It seems this is normally offered in statistics table of information_schema, but I see information_schema_p.statistics is missing.

information_schema.statistics (not _p) is available, but for whatever reason I'm unable to query it. Even for a simple SELECT with LIMIT 1 it just hangs.

Most ideal would be the ability to do SHOW INDEX FROM enwiki_p.revision, and even better somehow make this work on the views like revision_userindex.

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJun 6 2018, 5:48 PM
jcrespo added a project: DBA.EditedJun 6 2018, 5:53 PM
jcrespo added a subscriber: jcrespo.

SHOW INDEX FROM enwiki_p.revision will not be possible, at least not short term, because views cannot show indexes, and in order to do that for the underlying table, you need select grants, which is the whole point of having the views in the first place.

information_schema will not work because of the lack of those selects grants, plus the amount of objects on the server (~200000), which makes querying it very slow (solved partially on MySQL 8.0).

What we can do short term is offer an information_schema_p.statistics copy that is fast to query. We need to check how, but that should be doable.

Marostegui triaged this task as Medium priority.Jun 8 2018, 2:18 PM
Marostegui moved this task from Triage to Backlog on the DBA board.
Vvjjkkii renamed this task from Add statistics table to information_schema_p to 1ibaaaaaaa.Jul 1 2018, 1:05 AM
Vvjjkkii raised the priority of this task from Medium to High.
Vvjjkkii updated the task description. (Show Details)
Vvjjkkii removed a subscriber: Aklapper.
Marostegui renamed this task from 1ibaaaaaaa to Add statistics table to information_schema_p.Jul 2 2018, 5:16 AM
Marostegui lowered the priority of this task from High to Medium.
Marostegui updated the task description. (Show Details)
bd808 moved this task from Backlog to Wiki replicas on the Data-Services board.Nov 2 2018, 11:38 PM