Skip to content

Missing indexes in appconfig and flow_operations #25342

@nagilum99

Description

@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:

Details 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!

Metadata

Metadata

Assignees

No one assigned

    Type

    No type
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions