Page MenuHomePhabricator

ApiQueryLogEvents STRAIGHT_JOIN queries slow
Closed, ResolvedPublic

Description

enwiki has been seeing a bunch of slow ApiQueryLogEvents queries:

SELECT /* ApiQueryLogEvents::execute */ /*! STRAIGHT_JOIN */ log_type, log_action, log_timestamp, log_deleted, log_id, page_id, log_user, log_user_text, user_name, log_namespace, log_title, log_comment, log_params FROM logging LEFT JOIN user ON ((user_id=log_user)) LEFT JOIN page ON ((log_namespace=page_namespace) AND (log_title=page_title)) INNER JOIN change_tag ON ((log_id=ct_log_id)) WHERE (log_type != 'suppress') AND ct_tag = 'Possible self promotion in userspace' ORDER BY log_timestamp DESC LIMIT 11

SELECT /* ApiQueryLogEvents::execute */ /*! STRAIGHT_JOIN */ log_type, log_action, log_timestamp, log_deleted, log_namespace, log_title, ts_tags FROM logging LEFT JOIN user ON ((user_id=log_user)) LEFT JOIN page ON ((log_namespace=page_namespace) AND (log_title=page_title)) LEFT JOIN tag_summary ON ((log_id=ts_log_id)) INNER JOIN change_tag ON ((log_id=ct_log_id)) WHERE (log_type != 'suppress') AND ct_tag = 'VisualEditor' ORDER BY log_timestamp DESC LIMIT 11

... and several other similar forms.

In all cases the STRAIGHT_JOIN forces an index scan on logging.times index, or more rarely a range access on logging.type_time index. Both query plans hit tens of millions of rows and take many minutes.

Removing the STRAIGHT_JOIN allows the MariaDB query optimizer to choose a plan that takes seconds. Often it includes a filesort step, but more importantly it allows "index condition pushdown" which makes the filesort cheap.


Version: 1.23.0
Severity: major

Details

Reference
bz61889

Event Timeline

bzimport raised the priority of this task from to High.Nov 22 2014, 2:53 AM
bzimport set Reference to bz61889.
bzimport added a subscriber: Unknown Object (MLST).
Springle created this task.Feb 25 2014, 2:56 AM

Change 115381 had a related patch set uploaded by Anomie:
Remove STRAIGHT_JOIN from ApiQueryLogEvents

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

Change 115381 merged by Springle:
Remove STRAIGHT_JOIN from ApiQueryLogEvents

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

bd808 moved this task from Tag to Archive on the Performance Issue board.Mar 11 2015, 7:30 PM