Debounced Notify command

[ariselseng]

Office applications (specifically Excel) seems to do a LOT of changes (seems to do 1 per row when pasting for instance). My colleague pasted all rows from one csv to another and the whole server took minutes to register everything, though all changes were duplicates.

We could make Notify command remember previous event details and time, and then only trigger a change when a certain time has passed. (Maybe 250ms or something).

[MorrisJobke]

@cowai Are you talking about the external storage command?

[ariselseng]

Yes. occ files_external:notify

[ariselseng]

At work today I noticed actually like tens of duplicate events that all were in very short time (like 2s), but blocked the process for minutes. Definitely need debounce. I have only experienced this on files that were worked on with Microsoft Office programs.

[MorrisJobke]

Mind to propose some changes as code? 😉

[ariselseng]

@MorrisJobke It's coming. I am just prioritizing the much more important #14425 first.
Hold on!

[ariselseng]

@MorrisJobke Actually, a more efficient query fixes it for me. At first I did make some code to debounce it, but just using the indexes fixes it for me.

Roughly 1-2 seconds response time:

UPDATE *PREFIX*filecache SET size = -1
WHERE `path` = ?
AND `storage` IN (SELECT storage_id FROM *PREFIX*mounts WHERE mount_id = ?)'

to sub 20ms response time, due to indexes:

UPDATE *PREFIX*filecache SET size = -1
WHERE `storage` = (SELECT storage_id FROM *PREFIX*mounts WHERE mount_id = ?)
AND `path_hash` = ?'

Is there a legacy/compatibility reason why we use "IN (SELECT" instead of "= (SELECT" ?
Doing "=" was much faster.

[icewind1991]

(SELECT storage_id FROM *PREFIX*mounts WHERE mount_id = ?) can return multiple storage id's in case of external storages that available for multiple users.

switching to path_hash makes a lot of sense though

[ariselseng]

@icewind1991 Do you mean global mounts? I have one but only 1 user. Are you saying that there would be 1 storage per user then?

[ariselseng]

See these two benchmarks. Crazy difference:

MariaDB [nextcloud]> UPDATE oc_filecache SET size = 1 WHERE `storage` = (SELECT storage_id FROM oc_mounts WHERE mount_id = 7) AND path_hash = "bb9c1bf22a43d919ace26890ba6b8522";
Query OK, 0 rows affected (0.014 sec)
Rows matched: 1  Changed: 0  Warnings: 0

MariaDB [nextcloud]> UPDATE oc_filecache SET size = 1 WHERE `storage` IN (SELECT storage_id FROM oc_mounts WHERE mount_id = 7) AND path_hash = "bb9c1bf22a43d919ace26890ba6b8522";
Query OK, 0 rows affected (2.303 sec)
Rows matched: 1  Changed: 0  Warnings: 0

[ariselseng]

It is not using indexes:

MariaDB [nextcloud]> EXPLAIN UPDATE oc_filecache SET size = -1 WHERE `storage` IN (SELECT storage_id FROM oc_mounts WHERE mount_id = 7) AND path_hash = "bb9c1bf22a43d919ace26890ba6b8522";
+------+--------------------+--------------+----------------+--------------------------------------------+----------------------+---------+------+--------+-------------+
| id   | select_type        | table        | type           | possible_keys                              | key                  | key_len | ref  | rows   | Extra       |
+------+--------------------+--------------+----------------+--------------------------------------------+----------------------+---------+------+--------+-------------+
|    1 | PRIMARY            | oc_filecache | index          | NULL                                       | PRIMARY              | 8       | NULL | 525235 | Using where |
|    2 | DEPENDENT SUBQUERY | oc_mounts    | index_subquery | mounts_storage_index,mounts_mount_id_index | mounts_storage_index | 4       | func |      1 | Using where |
+------+--------------------+--------------+----------------+--------------------------------------------+----------------------+---------+------+--------+-------------+
2 rows in set (0.000 sec)

MariaDB [nextcloud]> EXPLAIN UPDATE oc_filecache SET size = -1 WHERE `storage` = (SELECT storage_id FROM oc_mounts WHERE mount_id = 7) AND path_hash = "bb9c1bf22a43d919ace26890ba6b8522";
+------+-------------+--------------+-------+------------------------------------------------------------------------------+-----------------------+---------+-------+------+-------------+
| id   | select_type | table        | type  | possible_keys                                                                | key                   | key_len | ref   | rows | Extra       |
+------+-------------+--------------+-------+------------------------------------------------------------------------------+-----------------------+---------+-------+------+-------------+
|    1 | PRIMARY     | oc_filecache | range | fs_storage_path_hash,fs_storage_mimetype,fs_storage_mimepart,fs_storage_size | fs_storage_path_hash  | 138     | NULL  |    1 | Using where |
|    2 | SUBQUERY    | oc_mounts    | ref   | mounts_mount_id_index                                                        | mounts_mount_id_index | 5       | const |    1 |             |
+------+-------------+--------------+-------+------------------------------------------------------------------------------+-----------------------+---------+-------+------+-------------+
2 rows in set (0.016 sec)