Missing indexes in appconfig and flow_operations

[nagilum99]

Steps to reproduce

set log_queries_not_using_indexes = 1 to mysql config.

Expected behaviour

Probably a very few logfile entrys from upgrades etc.

Actual behaviour

# Time: 2021-01-26T23:45:57.678222Z
# User@Host: owncloud[owncloud] @ localhost []  Id:     2
# Schema: owncloud  Last_errno: 0  Killed: 0
# Query_time: 0.011853  Lock_time: 0.011542  Rows_sent: 268  Rows_examined: 268  Rows_affected: 0
# Bytes_sent: 10121
use owncloud;
SET timestamp=1611704757;
SELECT * FROM `oc_appconfig`;
# Time: 2021-01-26T23:45:58.073818Z
# User@Host: owncloud[owncloud] @ localhost []  Id:     2
# Schema: owncloud  Last_errno: 0  Killed: 0
# Query_time: 0.011857  Lock_time: 0.011599  Rows_sent: 0  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 267
SET timestamp=1611704758;
SELECT `class`, `entity`, `events` FROM `oc_flow_operations` WHERE `events` <> '[]' GROUP BY `class`, `entity`, `events`;
# Time: 2021-01-26T23:45:58.456739Z
# User@Host: owncloud[owncloud] @ localhost []  Id:     3
# Schema: owncloud  Last_errno: 0  Killed: 0
# Query_time: 0.000376  Lock_time: 0.000090  Rows_sent: 268  Rows_examined: 268  Rows_affected: 0
# Bytes_sent: 10121
SET timestamp=1611704758;
SELECT * FROM `oc_appconfig`;
# Time: 2021-01-26T23:45:58.470741Z
# User@Host: owncloud[owncloud] @ localhost []  Id:     3
# Schema: owncloud  Last_errno: 0  Killed: 0
# Query_time: 0.000346  Lock_time: 0.000073  Rows_sent: 0  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 267
SET timestamp=1611704758;
SELECT `class`, `entity`, `events` FROM `oc_flow_operations` WHERE `events` <> '[]' GROUP BY `class`, `entity`, `events`;
# Time: 2021-01-26T23:46:00.565861Z
# User@Host: owncloud[owncloud] @ localhost []  Id:     4
# Schema: owncloud  Last_errno: 0  Killed: 0
# Query_time: 0.000383  Lock_time: 0.000103  Rows_sent: 268  Rows_examined: 268  Rows_affected: 0
# Bytes_sent: 10121
SET timestamp=1611704760;
SELECT * FROM `oc_appconfig`;
# Time: 2021-01-26T23:46:00.584120Z
# User@Host: owncloud[owncloud] @ localhost []  Id:     4
# Schema: owncloud  Last_errno: 0  Killed: 0
# Query_time: 0.000318  Lock_time: 0.000074  Rows_sent: 0  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 267
SET timestamp=1611704760;
SELECT `class`, `entity`, `events` FROM `oc_flow_operations` WHERE `events` <> '[]' GROUP BY `class`, `entity`, `events`;

Nextcloud version: (see Nextcloud admin page) 20.0.6

Updated from an older Nextcloud/ownCloud or fresh install: Originally Owncloud, several major Nextcloud versions upgraded

Signing status: No errors have been found.

List of activated apps:

Enabled: - accessibility: 1.6.0 - activity: 2.13.4 - bruteforcesettings: 2.0.1 - cloud_federation_api: 1.3.0 - comments: 1.10.0 - contactsinteraction: 1.1.0 - dav: 1.16.2 - deck: 1.2.3 - federatedfilesharing: 1.10.2 - files: 1.15.0 - files_pdfviewer: 2.0.1 - files_rightclick: 0.17.0 - files_sharing: 1.12.2 - files_trashbin: 1.10.1 - files_versions: 1.13.0 - logreader: 2.5.0 - lookup_server_connector: 1.8.0 - notifications: 2.8.0 - oauth2: 1.8.0 - photos: 1.2.3 - privacy: 1.4.0 - provisioning_api: 1.10.0 - serverinfo: 1.10.0 - settings: 1.2.0 - sharebymail: 1.10.0 - spreed: 10.0.5 - text: 3.1.0 - theming: 1.11.0 - twofactor_backupcodes: 1.9.0 - updatenotification: 1.10.0 - user_status: 1.0.1 - viewer: 1.4.0 - weather_status: 1.0.0 - workflowengine: 2.2.0

IMHO if the same querys happen that often (should roughly be 1 request per second being logged) there should be an Index.

Regards!

[skjnldsv]

cc @juliushaertl @blizzz

[szaimen]

Is this Issue still valid in NC21.0.2? If not, please close this issue. Thanks! :)

[juliushaertl]

Might not be a huge issue especially if there are no or a small amount of flow operations configured, but would still be good for larger amounts. We could probably add an index to the first couple of chars that wouldn't help much with the query since indexes are not used on not equal comparisons (<>). Not sure if in theory a nullable column would help with that.

[LukeOwlclaw]

I am using Nextcloud 22.2.5 and the problem still exists.

I followed the NC "Performance consideration":

log-queries-not-using-indexes
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time=100

The output is similar to what is shown above. Interesting is SELECT * FROM `oc_appconfig`; over and over again. Try e.g. opening NC System --> nextcloud/index.php/settings/admin/overview or just wait. You'll see the repeated query. Can't this be cached? The configuration should hardly ever change during runtime.

But I also see SELECT `class`, `entity`, `events` AS `events` FROM `oc_flow_operations` WHERE `events` <> '[]' GROUP BY `class`, `entity`, `events`; multiple times per second.

I am wondering is this just a problem for some users? @juliushaertl I do not understand you saying "Might not be a huge issue". Considering what the performance consideration linked above say: "The general rule is: database queries are very bad and should be avoided if possible."

What might also be worth the developers' time: Try running MySQL Tuning-Primer.sh. I get there (just for my NC installation) things like:

KEY BUFFER
No key reads?!
Seriously look into using some indexes

and

JOINS
Current join_buffer_size = 260.00 K
You have had 25 queries where a join could not use an index properly
You have had 658 joins without keys that check for key usage after each row

Also MySQLTuner reports that there are lots of performance enhancements from which NC would benefit.

[ChristophWurst]

Interesting is SELECT * FROM `oc_appconfig`; over and over again.

Fixed with https://github.com/nextcloud/server/pull/41755