@Samwalton9-WMF On the estimation meeting, we reached the conclusion that this Spike should be the parent of T20790: Store information on page watcher count in the Recent Changes table and based on our findings on this task we can estimate its complexity and time.

Change #1101593 had a related patch set uploaded (by Kgraessle; author: Kgraessle):

[mediawiki/core@master] [SPIKE] Naive impl and exploration of filter RecentChanges for pages with no watchers

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

[WIP] as I need to test out option 3, but wanted to get some feedback earlier rather than later.

How could we enable this functionality?

Option #1

Store data in recent changes table.

Pros: No performance concerns
Cons: Will only capture watchers at time of record creation and will not update, requires database schema changes

Option #2

Make a page for recent changes in unwatched articles, i.e. Special:Recentchangeslinked/Special:Unwatchedpages -

Pros: Real time updates, database schema changes not needed
Cons: More engineering work, introduces cognitive load for the user, does this add anything additional that option 3 doesn't?

Option #3

Match current pattern and add sql condition to query conditions to filter pages not watched by anyone
Notes:

• naive impl not sure how this would scale in production (see patch here need to test it against prod data)

Pros: Real time updates, no database changes needed, less engineering work than option 2, no extra page to increase
cognitive load for user
Cons: Possible performance implications (need to test)

Could there be any performance concerns with edit save time or loading RecentChanges as a result?

It depends on which option we go with.

Option #1

If we go with option 1, there's pretty much no performance concerns in my opinion, but it fails to capture the data we need which
is "pages that are currently not being watched" and results in stale data being stored in the db.

Option #2

This would also not introduce any performance concerns, but would add a page that could potentially confuse people
( especially since we already have an unwatched special page for admins ).

Option #3

This definitely has potential performance concerns that I will need to test against production data with. However, this
solution requires the least amount of change to the current implementation and does not introduce a separate page.

In T380719#10394137, @Kgraessle wrote:

Option #1

Store data in recent changes table.
...
Cons: Will only capture watchers at time of record creation and will not update

I think I agree with the comment at T20790#230777 that this is OK actually!

In T380719#10394186, @Samwalton9-WMF wrote:

In T380719#10394137, @Kgraessle wrote:

Option #1

Store data in recent changes table.
...
Cons: Will only capture watchers at time of record creation and will not update

I think I agree with the comment at T20790#230777 that this is OK actually!

Yeah I did see that comment, changes fall off after 30 days too, correct?

I guess I'm thinking about how useful the filtering would end up being to users in the following scenario:

• Recent Change created with no watchers and is forever noted as such
• Someone watches it almost immediately after the change has occurred
• Users filtering on recent changes will continue to see this page even though it now has a watcher

If I understand correctly we're ok with that because the pace at which recent changes occur is frequent enough for these pages to fall off the list?

In T380719#10394773, @Kgraessle wrote:

In T380719#10394186, @Samwalton9-WMF wrote:

In T380719#10394137, @Kgraessle wrote:

Option #1

Store data in recent changes table.
...
Cons: Will only capture watchers at time of record creation and will not update

I think I agree with the comment at T20790#230777 that this is OK actually!

Yeah I did see that comment, changes fall off after 30 days too, correct?

I guess I'm thinking about how useful the filtering would end up being to users in the following scenario:

• Recent Change created with no watchers and is forever noted as such
• Someone watches it almost immediately after the change has occurred
• Users filtering on recent changes will continue to see this page even though it now has a watcher

If I understand correctly we're ok with that because the pace at which recent changes occur is frequent enough for these pages to fall off the list?

If I'm understanding correctly Point 3 would only apply to that original change, right? i.e. subsequent edits to that page wouldn't be flagged as 'no watchers'?

And I think I agree - on the wikis where filtering RC is useful, there are so many changes that those edits are going to disappear fairly quickly.

In T380719#10394792, @Samwalton9-WMF wrote:

In T380719#10394773, @Kgraessle wrote:

In T380719#10394186, @Samwalton9-WMF wrote:

In T380719#10394137, @Kgraessle wrote:

Option #1

Store data in recent changes table.
...
Cons: Will only capture watchers at time of record creation and will not update

I think I agree with the comment at T20790#230777 that this is OK actually!

Yeah I did see that comment, changes fall off after 30 days too, correct?

I guess I'm thinking about how useful the filtering would end up being to users in the following scenario:

• Recent Change created with no watchers and is forever noted as such
• Someone watches it almost immediately after the change has occurred
• Users filtering on recent changes will continue to see this page even though it now has a watcher

If I understand correctly we're ok with that because the pace at which recent changes occur is frequent enough for these pages to fall off the list?

If I'm understanding correctly Point 3 would only apply to that original change, right? i.e. subsequent edits to that page wouldn't be flagged as 'no watchers'?

Yep, exactly.

And I think I agree - on the wikis where filtering RC is useful, there are so many changes that those edits are going to disappear fairly quickly.

In this case, I think option #1 is probably the better solution for our use case of filtering on RecentChanges for pages with no watchers since the second option would be creating a new page.

But, I'll let the other engineers take a look and weigh in.

We disucssed this in Moderator tools engineering and decided that option 1 makes sense as an MVP for this need. If a need for more frequent updates are needed in the future, we could potentially add on a maintenance script to update counts for pages that have been edited.

Change #1101593 abandoned by Kgraessle:

[mediawiki/core@master] [SPIKE] Naive impl and exploration of filter RecentChanges for pages with no watchers

Reason:

Decided on a different approach that would be more scalable

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

Final findings:

How could we enable this functionality?

We have decided to go with option #1 which is to store data in recent changes table.

Pros: No performance concerns
Cons: Will only capture watchers at time of record creation and will not update (though this seems to be expected and not a concern), requires database schema changes

Could there be any performance concerns with edit save time or loading RecentChanges as a result?

Option #1 has minimal performance concerns as discussed previously in T20790: Store information on page watcher count in the Recent Changes table.
Though, we discussed storing a watch_count instead of has_watchers field. I do not think storing the count will increase the load time, however @jsn.sherman @Scardenasmolinar this might be a good thing to create a spike patch to test with (so let me know if you want me to as part of this ticket or break it out into another one for the watcher count).

I suggest we track any further investigation here.

Change #1109498 had a related patch set uploaded (by Kgraessle; author: Kgraessle):

[mediawiki/core@master] [SPIKE] How could we enable editors to filter RecentChanges for pages with no watchers?

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

Storing the watch count would be no additional performance cost if we're using the WatchedItemStore as we'd be calling the same function countWatchers() regardless.
The WatchedItemStore does not appear to have a method to check if there are any watchers at all.
As far as I can tell it only has isWatched() which takes in a user parameter. (I'm assuming this means we're checking if the user we're passing in is watching the target).

I suppose we could also use straight up SQL to reduce the cost of counting all watchers if we wanted to keep performance in mind and only do a bool here. Do we think using the countWatchers() would be a performance concern on saving a recent change?

• ANSWER/WIP will fill this in when I get my Jupyter notebook access sorted to test the queries against each other. Will mark as stalled for now.

Also, I put a small WIP/spike patch to demo Option #1.

If there's no performance difference then I think storing the actual number is desirable! It would mean we could:

• Leave the door open to other filters (e.g. "On few users watchlists" with a number like 5)
• Do some data analysis on how many users see new edits on their watchlist and how many are missed, or only on very few editor's watchlists.

These are side benefits though, so if adding a bool is better that's fine!

Something else that occurred to me today is I think precise page watcher count is only visible to administrators in MediaWiki at the moment, if the watcher count is below 30. I think this is intended as a security measure to avoid vandals seeking out pages on few watchlists. Is there any concern about making that data more visible here? I'm not sure on all the details there, and this is a somewhat obscure route to retrieving that information (it requires an edit to the page to check).

In T380719#10447464, @Samwalton9-WMF wrote:

Something else that occurred to me today is I think precise page watcher count is only visible to administrators in MediaWiki at the moment, if the watcher count is below 30. I think this is intended as a security measure to avoid vandals seeking out pages on few watchlists. Is there any concern about making that data more visible here? I'm not sure on all the details there, and this is a somewhat obscure route to retrieving that information (it requires an edit to the page to check).

I think this comment is a fair one actually - although there is a vector of abuse here, the abuse is now also more readily visible to patrollers.

@OTichonova What do you think about this feature going in the 'Watchlisted pages' section? At the moment the scope of filters in the category are all about your watchlist, but I don't think it would be that confusing to expand the scope to be about page watchlisting in general.

It also occurs to me that this filter should only display when the user is on Special:RecentChanges, not when they're filtering on Special:Watchlist, because it's self-evident a page is watched if it's showing up in your watchlist, if such a delineation is possible. It seems like the 'Watchlisted pages' section is already hidden if you're on Special:Watchlist, so perhaps putting it there will inherently result in this behaviour.

In T380719#10447970, @Samwalton9-WMF wrote:

@OTichonova What do you think about this feature going in the 'Watchlisted pages' section? At the moment the scope of filters in the category are all about your watchlist, but I don't think it would be that confusing to expand the scope to be about page watchlisting in general.

@Samwalton9-WMF
That is exactly where I put the filter in my spike patch, and that only shows up for admins I believe (or that's what I noticed testing locally).

In T380719#10448387, @Kgraessle wrote:

In T380719#10447970, @Samwalton9-WMF wrote:

@OTichonova What do you think about this feature going in the 'Watchlisted pages' section? At the moment the scope of filters in the category are all about your watchlist, but I don't think it would be that confusing to expand the scope to be about page watchlisting in general.

@Samwalton9-WMF
That is exactly where I put the filter in my spike patch, and that only shows up for admins I believe (or that's what I noticed testing locally).

From a quick test it looks like the category only shows up for logged in users, but admin isn't necessary.

How could we enable this functionality?
We have decided to go with option #1 which is to store the data in recent changes table.

Pros: No performance concerns
Cons: Will only capture watchers at time of record creation and will not update (though this seems to be expected and not a concern), requires database schema changes

Could there be any performance concerns with edit save time or loading RecentChanges as a result?
Option #1 has minimal performance concerns as discussed previously in T20790: Store information on page watcher count in the Recent Changes table.

I tested two possible query implementations (one for simply retrieving if there are watchers, the other actually retrieving and storing the count).

The query I ran in prod which uses the same SQL as the countWatchers function on the WatchedItemStore:

SELECT COUNT(*) FROM `watchlist` WHERE wl_namespace = 0 AND wl_title = 'PAGE_TITLE_HERE'

which ran in 0.48998165130615234 seconds
whereas a simple custom query to only retrieve a bool:

SELECT 1 from `watchlist` where wl_title = 'PAGE_TITLE_HERE' and wl_namespace=0 LIMIT 1;

which ran in 0.3202946186065674 seconds.

Since there's not a large performance difference I suggest we continue with our plan of storing the rc_watch_count.

@Samwalton9-WMF
good catch. (I was testing locally as admin and just assumed it was hidden from regular users- my bad!)

In T380719#10489725, @Kgraessle wrote:

[...]

I tested two possible query implementations (one for simply retrieving if there are watchers, the other actually retrieving and storing the count).

The query I ran in prod which uses the same SQL as the countWatchers function on the WatchedItemStore:

SELECT COUNT(*) FROM `watchlist` WHERE wl_namespace = 0 AND wl_title = 'PAGE_TITLE_HERE'

which ran in 0.48998165130615234 seconds
whereas a simple custom query to only retrieve a bool:

SELECT 1 from `watchlist` where wl_title = 'PAGE_TITLE_HERE' and wl_namespace=0 LIMIT 1;

which ran in 0.3202946186065674 seconds.

Since there's not a large performance difference I suggest we continue with our plan of storing the rc_watch_count.

I suggest looking at how often this query is run. The absolute difference for a single query is small. But as a percentage, option 1 takes 53% more time. It could add up (though I don't know what role caching might play)

In T380719#10492947, @jsn.sherman wrote:

In T380719#10489725, @Kgraessle wrote:

[...]

I tested two possible query implementations (one for simply retrieving if there are watchers, the other actually retrieving and storing the count).

The query I ran in prod which uses the same SQL as the countWatchers function on the WatchedItemStore:

SELECT COUNT(*) FROM `watchlist` WHERE wl_namespace = 0 AND wl_title = 'PAGE_TITLE_HERE'

which ran in 0.48998165130615234 seconds
whereas a simple custom query to only retrieve a bool:

SELECT 1 from `watchlist` where wl_title = 'PAGE_TITLE_HERE' and wl_namespace=0 LIMIT 1;

which ran in 0.3202946186065674 seconds.

Since there's not a large performance difference I suggest we continue with our plan of storing the rc_watch_count.

I suggest looking at how often this query is run. The absolute difference for a single query is small. But as a percentage, option 1 takes 53% more time. It could add up (though I don't know what role caching might play)

Both of these queries would only run once per RecentChange save. see spike PR here

In T380719#10493004, @Kgraessle wrote:>

[...]

Both of these queries would only run once per RecentChange save. see spike PR here

My question was about the absolute count; eg. how much aggregate load would we be adding in enwiki where there are tons of rc inserts? It may not be an issue since these should be happening post revision save if I recall correctly (feel free to correct me if I'm wrong about that).

Maybe a better question would be: Is that a meaningful impact on the performance of RecentChange::save()? I don't know the total cost of running that method; perhaps this is such a small amount of time relative to the whole that it is insignificant.

Local Runtime (microseconds) of RecentChange.save() with and without different query options

Runtime without any added query
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.011082000000000036)
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.005800000000000027)

Runtime with watch count query
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.009535999999999989)
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.006083999999999978)

Runtime with has watchers query
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.010207999999999995)
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.006520999999999999)

I then ran and compared the queries against production data on the stats client with the two most watched articles: (can do more if we feel it's necessary)

Most watched article on english wikipedia (Main Page)

Watch Count Query Added time:

timestamp = time.time()
wmf.mariadb.run(
    """
    SELECT COUNT(*) FROM `watchlist` WHERE wl_namespace = 0 AND wl_title = 'Main_Page'
    """,
    "enwiki",
)
print(time.time()- timestamp)

0.20382046699523926

Has Watchers Query Added time:

timestamp = time.time()
wmf.mariadb.run(
    """
   SELECT 1 from `watchlist` where wl_title = 'Main_Page' and wl_namespace=0 LIMIT 1;
    """,
    "enwiki",
)
print(time.time()- timestamp)

0.15192270278930664

Second most watched article on english wikipedia

Watch Count Query Added time:

timestamp = time.time()
wmf.mariadb.run(
    """
    SELECT COUNT(*) FROM `watchlist` WHERE wl_namespace = 0 AND wl_title = 'Lea_Luboshutz'
    """,
    "enwiki",
)
print(time.time()- timestamp)

0.1617717742919922

Has Watchers Query Added time:

timestamp = time.time()
wmf.mariadb.run(
    """
   SELECT 1 from `watchlist` where wl_title = 'Lea_Luboshutz' and wl_namespace=0 LIMIT 1;
    """,
    "enwiki",
)
print(time.time()- timestamp)

0.15068554878234863

@jsn.sherman let me know if this covers what you were looking for

Katy to check runtime a few more times per engineering feedback. We can then call it good!

In T380719#10505580, @Kgraessle wrote:

Katy to check runtime a few more times per engineering feedback. We can then call it good!

This looks good to me; pending followup test runs suggeseted by @Scardenasmolinar

Adding more runtimes that were tested locally on different pages.

RecentChange.save() runtime with the has watchers query on three different pages locally:
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.00889300000000004)
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.005087999999999981)

mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.008272999999999975)
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.005682999999999994)

mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.007376999999999967)
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.005651000000000073)

RecentChange.save() runtime with the watch count query on three different pages locally:
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.008674999999999988)
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.005220999999999976)

mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.008348000000000022)
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.005697999999999981)

mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.00855499999999998)
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.005701000000000012)

RecentChange.save() runtime with no additional query:
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.007074999999999998)
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.006681000000000048)

mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.007860000000000034)
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.004925999999999986)

mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.007193999999999992)
mediawiki-jobrunner-1 | string(20) "TIME ELAPSED::::"
mediawiki-jobrunner-1 | float(0.005734000000000003)

It looks like we're having to count microseconds to see the difference in your local RecentChange.save() test, which I don't think is a problematic cost. Thanks for your work!

Change #1109498 abandoned by Kgraessle:

[mediawiki/core@master] [SPIKE] How could we enable editors to filter RecentChanges for pages with no watchers?

Reason:

N/A

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