On working the task T203565 The root cause of different query plans were solved, but now all the hosts have bad query plans for ApiQueryRevisions::run query across S1 API hosts.
After looking it closely it seems if the optimizer got USE INDEX (page_timestamp) or IGNORE INDEX (rev_timestamp) hint, then it will came with a good query plan. See details here: T203565#4600938
Description
Description
Related Objects
Related Objects
- Mentioned In
- T203565: Reclone db1114 (s1 api) from another API host
- Mentioned Here
- T88084: Using both rvuser and rvcontinue with prop=revisions causes database error on pages with a lot of revisions
T121333: Certain prop=revisions API queries timeout with "internal_api_error_DBQueryError"
T197486: prop=revisions API timing out for a specific user and pages they edited
T203565: Reclone db1114 (s1 api) from another API host
Event Timeline
Comment Actions
This seems like it may be related to T121333, T197486, and/or T88084.
Adding IGNORE INDEX (rev_timestamp) should be pretty straightforward, since no code path through ApiQueryRevisions should be using that index. Given that you seem to be looking at a query that would be better done using page_timestamp, somewhere in this block of code seems likely although it could be put elsewhere in that method too.
Forcing page_timestamp would be less straightforward: depending on the parameters supplied, the queries in that highlighted block[1] might be best served using page_user_timestamp, page_actor_timestamp, or maybe even usertext_timestamp instead. The biggest difficulty is in the one code path where the PHP code can't really choose between page_timestamp and usertext_timestamp.[2]
[1]: And other code paths, not through that block, want PRIMARY or rev_page_id.
[2]: Ideally it'd use a "page_usertext_timestamp" index on (rev_page, rev_user_text, rev_timestamp), but that index doesn't exist. The actor work will eventually eliminate that code path, but getting to that point is still a ways off.
Comment Actions
Indeed, I think it is related to those.
However in kibana the errors were dropped: https://logstash.wikimedia.org/app/kibana#/dashboard/DBQuery?_g=h@114715d&_a=h@90b3273
Comment Actions
The fixed url for logstash shall be: https://logstash.wikimedia.org/goto/ecf245216f5c5b21346c86e60ead0f80
Comment Actions
The whole API querying has changed so drastically that it really doesn't make sense to pursue this ticket. I have been fixing issues in a separate stream.