Folder size repeatedly updated in database during transfer

[PVince81]

Steps to reproduce

1. Mount a remote S3 storage (the slowness will help...) as external storage under "s3"
2. Create a subdir "s3/sub"
3. Upload some text files into s3
4. Upload some text files into "s3/sub"
5. When all transfers are gone, restart the server to make sure no process was running
6. Run select storage,fileid,path,size from oc_filecache where storage=3 order by path; and observe the size of the root and the "sub" directory
7. Now upload a single text file into "sub". During the upload, repeatedly rerun the SQL query

Expected result

The folder size is only computed once at the end of the transfer

Actual result

I see the folder size of "sub" and the root "" switching repeatedly between -1 and the correct value, even within the minute after the transfer was finished!

Apps

Encryption is enabled

Versions

Observed on OC 7.0.5.

Need to check if this happens only with encryption.
Also I seem to remember that some parts of the code (write hook?) would repeatedly update the size in the cache during the transfer.

Assigning to self to investigate later.

[MorrisJobke]

@PVince81 Is this maybe related to #15549?

[PVince81]

Not sure, maybe somewhat related. I need to dig deeper.

[jcfischer]

I am also still unhappy with the performance and the number of updates. #15549 does help a bit, but I still have to manually VACUUM our database at least every 12 hours. I counted the number of slow request (>30 ms) to our database in the last 12 hours...

postgres@drive-db1:/mnt/dbdata/main/pg_log$ grep 'UPDATE "oc_filecache' *.log | wc -l
1353328

This seems like a really excessive number of updates... Our database has accumulated 16 million outdated rows in the oc_filecache table for 15 million files in the same time span.

[jcfischer]

I see around twice as many updates with a full set of parameters than with just a few:

2015-04-14 21:08:05 CEST LOG:  duration: 34.010 ms  execute pdo_stmt_00000034: UPDATE "oc_filecache" SET "mimepart" = $1, "mimetype" = $2, "mtime" = $3, "size" = $4, "etag" = $5, "storage_mtime" = $6, "permissions" = $7, "parent"=$8 WHERE ("mimepart" <> $9 OR "mimetype" <> $10 OR "mtime" <> $11 OR "size" <> $12 OR "etag" <> $13 OR "storage_mtime" <> $14 OR "permissions" <> $15 OR "parent" <> $16 ) AND "fileid" = $17

Looking through the code, it seems that https://github.com/owncloud/core/blob/stable7/lib/private/files/cache/scanner.php#L144 is the source for these update calls. I don't have the energy to guess my way further up the callstack and find potential culprits for execssively calling this function, but that might be something you might want to look into, @PVince81

/cc @MorrisJobke

[PVince81]

@icewind1991 any clues ? We're all talking about OC 7.0.5, right ?

What do you mean with outdated rows ? Do you mean it is inserting rows instead of updating and leaved orphaned filecache entries ?

[jcfischer]

@PVince81 yes 7.0.5 and yes - Postgres doesn't overwrite outdated rows. it creates a new one and leaves the old one dead to be cleaned up. That is more secure and works nicely if the table is not updated too frequently.

[PVince81]

@icewind1991 mind doing a test to see if this has been fixed in recent versions ?

[PVince81]

This should have improved a lot in 9.0 and 9.1 as the code that does the filecache updates has been optimized.

[tflidd]

Is this only for external storage? Because my upload orgy on 9.0beta1 wasn't better than OC 8.x:
#20967 (comment)

I didn't rerun tests with more current versions.

[PVince81]

@tflidd 9.0beta1 is old. Do you mean 9.1beta1 ?

Some of the queries that update the sizes and etags have been optimized to be done in one go instead of doing one query per folder.

[PVince81]

Need to recheck whether this is still the case with 9.0.7 or 9.1.3