Nextcloud 35.0.1.1 database performance check complains about lack of index on a table:
Tables scanning sequentially
Notice ·pg.tables_without_index_use
Failing
Some tables have many sequential scans and no index scans, suggesting a missing index.
1 table(s) have > 1M sequential scans without any index scans.
oc_mimetypes
Add appropriate indexes to the affected tables (pg_stat_user_tables shows the full scan statistics).
This post SQL commands for missing indices - #15 by ernolf kindly lists several SQL commands to create missing indices on various tables for Postgres and MySQL that are not created by php occ db:add-missing-indices which were apparently reverse-engineered from the sources.
The table oc_mimetypes is not listed, and there does not seem to be a lack of consistency in naming of these indices across different tables.
I have tried to look at the sources to determine the name of the indices that should be created for this table, but I did not find the correct information (wood, tree…).
How and where can I find the info which indices (sources, documentation, headers) nextcloud expects for this table?
No, they are exactly the same. It was just for users that cannot run the occ command or not long enough to create the indices (hosted environments).
How large is your mimetype-table?
While selecting 1000 out of 100000 rows could be a candidate for an index, selecting 1 out of 100 rows will hardly be, because the 100 rows probably fit within a single disk page
As far as I understand now (and after \d oc_mimetypes in pgsql) this table consists of less than 100 rows and is apparently never written once after it is initialized, it contains the content of the mimetypes file, and has so many queries that the statistical analysis of the db performance monitor flags it as an Error for not having indices. (>1 million reads in whatever time frame it checked)
There are two observations now that a more than a day has passed:
The presentation of this DB performance check indicates a high level of severity. I understand now that a failed db check is not necessarily disruptive. But For me the presentation of this check and its status suggested a severe problem and that immediate action is required. Mea culpa.
Secondly there was too little indication that these checks are not run when accessing this web page, but rather scheduled. so corrective actions (like creating the indices) do not immediately show it as resolved.
A little hint for confused people like me would have been nice.
And one more thing: while the db:check occ command did not complain about missing indices on this table (maybe because they had been added after the check ran and before I started troubleshooting…), the table ended up having indices.
So now the system is happy, the next run of the db performance check did not find any failures and the system continues to run nicely.
These database checks can be extremely helpful to tune the server’s performance. Before you had tools like mysqltuner, tuning-primer and others, and even giving the same messages, there were sites explaining more details. You had to run the database server at least for 24h or better a few days to get a meaningful output. The database itself stores certain events when tables are read from cache or it starts to use disk-cache, the number of parallel connection etc.
Nextcloud just implemented them in this version. So they didn’t adjust the output to make it very Nextcloud specific yet or link to a more explanatory documentation.