very slow preview load and HTTP500 - Postgresql database index missing for storage+path_hash
florianschroen opened this issue · 4 comments
Hi,
i just want to share my experience and fix.
I installed the plugin for the first time on a uptodate nc docker instance with a separate pg container.
# occ status
- installed: true
- version: 28.0.1.1
- versionstring: 28.0.1
- edition:
- maintenance: false
- needsDbUpgrade: false
- productname: Nextcloud
- extendedSupport: false
# occ app:list | grep preview
- previewgenerator: 5.4.0
PSQL># SELECT version();
version
---------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.5 (Debian 15.5-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
The pageload was very slow. first preview request were answered, some got HTTP 500.
After digging into the DB, I noticed that the queries were extremely slow. which leaded to this pg log lines (and many more):
2024-01-13 15:07:25.717 UTC [57479] LOG: duration: 5389.743 ms execute <unnamed>: SELECT "fileid" FROM "oc_filecache" WHERE ("storage" = $1) AND ("path_hash" = $2)
2024-01-13 15:07:25.717 UTC [57479] DETAIL: parameters: $1 = '1', $2 = 'c164c3a63de651e7e52bac30733dee84'
2024-01-13 15:07:27.572 UTC [57526] LOG: duration: 6045.899 ms execute <unnamed>: SELECT "fileid" FROM "oc_filecache" WHERE ("storage" = $1) AND ("path_hash" = $2)
2024-01-13 15:07:27.572 UTC [57526] DETAIL: parameters: $1 = '1', $2 = 'd41d8cd98f00b204e9800998ecf8427e'
2024-01-13 15:07:27.712 UTC [57523] LOG: duration: 6238.780 ms execute <unnamed>: SELECT "fileid" FROM "oc_filecache" WHERE ("storage" = $1) AND ("path_hash" = $2)
2024-01-13 15:07:27.712 UTC [57523] DETAIL: parameters: $1 = '1', $2 = 'd41d8cd98f00b204e9800998ecf8427e'
2024-01-13 15:07:29.040 UTC [57478] LOG: duration: 6433.436 ms execute <unnamed>: SELECT "fileid" FROM "oc_filecache" WHERE ("storage" = $1) AND ("path_hash" = $2)
2024-01-13 15:07:29.040 UTC [57478] DETAIL: parameters: $1 = '1', $2 = 'c164c3a63de651e7e52bac30733dee84'
so i digged into the index of the table oc_filecache
.
owncloud=# select * from pg_indexes where tablename = 'oc_filecache';
schemaname | tablename | indexname | tablespace | indexdef
------------+--------------+------------------------+------------+--------------------------------------------------------------------------------------------
public | oc_filecache | fs_id_storage_size | | CREATE INDEX fs_id_storage_size ON public.oc_filecache USING btree (fileid, storage, size)
public | oc_filecache | fs_mtime | | CREATE INDEX fs_mtime ON public.oc_filecache USING btree (mtime)
public | oc_filecache | fs_parent | | CREATE INDEX fs_parent ON public.oc_filecache USING btree (parent)
public | oc_filecache | fs_parent_name_hash | | CREATE INDEX fs_parent_name_hash ON public.oc_filecache USING btree (parent, name)
public | oc_filecache | fs_size | | CREATE INDEX fs_size ON public.oc_filecache USING btree (size)
public | oc_filecache | fs_storage_mimepart | | CREATE INDEX fs_storage_mimepart ON public.oc_filecache USING btree (storage, mimepart)
public | oc_filecache | fs_storage_mimetype | | CREATE INDEX fs_storage_mimetype ON public.oc_filecache USING btree (storage, mimetype)
public | oc_filecache | fs_storage_size | | CREATE INDEX fs_storage_size ON public.oc_filecache USING btree (storage, size, fileid)
public | oc_filecache | fs_storage_path_prefix | | CREATE INDEX fs_storage_path_prefix ON public.oc_filecache USING btree (storage, path)
(9 rows)
there is no index for the problematic queries which indexes storage
and path_hash
.
so i added the missing index:
owncloud=# CREATE INDEX fs_storage_path_hash ON public.oc_filecache USING btree (storage, path_hash);
CREATE INDEX
owncloud=# select * from pg_indexes where tablename = 'oc_filecache';
schemaname | tablename | indexname | tablespace | indexdef
------------+--------------+------------------------+------------+--------------------------------------------------------------------------------------------
public | oc_filecache | fs_id_storage_size | | CREATE INDEX fs_id_storage_size ON public.oc_filecache USING btree (fileid, storage, size)
public | oc_filecache | fs_mtime | | CREATE INDEX fs_mtime ON public.oc_filecache USING btree (mtime)
public | oc_filecache | fs_parent | | CREATE INDEX fs_parent ON public.oc_filecache USING btree (parent)
public | oc_filecache | fs_parent_name_hash | | CREATE INDEX fs_parent_name_hash ON public.oc_filecache USING btree (parent, name)
public | oc_filecache | fs_size | | CREATE INDEX fs_size ON public.oc_filecache USING btree (size)
public | oc_filecache | fs_storage_mimepart | | CREATE INDEX fs_storage_mimepart ON public.oc_filecache USING btree (storage, mimepart)
public | oc_filecache | fs_storage_mimetype | | CREATE INDEX fs_storage_mimetype ON public.oc_filecache USING btree (storage, mimetype)
public | oc_filecache | fs_storage_size | | CREATE INDEX fs_storage_size ON public.oc_filecache USING btree (storage, size, fileid)
public | oc_filecache | fs_storage_path_prefix | | CREATE INDEX fs_storage_path_prefix ON public.oc_filecache USING btree (storage, path)
public | oc_filecache | fs_storage_path_hash | | CREATE INDEX fs_storage_path_hash ON public.oc_filecache USING btree (storage, path_hash)
(10 rows)
...and got a significantly increase of the preview/page load speed.
I am not into the nc-plugin code to check if this is individual problem of my installation / migration paths. Or if there is something missing for auto-creation of this index.
So can someone verify this, please?
This is an index that is already created automatically.
Did you run occ db:add-missing-indices
recently? Indexes are only created when running this command as the creation might take a long time.
When there was a mention on the admin page to do so, then yes. Otherwise no. (too much time has passed to remember what I did exactly)
Maybe I can find some info in the bash history.
Please try to run the command anyway and see if it makes a difference.
did it now. now output so far, so no missing indices
~# occ db:add-missing-indices
~# echo $?
0