Can't you just raise $wgRCMaxAge? And if you can't, are you sure the reason is not precisely to avoid such long-spanning queries?

I don't think that would be the best approach. If the problem is long queries, I would expect that the sensible approach is to limit them by a maximum number of revisions to fetch, rather than the maximum age of such revisions.

After all, it is more likely that mass vandalism goes unnoticed in low-activity wikis which have few edits spread across a long period of time (which would be unnecessarily cut off from Nuke's reach due to their age), than in high-activity wikis where editors would eventually bump into, and revert the vandalism in the normal course of wiki maintenance.

Yes but older pages are also more likely to have subsequent revisions, that's probably the rationale. Anyway, I'm not qualified to answer on what's the best approach here, just reminding those who can do so that raising $wgRCMaxAge is a possible approach (translatewiki.net sets it at 5 years).

@Ladsgroup (informally) noted at Wikimedia-Hackathon-2024 that it's likely Nuke could be changed to use the Revision table with a sensible time limit (e.g. 1 year) that's longer than the recentchanges limit.

Open question from me - how can we test Nuke's performance outside of production wikis using the Revision table instead of Recent changes? Would we need to do this on Test wiki, or is there a feasible way for us to do this locally?

In T33858#10191742, @Samwalton9-WMF wrote:

Open question from me - how can we test Nuke's performance outside of production wikis using the Revision table instead of Recent changes? Would we need to do this on Test wiki, or is there a feasible way for us to do this locally?

Not really, you can get the queries it produces and then try to run it on stat machines in production to get a sense of how slow they are. Also you can always set the replica to use 'vslow' group to make sure it doesn't cause wide-spread outage.

In T33858#10197033, @Ladsgroup wrote:

In T33858#10191742, @Samwalton9-WMF wrote:

Open question from me - how can we test Nuke's performance outside of production wikis using the Revision table instead of Recent changes? Would we need to do this on Test wiki, or is there a feasible way for us to do this locally?

Not really, you can get the queries it produces and then try to run it on stat machines in production to get a sense of how slow they are. Also you can always set the replica to use 'vslow' group to make sure it doesn't cause wide-spread outage.

this is a really great point and we could be taking this approach elsewhere. It looks like I may have access, but we should get all of our engineers aboard.

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

[mediawiki/extensions/Nuke@master] [WIP]: Investigate using the Revision table instead of Recent Changes for Nuke, to allow deleting older pages

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

Attaching the raw sql that is generated from the new query:

SELECT DISTINCT page_title, page_namespace
FROM `revision`
         JOIN `actor` ON ((actor_id = rev_actor))
         JOIN `page` ON ((page_id = rev_page))
WHERE (rev_parent_id = '0')
  AND (rev_timestamp > 1697916049)
  AND actor_name = 'Admin'
ORDER BY rev_timestamp DESC
LIMIT 500

That rev_timestamp condition is definitely wrong. We store timestamps in strings like '20241022153306'

In T33858#10250637, @Kgraessle wrote:

Attaching the raw sql that is generated from the new query:

SELECT DISTINCT page_title, page_namespace
FROM `revision`
         JOIN `actor` ON ((actor_id = rev_actor))
         JOIN `page` ON ((page_id = rev_page))
WHERE (rev_parent_id = '0')
  AND (rev_timestamp > 1697916049)
  AND actor_name = 'Admin'
ORDER BY rev_timestamp DESC
LIMIT 500

Okay, I'm trying to query a variant of this via jupyter on a stat host
https://wikitech.wikimedia.org/wiki/Data_Platform/Systems/Jupyter#Conda_environments
https://github.com/wikimedia/wmfdata-python/blob/c958ba90f32a77fa613afb35b0376f68fafaeb68/docs/quickstart.ipynb

Okay I ran a spot check in jupyter; it looks like querying revs may actually be faster when we limit it to the same timeframe as recent changes:

import wmfdata as wmf
import time
username=input()

rev_query="""
    SELECT DISTINCT page_title, page_namespace, rev_timestamp
    FROM `revision`
         JOIN `actor` ON ((actor_id = rev_actor))
         JOIN `page` ON ((page_id = rev_page))
    WHERE ( rev_parent_id = 0 )
    AND (rev_timestamp > 20240930000000)
    AND actor_name = '{}'
    ORDER BY rev_timestamp ASC
    LIMIT 500;
"""
start = time.time()
page_list = wmf.mariadb.run(
    rev_query.format(username),
    "enwiki"
)
end = time.time()
print(page_list)
print("revisions time: {}".format(end - start))

rc_query="""
    SELECT page_title, page_namespace, rc_timestamp
    FROM `recentchanges`
             JOIN `actor` ON ((actor_id = rc_actor))
             JOIN `page` ON ((page_id = rc_cur_id))
    WHERE ((rc_source = 'mw.new' OR (rc_log_type = 'upload' AND rc_log_action = 'upload')))
      AND actor_name = '{}'
    ORDER BY rc_timestamp ASC
    LIMIT 500;
"""

start = time.time()
page_list = wmf.mariadb.run(
    rc_query.format(username),
    "enwiki"
)
end = time.time()
print(page_list)
print("revisions time: {}".format(end - start))

revs table:

[...]
[153 rows x 3 columns]
revisions time: 0.13975977897644043

rc table:

[...]
[153 rows x 3 columns]
revisions time: 0.1946103572845459

If we don't filter on timestamp in the rev table query, of course it runs slower, but also can return a lot more information:

[...]
[500 rows x 3 columns]
revisions time: 1.1056437492370605

forgive my not fancy code, this was just me slapping something together.

For major differences, you can run queries in a databases and if one takes one minute and the other takes one second, you can safely say one is faster than the other. But for 0.14s vs 019s, you can't really make the call.

For fast queries, there are many other factors such as how warm innodb buffer pool is, how fragmented the pages are, if page look up caches are warm or not, how busy the server is at that moment and so many more I don't remember from top of my head. The best conclusion here is that none has major advantage over the other. If you need to do something in the critical path and every milliseconds would count, then we need to do benchmarking to find the best but that's not the case here.

In T33858#10274873, @Ladsgroup wrote:

For major differences, you can run queries in a databases and if one takes one minute and the other takes one second, you can safely say one is faster than the other. But for 0.14s vs 019s, you can't really make the call.

For fast queries, there are many other factors such as how warm innodb buffer pool is, how fragmented the pages are, if page look up caches are warm or not, how busy the server is at that moment and so many more I don't remember from top of my head. The best conclusion here is that none has major advantage over the other. If you need to do something in the critical path and every milliseconds would count, then we need to do benchmarking to find the best but that's not the case here.

Makes sense to me; thanks!

Thanks for this analysis! For next steps, would it make sense to switch Nuke to using the Revisions table with the same time frame as currently, and then cautiously expand the time frame?

If so, how could we evaluate error rates/query times to evaluate whether we're causing any issues with this?

You can search in https://logstash.wikimedia.org/app/dashboards#/view/43fcccd0-4df5-11ec-81e9-e1226573bad4 (slow queries dashboard in logstash) for the last week or so.

Also, we have max execution time option, you can set that for the query to make sure it can't be weaponized as a DDOS vector.

In T33858#10289062, @Samwalton9-WMF wrote:

Thanks for this analysis! For next steps, would it make sense to switch Nuke to using the Revisions table with the same time frame as currently, and then cautiously expand the time frame?

That is exactly what I would recommend; Based on our conversation today, I just wanted to make sure @Samwalton9-WMF
saw these two bits of advice together:

In T33858#10197033, @Ladsgroup wrote:

In T33858#10191742, @Samwalton9-WMF wrote:

Open question from me - how can we test Nuke's performance outside of production wikis using the Revision table instead of Recent changes? Would we need to do this on Test wiki, or is there a feasible way for us to do this locally?

Not really, you can get the queries it produces and then try to run it on stat machines in production to get a sense of how slow they are. Also you can always set the replica to use 'vslow' group to make sure it doesn't cause wide-spread outage.

In T33858#10289234, @Ladsgroup wrote:

You can search in https://logstash.wikimedia.org/app/dashboards#/view/43fcccd0-4df5-11ec-81e9-e1226573bad4 (slow queries dashboard in logstash) for the last week or so.

Also, we have max execution time option, you can set that for the query to make sure it can't be weaponized as a DDOS vector.

Thank you so much for all of your insight and expertise, @Ladsgroup!

Filed T379147: Update Nuke to use the Revision table instead of Recent Changes.

Change #1082077 abandoned by Kgraessle:

[mediawiki/extensions/Nuke@master] [WIP]: Investigate using the Revision table instead of Recent Changes for Nuke, to allow deleting older pages

Reason:

Changes already implemented

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