Database advisor performance rules report failures despite no slow queries or missing indexes

[dennysubke]

Bug description

After upgrading to Nextcloud 35.0.0, the Serverinfo database advisor starts reporting six Performance failures once MariaDB has been running for more than 24 hours:

• Joins without indexes (Select_full_join)
• High sorted-row rate (Sort_rows)
• Join-without-index rate (Join_without_index_rate)
• Full-index-scan rate (Handler_read_first_rate)
• Fixed-position read rate (Handler_read_rnd_rate)
• Sequential-row read rate (Handler_read_rnd_next_rate)

The warnings are hidden during the first 24 hours because these rules are counter-sensitive, then appear after the uptime gate expires.

On this installation, however, there is no evidence of an actual database performance problem:

• occ db:add-missing-indices --dry-run produces no output, so Nextcloud does not detect any missing application indexes.
• slow_query_log is enabled.
• long_query_time is set to 2 seconds.
• After about 27 hours of MariaDB uptime, Slow_queries = 1.
• The only entry in the slow query log is an administrative command used to resize the InnoDB log file:
  SET GLOBAL innodb_log_file_size=268435456;
  with Query_time: 2.004493.
• There are therefore no actual Nextcloud queries over 2 seconds in that period.

The raw status values at Uptime = 97368 seconds were:

Handler_read_first      5068
Handler_read_rnd        91432
Handler_read_rnd_next   3683350
Select_full_join        616
Select_range_check      0
Select_scan             19096
Sort_rows               84354
Uptime                  97368

Serverinfo displayed approximately:

• full joins without an index: 0.4271% of queries
• sorted rows: 2962/hour
• index-less join+scan rate: 722/hour
• full-index scans: 185/hour
• fixed-position reads: 3223/hour
• sequential-row reads: 135023/hour

The rate rules currently fail at thresholds above roughly 1 event per hour. For a normal active Nextcloud instance, these thresholds appear extremely strict and can result in multiple red "Failed" warnings even though there are no missing Nextcloud indexes and no real slow queries.

Expected behavior

The database advisor should distinguish between genuinely actionable performance problems and normal query activity on an active Nextcloud instance.

Possible approaches could include:

• using more realistic thresholds for the rate-based scan/sort rules,
• correlating these counters with query volume,
• downgrading some of these checks to informational/notices,
• or avoiding a red failure state unless there is stronger evidence of an actionable database problem.

Environment

• Nextcloud Server: 35.0.0
• MariaDB: 10.11.19
• Deployment: Docker / Umbrel
• Database health settings:
  • slow_query_log=ON
  • long_query_time=2
  • innodb_log_file_size=256M

Additional context

This appears separate from the existing replica false-positive tracked in #1146. No replication changes are involved here.