Change 913124 had a related patch set uploaded (by Kosta Harlan; author: Kosta Harlan):

[mediawiki/extensions/Echo@master] [WIP] Don't send page-linked notifications for pages created by bot users

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

Change 913124 merged by jenkins-bot:

[mediawiki/extensions/Echo@master] Don't send page-linked notifications for pages created by bot users

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

Mentioned in SAL (#wikimedia-operations) [2023-05-10T10:21:38Z] <Amir1> start of clean up of echo notification in wikidatawiki (T318523)

Before the clean up:

mysql:research@x1-analytics-replica.eqiad.wmnet [wikidatawiki]>  select count(*) from echo_event left join echo_notification on event_id = notification_event where notification_user is null limit 50;
+----------+
| count(*) |
+----------+
| 95549724 |
+----------+
1 row in set (3 min 8.308 sec)

In total, deleted 95754113 orphaned events and 95488357 target_page rows.

Now let's see how it grows.

Now:

mysql:research@x1-analytics-replica.eqiad.wmnet [wikidatawiki]> select count(*) from echo_notification;
+----------+
| count(*) |
+----------+
|  2903753 |
+----------+
1 row in set (1.080 sec)

mysql:research@x1-analytics-replica.eqiad.wmnet [wikidatawiki]> select count(*) from echo_event;
+----------+
| count(*) |
+----------+
|  3488592 |
+----------+
1 row in set (0.789 sec)

Echo notification table is not growing anymore but echo event is still growing with roughly the rate of 0.5M rows per day:

mysql:research@x1-analytics-replica.eqiad.wmnet [wikidatawiki]> select count(*) from echo_notification;
+----------+
| count(*) |
+----------+
|  2907145 |
+----------+
1 row in set (1.061 sec)

mysql:research@x1-analytics-replica.eqiad.wmnet [wikidatawiki]> select count(*) from echo_event;
+----------+
| count(*) |
+----------+
|  5721251 |
+----------+
1 row in set (1.573 sec)

Mentioned in SAL (#wikimedia-operations) [2023-05-16T10:13:44Z] <Amir1> cleaning up echo notification table in all wikis (T318523)

In T318523#8853789, @Ladsgroup wrote:

Echo notification table is not growing anymore but echo event is still growing with roughly the rate of 0.5M rows per day:

mysql:research@x1-analytics-replica.eqiad.wmnet [wikidatawiki]> select count(*) from echo_notification;
+----------+
| count(*) |
+----------+
|  2907145 |
+----------+
1 row in set (1.061 sec)

mysql:research@x1-analytics-replica.eqiad.wmnet [wikidatawiki]> select count(*) from echo_event;
+----------+
| count(*) |
+----------+
|  5721251 |
+----------+
1 row in set (1.573 sec)

Sorry, looks like I missed an update in Hooks.php#onLinksUpdateComplete.

Change 920272 had a related patch set uploaded (by Kosta Harlan; author: Kosta Harlan):

[mediawiki/extensions/Echo@master] LinksUpdate: Don't send page-linked notifications for pages created by bot users

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

Change 920272 merged by jenkins-bot:

[mediawiki/extensions/Echo@master] LinksUpdate: Don't send page-linked notifications for pages created by bot users

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

The patch is deployed. I cleaned it up and now we are at:

mysql:research@x1-analytics-replica.eqiad.wmnet [wikidatawiki]> select count(*) from echo_event;
+----------+
| count(*) |
+----------+
|  2637281 |
+----------+
1 row in set (0.827 sec)

mysql:research@x1-analytics-replica.eqiad.wmnet [wikidatawiki]> select count(*) from echo_notification;
+----------+
| count(*) |
+----------+
|  2924467 |
+----------+
1 row in set (1.069 sec)

I wait for a couple of days and check again.

After a week:

mysql:research@x1-analytics-replica.eqiad.wmnet [wikidatawiki]> select count(*) from echo_notification;
+----------+
| count(*) |
+----------+
|  2931514 |
+----------+
1 row in set (1.093 sec)

mysql:research@x1-analytics-replica.eqiad.wmnet [wikidatawiki]> select count(*) from echo_event;
+----------+
| count(*) |
+----------+
|  5959639 |
+----------+
1 row in set (1.771 sec)

We still have three million echo event added in a week :/ Can it be T221258: Avoid inserting echo_event rows when not needed?

In T318523#8901906, @Ladsgroup wrote:

After a week:

mysql:research@x1-analytics-replica.eqiad.wmnet [wikidatawiki]> select count(*) from echo_notification;
+----------+
| count(*) |
+----------+
|  2931514 |
+----------+
1 row in set (1.093 sec)

mysql:research@x1-analytics-replica.eqiad.wmnet [wikidatawiki]> select count(*) from echo_event;
+----------+
| count(*) |
+----------+
|  5959639 |
+----------+
1 row in set (1.771 sec)

We still have three million echo event added in a week :/ Can it be T221258: Avoid inserting echo_event rows when not needed?

It's quite possible :|

Checked on betalabs - no article-linked notifications are being sent to bots.

Also, I T221262: Actively delete echo_event rows when they become orphaned - the query returns quite few records (enwiki betalabs):

SELECT COUNT(*) FROM echo_event LEFT JOIN echo_notification ON notification_event=event_id WHERE notification_timestamp IS NULL;
+----------+
| COUNT(*) |
+----------+
|   194847 |
+----------+

@Tgr - extensions/Echo/maintenance/removeOrphanedEvents.php is not run?

In T318523#9076031, @Etonkovidova wrote:

@Tgr - extensions/Echo/maintenance/removeOrphanedEvents.php is not run?

Nothing is run on beta AFAIK: T125976: Run mediawiki::maintenance scripts in Beta Cluster