Change #1243234 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/extensions/CheckUser@master] [WIP] Add filter for hiding cases with no blocked accounts

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

mpostoronca opened https://gitlab.wikimedia.org/repos/data-engineering/schemas-event-secondary/-/merge_requests/116

Draft: suggested_investigations/interaction: Add hide_cases_with_no_blocked_users filter

Change #1243234 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@master] SI: Add filter for hiding cases with no blocked accounts

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

Testing locally, I am regularly seeing the query time out and return the warning

The filters you have applied are too broad, please narrow your search for better results.

It is unclear how to fix this other than perhaps raising the limit.

In some cases it is spending nearly 9 seconds in total doing database queries.

Do we need to think about optimising this? @Dreamy_Jazz

dreamyjazz merged https://gitlab.wikimedia.org/repos/data-engineering/schemas-event-secondary/-/merge_requests/116

suggested_investigations/interaction: Add hide_cases_with_no_blocked_users filter

In T418293#11671956, @dom_walden wrote:

Testing locally, I am regularly seeing the query time out and return the warning

The filters you have applied are too broad, please narrow your search for better results.

It is unclear how to fix this other than perhaps raising the limit.

The limit is hit after checking about 6,000 cases. Raising could be done, but I'd like to avoid that unless it's necessary. I'm guessing most of your cases locally do not have blocked users?

Perhaps it would be useful to see if we hit this limit frequently on production?

In some cases it is spending nearly 9 seconds in total doing database queries.

Do we need to think about optimising this? @Dreamy_Jazz

What database queries are happening often? Maybe it's better that we check the performance in prod where more cases I expect would have blocked users?

There probably are improvements we can make to this though, so would agree we should look into these

Change #1247965 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/extensions/CheckUser@master] SI: Update instrumentation schema

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

Change #1247968 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/extensions/CheckUser@wmf/1.46.0-wmf.18] SI: Update instrumentation schema

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

Change #1247968 merged by Dreamy Jazz:

[mediawiki/extensions/CheckUser@wmf/1.46.0-wmf.18] SI: Update instrumentation schema

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

Mentioned in SAL (#wikimedia-operations) [2026-03-04T11:12:07Z] <dreamyjazz@deploy2002> Started scap sync-world: Backport for [[gerrit:1247968|SI: Update instrumentation schema (T418293)]]

Mentioned in SAL (#wikimedia-operations) [2026-03-04T11:17:32Z] <dreamyjazz@deploy2002> dreamyjazz: Backport for [[gerrit:1247968|SI: Update instrumentation schema (T418293)]] synced to the testservers (see https://wikitech.wikimedia.org/wiki/Mwdebug). Changes can now be verified there.

Running Excimer on SI on testwiki with this filter enabled:

I think we will need to improve the speed of the GlobalBlockCheck code. I'll file a new task for that, because it requires changes to GlobalBlocking for this to work properly.

I've filed T418998 for this

Mentioned in SAL (#wikimedia-operations) [2026-03-04T11:28:30Z] <dreamyjazz@deploy2002> Finished scap sync-world: Backport for [[gerrit:1247968|SI: Update instrumentation schema (T418293)]] (duration: 16m 22s)

In T418293#11672559, @Dreamy_Jazz wrote:

The limit is hit after checking about 6,000 cases. Raising could be done, but I'd like to avoid that unless it's necessary. I'm guessing most of your cases locally do not have blocked users?

I was referring to the limit parameter. I have seen circumstances where raising that will prevent it from timing out.

I guess you are referring to the successive calls to SuggestedInvestigationsCasesPager. The most I have seen, after settings limit=100 (max), is about 4700.

Of the 22054 rows in the cusi_user table 21390 are locally or globally blocked or locked.

Perhaps it would be useful to see if we hit this limit frequently on production?

What database queries are happening often? Maybe it's better that we check the performance in prod where more cases I expect would have blocked users?

Other than GlobalBlockLookup as you mention above, it spends a lot of time doing CentralAuthUser::loadFromDatabase.

Performance issues aside, doing high volume testing locally, I saw that the new filter only returns cases where at least one user is locally/globally blocked or locked and does not return cases where no users are blocked.

However, I did not confirm that there were any cases with blocked users that the filter misses.

In T418293#11673481, @dom_walden wrote:

In T418293#11672559, @Dreamy_Jazz wrote:

The limit is hit after checking about 6,000 cases. Raising could be done, but I'd like to avoid that unless it's necessary. I'm guessing most of your cases locally do not have blocked users?

I was referring to the limit parameter. I have seen circumstances where raising that will prevent it from timing out.

I guess you are referring to the successive calls to SuggestedInvestigationsCasesPager. The most I have seen, after settings limit=100 (max) is about 4700.

Yeah, the code we have tries to limit the number of rows that we check vs rows that we end up discarding without needing to check, so each query for rows to check in PHP increases the limit progressively.

I'll add a warning entry that is logged if the PHP filter limits are hit so we can track this and raise the limit if needed.

What database queries are happening often? Maybe it's better that we check the performance in prod where more cases I expect would have blocked users?

Other than GlobalBlockLookup as you mention above, it spends a lot of time doing CentralAuthUser::loadFromDatabase.

Yeah, that should be addressed through T418998: SI: Improve the GlobalBlockCheck class to batch lookups for global blocks and central IDs. Thanks

Change #1248024 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/extensions/CheckUser@master] SI: Log when PHP filter limits hit

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

Moving back to 'Needs Review' for the creation of that logstash warning

Change #1247965 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@master] SI: Update instrumentation schema

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

Change #1248024 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@master] SI: Log when PHP filter limits hit

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