Page MenuHomePhabricator

Add page_props.pp_value index to Wiki Replicas
Closed, ResolvedPublic

Description

Example query that is slow today (on enwiki):

MariaDB [enwiki_p]> SELECT * FROM page_props p1 WHERE p1.pp_propname='wikibase_item' AND p1.pp_value IN ('Q10593331') ;
+----------+---------------+-----------+------------+
| pp_page  | pp_propname   | pp_value  | pp_sortkey |
+----------+---------------+-----------+------------+
| 24879621 | wikibase_item | Q10593331 |       NULL |
+----------+---------------+-----------+------------+
1 row in set (27.48 sec)

(yes, I could get that specific result via the wikidata replica, but that's not the point of my request)

Details

Related Gerrit Patches:

Event Timeline

Magnus created this task.Jul 18 2016, 12:54 PM
Restricted Application added a project: Cloud-Services. · View Herald TranscriptJul 18 2016, 12:54 PM
Restricted Application added subscribers: Zppix, Aklapper. · View Herald Transcript
chasemp triaged this task as Medium priority.Jul 25 2016, 2:09 PM
chasemp added a project: DBA.
bd808 moved this task from Backlog to Wiki replicas on the Data-Services board.Oct 17 2017, 12:08 AM
bd808 moved this task from Wiki replicas to Backlog on the Data-Services board.
bd808 moved this task from Backlog to Wiki replicas on the Data-Services board.Oct 26 2017, 3:21 PM
bd808 added a subscriber: bd808.Oct 26 2017, 3:45 PM

The *.{analytics,web}.db.svc.eqiad.wmflabs servers should have these indexes (from maintenance/tables.sql) on the page_props table:

CREATE UNIQUE INDEX /*i*/pp_page_propname ON /*_*/page_props (pp_page,pp_propname);
CREATE UNIQUE INDEX /*i*/pp_propname_page ON /*_*/page_props (pp_propname,pp_page);
CREATE UNIQUE INDEX /*i*/pp_propname_sortkey_page ON /*_*/page_props (pp_propname,pp_sortkey,pp_page);
Marostegui added a subscriber: Marostegui.EditedOct 26 2017, 3:50 PM

The *.{analytics,web}.db.svc.eqiad.wmflabs servers should have these indexes (from maintenance/tables.sql) on the page_props table:

CREATE UNIQUE INDEX /*i*/pp_page_propname ON /*_*/page_props (pp_page,pp_propname);
CREATE UNIQUE INDEX /*i*/pp_propname_page ON /*_*/page_props (pp_propname,pp_page);
CREATE UNIQUE INDEX /*i*/pp_propname_sortkey_page ON /*_*/page_props (pp_propname,pp_sortkey,pp_page);

On all the wikis I assume?

They are actually there:

mysql:root@localhost [enwiki]> select @@hostname;
+------------+
| @@hostname |
+------------+
| labsdb1010 |
+------------+
1 row in set (0.00 sec)

mysql:root@localhost [enwiki]> show create table page_props\G
*************************** 1. row ***************************
       Table: page_props
Create Table: CREATE TABLE `page_props` (
  `pp_page` int(11) NOT NULL DEFAULT '0',
  `pp_propname` varbinary(60) NOT NULL DEFAULT '',
  `pp_value` blob NOT NULL,
  `pp_sortkey` float DEFAULT NULL,
  PRIMARY KEY (`pp_page`,`pp_propname`),
  UNIQUE KEY `pp_propname_page` (`pp_propname`,`pp_page`),
  UNIQUE KEY `pp_propname_sortkey_page` (`pp_propname`,`pp_sortkey`,`pp_page`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
1 row in set (0.00 sec)
bd808 added a comment.Oct 26 2017, 3:52 PM

The *.{analytics,web}.db.svc.eqiad.wmflabs servers should have these indexes (from maintenance/tables.sql) on the page_props table:

The pp_propname index is used, but since there is no index on pp_value this is a giant scan.

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEpage_propsrefpp_propname_page,pp_propname_sortkey_pagepp_propname_page62const12297412Using index condition; Using where
bd808 renamed this task from Add pp_propname/pp_value index to Labs replica to Add page_props.pp_value index to Wiki Replicas.Oct 26 2017, 3:54 PM

If you want it to be added there can you please add it here: ./modules/role/files/labs/db/views/extra-wikireplicas-only-indexes.sql

Marostegui moved this task from Triage to Backlog on the DBA board.Oct 27 2017, 9:47 AM

Change 386973 had a related patch set uploaded (by BryanDavis; owner: Bryan Davis):
[operations/puppet@production] wikireplicas: Add index for page_props.pp_value

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

Change 386973 merged by Marostegui:
[operations/puppet@production] wikireplicas: Add index for page_props.pp_value

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

Marostegui added a comment.EditedNov 3 2017, 12:46 PM

Change 386973 merged by Marostegui:
[operations/puppet@production] wikireplicas: Add index for page_props.pp_value
https://gerrit.wikimedia.org/r/386973

I have reverted this change after merging it, because I didn't notice the column is a blob. To be able to add a key over a blob you must specify its length.
See this (this is just an example, so I picked 10 as a random value):

MariaDB [test]> create index pp_value on page_props (pp_value);
ERROR 1170 (42000): BLOB/TEXT column 'pp_value' used in key specification without a key length

MariaDB [test]> create index pp_value on page_props (pp_value(10));
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> show create table page_props\G
*************************** 1. row ***************************
       Table: page_props
Create Table: CREATE TABLE `page_props` (
  `pp_page` int(11) NOT NULL DEFAULT '0',
  `pp_propname` varbinary(60) NOT NULL DEFAULT '',
  `pp_value` blob NOT NULL,
  `pp_sortkey` float DEFAULT NULL,
  PRIMARY KEY (`pp_page`,`pp_propname`),
  UNIQUE KEY `pp_propname_page` (`pp_propname`,`pp_page`),
  UNIQUE KEY `pp_propname_sortkey_page` (`pp_propname`,`pp_sortkey`,`pp_page`),
  KEY `pp_value` (`pp_value`(10))
) ENGINE=InnoDB DEFAULT CHARSET=binary
bd808 added a comment.Nov 3 2017, 7:25 PM

I have reverted this change after merging it, because I didn't notice the column is a blob. To be able to add a key over a blob you must specify its length.

Oops. I didn't notice that either. I'll put up a better patch that only indexes the first 767 bytes.

Change 388572 had a related patch set uploaded (by BryanDavis; owner: Bryan Davis):
[operations/puppet@production] wikireplicas: Add partial index for page_props.pp_value

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

Change 388572 merged by Arturo Borrero Gonzalez:
[operations/puppet@production] wikireplicas: Add partial index for page_props.pp_value

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

bd808 assigned this task to aborrero.Mar 2 2018, 4:53 PM
bd808 edited projects, added cloud-services-team (Kanban); removed Patch-For-Review.
bd808 added a subscriber: aborrero.

Index creation commands still need to be run on the sanitarium side. Assigning to @aborrero for follow up.

I talked with @Marostegui about doing this next week.

I talked with @Marostegui about doing this next week.

Let's chat about it next week, I am not sure if this will be _done_ next week, we have many things going on and with more priority than this (backups, HW failing, etc). This requires altering ALL the wikis, so it can take a while.

aborrero changed the task status from Open to Stalled.Mar 5 2018, 12:25 PM

DBAs are currently busy and can't take a look at this. Will revisit later.

Bstorm closed this task as Resolved.Apr 12 2018, 2:50 PM
bd808 moved this task from Inbox to Done on the cloud-services-team (Kanban) board.May 6 2018, 6:48 PM