A lot of db queries while uploading a file

ℹ️ Support
1

Hello everybody,
i’m using a odroid c2 to run nextcloud 11.0.2. I use the app " Preview Generator 1.0.1" to generate previews of all my pictures. So there are about 16k subdirectories under “data/appdata_/preview”.
By uploading a file a lot of db queries occurs and apache, mysql an redis have a high cpu load. After I activated the general log for mysql and I recognized the following db queries (in my case about 30k).
Short overview:
125 Query SELECT fileid, storage, path, parent, name, mimetype, mimepart, size, mtime,
storage_mtime, encrypted, etag, permissions, checksum
FROM oc_filecache WHERE storage = ‘3’ AND path_hash = 'a82410b919497d56398e6bb646b6766f’
125 Query SELECT fileid, storage, path, parent, name, mimetype, mimepart, size, mtime,
storage_mtime, encrypted, etag, permissions, checksum
FROM oc_filecache WHERE storage = ‘3’ AND path_hash = '78e15369a08d47f5f6b72e3e4b1d26e1’
125 Query SELECT fileid, storage, path, parent, name, mimetype, mimepart, size, mtime,
storage_mtime, encrypted, etag, permissions, checksum
FROM oc_filecache WHERE storage = ‘3’ AND path_hash = 'b6ed211d624abd9a84cb75bc2ae9b665’
125 Query SELECT fileid, storage, path, parent, name, mimetype, mimepart, size, mtime,
storage_mtime, encrypted, etag, permissions, checksum
FROM oc_filecache WHERE storage = ‘3’ AND path_hash = '345c08634db7c93aba65870135604bab’
125 Query SELECT fileid, storage, path, parent, name, mimetype, mimepart, size, mtime,
storage_mtime, encrypted, etag, permissions, checksum
FROM oc_filecache WHERE storage = ‘3’ AND path_hash = '21fcd52484eecf89570179a1eda23491’
125 Query SELECT fileid, storage, path, parent, name, mimetype, mimepart, size, mtime,
storage_mtime, encrypted, etag, permissions, checksum
FROM oc_filecache WHERE storage = ‘3’ AND path_hash = 'eac77e94f7662be4c6975acf4afa628c’
125 Query SELECT fileid, storage, path, parent, name, mimetype, mimepart, size, mtime,
storage_mtime, encrypted, etag, permissions, checksum
FROM oc_filecache WHERE storage = ‘3’ AND path_hash = '905302bab297b50486fbe01dc5927142’
125 Query SELECT fileid, storage, path, parent, name, mimetype, mimepart, size, mtime,
storage_mtime, encrypted, etag, permissions, checksum
FROM oc_filecache WHERE storage = ‘3’ AND path_hash = 'bf795dabc1a80c5b12e1c8428d0d4d6d’
125 Query SELECT fileid, storage, path, parent, name, mimetype, mimepart, size, mtime,
storage_mtime, encrypted, etag, permissions, checksum
FROM oc_filecache WHERE storage = ‘3’ AND path_hash = 'd6492d127536dff86d24566d7835c53f’
125 Query SELECT fileid, storage, path, parent, name, mimetype, mimepart, size, mtime,
storage_mtime, encrypted, etag, permissions, checksum
FROM oc_filecache WHERE storage = ‘3’ AND path_hash = ‘734cd2d7a6784054fb65273306339edf’

Do anyone know about such a behavior? Just let me know if yout need further data.

I thanks in advance for your support

apache:
Server version: Apache/2.4.18 (Ubuntu)
Server built: 2016-07-14T12:32:26

mysql:
mysql Ver 15.1 Distrib 10.0.29-MariaDB, for debian-linux-gnu (aarch64) using readline 5.2

redis:
Redis server v=3.0.6 sha=00000000:0 malloc=jemalloc-3.6.0 bits=64 build=687a2a319020fa42

2

Additional information:
Uploading a small file (size: 1mb) takes about 1-2 minutes. After a short time the upload hangs at 100% for the remaining time.
I think this behavior has something to do with the problem described above.

3

Yes, the slow upload of a large number of files is a known problem. I did some test back in OC 8.2:

Since then, they should have improved the number of required queries but I didn’t run test on a newer version yet.

4

I’m not sure if we have the same problem here.

In my case there are about 30k queries(select from oc_filecache) while uploading a single(!) file. Meanwhile these queries were porcessed by the server, I can upload files with normal speed! (It is always the first file which hangs)

5

Hello everybody,
a small update / test:

  • creating a backup of my whole nextcloud environment
  • removing the directory appdata/preview
  • rescan the filesystem with occ files:scan --all
    => The high number of queries are gone and the upload of a single file is fast again. I started to create thumbnails with the preview-generator app. The more files were created, the higher the number of queries.
6

Let’s ask the guys responsible for previews: @rullzer @georgehrke @oparoz