"2062 Read timeout is reached" DBQueryError when trying to load specific users' watchlists (with +1000 articles) on several wikis
Open, HighPublic

Description

With a large number of watched articles (more than 700), the watchlist is loaded for a very long time. If articles are more than 1000, it does not load.

"Database error: An error has occurred in the database, this may indicate a software error. An unrecoverable exception of the type" Wikimedia\Rdbms\DBQueryError "

Related Objects

There are a very large number of changes, so older changes are hidden. Show Older Changes
Aklapper renamed this task from "2062 Read timeout is reached" DBQueryError when trying to load specific users' watchlists (with +1000 articles) on ruwiki to "2062 Read timeout is reached" DBQueryError when trying to load specific users' watchlists (with +1000 articles) on several wikis.
Aklapper added a subscriber: Darwinius.

@Aklapper I am 99% sure T171898 is a duplicate, too, although I am unsure in which way to merge?

@Catrope Are you working on this? I am not asking you to do it, just in negative option, I will try to escalate it for someone else to help, as my changes were only mitigations and not intended as proper fixes (and they do not apply to commons).

Pyb added a subscriber: Pyb.Aug 24 2017, 11:46 AM
KTC added a subscriber: KTC.Aug 24 2017, 1:00 PM

@Catrope Are you working on this? I am not asking you to do it, just in negative option, I will try to escalate it for someone else to help, as my changes were only mitigations and not intended as proper fixes (and they do not apply to commons).

He's out until September, so no.

I did a followup check, and even a no-frills query (simplified version of above) with just recentchanges, watchlist, and page (no ORES, no FlaggedRevs, no change_tags) still scans over 130,000 rows.

This is consistent with the discussion above:

1mysql:wikiadmin@db1093 [ruwiki]> EXPLAIN SELECT rc_id,rc_timestamp,rc_user,rc_user_text,rc_namespace,rc_title,rc_comment,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,wl_notificationtimestamp,page_latest FROM `recentchanges` INNER JOIN `watchlist` ON (wl_user = '529055' AND (wl_namespace=rc_namespace) AND (wl_title=rc_title)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) WHERE (rc_type != '6') AND (rc_source != 'wb') AND (rc_timestamp > '20170716043245') AND ((rc_type != 3) OR ((rc_deleted & 1) != 1)) ORDER BY rc_timestamp DESC LIMIT 1000 \G
2*************************** 1. row ***************************
3id: 1
4select_type: SIMPLE
5table: watchlist
6type: ref
7possible_keys: wl_user,namespace_title,wl_user_notificationtimestamp
8key: wl_user
9key_len: 4
10ref: const
11rows: 138546
12Extra: Using temporary; Using filesort
13*************************** 2. row ***************************
14id: 1
15select_type: SIMPLE
16table: recentchanges
17type: ref
18possible_keys: rc_timestamp,rc_namespace_title,rc_ns_usertext,tmp_3,rc_name_type_patrolled_timestamp
19key: rc_namespace_title
20key_len: 261
21ref: ruwiki.watchlist.wl_namespace,ruwiki.watchlist.wl_title
22rows: 13
23Extra: Using where
24*************************** 3. row ***************************
25id: 1
26select_type: SIMPLE
27table: page
28type: eq_ref
29possible_keys: PRIMARY
30key: PRIMARY
31key_len: 4
32ref: ruwiki.recentchanges.rc_cur_id
33rows: 1
34Extra:
353 rows in set (0.00 sec)

The original query is not that slow on Terbium right now (3.84 sec). I guess that is improved by @jcrespo assigning the better server.

@Trizek-WMF, should the deployment of advanced filters to watchlist (planned Sep 5th) be postponed until this is solved? The beta feature is slow as it is, deploying it over this issue might make the symptoms worse for affected users.

The original query is not that slow on Terbium right now (3.84 sec). I guess that is improved by @jcrespo assigning the better server.

Yes, but a) that server is not supposed to be there, it is taking away resources from other projects b) It only applies to ruwiki/frwiki/jawiki c) 4 seconds on that beast of a server is seriously slow, and a recipe for pileups d) it will be slower when it has to scratch disk (not on memory) e) I think we can make it 10-100x better on software. A 4 second query would be ok as a jobque/maintanace/analytics query, but not at all as an interactive/http request generated query.

@Trizek-WMF, should the deployment of advanced filters to watchlist (planned Sep 5th) be postponed until this is solved? The beta feature is slow as it is, deploying it over this issue might make the symptoms worse for affected users.

It depends if the problem is fixed or not before the release date.

Any idea of a possible schedule to have a fix on this issue?

Restricted Application added a subscriber: jeblad. · View Herald TranscriptFri, Aug 25, 8:19 AM
AlexRus removed a subscriber: AlexRus.Fri, Aug 25, 8:23 AM

Yes, but a) that server is not supposed to be there, it is taking away resources from other projects b) It only applies to ruwiki/frwiki/jawiki c) 4 seconds on that beast of a server is seriously slow, and a recipe for pileups d) it will be slower when it has to scratch disk (not on memory) e) I think we can make it 10-100x better on software. A 4 second query would be ok as a jobque/maintanace/analytics query, but not at all as an interactive/http request generated query.

I agree. I wasn't saying "Everything's fine" (in fact, I did some investigation on it), just adding context.

jeblad removed a subscriber: jeblad.Fri, Aug 25, 9:33 PM

TLDR, but I wanted to make sure folks working on this know that we're planning soon to paginate the Watchlist. In some cases, I imagine that might change the loading time, since it will enable us to impose a fixed limit on the number of results per pate. Tickets https://phabricator.wikimedia.org/T163429 and https://phabricator.wikimedia.org/T164136 and https://phabricator.wikimedia.org/T164134

Johan added a subscriber: Johan.Thu, Aug 31, 11:01 AM

This has been marked with the user-notice tag. A couple of questions:

Are other wikis than ruwiki/frwiki/jawiki affected? To which degree is this still a problem (right now, I know the solution is an imperfect, temporary fix)?

@Johan Yes, at least rowiki and commonswiki are affected, which means that very likely all the wikis from the same group (using the same server) as those 2 are affected. For these groups no workaround has been put in place, so the issue is still a problem for many experienced users.

Fae awarded a token.Mon, Sep 4, 12:26 PM
Fae added a subscriber: Fae.
Jmabel added a subscriber: Jmabel.Mon, Sep 4, 4:15 PM

I've had this affect me almost every time I try to access my watchlist in the last month. You may say it doesn't affect many users, but I say that the ones it affects are precisely those of us who are doing the most work.

Jmabel awarded a token.Mon, Sep 4, 4:16 PM

On ca.wiki some users report it is solved restoring all default settings in preferences.

I don't see "Unbreak now" priority here due to the number of affected users and workarounds available. Please see
https://www.mediawiki.org/wiki/Phabricator/Project_management#Setting_task_priorities

@Aklapper, considering the significant increase in reports and wikis affected, do you think we could have this as "unbreak now"?

Liuxinyu970226 added a comment.EditedFri, Sep 8, 12:23 PM

I'm sorry but @Strainu there's another task that affects globally but not UBN (T95309, it's under Low priority), it also has 6 duplicated bugs

Bawolff added a subscriber: Bawolff.Sat, Sep 9, 8:57 PM
Josve05a added a subscriber: Josve05a.
Bawolff added a comment.EditedSun, Sep 10, 1:09 AM

So to summarize this bug [correct me if I'm wrong on any of this]:

  • Watchlist has always been a resource hog, but recently has been pushed over the edge to where a not insignificant minority of users cannot use their watchlist
  • Based on people who are complaining at commons, problems seem to start to arise with watchlists > 20,000 entries.
  • ORES has made the query significantly more complicated (by joining against 4 additional tables. However each join should only touch one additional row, I think). This might have pushed things slightly to be over the edge, but if so, is probably not at fault so much as the straw that broke the cammel's back.
    • This does not however apply to commons, where ORES is disabled
  • Wikidata has made this query much worse by storing all the wikibase change notifications in the recent changes table. For example, in the last 2 days on commons:
mysql:wikiadmin@db1081 [commonswiki]> select count(*),rc_source from recentchanges where rc_timestamp > '20170908000000' group by rc_source limit 50;
+----------+---------------+
| count(*) | rc_source     |
+----------+---------------+
|   436572 | mw.categorize |
|   123881 | mw.edit       |
|    45326 | mw.log        |
|     5617 | mw.new        |
|  3213150 | wb            |
+----------+---------------+
5 rows in set (10.14 sec)

Not that long ago, wb and mw.categorize would not have existed. So we went from 174,824 entries to 3,824,546 for a 2 day period in recentchanges. This means changing the join order to make recentchanges the primary table, is no longer acceptable even for people with a million entries on their watchlist (Yes one of the people on that thread has just short of a million entries on their watchlist).

So where do we go from here?

The solutions proosed on this bug so far have been:

  • Stricter LIMIT's/paging in watchlist - This is pretty inrrelavent on a filesort query
  • Throw hardware at the problem. Some success there, but that's really not a long term solution, or even really a near term one.
  • Disable various things like ORES to simplify the query as a starting point - Sure that might make sense as a starting point, although I don't really think ORES is much at fault here.
  • extend recentchange name_page index to be name_page_recentchanges so as to be able to do range queries in the join. I'm doubtful that will help all that much in the commons case, where pages with large number of edits is rare. Maybe it'd help in 'pedia, but even still, probably not by that much. Still may be worth doing.

I doubt any of those will really yield results. As jcrespo said we need to fix the query.

So how do we fix the query. Ideas?

  • Watchlist has always been an evil query. AFAIK one of the original points of special slaves was to isolate watchlist from the rest of cluster so it didn't topple everything. Maybe there is a technology other than relation db that would be better for this Many-to-many join plus sort thing. It is in a sense similar to category intersection - at the risk of sounding crazy, maybe elastic search or something would work better for this. This is obviously a long term idea.
  • Either fetching wl_notifcation_timestamp separately, or plopping it on to the end of the index to use a covering index (Probably some speedup, but not significant enough)
  • Only SELECTing rc_id (fetching other fields later) and using rc_id instead of rc_timestamp for the date range and ORDER BY - with the idea being to minimize the size of the temporary table (so its more likely to fit in memory/minimize IO), and better fetching performance since we're sorting based on how the rows are ordered in the db (? This goes a little beyond my understanding of how dbs work, I might be misunderstanding something here).
  • Disable showing wikidata in watchlist. This is probably unacceptable.
  • Given that rc seems to be flooded with wikidata entries (I'm assuming this is causing problems for two reasons - less data locality because the data is more spread out from all the extra data, and more filtering as each (rc_namespace,rc_title) pair might have more edits than previously) perhaps we should look into partitioning the recentchanges table by whether or not rc_source = 'wb'? Or at least an index where the first part is whether or not the entry is wikidata (This is made more complicated as there are many types possible in rc_source / rc_type, and we only want to spearate based on whether or not rc_source = 'wb' but we probably don't want to be doing range queries here).
  • The query scales with the size of Recentchanges table and the size of watchlist. Maybe we reduce $wgRCMaxAge. this would probably piss off users. I'm also not sure how effective it would be with wikidata taking over the rc table.
  • Trying to reduce the number of fields returned, using ints instead of page titles, etc might reduce memory usage of the temp table, which may significantly speed things up. This is hard to do with the current schema, and also I'm unclear if this would actually help anything.
  • Most users with large watchlists don't enable "show categorization" or "show wikidata". People with large watchlists also probably don't need all 30 days of the RC table (Or do they. I'm not really sure, especially if filtering for patrolled). We could create a table that has a summary of the recentchanges table, with unneeded fields removed, no wikibase, no categorization, maybe only last 7 days of changes, in order for it to be small. This is in the hope that more of the table would fit in memory making queries faster, and maybe for people with insanely large watchlists, the table would be small enough that the opposite table join order would be fessible. This seems very ugly to me, but is imo the most promising of all the ideas I am aware of so far.

@jcrespo: Any other thoughts on what could be done to make the db happy for watchlists? Does any of these ideas sound sane? If so which one?

Strainu added a comment.EditedSun, Sep 10, 2:32 PM
  • Based on people who are complaining at commons, problems seem to start to arise with watchlists > 20,000 entries.

More like 2K, not 20K. This really affects more people than originally thought, but I guess that kind of information tends to get lost in the many replies and duplicate bugs.

  • Based on people who are complaining at commons, problems seem to start to arise with watchlists > 20,000 entries.

More like 2K, not 20K. This really affects more people than originally thought, but I guess that kind of information tends to get lost in the many replies and duplicate bugs.

:are you referring to Răzvan Socol (WZ0f2wpAMCYAAEJrINAAAAAP)? Because that was attempting to delete his watchlist, which is slightly different from viewing the watchlist (nonetheless taking more than 4 seconds to delete only 2700 rows seems excessively long. Maybe that server was just overburdened at the time(?)). In any case i think ill unmerge that bug as the reading and writing issues will need separate fixes i think.

Indeed, I have about 2700 pages in my watchlist and I was affected by both bugs (the timeout when displaying Special:Watchlist and the timeout when attempting to delete a lot of items from the watchlist).
In the last few days, the display of Special:Watchlist stated to work, but very slow (about 15 to 50 seconds) with a message saying the changes over the last N seconds were not displayed due to the busy server.
When I first viewed Special:Watchlist today, it also worked slow (about 15 seconds), but refreshing the page was fast.

I guess throwing more hardware to the problem helped: after reducing the initial query time to less than 60 seconds, the subsequent queries used some cached some data. But I believe something still should be done, especially for the other users with larger watchlists. I'm not familiar with the possible optimizations of a MySQL query, but if that would have been a Microsoft SQL Server, I would have used some join hints or index hints to force a specific execution plan. If that did not work, other options would be indexed views and breaking the query into smaller steps (using temporary tables).

Yamaha5 removed a subscriber: Yamaha5.Sun, Sep 10, 5:56 PM
Bawolff added a comment.EditedSun, Sep 10, 8:48 PM
  • Wikidata has made this query much worse by storing all the wikibase change notifications in the recent changes table. For example, in the last 2 days on commons: ` mysql:wikiadmin@db1081 [commonswiki]> select count(*),rc_source from recentchanges where rc_timestamp > '20170908000000' group by rc_source limit 50; +----------+---------------+ | count(*) | rc_source | +----------+---------------+ | 436572 | mw.categorize | | 123881 | mw.edit | | 45326 | mw.log | | 5617 | mw.new | | 3213150 | wb | +----------+---------------+ 5 rows in set (10.14 sec) ` Not that long ago, wb and mw.categorize would not have existed. So we went from 174,824 entries to 3,824,546 for a 2 day period in recentchanges. This means changing the join order to make recentchanges the primary table, is no longer acceptable even for people with a million entries on their watchlist (Yes one of the people on that thread has just short of a million entries on their watchlist).

To go further on that point:

I definitely think wikidata perhaps with some help from category watchlist feature are at fault here.

According to https://logstash.wikimedia.org/goto/79fdea023284e931cbb0b7f8130d1fc3 (prior to adding the new slave). The top 4 wikis where this is happening are commons, rowiki, ruwiki, itwiki.

Compare how the breakdown of real edits to wikidata edits (wb=wikidata edits) over 1 week period is for this wikis this is affecting compared to similar size wikis that aren't affected:

rowiki

MariaDB [rowiki_p]> select count, percent, IFNULL(rc_source,"TOTAL") 'rcsource' from (select count(*) 'count', count(*)/(SELECT count(*) from recentchanges where rc_timestamp between '20170903000000' and '20170910000000' )*100 as 'percent', rc_source from recentchanges where   rc_timestamp between '20170903000000' and '20170910000000' group by 3 WITH Rollup  limit 40) as t order by 1;
+---------+----------+---------------+
| count   | percent  | rcsource      |
+---------+----------+---------------+
|     376 |   0.0374 | mw.log        |
|     477 |   0.0474 | mw.new        |
|    3306 |   0.3288 | mw.categorize |
|    6540 |   0.6504 | mw.edit       |
|  994762 |  98.9359 | wb            |
| 1005461 | 100.0000 | TOTAL         |
+---------+----------+---------------+
6 rows in set (6.24 sec)

vs kowiki which is similar size (Actually a bit bigger) but is not encountering the watchlist issue:

MariaDB [kowiki_p]> select count, percent, IFNULL(rc_source,"TOTAL") 'rcsource' from (select count(*) 'count', count(*)/(SELECT count(*) from recentchanges where rc_timestamp between '20170903000000' and '20170910000000' )*100 as 'percent', rc_source from recentchanges where   rc_timestamp between '20170903000000' and '20170910000000' group by 3 WITH Rollup  limit 40) as t order by 1;
+-------+----------+---------------+
| count | percent  | rcsource      |
+-------+----------+---------------+
|  1255 |   1.3699 | mw.new        |
|  1371 |   1.4965 | mw.log        |
|  9982 |  10.8960 | mw.categorize |
| 38614 |  42.1495 | wb            |
| 40390 |  44.0881 | mw.edit       |
| 91612 | 100.0000 | TOTAL         |
+-------+----------+---------------+
6 rows in set (2.36 sec)

Despite the fact that kowiki has almost 10 times the edit, it still has a tenth of the rc entries that rowiki has due, due to flooding from wikidata.

Commons also is mostly flooded with wikidata:
Total rc entries in that time period was 23,978,989. but my other queries timed out when i tried to filter out wikidata :(. Over shorter time periods, there was an order of magnitude more wikidata than any other type of edit.

ruwiki:

MariaDB [ruwiki_p]> select count, percent, IFNULL(rc_source,"TOTAL") 'rc_source' from (select count(*) 'count', count(*)/(SELECT count(*) from recentchanges where rc_timestamp between '20170903000000' and '20170910000000' )*100 as 'percent', rc_source from recentchanges where   rc_timestamp between '20170903000000' and '20170910000000' group by 3 WITH Rollup  limit 40) as t order by 1;
+---------+----------+---------------+
| count   | percent  | rc_source     |
+---------+----------+---------------+
|      20 |   0.0003 | flow          |
|    7090 |   0.0963 | mw.new        |
|   71842 |   0.9760 | mw.categorize |
|  125060 |   1.6990 | mw.edit       |
|  136376 |   1.8527 | mw.log        |
| 7020458 |  95.3757 | wb            |
| 7360846 | 100.0000 | TOTAL         |
+---------+----------+---------------+
7 rows in set (6 min 29.41 sec)

itwiki:

MariaDB [itwiki_p]> select count, percent, IFNULL(rc_source,"TOTAL") 'rc_source' from (select count(*) 'count', count(*)/(SELECT count(*) from recentchanges where rc_timestamp between '20170903000000' and '20170910000000' )*100 as 'percent', rc_source from recentchanges where   rc_timestamp between '20170903000000' and '20170910000000' group by 3 WITH Rollup  limit 40) as t order by 1;
+---------+----------+---------------+
| count   | percent  | rc_source     |
+---------+----------+---------------+
|    4197 |   0.1950 | mw.log        |
|    9830 |   0.4566 | mw.new        |
|  148341 |   6.8911 | mw.categorize |
|  171744 |   7.9783 | mw.edit       |
| 1818539 |  84.4790 | wb            |
| 2152651 | 100.0000 | TOTAL         |
+---------+----------+---------------+
6 rows in set (1 min 16.01 sec)

vs spanish, which is not affected but similar in size to russian and italian:

MariaDB [eswiki_p]> select count, percent, IFNULL(rc_source,"TOTAL") 'rc_source' from (select count(*) 'count', count(*)/(SELECT count(*) from recentchanges where rc_timestamp between '20170903000000' and '20170910000000' )*100 as 'percent', rc_source from recentchanges where   rc_timestamp between '20170903000000' and '20170910000000' group by 3 WITH Rollup  limit 40) as t order by 1;
+--------+----------+---------------+
| count  | percent  | rc_source     |
+--------+----------+---------------+
|   8535 |   2.0968 | mw.log        |
|   8638 |   2.1221 | mw.new        |
|  26850 |   6.5962 | mw.categorize |
| 152139 |  37.3758 | mw.edit       |
| 210890 |  51.8091 | wb            |
| 407052 | 100.0000 | TOTAL         |
+--------+----------+---------------+
6 rows in set (8.01 sec)

Also of interest, enwiki which is reporting some occasional issues of this sort, but not near as much as the other wikis. It has quite a bit lower number of wikidata entries:

MariaDB [enwiki_p]> select count, percent, IFNULL(rc_source,"TOTAL") 'rc_source' from (select count(*) 'count', count(*)/(SELECT count(*) from recentchanges where rc_timestamp between '20170903000000' and '20170910000000' )*100 as 'percent', rc_source from recentchanges where   rc_timestamp between '20170903000000' and '20170910000000' group by 3 WITH Rollup  limit 40) as t order by 1;
+---------+----------+---------------+
| count   | percent  | rc_source     |
+---------+----------+---------------+
|   55385 |   2.5381 | mw.new        |
|   78058 |   3.5771 | mw.log        |
|  449261 |  20.5878 | wb            |
|  550539 |  25.2289 | mw.categorize |
| 1048931 |  48.0682 | mw.edit       |
| 2182174 | 100.0000 | TOTAL         |
+---------+----------+---------------+
6 rows in set (8.13 sec)

You can see a clear pattern forming, where this issue seems to present on wikis that are flooded with wikidata rc entries. I believe the extra entries are preventing the query plan for large watchlists where recentchanges is the primary table being used, and thus making a lot of large temporary tables (presumably putting extra pressure on memory and io) where previously a temporary table would not be used, especially on a small wiki like rowiki where previously a weeks worth of recentchanges was only 7000 rows (vs a million now).

Strainu added a comment.EditedSun, Sep 10, 9:47 PM

:are you referring to Răzvan Socol (WZ0f2wpAMCYAAEJrINAAAAAP)? Because that was attempting to delete his watchlist, which is slightly different from viewing the watchlist (nonetheless taking more than 4 seconds to delete only 2700 rows seems excessively long. Maybe that server was just overburdened at the time(?)). In any case i think ill unmerge that bug as the reading and writing issues will need separate fixes i think.

Also @Andrei_Stroe reported seeing the issue with 3K pages in the watchlist. However, if Wikidata is to blame, then probably the limit is unimportant since it varies according to the level of activity from Wikidata more than anything else.

What I don't understand is why there are such wild variations in the number of Wikidata entries between wikis. For instance, virtually all ro.wp articles should also be in en.wp, which in turn should mean that all wikidata entries appearing in ro.wp should appear in en.wp. However, the count is double for ro.wp compared to en.wp.

A possible solution could be to segment the Wikidata RC feed even further. For instance, I have it disabled because I don't care about all the changes in items linked from the item of my followed pages, so I find it's polluting the feed. Perhaps changing the default to only showing the changing in the pages' items only would improve the current situation.

Bawolff added a comment.EditedMon, Sep 11, 4:06 AM

:are you referring to Răzvan Socol (WZ0f2wpAMCYAAEJrINAAAAAP)? Because that was attempting to delete his watchlist, which is slightly different from viewing the watchlist (nonetheless taking more than 4 seconds to delete only 2700 rows seems excessively long. Maybe that server was just overburdened at the time(?)). In any case i think ill unmerge that bug as the reading and writing issues will need separate fixes i think.

Also @Andrei_Stroe reported seeing the issue with 3K pages in the watchlist. However, if Wikidata is to blame, then probably the limit is unimportant since it varies according to the level of activity from Wikidata more than anything else.

I think there might be some confusion in watchlist numbers, as to whether you count subject page + talk page as 1 entry or 2 entries. In either case 3k or 6k is small enough that I think its reasonable for users to expect a watchlist of that size to work.

I believe that the the scalability of the query varies both with the size of the user's watchlist and the size of the recentchanges table. I think both have a significant effect, but I actually think the effect of the user's watchlist size is probably much greater on scalability than size of recentchanges table. Its just that watchlist sizes have remained fairly constant, well recentchanges table size has increased dramatically.

What I don't understand is why there are such wild variations in the number of Wikidata entries between wikis. For instance, virtually all ro.wp articles should also be in en.wp, which in turn should mean that all wikidata entries appearing in ro.wp should appear in en.wp. However, the count is double for ro.wp compared to en.wp.

Its how wikidata is used in infoboxes. en wikipedia is kind of skeptical of using wikidata in infoboxes, so there is less wikidata entries there. Commons for example uses author infoboxes all over the place that use wikidata. So for example, this single edit on wikidata https://www.wikidata.org/w/index.php?title=Q641&diff=prev&oldid=555165074 adjusting the population of Venice creates 1600 recentchange entries on commons, one for every creatorbox where the creator was born/died/worked in venice. (This is really wasteful because the population property isn't even used on commons in boxes like {{Creator:Sebastiano_Ricci}}. Perhaps wikidata devs could add an api for fetching only the relavent statements, and an eu_aspect value to represent which specific property was used. This would probably significantly reduce pressure on the recentchanges table. Not to mention making watchlists more useful for people who want to see wikidata changing things, but don't care about properties not used on the local page [ancedotally I've heard lots of users complain that showing wikidata edits on their watchlist is too noisy to be useful]). [EDIT: This is T151717 ] [ *EDIT2*: At least in the commons case, its largely about getting labels in the non-default language, so T172914 is actually what is most needed here]

I would also note that Venice is a random example, not an example meant to demonstrate the problem. For the worst example - each edit to Q54919 -'Virtual International Authority File' will cause 2,758,301 rc entries on commons (Assuming there is no cut-off i am unaware of), and it is not unusual to edit that page - there were 11 edits to it in july.

A possible solution could be to segment the Wikidata RC feed even further. For instance, I have it disabled because I don't care about all the changes in items linked from the item of my followed pages, so I find it's polluting the feed. Perhaps changing the default to only showing the changing in the pages' items only would improve the current situation.

Its more that the entries are in the table so the db has to sort through them regardless of if it shows them to you in your watchlist. One possible way forward would be to use a different method of storing wikidata changes so its more segmented, or have a more specialized version of the recentchanges table that doesn't include the wikidata stuff so that the people who don't want to see it aren't bogged down by it.

Jmabel added a comment.EditedMon, Sep 11, 5:13 AM

virtually all ro.wp articles should also be in en.wp

Strainu, according to Darren Gergle's research, that is much less the case than you would think. But I'm pretty sure that Bawolff's remarks about infoboxes etc. are even more on point.

Bawolff added a comment.EditedMon, Sep 11, 6:55 AM

Ok, so my proposal for optimizing the watchlist query would be to change (This is based on commons):

SELECT  rc_id,rc_timestamp,rc_user,rc_user_text,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_comment AS `rc_comment_text`,NULL AS `rc_comment_data`,NULL AS `rc_comment_cid`,page_latest,wl_notificationtimestamp,(SELECT  GROUP_CONCAT(ct_tag SEPARATOR ',')  FROM `change_tag`    WHERE ct_rc_id=rc_id  ) AS `ts_tags` 
FROM `recentchanges` INNER JOIN `watchlist` ON (wl_user = '38908' AND (wl_namespace=rc_namespace) AND (wl_title=rc_title))
LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE (rc_type != '6') AND (rc_source != 'wb') AND ((rc_this_oldid=page_latest) OR rc_type IN ('3','142') ) AND (rc_timestamp > '20170908004051') AND ((rc_type != 3) OR ((rc_deleted & 9) != 9)) 
ORDER BY rc_timestamp DESC LIMIT 60;

Into 2 steps. First:

SELECT rc_id 
FROM `recentchanges`
INNER JOIN `watchlist` ON (wl_user = '611' AND (wl_namespace=rc_namespace) AND (wl_title=rc_title))
LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE (rc_type != '6') AND (rc_source != 'wb') AND ((rc_this_oldid=page_latest) OR rc_type IN ('3','142') ) AND (rc_id > 912664338) AND ((rc_type != 3) OR ((rc_deleted & 9) != 9)) 
ORDER BY rc_id DESC LIMIT 60;

To get a list of id numbers. Followed by a trivial query for SELECT <all various rc fields> FROM recentchanges LEFT JOIN page ... WHERE rc_id in (12345,324324,234343,...) ORDER BY rc_id desc;
(Perhaps falling back to the old query in cases where extensions do something weird, or weird cases with tag filtering).

The key differences are:

  • Only have rc_id in the SELECT field. The goal is to minimize the size of the records in the temporary table, thus making large temporary tables more likely to fit in memory, reducing I/O needed to move around the data, etc. (It is unclear to me that in cases of a using temporary with a simplish ORDER BY and a LIMIT, if mariadb is smart enough to insert things into temporary table in sorted order and drop rows from the temporary table as its being populated if its clear that the row would sort below what would be returned by the LIMIT, thus keeping temp table size down. If mariadb is smart enough to do that, this would probably have a little effect, but not much. If mariadb is not smart enough, I think this could have a big effect as the temporary table could have hundreds of thousands of rows, and might even end up being written to disk in worst case)
    • Additionally, removing wl_notification_timestamp from the select fields means that a covering index can be used for the watchlist table. I suspect this will help things as we often need to fetch many rows (Usually in the neighbourhood of 20,000-200,000 rows for power users) from the watchlist table.
  • ORDER BY rc_id instead of rc_timestamp. Id is monotonically increasing, so this should be the same, and would hopefully help with the above point with keeping record size down in the temporary table.
  • Using rc_id > NUMBER instead of rc_timestamp > SOMEDATE - (with the id number fetched in a separate query) This allows us to use index condition pushdown, since rc_id is on the end of the name_time index. (This is basically @Catrope 's idea about appending rc_timestamp on the end of the index, but without the schema change.) This should reduce the number of full rows read somewhat. Unclear how much of an effect there will be as it depends on the distribution of edits vs unique pages on a wiki, but I definitely think it will help things.
  • Remove change_tag dependent subquery if we are not filtering based on tag. If we don't need it to filter, that's one more thing we can push out of the expensive query and into a second query

I did some testing of some user's who had ~100,000 entries on their watchlist using a LIMIT of 60. The old query took around 2.15 seconds, the new one about 0.6 seconds (I wasn't using the watchlist slave, I guess that'd be why even when I do the original query its orders of magnitude faster than what users were reporting on wiki). This is not a ground breaking improvement, but still a significant one. I believe this is the best we can do optimizing the watchlist query without totally overhauling the watchlist system (If anyone has any other ideas?)

I think if we can use this new optimized query, and get T151717 deployed - this would solve the immediate problem for most users (I should emphasize I'm guessing here). For the long term, I think we need to reconsider how we do watchlists. They are basically the sorted intersection of two large lists. I believe we should look into generating them via elasticsearch (I say that without knowing much about elasticsearch).

The explain's for the original query and the new proposed query are respectively:

Original query:

mysql:wikiadmin@db1084 [commonswiki]> explain  SELECT  rc_id,rc_timestamp,rc_user,rc_user_text,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_comment AS `rc_comment_text`,NULL AS `rc_comment_data`,NULL AS `rc_comment_cid`,page_latest,wl_notificationtimestamp,(SELECT  GROUP_CONCAT(ct_tag SEPARATOR ',')  FROM `change_tag`    WHERE ct_rc_id=rc_id  ) AS `ts_tags`  FROM `recentchanges` INNER JOIN `watchlist` ON (wl_user = '611' AND (wl_namespace=rc_namespace) AND (wl_title=rc_title)) LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE (rc_type != '6') AND (rc_source != 'wb') AND ((rc_this_oldid=page_latest) OR rc_type IN ('3','142') ) AND (rc_timestamp > '20170908004051') AND ((rc_type != 3) OR ((rc_deleted & 9) != 9))  ORDER BY rc_timestamp DESC LIMIT 60 \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: watchlist
         type: ref
possible_keys: wl_user,namespace_title,wl_user_notificationtimestamp
          key: wl_user_notificationtimestamp
      key_len: 4
          ref: const
         rows: 564914
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: recentchanges
         type: ref
possible_keys: rc_timestamp,rc_namespace_title,rc_ns_usertext,tmp_3,rc_name_type_patrolled_timestamp
          key: rc_namespace_title
      key_len: 261
          ref: commonswiki.watchlist.wl_namespace,commonswiki.watchlist.wl_title
         rows: 4
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: page
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: commonswiki.recentchanges.rc_cur_id
         rows: 1
        Extra: Using where
*************************** 4. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: change_tag
         type: ref
possible_keys: ct_rc_id,change_tag_rc_tag
          key: ct_rc_id
      key_len: 5
          ref: commonswiki.recentchanges.rc_id
         rows: 1
        Extra: Using index
4 rows in set (0.00 sec)

Actual query took 2.28 seconds (Stayed within 0.1 seconds of that during three runs)

Handler statistics:

+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Handler_commit             | 1      |
| Handler_delete             | 0      |
| Handler_discover           | 0      |
| Handler_external_lock      | 0      |
| Handler_icp_attempts       | 0      |
| Handler_icp_match          | 0      |
| Handler_mrr_init           | 0      |
| Handler_mrr_key_refills    | 0      |
| Handler_mrr_rowid_refills  | 0      |
| Handler_prepare            | 0      |
| Handler_read_first         | 0      |
| Handler_read_key           | 226393 |
| Handler_read_last          | 0      |
| Handler_read_next          | 452471 |
| Handler_read_prev          | 0      |
| Handler_read_retry         | 0      |
| Handler_read_rnd           | 60     |
| Handler_read_rnd_deleted   | 0      |
| Handler_read_rnd_next      | 352    |
| Handler_rollback           | 0      |
| Handler_savepoint          | 0      |
| Handler_savepoint_rollback | 0      |
| Handler_tmp_update         | 0      |
| Handler_tmp_write          | 349    |
| Handler_update             | 0      |
| Handler_write              | 0      |
+----------------------------+--------+
26 rows in set (0.15 sec)

proposed query:

mysql:wikiadmin@db1084 [commonswiki]> explain   SELECT rc_id  FROM `recentchanges` INNER JOIN `watchlist` ON (wl_user = '611' AND (wl_namespace=rc_namespace) AND (wl_title=rc_title)) LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE (rc_type != '6') AND (rc_source != 'wb') AND ((rc_this_oldid=page_latest) OR rc_type IN ('3','142') ) AND (rc_id > 912664338) AND ((rc_type != 3) OR ((rc_deleted & 9) != 9))  ORDER BY rc_id DESC LIMIT 60 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: watchlist
         type: ref
possible_keys: wl_user,namespace_title,wl_user_notificationtimestamp
          key: wl_user
      key_len: 4
          ref: const
         rows: 629996
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: recentchanges
         type: ref
possible_keys: PRIMARY,rc_namespace_title,rc_ns_usertext,tmp_3,rc_name_type_patrolled_timestamp
          key: rc_namespace_title
      key_len: 261
          ref: commonswiki.watchlist.wl_namespace,commonswiki.watchlist.wl_title
         rows: 4
        Extra: Using index condition; Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: page
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: commonswiki.recentchanges.rc_cur_id
         rows: 1
        Extra: Using where
3 rows in set (0.00 sec)

Query took 1.6 seconds on first run, and then 0.8 seconds on subsequent runs

Handler stats:

+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Handler_commit             | 1      |
| Handler_delete             | 0      |
| Handler_discover           | 0      |
| Handler_external_lock      | 0      |
| Handler_icp_attempts       | 226583 |
| Handler_icp_match          | 21989  |
| Handler_mrr_init           | 0      |
| Handler_mrr_key_refills    | 0      |
| Handler_mrr_rowid_refills  | 0      |
| Handler_prepare            | 0      |
| Handler_read_first         | 0      |
| Handler_read_key           | 226162 |
| Handler_read_last          | 0      |
| Handler_read_next          | 247919 |
| Handler_read_prev          | 0      |
| Handler_read_retry         | 0      |
| Handler_read_rnd           | 60     |
| Handler_read_rnd_deleted   | 0      |
| Handler_read_rnd_next      | 352    |
| Handler_rollback           | 0      |
| Handler_savepoint          | 0      |
| Handler_savepoint_rollback | 0      |
| Handler_tmp_update         | 0      |
| Handler_tmp_write          | 349    |
| Handler_update             | 0      |
| Handler_write              | 0      |
+----------------------------+--------+

Jcrespo thinks the idea of selecting less fields to reduce temp table size is unlikely to have very much effect. It seems like most of the gain from the other query is from ICP. Maybe there's a little bit from covering indexes, hard to tell.

I did actually start an implementation of this other query. I'm not sure if its worth continuing in light of @jcrespo 's comments, but in any case, what I had is at P5989

Bawolff added a comment.EditedMon, Sep 11, 6:33 PM

A further optimization to the query above might be to add an index, perhaps only on watchlist slaves, on (rc_namespace,rc_title,rc_type,rc_this_oldid,rc_user,rc_deleted,rc_patrolled) so that there can be a covering index on recentchanges as well ( im not sure if icp can do range type stuff or just filtering. If its the former than rc_id perhaps should be added between rc_title and rc_type. Im also assuming rc_source != 'wb' can be replaced with rc_type != 5 in the query). This assumes that that is not already the case - i think in the past we used to have more covering indexes on watchlist slaves than normal schema but im not sure thats still the case (guess i should go check... [edit: watchlist slaves do indeed use the normal mw indexes for recentchanges and watchlist])

Addendum: taking this idea even further - as i understand it, a big part of the problem here is wikidata flooding reducing data locality. To address that we could maybe add a tinyint virtual column defined as rc_source='wb' and put it at the begining of this covering index. That way all the wikidata edits would be together and separate from all the other entries in the covering index.

l

greg added a comment.Mon, Sep 11, 10:19 PM
  • Wikidata has made this query much worse by storing all the wikibase change notifications in the recent changes table.

To go further on that point:

I definitely think wikidata perhaps with some help from category watchlist feature are at fault here.

Adding Wikidata.

This is my view of the issue, based on the comments above.

Short term:

  • If the number of watchlist items for the users is less than N (N to be determined), join Watchlist -> recentchanges, then "suffer" a small in-memory sort (I belive this is the current situation)
  • Else, if the number of recentchanges is less than M, join recentchanges -> Watchlist (we may need to force the JOIN direction)
  • Else, scan recentchanges in inverse timestamp order, paged in small batches of size S, and join in the direction small recentchanges batch -> watchlist. It will not be more efficient, but it will get results faster if they are very common (and queries will not timeout).

Long term, I do not see elastic helping (it could help with advanced search, like tags/multiple filtering options, but I do not see it for live querying per user- the point is to avoid "prerendering" of watchlist and always get live results). However, I proposed in the past have a separate subsystem to track global changes and usage (for thing like wikidata and commons), separate from the main metadata databases. Polluting 85%-95% of recentchanges is a bit too much. By making it shared between projects but a different "master", maybe it could be more efficient than now, were probably lots of large recentchanges are being appended to every single project. Querying 2 services could be faster and definitely would improve the amount of writes per master, too. If that is a large scope goal, we could create a "wikibaserecentchanges" for wikibase clients, and separate the entries for each source. It will not be pretty, but at least it would allow people with a large watchlist to make rcs work disabling the "show wikidata changes".

Using STRAIGHT_JOIN and making sure an index is used more efficiently could help too (here I change the original query to order by rc_timestamp, and the explain is better, but the query is actually slower P5988#33002).

jcrespo added a comment.EditedTue, Sep 12, 10:25 AM

I am testing with a new index on dbstore1002; meanwhile I had a chat with Bawolff and he mentioned that rc used to be a small table where many indexes and inefficient scanning was possible because it was a much smaller summary of revision. Apparently with the latest additions- for example for commonswiki-, we have now a 100M row, 143GB table to scan. So separating wikidata-derived edits is still a possibility.

jcrespo added a comment.EditedTue, Sep 12, 12:48 PM

After testing some indexes, I do not see a huge improvement- we can reduce from scanning 100M rows to 18M, but there can be always a combination of query parameters that does not filter many rows on recentchanges. Paging by id (or timestamp) is the only reliable solution to make the queries in smaller batches so they do not fail:

root@dbstore1002[commonswiki]> EXPLAIN SELECT STRAIGHT_JOIN rc_id,rc_timestamp,rc_user,rc_user_text,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_comment AS `rc_comment_text`,NULL AS `rc_comment_data`,NULL AS `rc_comment_cid`,page_latest,wl_notificationtimestamp,(SELECT  GROUP_CONCAT(ct_tag SEPARATOR ',')  FROM `change_tag`    WHERE ct_rc_id=rc_id  ) AS `ts_tags`  FROM `recentchanges` INNER JOIN `watchlist` ON (wl_user = '611' AND (wl_namespace=rc_namespace) AND (wl_title=rc_title)) LEFT JOIN `page` ON ((rc_cur_id=page_id))   WHERE (rc_type != '6') AND (rc_source != 'wb') AND ((rc_this_oldid=page_latest) OR rc_type IN ('3','142') ) AND ((rc_type != 3) OR ((rc_deleted & 9) != 9)) AND rc_id BETWEEN 922007962 and 922207962 ORDER BY rc_id DESC LIMIT 60 \G

Further optimizations could be done by appending rc_id (or rc_timestamp) to existing effective indexes. Even this could be done on javascript on the background of the client so the "first byte" is not delayed so much- but I will leave the actual developers to comment on what they think is the safest option of all ideas here (separating wb updates, paging results, extra indexes, limiting the size of watchlists, but allowing multiple of them, etc.).

Thanks for the detailed analysis @Bawolff and @jcrespo . My $0.02:

Given the overwhelming number of Wikidata entries in the RC table (95% or more on some wikis, I was very surprised by that), I think that's the main problem that we should tackle. @Bawolff's split query approach is also promising, but it's kind of orthogonal as it speeds up the whole query more or less independent of the Wikidata issue, and I'm a bit concerned that the code is kind of scary and possibly fragile.

There are four main ways that I can see us tackling the Wikidata flood problem:

  1. Reducing wasteful Wikidata entires (unused languages, unused properties)
  2. Segregating Wikidata RC entries (and possibly categorization too, since that's significant on some wikis) into a separate table
  3. Adding rc_type or rc_source (which one? these two fields seem largely redundant with each other) to the start of some indexes, so that queries that don't need to look at Wikidata rows can filter them out efficiently
  4. Disable Wikidata RC on large wikis until we have a more scalable implementation of the feature

#1 is something that should be done regardless because it would make users happy (people already complain about being spammed with updates to languages they don't speak), and it sounds like it's already being worked on. I don't know how hard that is or how long it would take, but AFAIK the Wikidata team started working on it a month ago, so it might take a while yet. Every reduction in wasteful entries would only affect new entries, so it would slowly improve query performance over days or weeks after deployment, as the older generation of wasteful entries falls off the time horizons of these queries. And a very large reduction would need to be achieved to address the issue properly. Right now, Wikidata is 95% of the RC table on commons; if the number of Wikidata rows were reduced by a factor of 10, it would still be 67%, and we'd need a 21x reduction just to get below 50%. So while this seems like a good long-term plan, I worry that it wouldn't make a dent for quite some time.

#2 would require creating a new table, and would require changing the code that generates RC entries (to write them to the right table) and all the code that queries the RC table (to query the right table, or when needed to query both tables with a UNION). The latter could be done in a similar way to @Bawolff's proof of concept for the split query, by rewriting the query right before it's executed. It'd require some trickery and could be a bit fragile, but we'd get an immediate performance boost for queries that exclude Wikidata (they'd need to scan 20x fewer rows on the worst-flooded wikis) and hopefully still decent performance for queries that don't.

#3 should in theory (AIUI) have the same performance benefits as #2, but without having to change any code: if an index starting with rc_type is used, the query should behave roughly as if every type of query is in its own table. I wonder if this would work in practice though, because I don't expect that MySQL would use an index to satisfy a != condition (even if that value is 95% of the table); but maybe rc_type IN (all, other, values) would work?

@jcrespo: How well do you think an index could approximate the effect of a separate table? Would a PARTITION like we use on the revision table work, perhaps?

As for #4, that should be pretty easy to do (IIRC Wikidata RC generation is disableable, and a background script could delete old rows starting with the most recent ones) and should alleviate the issue pretty quickly too, but it would also be disruptive to people who use the feature; on the other hand, query timeouts on the watchlist are also disruptive, to a different group of people. So I think we should only do this if there isn't another way that we can get the issue under control in a reasonable amount of time.

@Catrope Unfortunately it will take quite a bit of time to research and investigate the index, and I'm not able to attend to this right now due to several other currently happening infrastructure fires going on. I already commented some possibilities and I am open to other solutions, as long as they work and are reasonable. Would anyone else available take some time to test it on beta or somewhere else with equivalent sizes/row distribution? Thanks!

mark added a project: DBA.Fri, Sep 22, 9:56 AM
mark moved this task from Triage to Backlog (help welcome) on the DBA board.

Adding rc_type or rc_source (which one? these two fields seem largely redundant with each other) to the start of some indexes, so that queries that don't need to look at Wikidata rows can filter them out efficiently

They're not fully redundant, since rc_type for Wikidata is RC_EXTERNAL (from core, thus not Wikidata-specific).

@Catrope and I discussed, and think any new indexes for this purpose should have rc_source.

We should also look into @Catrope's idea of using IN here (but rc_source IN). If this allows using the index effectively, we'd need to add a hook to know about all the rc_source's. Then PHP can convert "not Wikibase" to rc_source IN ('source1', 'source2', 'source3').

Related question, will having multiple such IN clauses (e.g. rc_source IN ('mw.log', 'mw.edit', 'flow') AND rc_source IN ('mw.log', 'mw.edit', 'mw.new' )) affect performance? The EXPLAIN is the same as if they are combined.

They're not fully redundant, since rc_type for Wikidata is RC_EXTERNAL (from core, thus not Wikidata-specific).

Afaict only wikidata uses it. Flow uses a different number. Personally i think it would have made more sense to stick with numbers and have each ext pick a unique one, but that ship has sailed...

The IN would at best be a range condition (although im not sure mariadb would see it that way because it cant tell its a contiguous range). Usually the range has to be the rightmost part of the index we're using, so i dont think that would work, as the idea is to segregate the table by putting the wikidata stuff by putting that at the leftmost part of the index. (It could maybe do index merge or we could manually UNION, but it seems like mariadb doesnt usually choose that path, and im not sure thats really ideal given how many rows the query already has to look at). I think perhaps an option here would be a virtual is wikidata column so it could be a binary condition and not a range, but itd have to be a wmf hack as mw min mysql version doesnt support virtual columns .