search

owncloud / core

:cloud: ownCloud web server core (Files, DAV, etc.)
https://owncloud.com
GNU Affero General Public License v3.0
8.36k stars 2.06k forks source link

mimetypes should be cached across sessions and not be constantly written to?!? #39843

Closed butonic closed 2 years ago

butonic commented 2 years ago

When looking at the database queries we recently saw an analysis that revealed

{"type":"QUERY","reqId":"Yh4y36SZHtowNFh50epIzgAAAJE","diagnostics":{"sqlStatement":"SELECT `id`, `mimetype` FROM `oc_mimetypes`","sqlParams":"array ( )","sqlQueryDurationmsec":0.19311904907226562,"sqlTimestamp":1646146272.039301}}

To be the query that creates most of the work, at least for oracle:

But also the mysql performance schema lists it as the second most written table which is weird ... are we inserting mimetypes all the time????:

mysql> select * from sys.schema_table_statistics LIMIT 10;
+--------------+---------------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+------------+------------------+------------------+-----------------+
| table_schema | table_name          | total_latency | rows_fetched | fetch_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency | io_read_requests | io_read    | io_read_latency | io_write_requests | io_write   | io_write_latency | io_misc_requests | io_misc_latency |
+--------------+---------------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+------------+------------------+------------------+-----------------+
| owncloud     | oc_filecache        | 1.47 s        |        87437 | 1.39 s        |           195 | 24.93 ms       |          514 | 52.37 ms       |            0 |   0 ps         |               53 | 848.00 KiB | 236.68 ms       |               456 | 7.12 MiB   | 23.86 ms         |              246 | 1.66 s          |
| owncloud     | oc_appconfig        | 369.62 ms     |       174585 | 369.62 ms     |             0 |   0 ps         |            0 |   0 ps         |            0 |   0 ps         |                0 |    0 bytes |   0 ps          |                 0 |    0 bytes |   0 ps           |                0 |   0 ps          |
| owncloud     | oc_share            | 117.64 ms     |        59835 | 117.64 ms     |             0 |   0 ps         |            0 |   0 ps         |            0 |   0 ps         |                0 |    0 bytes |   0 ps          |                 0 |    0 bytes |   0 ps           |                0 |   0 ps          |
| owncloud     | oc_mimetypes        | 91.23 ms      |        57552 | 37.45 ms      |           108 | 53.77 ms       |            0 |   0 ps         |            0 |   0 ps         |                0 |    0 bytes |   0 ps          |                11 | 176.00 KiB | 510.30 us        |               11 | 76.67 ms        |
| owncloud     | oc_mounts           | 58.46 ms      |        14111 | 58.46 ms      |             0 |   0 ps         |            0 |   0 ps         |            0 |   0 ps         |                0 |    0 bytes |   0 ps          |                 0 |    0 bytes |   0 ps           |                0 |   0 ps          |
| owncloud     | oc_preferences      | 57.17 ms      |        22012 | 57.17 ms      |             0 |   0 ps         |            0 |   0 ps         |            0 |   0 ps         |                0 |    0 bytes |   0 ps          |                 0 |    0 bytes |   0 ps           |                0 |   0 ps          |
| owncloud     | oc_persistent_locks | 39.83 ms      |         9280 | 39.83 ms      |             0 |   0 ps         |            0 |   0 ps         |            0 |   0 ps         |                0 |    0 bytes |   0 ps          |                 0 |    0 bytes |   0 ps           |                0 |   0 ps          |
| owncloud     | oc_accounts         | 31.92 ms      |         1893 | 31.79 ms      |             0 |   0 ps         |            8 | 137.73 us      |            0 |   0 ps         |                0 |    0 bytes |   0 ps          |                 8 | 128.00 KiB | 237.77 us        |                8 | 24.98 ms        |
| owncloud     | oc_group_user       | 30.96 ms      |        13094 | 30.96 ms      |             0 |   0 ps         |            0 |   0 ps         |            0 |   0 ps         |                0 |    0 bytes |   0 ps          |                 0 |    0 bytes |   0 ps           |                0 |   0 ps          |
| owncloud     | oc_share_external   | 23.47 ms      |         3138 | 23.47 ms      |             0 |   0 ps         |            0 |   0 ps         |            0 |   0 ps         |                0 |    0 bytes |   0 ps          |                 0 |    0 bytes |   0 ps           |                0 |   0 ps          |
+--------------+---------------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+------------+------------------+------------------+-----------------+
10 rows in set (0.04 sec)

🤔 

mysql> select a.event_time, a.command_type, convert(a.argument using utf8) from general_log a WHERE user_host LIKE 'owncloud%' AND command_type='Execute' AND convert(a.argument using utf8) LIKE 'INSERTINTO oc_f%';
+----------------------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| event_time                 | command_type | convert(a.argument using utf8)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------------------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2022-03-01 20:45:10.846758 | Execute      | INSERT INTO oc_filecache( storage, path_hash, path, etag, mimetype, mimepart, permissions, name, parent, checksum) VALUES(1,'d41d8cd98f00b204e9800998ecf8427e','','5ced345f4ea649a39cf6e8f07cc272dd',(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),31,'',-1,'') ON DUPLICATE KEY UPDATE etag='5ced345f4ea649a39cf6e8f07cc272dd',mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),permissions=31,name='',parent=-1,checksum=''                                                 |
| 2022-03-01 20:45:10.860661 | Execute      | INSERT INTO oc_filecache( storage, name, parent, checksum, path_hash, path, etag, mimetype, mimepart, permissions) VALUES(1,'files',35335,'','45b963397aa40d4a0063e0d85e4fe7a1','files','0fde5fce68f7badfd1f334024fa76e9a',(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),31) ON DUPLICATE KEY UPDATE name='files',parent=35335,checksum='',etag='0fde5fce68f7badfd1f334024fa76e9a',mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),permissions=31                            |
| 2022-03-01 20:45:10.880142 | Execute      | INSERT INTO oc_filecache( storage, permissions, name, parent, checksum, path_hash, path, etag, mimetype, mimepart) VALUES(1,31,'files_trashbin',35335,'','fb66dca5f27af6f15c1d1d81e6f8d28b','files_trashbin','ccda679debe251c117bbd68f956a669b',(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd')) ON DUPLICATE KEY UPDATE permissions=31,name='files_trashbin',parent=35335,checksum='',etag='ccda679debe251c117bbd68f956a669b',mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd') |
| 2022-03-01 20:45:10.894324 | Execute      | INSERT INTO oc_filecache( storage, etag, mimetype, mimepart, permissions, name, parent, checksum, path_hash, path) VALUES(1,'1a26c26709952aa566430b6bb84e30c1',(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),31,'files',70174,'','3014a771cbe30761f2e9ff3272110dbf','files_trashbin/files') ON DUPLICATE KEY UPDATE etag='1a26c26709952aa566430b6bb84e30c1',mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),permissions=31,name='files',parent=70174,checksum=''             |
| 2022-03-01 20:45:10.905573 | Execute      | INSERT INTO oc_filecache( storage, mimetype, mimepart, permissions, name, parent, checksum, path_hash, path, etag) VALUES(1,(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),31,'versions',70174,'','c639d144d3f1014051e14a98beac5705','files_trashbin/versions','879ab489c5da522f2f0a6a53df105389') ON DUPLICATE KEY UPDATE mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),permissions=31,name='versions',parent=70174,checksum='',etag='879ab489c5da522f2f0a6a53df105389'    |
| 2022-03-01 20:45:10.916865 | Execute      | INSERT INTO oc_filecache( storage, path_hash, path, etag, mimetype, mimepart, permissions, name, parent, checksum) VALUES(1,'5128f35c9b4be13788ba41bdb6d1fc1f','uploads','379036057b5e5e04eb5f2901898643ae',(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),31,'uploads',35335,'') ON DUPLICATE KEY UPDATE etag='379036057b5e5e04eb5f2901898643ae',mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),permissions=31,name='uploads',parent=35335,checksum=''                      |
| 2022-03-01 20:45:15.953333 | Execute      | INSERT INTO oc_filecache( storage, path_hash, path, etag, mimetype, mimepart, permissions, name, parent, checksum) VALUES(1,'d41d8cd98f00b204e9800998ecf8427e','','5ced345f4ea649a39cf6e8f07cc272dd',(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),31,'',-1,'') ON DUPLICATE KEY UPDATE etag='5ced345f4ea649a39cf6e8f07cc272dd',mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),permissions=31,name='',parent=-1,checksum=''                                                 |
| 2022-03-01 20:45:15.971638 | Execute      | INSERT INTO oc_filecache( storage, path_hash, path, etag, mimetype, mimepart, permissions, name, parent, checksum) VALUES(1,'45b963397aa40d4a0063e0d85e4fe7a1','files','0fde5fce68f7badfd1f334024fa76e9a',(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),31,'files',35335,'') ON DUPLICATE KEY UPDATE etag='0fde5fce68f7badfd1f334024fa76e9a',mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),permissions=31,name='files',parent=35335,checksum=''                            |
| 2022-03-01 20:45:15.976654 | Execute      | INSERT INTO oc_filecache( storage, name, parent, checksum, path_hash, path, etag, mimetype, mimepart, permissions) VALUES(1,'files_trashbin',35335,'','fb66dca5f27af6f15c1d1d81e6f8d28b','files_trashbin','ccda679debe251c117bbd68f956a669b',(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),31) ON DUPLICATE KEY UPDATE name='files_trashbin',parent=35335,checksum='',etag='ccda679debe251c117bbd68f956a669b',mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),permissions=31 |
| 2022-03-01 20:45:15.981732 | Execute      | INSERT INTO oc_filecache( storage, permissions, name, parent, checksum, path_hash, path, etag, mimetype, mimepart) VALUES(1,31,'files',70174,'','3014a771cbe30761f2e9ff3272110dbf','files_trashbin/files','1a26c26709952aa566430b6bb84e30c1',(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd')) ON DUPLICATE KEY UPDATE permissions=31,name='files',parent=70174,checksum='',etag='1a26c26709952aa566430b6bb84e30c1',mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd')             |
| 2022-03-01 20:45:15.994635 | Execute      | INSERT INTO oc_filecache( storage, mimetype, mimepart, permissions, name, parent, checksum, path_hash, path, etag) VALUES(1,(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),31,'versions',70174,'','c639d144d3f1014051e14a98beac5705','files_trashbin/versions','879ab489c5da522f2f0a6a53df105389') ON DUPLICATE KEY UPDATE mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),permissions=31,name='versions',parent=70174,checksum='',etag='879ab489c5da522f2f0a6a53df105389'    |
| 2022-03-01 20:45:16.000005 | Execute      | INSERT INTO oc_filecache( storage, name, parent, checksum, path_hash, path, etag, mimetype, mimepart, permissions) VALUES(1,'uploads',35335,'','5128f35c9b4be13788ba41bdb6d1fc1f','uploads','379036057b5e5e04eb5f2901898643ae',(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),31) ON DUPLICATE KEY UPDATE name='uploads',parent=35335,checksum='',etag='379036057b5e5e04eb5f2901898643ae',mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),permissions=31                      |
+----------------------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

now ... this is happening on my personal instance which might be suffering from various quirks ... I'll dig deeper to see if I can come up with the root cause of this ...

Unfortunately, these queries do not show up in the diagnostics app log ... for whatever reason ...

butonic commented 2 years ago

The only upsert on the filecache is https://github.com/owncloud/core/blob/cba1e6b01e13b265b6e5356d46c3def922bbb7c5/lib/private/Files/Cache/Cache.php#L307

I have 20k small files in my trash because I use Joplin to sync notes with owncloud ... and I seem to have an old upload bitrotting in my uploads folder ...

double checking cron

butonic commented 2 years ago

ok, there were a few hundred jobs that had piled up ... but even after getting them executed a simple PROPFIND produces:

mysql> select a.event_time, a.command_type, convert(a.argument using utf8) from mysql.general_log a WHERE user_host LIKE 'owncloud%' AND command_type='Execute' AND convert(a.argument using utf8) LIKE 'INSERT INTO oc_f%';
+----------------------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| event_time                 | command_type | convert(a.argument using utf8)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------------------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2022-03-01 21:35:33.740340 | Execute      | INSERT INTO oc_filecache( storage, etag, mimetype, mimepart, permissions, name, parent, checksum, path_hash, path) VALUES(1,'5ced345f4ea649a39cf6e8f07cc272dd',(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),31,'',-1,'','d41d8cd98f00b204e9800998ecf8427e','') ON DUPLICATE KEY UPDATE etag='5ced345f4ea649a39cf6e8f07cc272dd',mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),permissions=31,name='',parent=-1,checksum=''                                                 |
| 2022-03-01 21:35:33.747383 | Execute      | INSERT INTO oc_filecache( storage, etag, mimetype, mimepart, permissions, name, parent, checksum, path_hash, path) VALUES(1,'0fde5fce68f7badfd1f334024fa76e9a',(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),31,'files',35335,'','45b963397aa40d4a0063e0d85e4fe7a1','files') ON DUPLICATE KEY UPDATE etag='0fde5fce68f7badfd1f334024fa76e9a',mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),permissions=31,name='files',parent=35335,checksum=''                            |
| 2022-03-01 21:35:33.752871 | Execute      | INSERT INTO oc_filecache( storage, path_hash, path, etag, mimetype, mimepart, permissions, name, parent, checksum) VALUES(1,'fb66dca5f27af6f15c1d1d81e6f8d28b','files_trashbin','ccda679debe251c117bbd68f956a669b',(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),31,'files_trashbin',35335,'') ON DUPLICATE KEY UPDATE etag='ccda679debe251c117bbd68f956a669b',mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),permissions=31,name='files_trashbin',parent=35335,checksum='' |
| 2022-03-01 21:35:33.759794 | Execute      | INSERT INTO oc_filecache( storage, name, parent, checksum, path_hash, path, etag, mimetype, mimepart, permissions) VALUES(1,'files',70174,'','3014a771cbe30761f2e9ff3272110dbf','files_trashbin/files','1a26c26709952aa566430b6bb84e30c1',(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),31) ON DUPLICATE KEY UPDATE name='files',parent=70174,checksum='',etag='1a26c26709952aa566430b6bb84e30c1',mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),permissions=31             |
| 2022-03-01 21:35:33.765154 | Execute      | INSERT INTO oc_filecache( storage, permissions, name, parent, checksum, path_hash, path, etag, mimetype, mimepart) VALUES(1,31,'versions',70174,'','c639d144d3f1014051e14a98beac5705','files_trashbin/versions','879ab489c5da522f2f0a6a53df105389',(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd')) ON DUPLICATE KEY UPDATE permissions=31,name='versions',parent=70174,checksum='',etag='879ab489c5da522f2f0a6a53df105389',mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd')    |
| 2022-03-01 21:35:33.771952 | Execute      | INSERT INTO oc_filecache( storage, etag, mimetype, mimepart, permissions, name, parent, checksum, path_hash, path) VALUES(1,'379036057b5e5e04eb5f2901898643ae',(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),31,'uploads',35335,'','5128f35c9b4be13788ba41bdb6d1fc1f','uploads') ON DUPLICATE KEY UPDATE etag='379036057b5e5e04eb5f2901898643ae',mimetype=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd/unix-directory'),mimepart=(SELECT id FROM oc_mimetypes WHERE mimetype='httpd'),permissions=31,name='uploads',parent=35335,checksum=''                      |
+----------------------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set, 2 warnings (0.01 sec)

so the list of folders that are being inserted into storage 1 (which is my personal home) is definitively:

there are more folders in my data dir, but they don't seem to be recreated ...

butonic commented 2 years ago

Ok that is caused by my parallel deployment of ocis: https://github.com/cs3org/reva/blob/master/pkg/storage/fs/owncloudsql/owncloudsql.go#L665-L670