Page MenuHomePhabricator
Create Task
Maniphest T308206

Slow query on echo_event table
Closed, ResolvedPublic
Actions

Description

Just saw this query that does a full scan on echo_event table. The particular query was captured on metawiki, but I would guess it happens everywhere.

root@db1127.eqiad.wmnet[metawiki]> explain SELECT /* EchoEventMapper::fetchByPage  */  event_id,event_type,event_variant,event_agent_id,event_agent_ip,event_extra,event_page_id,event_deleted  FROM `echo_event`    WHERE event_page_id = 11831723;
+------+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+------------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | echo_event | ALL  | NULL          | NULL | NULL    | NULL | 2525157 | Using where |
+------+-------------+------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.001 sec)



root@db1127.eqiad.wmnet[metawiki]> select count(*) from echo_event;
+----------+
| count(*) |
+----------+
|  3669017 |
+----------+
1 row in set (1.634 sec)

Index missing on these wikis: T308206#7923360

  • labswiki
    • eqiad
    • codfw
  • mediawikiwiki
    • eqiad
    • codfw
  • metawiki
    • eqiad
    • codfw
  • officewiki
    • eqiad
    • codfw

Related Objects
Search...

View Standalone Graph
This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.
StatusSubtypeAssignedTask
· · ·
ResolvedLadsgroupT104459 Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas
ResolvedMarosteguiT308206 Slow query on echo_event table
· · ·

Event Timeline

Marostegui created this task.May 12 2022, 6:51 AM
Restricted Application added a project: Growth-Team. · View Herald TranscriptMay 12 2022, 6:51 AM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript
Marostegui added a comment.May 12 2022, 6:52 AM

The query takes around 5 seconds to run, it is not super bad, but let's have this task to see if we can easily fix this.

Ladsgroup subscribed.May 12 2022, 7:01 AM

This is weird but it might be the table having issues.

There is an index on event_page_id but it's not using it:

root@db1120.eqiad.wmnet[metawiki]> show index from echo_event;
+------------+------------+--------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name           | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+--------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| echo_event |          0 | PRIMARY            |            1 | event_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| echo_event |          1 | echo_event_type    |            1 | event_type    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| echo_event |          1 | echo_event_page_id |            1 | event_page_id | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+--------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.001 sec)

Note the 0 Cardinality everywhere and possible_keys being NULL in the explain. We probably need to rebuild the table I think.

Ladsgroup added a comment.May 12 2022, 7:05 AM

I did an optimize table on db1120:

root@db1120.eqiad.wmnet[metawiki]> OPTIMIZE TABLE echo_event;
+---------------------+----------+----------+-------------------------------------------------------------------+
| Table               | Op       | Msg_type | Msg_text                                                          |
+---------------------+----------+----------+-------------------------------------------------------------------+
| metawiki.echo_event | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| metawiki.echo_event | optimize | status   | OK                                                                |
+---------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.062 sec)

That didn't fix the cardinality number but it fixed the query:

+------+-------------+------------+------+--------------------+--------------------+---------+-------+------+-------+
| id   | select_type | table      | type | possible_keys      | key                | key_len | ref   | rows | Extra |
+------+-------------+------------+------+--------------------+--------------------+---------+-------+------+-------+
|    1 | SIMPLE      | echo_event | ref  | echo_event_page_id | echo_event_page_id | 5       | const | 1    |       |
+------+-------------+------------+------+--------------------+--------------------+---------+-------+------+-------+
1 row in set (0.001 sec)
Marostegui added a comment.May 12 2022, 7:17 AM

At least it is consistent and the optimizer behaves like that everywhere.

Marostegui claimed this task.May 12 2022, 7:17 AM
Marostegui added a project: DBA.
Stashbot added a comment.May 12 2022, 7:18 AM

Mentioned in SAL (#wikimedia-operations) [2022-05-12T07:18:52Z] <marostegui> dbmaint s7@codfw T308206

Marostegui added a comment.May 12 2022, 7:20 AM
In T308206#7923290, @Ladsgroup wrote:

I did an optimize table on db1120:

root@db1120.eqiad.wmnet[metawiki]> OPTIMIZE TABLE echo_event;
+---------------------+----------+----------+-------------------------------------------------------------------+
| Table               | Op       | Msg_type | Msg_text                                                          |
+---------------------+----------+----------+-------------------------------------------------------------------+
| metawiki.echo_event | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| metawiki.echo_event | optimize | status   | OK                                                                |
+---------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.062 sec)

That didn't fix the cardinality number but it fixed the query:

+------+-------------+------------+------+--------------------+--------------------+---------+-------+------+-------+
| id   | select_type | table      | type | possible_keys      | key                | key_len | ref   | rows | Extra |
+------+-------------+------------+------+--------------------+--------------------+---------+-------+------+-------+
|    1 | SIMPLE      | echo_event | ref  | echo_event_page_id | echo_event_page_id | 5       | const | 1    |       |
+------+-------------+------------+------+--------------------+--------------------+---------+-------+------+-------+
1 row in set (0.001 sec)

@Ladsgroup that is x1, not s7 :). The issue is on s7
There is no index on event_page_id on that table.

Marostegui added a comment.May 12 2022, 7:22 AM

Tested the index:

root@db2121.codfw.wmnet[metawiki]> alter table echo_event add key (event_page_id);
Query OK, 0 rows affected (6.011 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db2121.codfw.wmnet[metawiki]> explain SELECT /* EchoEventMapper::fetchByPage  */  event_id,event_type,event_variant,event_agent_id,event_agent_ip,event_extra,event_page_id,event_deleted  FROM `echo_event`    WHERE event_page_id = 11831723;
+------+-------------+------------+------+---------------+---------------+---------+-------+------+-------+
| id   | select_type | table      | type | possible_keys | key           | key_len | ref   | rows | Extra |
+------+-------------+------------+------+---------------+---------------+---------+-------+------+-------+
|    1 | SIMPLE      | echo_event | ref  | event_page_id | event_page_id | 5       | const | 1    |       |
+------+-------------+------------+------+---------------+---------------+---------+-------+------+-------+
1 row in set (0.033 sec)

And reverted it back.

Marostegui removed Marostegui as the assignee of this task.May 12 2022, 7:22 AM
Marostegui removed a project: DBA.
Marostegui claimed this task.May 12 2022, 7:26 AM
Marostegui added a project: DBA.

So the issue is that some wikis (T119154) have echo tables locally and not on x1. Those wikis have a different schema and they are missing the echo_event_page_id index.
I will add that index to

labswiki
mediawikiwiki
metawiki
officewiki

Marostegui triaged this task as High priority.May 12 2022, 7:27 AM
Marostegui moved this task from Triage to In progress on the DBA board.
Marostegui added a project: Schema-change-in-production.
Marostegui updated the task description. (Show Details)
Stashbot added a comment.May 12 2022, 7:29 AM

Mentioned in SAL (#wikimedia-operations) [2022-05-12T07:29:12Z] <marostegui> dbmaint s3@eqiad T308206

Stashbot added a comment.May 12 2022, 7:29 AM

Mentioned in SAL (#wikimedia-operations) [2022-05-12T07:29:17Z] <marostegui> dbmaint s3@codfw T308206

Marostegui updated the task description. (Show Details)May 12 2022, 7:30 AM
Marostegui updated the task description. (Show Details)
Stashbot added a comment.May 12 2022, 7:32 AM

Mentioned in SAL (#wikimedia-operations) [2022-05-12T07:32:19Z] <marostegui> dbmaint s6@codfw T308206

Stashbot added a comment.May 12 2022, 7:32 AM

Mentioned in SAL (#wikimedia-operations) [2022-05-12T07:32:23Z] <marostegui> dbmaint s6@eqiad T308206

Marostegui updated the task description. (Show Details)May 12 2022, 7:32 AM
Stashbot added a comment.May 12 2022, 7:33 AM

Mentioned in SAL (#wikimedia-operations) [2022-05-12T07:33:55Z] <marostegui> dbmaint s7@codfw T308206

Marostegui updated the task description. (Show Details)May 12 2022, 7:34 AM
Marostegui added a comment.EditedMay 12 2022, 7:37 AM

s7 eqiad

  • dbstore1003
  • db1181
  • db1174
  • db1171
  • db1170
  • db1158
  • db1136
  • db1127
  • db1101
  • db1098
Marostegui closed this task as Resolved.May 12 2022, 7:40 AM
Marostegui updated the task description. (Show Details)

Index added everywhere, and the query works fine on all the above reported wikis.

Marostegui added a parent task: T282857: Automate the check and fix of object, schema and data drifts between mediawiki HEAD, production masters and replicas.May 12 2022, 7:41 AM
Marostegui added a parent task: T104459: Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas.
Marostegui removed a parent task: T282857: Automate the check and fix of object, schema and data drifts between mediawiki HEAD, production masters and replicas.
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