Page MenuHomePhabricator
Create Task
Maniphest T187295

Apply AbuseFilter patch-fix-index
Closed, ResolvedPublic
Actions

Description

From a quick analysis on T179131, it seems that at least some of the AbuseFilter indexes are incorrect in production (at least on enwiki)

enwiki has

mysql:wikiadmin@db1066 [enwiki]> show indexes from abuse_filter_log;
+------------------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name       | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| abuse_filter_log |          0 | PRIMARY        |            1 | afl_id        | A         |    19022925 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_timestamp  |            1 | afl_timestamp | A         |    19022925 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_namespace  |            1 | afl_namespace | A         |        4264 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_namespace  |            2 | afl_title     | A         |     6340975 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_ip         |            1 | afl_ip        | A         |     2377865 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_rev_id     |            1 | afl_rev_id    | A         |     9511462 |     NULL | NULL   | YES  | BTREE      |         |               |
| abuse_filter_log |          1 | afl_log_id     |            1 | afl_log_id    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| abuse_filter_log |          1 | user_timestamp |            1 | afl_user      | A         |     6340975 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | user_timestamp |            2 | afl_user_text | A         |    19022925 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | user_timestamp |            3 | afl_timestamp | A         |    19022925 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_filter     |            1 | afl_filter    | A         |       56615 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_filter     |            2 | afl_timestamp | A         |    19022925 |     NULL | NULL   |      | BTREE      |         |               |
+------------------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
12 rows in set (0.00 sec)

It should have had the changes below applied

https://github.com/wikimedia/mediawiki-extensions-AbuseFilter/blob/master/db_patches/patch-fix-indexes.sql

ALTER TABLE /*_*/abuse_filter_log ADD KEY filter_timestamp (afl_filter,afl_timestamp);
ALTER TABLE /*_*/abuse_filter_log ADD KEY user_timestamp (afl_user,afl_user_text,afl_timestamp);
ALTER TABLE /*_*/abuse_filter_log ADD KEY page_timestamp (afl_namespace, afl_title, afl_timestamp);
ALTER TABLE /*_*/abuse_filter_log ADD KEY ip_timestamp (afl_ip, afl_timestamp);
ALTER TABLE /*_*/abuse_filter_log ADD KEY wiki_timestamp (afl_wiki, afl_timestamp);

ALTER TABLE /*_*/abuse_filter_log DROP KEY afl_filter;
ALTER TABLE /*_*/abuse_filter_log DROP KEY afl_user;
ALTER TABLE /*_*/abuse_filter_log DROP KEY afl_namespace;
ALTER TABLE /*_*/abuse_filter_log DROP KEY afl_ip;

Meaning the resultant table should look like

CREATE TABLE /*$wgDBprefix*/abuse_filter_log (
	afl_id BIGINT unsigned NOT NULL AUTO_INCREMENT,
	afl_filter varchar(64) binary NOT NULL,
	afl_user BIGINT unsigned NOT NULL,
	afl_user_text varchar(255) binary NOT NULL,
	afl_ip varchar(255) not null,
	afl_action varbinary(255) not null,
	afl_actions varbinary(255) not null,
	afl_var_dump BLOB NOT NULL,
	afl_timestamp binary(14) NOT NULL,
	afl_namespace int NOT NULL,
	afl_title varchar(255) binary NOT NULL,
	afl_wiki varchar(64) binary NULL,
	afl_deleted tinyint(1) NOT NULL DEFAULT 0,
	afl_patrolled_by int unsigned NULL,
	afl_rev_id int unsigned,
	afl_log_id int unsigned,

	PRIMARY KEY (afl_id),
	KEY filter_timestamp (afl_filter,afl_timestamp),
	KEY user_timestamp (afl_user,afl_user_text,afl_timestamp),
	KEY (afl_timestamp),
	KEY page_timestamp (afl_namespace, afl_title, afl_timestamp),
	KEY ip_timestamp (afl_ip, afl_timestamp),
	KEY (afl_rev_id),
	KEY (afl_log_id),
	KEY wiki_timestamp (afl_wiki, afl_timestamp)
) /*$wgDBTableOptions*/;

ALTER table to run:

ALTER TABLE /*_*/abuse_filter_log ADD KEY IF NOT EXISTS filter_timestamp (afl_filter,afl_timestamp), ADD KEY IF NOT EXISTS user_timestamp (afl_user,afl_user_text,afl_timestamp), ADD KEY IF NOT EXISTS page_timestamp (afl_namespace, afl_title, afl_timestamp), ADD KEY IF NOT EXISTS ip_timestamp (afl_ip, afl_timestamp), ADD KEY IF NOT EXISTS wiki_timestamp (afl_wiki, afl_timestamp),DROP KEY IF EXISTS afl_filter, DROP KEY IF EXISTS afl_user, DROP KEY IF EXISTS afl_namespace, DROP KEY IF EXISTS afl_ip;

Execution progress:

s1

s2

s3

s4

s5

s6

s7

s8
wikidatawiki already has the right schema

wikitech alread has the right schema.

Details

SubjectRepoBranchLines +/-
db-eqiad.php: Depool db1083operations/mediawiki-configmaster+1 -1
Customize query in gerrit

Related Objects
Search...

StatusSubtypeAssignedTask
 Resolved• MarosteguiT187295 Apply AbuseFilter patch-fix-index
 ResolvedLadsgroupT104459 Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas
 ResolvedNoneT57455 Provide dynamic report of differences between replica databases and production databases
 Resolved• MarosteguiT51199 Add index log_type_action
 Resolved• MarosteguiT217397 logging.log_title_time and logging.log_title_type_time indexes are not on tables.sql but they exist on most of the wikis
 Resolved• MarosteguiT250055 Remove image.img_deleted column from production
 Resolved• MarosteguiT250067 user_newtalk has two indexes not renamed in s4
 Resolved• MarosteguiT250062 ipb_parent_block_id_2 index on ipblocks table on s8 only
 Resolved• MarosteguiT250060 tl_namespace index on templatelinks is unique only in s8
 ResolvedReedyT249972 Wikitech has lots of database drifts with core and rest of the databases
 Resolved• MarosteguiT249973 db1110 has 5 important database drifts that are unique to the host
 Resolved• MarosteguiT250056 lc_lang_key index is lingering in production
 Resolved• MarosteguiT250057 type_acton index in logging table is lingering in production
 DeclinedNoneT250058 searchindex indexes are missing in production
 Resolved• MarosteguiT250063 inverse_timestamp column exists in text table, it shouldn't
 Resolved• MarosteguiT250066 text table still has old_* fields and indexes on some hosts
 ResolvedLadsgroupT250071 Rename ipb_address index on ipb_address to ipb_address_unique
 Resolved• MarosteguiT252126 tl_from index on templatelinks is lingering in production
 Resolved• MarosteguiT256679 text table in db1144 drifts from core considerably
 Resolved• MarosteguiT256680 imagelinks has index mismatch on s8
 Resolved• MarosteguiT256682 page_restrictions indexes have been majestically drifting from code
 Resolved• MarosteguiT256684 pl_from index still lingers in random hosts
 Resolved• MarosteguiT256685 pl_namespace index on pagelinks is unique only in s8
 Resolved• MarosteguiT260111 All sorts of random drifts in wikis in s3
 ResolvedLadsgroupT265321 ipblocks_restrictions.ir_type is tinyint(1) in code but tinyint(4) in production
 Resolved• MarosteguiT265349 querycache qc_type and qc_title have different nullabality on s1 only
 Resolved• MarosteguiT265445 Index uniqueness mismatches in links tables in wikis that were moved from s3 to s5
 ResolvedLadsgroupT269348 wikitech database has almost all of its varbinary fields wrong
 ResolvedKormatT277118 iw_url in interwiki is varbinary(127) in production but blob in code
 Resolved• MarosteguiT277354 "chemical" major mime type was never added to production database
 ResolvedKormatT278619 ipb_timestamp is varbinary(14) in old wikis while being binary(14) in the code since 2007
 Resolved• MarosteguiT278621 job_cmd is varbinary(255) in production while being varbinary(60) in code since 2007
 ResolvedKormatT277116 fa_deleted_timestamp and fa_timestamp are binary(14) in code but varbinary(14) in production
 Resolved• MarosteguiT308206 Slow query on echo_event table

Event Timeline

Reedy created this task.Feb 14 2018, 8:48 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptFeb 14 2018, 8:48 AM
Reedy added a comment.Feb 14 2018, 8:50 AM

It gets more fun...

db1066 and db1083 have different indexes!

mysql:wikiadmin@db1083 [enwiki]> show indexes from abuse_filter_log;
+------------------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name       | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| abuse_filter_log |          0 | PRIMARY        |            1 | afl_id        | A         |    19386588 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_filter     |            1 | afl_filter    | A         |       36997 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_timestamp  |            1 | afl_timestamp | A         |    19386588 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_namespace  |            1 | afl_namespace | A         |        4156 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_namespace  |            2 | afl_title     | A         |     9693294 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_ip         |            1 | afl_ip        | A         |     1615549 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_rev_id     |            1 | afl_rev_id    | A         |    19386588 |     NULL | NULL   | YES  | BTREE      |         |               |
| abuse_filter_log |          1 | afl_log_id     |            1 | afl_log_id    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| abuse_filter_log |          1 | user_timestamp |            1 | afl_user      | A         |     4846647 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | user_timestamp |            2 | afl_user_text | A         |    19386588 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | user_timestamp |            3 | afl_timestamp | A         |    19386588 |     NULL | NULL   |      | BTREE      |         |               |
+------------------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
11 rows in set (0.00 sec)
Reedy added a comment.Feb 14 2018, 8:54 AM

And dewiki has even less... Same as frwiki and mediawikiwiki (at least)...

mysql:wikiadmin@db1082 [dewiki]> show indexes from abuse_filter_log;
+------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| abuse_filter_log |          0 | PRIMARY       |            1 | afl_id        | A         |      526522 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_filter    |            1 | afl_filter    | A         |         472 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_user      |            1 | afl_user      | A         |      105304 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_timestamp |            1 | afl_timestamp | A         |      526522 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_namespace |            1 | afl_namespace | A         |          38 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_namespace |            2 | afl_title     | A         |      526522 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_ip        |            1 | afl_ip        | A         |       19500 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_rev_id    |            1 | afl_rev_id    | A         |      131630 |     NULL | NULL   | YES  | BTREE      |         |               |
| abuse_filter_log |          1 | afl_log_id    |            1 | afl_log_id    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
9 rows in set (0.00 sec)

Much newer wikis look to be correct

mysql:wikiadmin@db1077 [wikimania2017wiki]> show indexes from abuse_filter_log;
+------------------+------------+------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name         | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| abuse_filter_log |          0 | PRIMARY          |            1 | afl_id        | A         |          39 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | filter_timestamp |            1 | afl_filter    | A         |          13 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | filter_timestamp |            2 | afl_timestamp | A         |          39 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | user_timestamp   |            1 | afl_user      | A         |          39 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | user_timestamp   |            2 | afl_user_text | A         |          39 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | user_timestamp   |            3 | afl_timestamp | A         |          39 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_timestamp    |            1 | afl_timestamp | A         |          39 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | page_timestamp   |            1 | afl_namespace | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | page_timestamp   |            2 | afl_title     | A         |          39 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | page_timestamp   |            3 | afl_timestamp | A         |          39 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | ip_timestamp     |            1 | afl_ip        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | ip_timestamp     |            2 | afl_timestamp | A         |          39 |     NULL | NULL   |      | BTREE      |         |               |
| abuse_filter_log |          1 | afl_rev_id       |            1 | afl_rev_id    | A         |          13 |     NULL | NULL   | YES  | BTREE      |         |               |
| abuse_filter_log |          1 | afl_log_id       |            1 | afl_log_id    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| abuse_filter_log |          1 | wiki_timestamp   |            1 | afl_wiki      | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| abuse_filter_log |          1 | wiki_timestamp   |            2 | afl_timestamp | A         |          39 |     NULL | NULL   |      | BTREE      |         |               |
+------------------+------------+------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
16 rows in set (0.00 sec)

mysql:wikiadmin@db1077 [wikimania2017wiki]>
Reedy added a comment.Feb 14 2018, 8:55 AM

Can a DBA do some sort of schema diff across the cluster for this tables indexes? To find out what state we're in more globally? Thanks!

MarcoAurelio subscribed.Feb 14 2018, 8:56 AM
Reedy edited projects, added Schema-change-in-production, DBA; removed Schema-change.Feb 14 2018, 9:28 AM
jcrespo moved this task from Triage to Backlog on the DBA board.Feb 14 2018, 9:31 AM
MarcoAurelio added a comment.Feb 14 2018, 10:58 AM

@Reedy Can you apply that patch on Beta Cluster? Thanks.

Reedy added a comment.Feb 14 2018, 11:00 AM
In T187295#3971317, @MarcoAurelio wrote:

@Reedy Can you apply that patch on Beta Cluster? Thanks.

No, there's no need. Pretty sure all the wikis were created after that was committed.. And if they weren't it's in the DB updaters, so they should've been applied anyway

MarcoAurelio added a comment.Feb 14 2018, 11:05 AM

Ok. Thanks for the explanation :)

Daimona added a project: AbuseFilter.May 21 2018, 4:06 PM
jcrespo triaged this task as Medium priority.Jun 6 2018, 5:56 PM
jcrespo moved this task from Backlog to Pending comment on the DBA board.
• Marostegui moved this task from Pending comment to Backlog on the DBA board.Oct 24 2018, 1:41 PM
jcrespo added a subtask: T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.Feb 8 2019, 9:49 AM
• Marostegui moved this task from Backlog to Pending comment on the DBA board.Feb 20 2019, 7:17 AM
• Marostegui claimed this task.Feb 21 2019, 11:10 AM
• Marostegui added subscribers: Daimona, • Marostegui.

As this drift has already created some issues I will try to work on this as a background task, trying to fix hosts slowly but steady.
Now that we can use ADD KEY IF NOT EXISTS and DROP KEY IF EXISTS it will be slightly easier, however from a first glance there are lots of drifts even between hosts on the same section.

• Marostegui moved this task from Pending comment to In progress on the DBA board.Feb 21 2019, 11:19 AM

@Daimona I have done a quick grep on mediawiki-extensions-AbuseFilter and on mediawiki-core repo to make sure there are no FORCE INDEX on any of the following ones:

afl_filter
afl_user
afl_namespace
afl_ip

It'd be great if you can double check it for me, as you probably know better where it is more likely to find those (if there's any)

Daimona added a comment.Feb 21 2019, 11:24 AM

@Marostegui I checked as well and there seems to be no FORCE INDEX at all. BTW should you need anything else I can reach out to you on IRC or wherever you want.

• Marostegui added a comment.Feb 21 2019, 11:24 AM

Thank you!

• Marostegui updated the task description. (Show Details)Feb 21 2019, 2:05 PM
• Marostegui moved this task from Backlog to In progress on the Schema-change-in-production board.
• Marostegui updated the task description. (Show Details)Feb 22 2019, 8:36 AM

I have ran the ALTER on codfw DC for s5 section:

cebwiki
dewiki
enwikivoyage
mgwiktionary
shwiki
srwiki

Next week I am going to start with eqiad, I will go slow as we are touching many indexes, I will go slow to make sure we are not introducing any sort of regression on queries.

• Marostegui added a comment.EditedFeb 25 2019, 6:24 AM

s5 eqiad progress

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • dbstore1002
  • db1124
  • db1113
  • db1110
  • db1102
  • db1100
  • db1097
  • db1096
  • db1082
  • db1070

@Daimona I have dropped+created the indexes on a bunch of hosts on s5, can you check from your side that everything looks good (querytime-wise) on the following wikis?

cebwiki
dewiki
enwikivoyage
mgwiktionary
shwiki
srwiki

I just want to make sure that we are not introducing any regressions by removing some of the indexes (and adding some).

Daimona added a comment.Feb 25 2019, 7:43 AM

@Marostegui Thanks! I tried several different queries on dewiki keeping an eye on Tendril, and nothing suspicious showed up, so I think you can safely go on. I'll continue monitoring Tendril.
BTW, IIRC the only wiki where I constantly see slow AbuseFilter-related queries is enwiki, which has the old indexes. Also, please note that many AF-related slow queries are actually bad queries on the recentchanges table (T214191), so not really related to these indexes.

• Marostegui added a comment.Feb 25 2019, 8:00 AM

Thanks @Daimona!
Is this an example of a slow query?

SELECT /* IndexPager::buildQueryInfo (AbuseLogPager) xxx */ * FROM `abuse_filter_log` LEFT JOIN `abuse_filter` ON ((af_id=afl_filter)) WHERE afl_filter = '550' AND ((afl_deleted = '0') OR (afl_deleted IS NULL)) ORDER BY afl_timestamp LIMIT 51 /*
• Marostegui updated the task description. (Show Details)Feb 25 2019, 8:05 AM
• Marostegui updated the task description. (Show Details)Feb 25 2019, 8:09 AM
• Marostegui added a comment.Feb 25 2019, 8:47 AM

s6 eqiad progress

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • dbstore1002
  • dbstore1001
  • db1125
  • db1113
  • db1098
  • db1096
  • db1093
  • db1088
  • db1085
  • db1061
• Marostegui updated the task description. (Show Details)Feb 25 2019, 8:48 AM
Stashbot subscribed.Feb 25 2019, 8:52 AM

Mentioned in SAL (#wikimedia-operations) [2019-02-25T08:52:03Z] <marostegui> Deploy schema change on s2 on codfw master - lag will happen on s2 codfw - T187295

• Marostegui updated the task description. (Show Details)Feb 25 2019, 8:57 AM
Daimona added a comment.Feb 25 2019, 9:16 AM
In T187295#4979679, @Marostegui wrote:

Thanks @Daimona!
Is this an example of a slow query?

SELECT /* IndexPager::buildQueryInfo (AbuseLogPager) xxx */ * FROM `abuse_filter_log` LEFT JOIN `abuse_filter` ON ((af_id=afl_filter)) WHERE afl_filter = '550' AND ((afl_deleted = '0') OR (afl_deleted IS NULL)) ORDER BY afl_timestamp LIMIT 51 /*

Uhm, has it been reported as slow? I couldn't spot it, if so. Yes, this query being slow could be a side-effect of changing indexes.

• Marostegui added a comment.Feb 25 2019, 9:17 AM
In T187295#4979911, @Daimona wrote:
In T187295#4979679, @Marostegui wrote:

Thanks @Daimona!
Is this an example of a slow query?

SELECT /* IndexPager::buildQueryInfo (AbuseLogPager) xxx */ * FROM `abuse_filter_log` LEFT JOIN `abuse_filter` ON ((af_id=afl_filter)) WHERE afl_filter = '550' AND ((afl_deleted = '0') OR (afl_deleted IS NULL)) ORDER BY afl_timestamp LIMIT 51 /*

Uhm, has it been reported as slow? I couldn't spot it, if so. Yes, this query being slow could be a side-effect of changing indexes.

No, that query was being reported on enwiki, which hasn't been touched yet.
Can you give one of the slow ones you've seen?

Stashbot added a comment.Feb 25 2019, 9:54 AM

Mentioned in SAL (#wikimedia-operations) [2019-02-25T09:54:49Z] <marostegui> Deploy schema change on db1074, this will generate lag on labsdb:s2 - T187295

• Marostegui added a comment.Feb 25 2019, 10:12 AM

s2 eqiad progress

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • dbstore1002
  • db1125
  • db1122
  • db1105
  • db1103
  • db1095
  • db1090
  • db1076
  • db1074
  • db1066
• Marostegui updated the task description. (Show Details)Feb 25 2019, 10:12 AM

@Daimona the following wikis are now fully altered, I am going to give it some hours before continuing and will monitor tendril:
s2:
bgwiki
bgwiktionary
cswiki
enwikiquote
enwiktionary
eowiki
fiwiki
idwiki
itwiki
nlwiki
nowiki
plwiki
ptwiki
svwiki
thwiki
trwiki
zhwiki

s5:
cebwiki
dewiki
enwikivoyage
mgwiktionary
shwiki
srwiki

s6:
frwiki
jawiki
ruwiki

Daimona added a comment.Feb 25 2019, 10:36 AM
In T187295#4979912, @Marostegui wrote:
In T187295#4979911, @Daimona wrote:
In T187295#4979679, @Marostegui wrote:

Thanks @Daimona!
Is this an example of a slow query?

SELECT /* IndexPager::buildQueryInfo (AbuseLogPager) xxx */ * FROM `abuse_filter_log` LEFT JOIN `abuse_filter` ON ((af_id=afl_filter)) WHERE afl_filter = '550' AND ((afl_deleted = '0') OR (afl_deleted IS NULL)) ORDER BY afl_timestamp LIMIT 51 /*

Uhm, has it been reported as slow? I couldn't spot it, if so. Yes, this query being slow could be a side-effect of changing indexes.

No, that query was being reported on enwiki, which hasn't been touched yet.
Can you give one of the slow ones you've seen?

Ah yes, IIRC that query is one of the reported slow queries happening due to the lack of the new indexes. I remember asking about it in #wikimedia-databases a few days ago. Thanks for your work, I'll keep an eye on Tendril as well.

Daimona added a comment.Feb 25 2019, 4:01 PM

Nothing weird seems to have happened during the last 6 hours, and this is good. However, I guess that any possible trouble won't be visible before reaching enwiki...

• Marostegui added a comment.Feb 25 2019, 4:03 PM
In T187295#4981381, @Daimona wrote:

Nothing weird seems to have happened during the last 6 hours, and this is good. However, I guess that any possible trouble won't be visible before reaching enwiki...

Yeah, my plan was to go to 1 enwiki slave next and executing the above queries (and others) manually before and after the index changes.

Daimona added a comment.Feb 25 2019, 4:05 PM

Great! Let me know if I can help, e.g. by playing around with AF on enwiki.

• Marostegui added a comment.Feb 25 2019, 4:07 PM

Yeah, I will ask for that once we have more replicas altered, otherwise you might sometimes reach one that is altered and another time one that is not :)
Thank you!

gerritbot subscribed.Feb 26 2019, 6:11 AM

Change 492951 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1083

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

gerritbot added a project: Patch-For-Review.Feb 26 2019, 6:11 AM
gerritbot added a comment.Feb 26 2019, 6:16 AM

Change 492951 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1083

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

Stashbot added a comment.Feb 26 2019, 6:17 AM

Mentioned in SAL (#wikimedia-operations) [2019-02-26T06:17:19Z] <marostegui@deploy1001> Synchronized wmf-config/db-eqiad.php: Depool db1083 T187295 (duration: 00m 51s)

Stashbot added a comment.Feb 26 2019, 6:17 AM

Mentioned in SAL (#wikimedia-operations) [2019-02-26T06:17:38Z] <marostegui> Change abuse_filter_log indexes on db1083 - T187295

• Marostegui added a comment.Feb 26 2019, 6:25 AM

I used the following query on db1083 to measure the impact of the index change (I executed the query twice to make sure it was "warm"):

SELECT /* IndexPager::buildQueryInfo (AbuseLogPager) xx */ * FROM `abuse_filter_log` LEFT JOIN `abuse_filter` ON ((af_id=afl_filter)) WHERE afl_filter = '423' AND ((afl_deleted = '0') OR (afl_deleted IS NULL)) ORDER BY afl_timestamp DESC LIMIT 51;

51 rows in set (36.97 sec)

This is the explain:

root@db1083.eqiad.wmnet[(none)]> show explain for 1313887033;
+------+-------------+------------------+--------+---------------+---------------+---------+------------------------------------+----------+-----------------------------+
| id   | select_type | table            | type   | possible_keys | key           | key_len | ref                                | rows     | Extra                       |
+------+-------------+------------------+--------+---------------+---------------+---------+------------------------------------+----------+-----------------------------+
|    1 | SIMPLE      | abuse_filter_log | index  | afl_filter    | afl_timestamp | 16      | const                              | 22661459 | Using where; Using filesort |
|    1 | SIMPLE      | abuse_filter     | eq_ref | PRIMARY       | PRIMARY       | 8       | enwiki.abuse_filter_log.afl_filter |        1 | Using where                 |
+------+-------------+------------------+--------+---------------+---------------+---------+------------------------------------+----------+-----------------------------+
2 rows in set, 1 warning (0.00 sec)

After the index change the query flies:

51 rows in set (0.00 sec)

And the explain is using the new index:

+------+-------------+------------------+--------+------------------+------------------+---------+------------------------------------+--------+-------------+
| id   | select_type | table            | type   | possible_keys    | key              | key_len | ref                                | rows   | Extra       |
+------+-------------+------------------+--------+------------------+------------------+---------+------------------------------------+--------+-------------+
|    1 | SIMPLE      | abuse_filter_log | ref    | filter_timestamp | filter_timestamp | 66      | const                              | 119340 | Using where |
|    1 | SIMPLE      | abuse_filter     | eq_ref | PRIMARY          | PRIMARY          | 8       | enwiki.abuse_filter_log.afl_filter |      1 | Using where |
+------+-------------+------------------+--------+------------------+------------------+---------+------------------------------------+--------+-------------+
2 rows in set (0.01 sec)
• Marostegui added a comment.EditedFeb 26 2019, 6:26 AM

s1 eqiad progress

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • dbstore1002
  • dbstore1001
  • db1124
  • db1119
  • db1118
  • db1106
  • db1105
  • db1099
  • db1089
  • db1083
  • db1080
  • db1067
• Marostegui updated the task description. (Show Details)Feb 26 2019, 6:26 AM
• Marostegui updated the task description. (Show Details)Feb 26 2019, 8:07 AM
Daimona added a comment.Feb 26 2019, 8:31 AM

@Marostegui Thanks, that's nice to hear! Given that queries like that one are pretty common, this is surely a huge performance boost.

jcrespo awarded a token.Feb 26 2019, 8:54 AM
• Marostegui added a comment.Feb 26 2019, 9:41 AM
In T187295#4983856, @Daimona wrote:

@Marostegui Thanks, that's nice to hear! Given that queries like that one are pretty common, this is surely a huge performance boost.

Only two hosts pending on s1, db1089 and db1119 - I am going to give it some time to make sure no other slow queries show up for this table (which I am not seeing so far on tendril - in fact, they are disappearing as we alter more and more s1 hosts, but just in case).

• Marostegui added a comment.Feb 27 2019, 6:25 AM

db1119 has been altered, only db1089 left on s1. I have not seen any new slow queries popping up for enwiki hosts on tendril, in fact, I am seeing less (yay!).
Going to give it a few more hours before finishing with db1089 (and then the master)

• Marostegui added a comment.Feb 27 2019, 2:00 PM

No more slow queries on enwiki apart from db1089 which isn't altered, so I am going to go ahead and alter that one and the master to get enwiki over with.

Stashbot added a comment.Feb 27 2019, 2:01 PM

Mentioned in SAL (#wikimedia-operations) [2019-02-27T14:01:07Z] <marostegui> Change indexes on abuse_filter_log on db1089 - T187295

• Marostegui updated the task description. (Show Details)Feb 27 2019, 2:21 PM
• Marostegui added a comment.Feb 27 2019, 2:24 PM

s7 comes next as it has shows often slow queries there too for arwiki and eswiki

Stashbot added a comment.Feb 27 2019, 2:26 PM

Mentioned in SAL (#wikimedia-operations) [2019-02-27T14:25:59Z] <marostegui> Deploy schema change on abuse_filter_log on s7 codfw - lag will be generated on codfw - T187295

• Marostegui updated the task description. (Show Details)Feb 27 2019, 2:36 PM
• Marostegui added a comment.EditedFeb 27 2019, 3:39 PM

s7 eqiad progress

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • dbstore1002
  • db1125
  • db1116
  • db1101
  • db1098
  • db1094
  • db1090
  • db1086
  • db1079
  • db1062
• Marostegui updated the task description. (Show Details)Feb 28 2019, 6:57 AM
• Marostegui updated the task description. (Show Details)Feb 28 2019, 7:07 AM
• Marostegui updated the task description. (Show Details)Feb 28 2019, 7:35 AM
• Marostegui added a comment.Feb 28 2019, 8:20 AM

s4 eqiad progress

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • dbstore1002
  • db1125
  • db1121
  • db1103
  • db1102
  • db1097
  • db1091
  • db1084
  • db1081
  • db1068
• Marostegui updated the task description. (Show Details)Feb 28 2019, 8:21 AM
Stashbot added a comment.Feb 28 2019, 8:22 AM

Mentioned in SAL (#wikimedia-operations) [2019-02-28T08:22:20Z] <marostegui> Change abuse_filter_log indexes on s3 codfw, lag will appear on codfw - T187295

• Marostegui updated the task description. (Show Details)Feb 28 2019, 8:31 AM
• Marostegui added a comment.EditedFeb 28 2019, 8:59 AM

s3 eqiad progress

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • dbstore1002
  • db1124
  • db1123
  • db1095
  • db1077
  • db1075
  • db1078
• Marostegui updated the task description. (Show Details)Feb 28 2019, 9:44 AM

All done - I will keep an eye on tendril for the next 24h before closing this to make sure nothing pops up.

Daimona awarded a token.Feb 28 2019, 1:13 PM
• Marostegui closed this task as Resolved.Mar 1 2019, 8:04 AM

Everything looks good!

Ladsgroup closed subtask T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas as Resolved.May 15 2021, 11:43 AM
Maintenance_bot removed a project: Patch-For-Review.May 15 2021, 12:10 PM
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