Some queries to new replica hosts are dramatically slower than labsdb; missing indexes?
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	MusikAnimal
	Sep 29 2017, 4:04 PM

Description

We moved XTools to enwiki.analytics.db.svc.eqiad.wmflabs, and it worked fine for some tools like ArticleInfo, but for others such as the Edit Counter and Pages Created, it went crazy crazy slow compared to enwiki.labsdb (~178 secs vs. ~3 secs for User:L235). My guess is the indexing is not the same. Sorry I'm unable to debug right now... but here's the query for the Pages Created tool (User:L235):

(SELECT DISTINCT page_namespace AS namespace, 'rev' AS type, page_title AS page_title,
    page_len, page_is_redirect, rev_timestamp AS rev_timestamp,
    rev_user, rev_user_text AS username, rev_len, rev_id , pa_class, pa_importance, pa_page_revision
FROM `enwiki_p`.`page`
JOIN `enwiki_p`.`revision_userindex` ON page_id = rev_page
LEFT JOIN `enwiki_p`.`page_assessments` ON rev_page = pa_page_id
WHERE  rev_user = '11656865' AND rev_timestamp > 1  AND rev_parent_id = '0'  AND page_namespace = '0'   AND page_is_redirect = '0' 
GROUP BY rev_page
)

UNION

(SELECT a.ar_namespace AS namespace, 'arc' AS type, a.ar_title AS page_title,
    0 AS page_len, '0' AS page_is_redirect, MIN(a.ar_timestamp) AS rev_timestamp,
    a.ar_user AS rev_user, a.ar_user_text AS username, a.ar_len AS rev_len,
    a.ar_rev_id AS rev_id , NULL AS pa_class, NULL AS pa_page_id, NULL AS pa_page_revision
FROM `enwiki_p`.`archive_userindex` a
JOIN
(
    SELECT b.ar_namespace, b.ar_title
    FROM `enwiki_p`.`archive_userindex` AS b
    LEFT JOIN `enwiki_p`.`logging_userindex` ON log_namespace = b.ar_namespace AND log_title = b.ar_title
        AND log_user = b.ar_user AND (log_action = 'move' OR log_action = 'move_redir')
    WHERE  ar_user = '11656865' AND ar_timestamp > 1  AND b.ar_parent_id = '0'  AND ar_namespace = '0'  AND log_action IS NULL
) AS c ON c.ar_namespace= a.ar_namespace AND c.ar_title = a.ar_title
GROUP BY a.ar_namespace, a.ar_title
HAVING  rev_user = '11656865' 
)

I have moved production XTools back to enwiki.labsdb, so if you check that's why it isn't going slow right now.

Details

	Subject	Repo	Branch	Lines +/-
	mariadb: Add file to control additional wikireplicas-only indexes	operations/puppet	production	+8 -0

Customize query in gerrit

Related Objects
Search...

Status	Assigned	Task
Resolved	jcrespo	T140788 Labs databases rearchitecture (tracking)
Resolved	bd808	T166402 Program 7 Outcome 3: data services
Resolved	bd808	T142807 Migrate all users to new Wiki Replica cluster and decommission old hardware
Declined	jcrespo	T150767 Wikireplica service for tools and labs - issues and missing available views (tracking)
Resolved	jcrespo	T177096 Some queries to new replica hosts are dramatically slower than labsdb; missing indexes?
Resolved	bd808	T177223 Determine schema differences between labsdb1001 and labsdb1009

Event Timeline

MusikAnimal created this task.Sep 29 2017, 4:04 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptSep 29 2017, 4:04 PM

bd808 renamed this task from New replica hosts are dramatically slower than labsdb to Some queries to new replica hosts are dramatically slower than labsdb; missing indexes?.Sep 29 2017, 4:07 PM

bd808 edited projects, added Data-Services, DBA; removed cloud-services-team.

jcrespo moved this task from Triage to Backlog (help welcome) on the DBA board.Sep 29 2017, 4:11 PM

So these are the two query plans:

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

mysql:root@localhost [enwiki]> explain (SELECT DISTINCT page_namespace AS namespace, 'rev' AS type, page_title AS page_title,     page_len, page_is_redirect, rev_timestamp AS rev_timestamp,     rev_user, rev_user_text AS username, rev_len, rev_id , pa_class, pa_importance, pa_page_revision FROM `enwiki_p`.`page` JOIN `enwiki_p`.`revision_userindex` ON page_id = rev_page LEFT JOIN `enwiki_p`.`page_assessments` ON rev_page = pa_page_id WHERE  rev_user = '11656865' AND rev_timestamp > 1  AND rev_parent_id = '0'  AND page_namespace = '0'   AND page_is_redirect = '0'  GROUP BY rev_page )  UNION  (SELECT a.ar_namespace AS namespace, 'arc' AS type, a.ar_title AS page_title,     0 AS page_len, '0' AS page_is_redirect, MIN(a.ar_timestamp) AS rev_timestamp,     a.ar_user AS rev_user, a.ar_user_text AS username, a.ar_len AS rev_len,     a.ar_rev_id AS rev_id , NULL AS pa_class, NULL AS pa_page_id, NULL AS pa_page_revision FROM `enwiki_p`.`archive_userindex` a JOIN (     SELECT b.ar_namespace, b.ar_title     FROM `enwiki_p`.`archive_userindex` AS b     LEFT JOIN `enwiki_p`.`logging_userindex` ON log_namespace = b.ar_namespace AND log_title = b.ar_title         AND log_user = b.ar_user AND (log_action = 'move' OR log_action = 'move_redir')     WHERE  ar_user = '11656865' AND ar_timestamp > 1  AND b.ar_parent_id = '0'  AND ar_namespace = '0'  AND log_action IS NULL ) AS c ON c.ar_namespace= a.ar_namespace AND c.ar_title = a.ar_title GROUP BY a.ar_namespace, a.ar_title HAVING  rev_user = '11656865'  );
+------+--------------+------------------+--------+-----------------------------------------------------+----------------------+---------+-----------------------------------------------------+-------+----------------------------------------------+
| id   | select_type  | table            | type   | possible_keys                                       | key                  | key_len | ref                                                 | rows  | Extra                                        |
+------+--------------+------------------+--------+-----------------------------------------------------+----------------------+---------+-----------------------------------------------------+-------+----------------------------------------------+
|    1 | PRIMARY      | revision         | ref    | PRIMARY,rev_timestamp,page_timestamp,user_timestamp | user_timestamp       | 4       | const                                               | 17842 | Using where; Using temporary; Using filesort |
|    1 | PRIMARY      | page_assessments | ref    | PRIMARY                                             | PRIMARY              | 4       | enwiki.revision.rev_page                            |     1 |                                              |
|    1 | PRIMARY      | page             | eq_ref | PRIMARY,name_title,page_redirect_namespace_len      | PRIMARY              | 4       | enwiki.revision.rev_page                            |     1 | Using where                                  |
|    2 | UNION        | archive          | ref    | name_title_timestamp,user_timestamp                 | user_timestamp       | 4       | const                                               |  1915 | Using where; Using temporary; Using filesort |
|    2 | UNION        | logging          | ref    | type_time,user_time                                 | user_time            | 4       | enwiki.archive.ar_user                              |     1 | Using where                                  |
|    2 | UNION        | archive          | ref    | name_title_timestamp                                | name_title_timestamp | 261     | enwiki.archive.ar_namespace,enwiki.archive.ar_title |     2 | Using where                                  |
| NULL | UNION RESULT | <union1,2>       | ALL    | NULL                                                | NULL                 | NULL    | NULL                                                |  NULL |                                              |
+------+--------------+------------------+--------+-----------------------------------------------------+----------------------+---------+-----------------------------------------------------+-------+----------------------------------------------+
7 rows in set (0.01 sec)

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

mysql:root@localhost [enwiki]> explain (SELECT DISTINCT page_namespace AS namespace, 'rev' AS type, page_title AS page_title,     page_len, page_is_redirect, rev_timestamp AS rev_timestamp,     rev_user, rev_user_text AS username, rev_len, rev_id , pa_class, pa_importance, pa_page_revision FROM `enwiki_p`.`page` JOIN `enwiki_p`.`revision_userindex` ON page_id = rev_page LEFT JOIN `enwiki_p`.`page_assessments` ON rev_page = pa_page_id WHERE  rev_user = '11656865' AND rev_timestamp > 1  AND rev_parent_id = '0'  AND page_namespace = '0'   AND page_is_redirect = '0'  GROUP BY rev_page )  UNION  (SELECT a.ar_namespace AS namespace, 'arc' AS type, a.ar_title AS page_title,     0 AS page_len, '0' AS page_is_redirect, MIN(a.ar_timestamp) AS rev_timestamp,     a.ar_user AS rev_user, a.ar_user_text AS username, a.ar_len AS rev_len,     a.ar_rev_id AS rev_id , NULL AS pa_class, NULL AS pa_page_id, NULL AS pa_page_revision FROM `enwiki_p`.`archive_userindex` a JOIN (     SELECT b.ar_namespace, b.ar_title     FROM `enwiki_p`.`archive_userindex` AS b     LEFT JOIN `enwiki_p`.`logging_userindex` ON log_namespace = b.ar_namespace AND log_title = b.ar_title         AND log_user = b.ar_user AND (log_action = 'move' OR log_action = 'move_redir')     WHERE  ar_user = '11656865' AND ar_timestamp > 1  AND b.ar_parent_id = '0'  AND ar_namespace = '0'  AND log_action IS NULL ) AS c ON c.ar_namespace= a.ar_namespace AND c.ar_title = a.ar_title GROUP BY a.ar_namespace, a.ar_title HAVING  rev_user = '11656865'  );
+------+--------------+------------------+--------+-----------------------------------------------------------------------------+----------------------+---------+-----------------------------------------------------+----------+---------------------------------------------------------------------+
| id   | select_type  | table            | type   | possible_keys                                                               | key                  | key_len | ref                                                 | rows     | Extra                                                               |
+------+--------------+------------------+--------+-----------------------------------------------------------------------------+----------------------+---------+-----------------------------------------------------+----------+---------------------------------------------------------------------+
|    1 | PRIMARY      | revision         | ref    | rev_timestamp,page_timestamp,user_timestamp,page_user_timestamp,rev_page_id | user_timestamp       | 4       | const                                               |    27144 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | PRIMARY      | page_assessments | ref    | PRIMARY                                                                     | PRIMARY              | 4       | enwiki.revision.rev_page                            |        1 |                                                                     |
|    1 | PRIMARY      | page             | eq_ref | PRIMARY,name_title,page_redirect_namespace_len                              | PRIMARY              | 4       | enwiki.revision.rev_page                            |        1 | Using where                                                         |
|    2 | UNION        | archive          | ref    | name_title_timestamp                                                        | name_title_timestamp | 4       | const                                               | 25100957 | Using index condition; Using where; Using temporary; Using filesort |
|    2 | UNION        | logging          | ref    | type_time,user_time,log_user_type_time                                      | user_time            | 4       | enwiki.archive.ar_user                              |        1 | Using where                                                         |
|    2 | UNION        | archive          | ref    | name_title_timestamp                                                        | name_title_timestamp | 261     | enwiki.archive.ar_namespace,enwiki.archive.ar_title |        2 | Using where                                                         |
| NULL | UNION RESULT | <union1,2>       | ALL    | NULL                                                                        | NULL                 | NULL    | NULL                                                |     NULL |                                                                     |
+------+--------------+------------------+--------+-----------------------------------------------------------------------------+----------------------+---------+-----------------------------------------------------+----------+---------------------------------------------------------------------+
7 rows in set (0.00 sec)

There is a missing index on the archive table - these are the tables

mysql:root@localhost [enwiki]> select @@hostname; show create table archive\G
+------------+
| @@hostname |
+------------+
| labsdb1001 |
+------------+
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: archive
Create Table: CREATE TABLE `archive` (
  `ar_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ar_namespace` int(11) NOT NULL DEFAULT '0',
  `ar_title` varbinary(255) NOT NULL DEFAULT '',
  `ar_text` mediumblob NOT NULL,
  `ar_comment` tinyblob NOT NULL,
  `ar_user` int(5) unsigned NOT NULL DEFAULT '0',
  `ar_user_text` varbinary(255) NOT NULL DEFAULT '',
  `ar_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `ar_minor_edit` tinyint(1) NOT NULL DEFAULT '0',
  `ar_flags` tinyblob NOT NULL,
  `ar_rev_id` int(8) unsigned DEFAULT NULL,
  `ar_text_id` int(8) unsigned DEFAULT NULL,
  `ar_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `ar_len` int(8) unsigned DEFAULT NULL,
  `ar_page_id` int(10) unsigned DEFAULT NULL,
  `ar_parent_id` int(10) unsigned DEFAULT NULL,
  `ar_sha1` varbinary(32) NOT NULL DEFAULT '',
  `ar_content_model` varbinary(32) DEFAULT NULL,
  `ar_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`ar_id`),
  KEY `name_title_timestamp` (`ar_namespace`,`ar_title`,`ar_timestamp`),
  KEY `usertext_timestamp` (`ar_user_text`,`ar_timestamp`),
  KEY `ar_revid` (`ar_rev_id`),
  KEY `user_timestamp` (`ar_user`,`ar_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=87026405 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED `compression`='tokudb_zlib'

mysql:root@localhost [enwiki]> select @@hostname; show create table archive\G
+------------+
| @@hostname |
+------------+
| labsdb1011 |
+------------+
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: archive
Create Table: CREATE TABLE `archive` (
  `ar_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ar_namespace` int(11) NOT NULL DEFAULT '0',
  `ar_title` varbinary(255) NOT NULL DEFAULT '',
  `ar_text` mediumblob NOT NULL,
  `ar_comment` tinyblob NOT NULL,
  `ar_user` int(5) unsigned NOT NULL DEFAULT '0',
  `ar_user_text` varbinary(255) NOT NULL DEFAULT '',
  `ar_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `ar_minor_edit` tinyint(1) NOT NULL DEFAULT '0',
  `ar_flags` tinyblob NOT NULL,
  `ar_rev_id` int(8) unsigned DEFAULT NULL,
  `ar_text_id` int(8) unsigned DEFAULT NULL,
  `ar_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `ar_len` int(8) unsigned DEFAULT NULL,
  `ar_page_id` int(10) unsigned DEFAULT NULL,
  `ar_parent_id` int(10) unsigned DEFAULT NULL,
  `ar_sha1` varbinary(32) NOT NULL DEFAULT '',
  `ar_content_model` varbinary(32) DEFAULT NULL,
  `ar_content_format` varbinary(64) DEFAULT NULL,
  PRIMARY KEY (`ar_id`),
  KEY `name_title_timestamp` (`ar_namespace`,`ar_title`,`ar_timestamp`),
  KEY `usertext_timestamp` (`ar_user_text`,`ar_timestamp`),
  KEY `ar_revid` (`ar_rev_id`)
) ENGINE=InnoDB AUTO_INCREMENT=87026405 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
1 row in set (0.00 sec)

The new labs replica doesn't have the user_timestamp index which is being used for this query.
I will add it probably on Monday, as I don't want to leave an ALTER table running there on a Friday evening :-)

I just realised that index is NOT on tables.sql, so it must have been added on the old labs server for some reason, so that is why it is not on the new labs server, so I am not sure this should be added there as these hosts are filtered copies of production and thus the schemas are the same.
So I will NOT add the index as that would mean we are drifting schemas from production

Marostegui moved this task from In progress to Backlog (help welcome) on the DBA board.Sep 29 2017, 4:40 PM

Explain via https://quarry.wmflabs.org/query/21882

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	revision	ref	rev_timestamp,page_timestamp,user_timestamp,page_user_timestamp,rev_page_id	user_timestamp	4	const	26100	Using index condition; Using where; Using temporary; Using filesort
1	PRIMARY	page_assessments	ref	PRIMARY	PRIMARY	4	enwiki.revision.rev_page	1
1	PRIMARY	page	eq_ref	PRIMARY,name_title,page_redirect_namespace_len	PRIMARY	4	enwiki.revision.rev_page	1	Using where
2	UNION	archive	ref	name_title_timestamp	name_title_timestamp	4	const	25098625	Using index condition; Using where; Using temporary; Using filesort
2	UNION	logging	ref	type_time,user_time,log_user_type_time	user_time	4	enwiki.archive.ar_user	1	Using where
2	UNION	archive	ref	name_title_timestamp	name_title_timestamp	261	enwiki.archive.ar_namespace,enwiki.archive.ar_title	2	Using where
	UNION RESULT	<union1,2>	ALL

In T177096#3647048, @Marostegui wrote:

I just realised that index is NOT on tables.sql, so it must have been added on the old labs server for some reason, so that is why it is not on the new labs server, so I am not sure this should be added there as these hosts are filtered copies of production and thus the schemas are the same.
So I will NOT add the index as that would mean we are drifting schemas from production

If we dig through old bugs here in Phabricator I'm pretty sure we will find a large number of indices that have been added to the the Wiki Replica servers to improve performance of various analytics queries. I'm not sure that 1:1 parity with product indexes is a good thing to shoot for in this case. I think we should have all prod indices, but that it is also reasonable to have additional indices that make non-MediaWiki queries more performant.

In T177096#3647075, @bd808 wrote:

I think we should have all prod indices, but that it is also reasonable to have additional indices that make non-MediaWiki queries more performant.

Yes, pwetty pwease :)

zhuyifei1999 subscribed.Sep 29 2017, 4:56 PM

but that it is also reasonable to have additional indices that make non-MediaWiki queries more performant.

I don't disagree, we can have both extra indexes and tables but a process should be setup (puppet, etc.) to make those changes repeatable. Eg. T59617

In T177096#3647096, @jcrespo wrote:

but that it is also reasonable to have additional indices that make non-MediaWiki queries more performant.

I don't disagree, we can have both extra indexes and tables but a process should be setup (puppet, etc.) to make those changes repeatable. Eg. T59617

Hit the nail on the head!
If we don't have something in place, every time we clone or rebuild a labs host, we will hit the same problem we just did :-)

To be clear, I am not opposed to make the query fast per se :-)

So, thinking about it. Should we:

Create the index to unblock @MusikAnimal
Think a way of documenting, puppetizing or something to avoid this from hitting us again?

I'd be ok with stalling the index fix for a few days if we can get something properly designed and built relatively quickly. I would be glad to help work on scripting to automate repeatable schema changes along the lines of the https://gerrit.wikimedia.org/r/#/c/375349 script proposed for T59617 and other custom stuff that we already do if hands on keyboards is what is needed.

Maybe it's time to have a meeting or detailed discussion on one of these bugs about what the "right" solution looks like?

In T177096#3647333, @bd808 wrote:

I'd be ok with stalling the index fix for a few days if we can get something properly designed and built relatively quickly. I would be glad to help work on scripting to automate repeatable schema changes along the lines of the https://gerrit.wikimedia.org/r/#/c/375349 script proposed for T59617 and other custom stuff that we already do if hands on keyboards is what is needed.

That's my concern, that I am not sure we (DBAs) have the bandwidth to be able to do that now, and I wouldn't want to block tools migrations to the new replicas.
If you guys can get something up quickly that'd be awesome though :-)

From my point of view, ideally we should have some sort of cronjob or similar that could compare the replicas schemas with tables.sql and generate a sql patch every day, or every week (doesn't really matter at this point).
So whenever we clone a new replica (if it is not from another replica, but from production) we could simple apply the patch that would contain the list of ALTER tables and voilá!

In T177096#3649817, @Marostegui wrote:

From my point of view, ideally we should have some sort of cronjob or similar that could compare the replicas schemas with tables.sql and generate a sql patch every day, or every week (doesn't really matter at this point).
So whenever we clone a new replica (if it is not from another replica, but from production) we could simple apply the patch that would contain the list of ALTER tables and voilá!

Setting up something to reverse engineer the schema changes seems possible. A tool like https://github.com/mmatuson/SchemaSync might not be too difficult to deploy somewhere (other than deb packaging horrors).

It also seems like it would be easy and straight forward to work in the other direction and have a script that manages a collection of ALTER patches that are purposefully added. The pattern of having an "updates" table where application of patch files is logged is pretty well established in the world of schema migrations. We could either do this per-wikidb or at a global level for the wiki replica server. In this model patching would be a process of adding a new migration patch and then running a tool that ensured that either a single wikidb or all of them are up to date with the collection of patches that are expected to be applied.

bd808 created subtask T177223: Determine schema differences between labsdb1001 and labsdb1009.Oct 2 2017, 3:33 PM

bd808 added a parent task: T142807: Migrate all users to new Wiki Replica cluster and decommission old hardware.

jcrespo added a parent task: T150767: Wikireplica service for tools and labs - issues and missing available views (tracking).Oct 4 2017, 3:23 PM

Change 382170 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] mariadb: Add file to control additional wikireplicas-only indexes

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

gerritbot added a project: Patch-For-Review.Oct 4 2017, 3:29 PM

In T177096#3657674, @gerritbot wrote:

Change 382170 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] mariadb: Add file to control additional wikireplicas-only indexes

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

Nice start!

This should fix things:

root@labsdb1010[enwiki]> ALTER TABLE archive ADD KEY `user_timestamp` (`ar_user`,`ar_timestamp`);
Query OK, 0 rows affected, 4 warnings (6 min 45.34 sec)
Records: 0  Duplicates: 0  Warnings: 4

mysql> (SELECT ...
2 rows in set (0.61 sec)

I was going to propose to not use the index by rewriting the query, but actually the index, given the size of the archive table, could be even interesting for production, too; moreso due to the specifics of the view creation/filtering; so I think it is a legitimate request.

Change 382170 merged by Jcrespo:
[operations/puppet@production] mariadb: Add file to control additional wikireplicas-only indexes

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

@MusikAnimal Your query takes now 3 second cold, 0.13 seconds hot on the new servers, please migrate back to the new servers.

Lightning fast! :D Many thanks for the prompt assistance

MusikAnimal mentioned this in T177570: Request to increase active connection quota for user s51187 on analytics.db.svc.eqiad.wmflabs.Oct 6 2017, 4:26 PM

jcrespo mentioned this in T160188: Quarry runs thousands times slower in last months.Oct 9 2017, 7:50 AM

bd808 changed the status of subtask T177223: Determine schema differences between labsdb1001 and labsdb1009 from Open to Stalled.Oct 18 2017, 3:00 AM

bd808 changed the status of subtask T177223: Determine schema differences between labsdb1001 and labsdb1009 from Stalled to Open.Dec 1 2017, 5:43 AM

bd808 closed subtask T177223: Determine schema differences between labsdb1001 and labsdb1009 as Resolved.Jan 10 2018, 6:29 AM

Some queries to new replica hosts are dramatically slower than labsdb; missing indexes?Closed, ResolvedPublicActions

Description

Details

Related ObjectsSearch...

Event Timeline

Some queries to new replica hosts are dramatically slower than labsdb; missing indexes?
Closed, ResolvedPublic
Actions

Related Objects
Search...