phpMyAdmin Advisor & upload long pause

I notice that my first upload sync with nextcloud was having some issues… would get alot of long pauses before it would start back up again… uploaded a total of 140gbs… Now with owncloud I never had this upload issuess… Strange thing is I really didn’t get any errors… I got a fetal webdav error and another php error but i think that happened when I forced closed the desktop app… Everything did upload but it took 3x as long as owncloud used to. Now two things I did afterwards… I changed the php memory limit to 768 and I dropped the strict-Transport-Security from 31536000 to the recommended size of 15768000 but not being an expert I am not sure any of that did anything. But in phpMyAdmin in the adviser I am getting a lot of suggestions and was wondering if I should be concerned about any of them… Would be helpful if anyone knows if I should be taking any of these suggestions seriously…

I am on mysql 5.7…


The query cache is not enabled —

The query cache is known to greatly improve performance if configured correctly. Enable it by setting query_cache_size to a 2 digit MiB value and setting query_cache_type to ‘ON’. Note: If you are using memcached, ignore this recommendation.


There are too many joins without indexes.

This means that joins are doing full table scans. Adding indexes for the columns being used in the join conditions will greatly speed up table joins.


The rate of reading the first index entry is high.

This usually indicates frequent full index scans. Full index scans are faster than table scans but require lots of CPU cycles in big tables, if those tables that have or had high volumes of UPDATEs and DELETEs, running ‘OPTIMIZE TABLE’ might reduce the amount of and/or speed up full index scans. Other than that full index scans can only be reduced by rewriting queries.


The rate of reading data from a fixed position is high.

This indicates that many queries need to sort results and/or do a full table scan, including join queries that do not use indexes. Add indexes where applicable.


The rate of reading the next table row is high.

This indicates that many queries are doing full table scans. Add indexes where applicable.


MyISAM key buffer (index cache) % used is low.

You may need to decrease the size of key_buffer_size, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.


The % of indexes that use the MyISAM key buffer is low.

You may need to increase key_buffer_size.


The rate of opening tables is high.

Opening tables requires disk I/O which is costly. Increasing table_open_cache might avoid this.


Can you check your webserver/php logfiles as well? Fatal error should give you some more details on what is going wrong.

If you compare to owncloud, which version are you comparing to (9.0, 8.2, 8.1)? I suppose you run the same configuration.

Tuning your mysql-server can be a good idea, tuning-primer.sh is a nice script which gives you some advice about cache-settings. Regarding the upload performance: https://forum.owncloud.org/viewtopic.php?f=31&t=30083#p95636.

But optimizing is a different thing, if you still get errors, you should handle them first. If you get file-locking problems, you should consider using redis (see documentation).

I was on the latest owncloud when I switch over… I always did upgrades when they came out… As for the same server setup… NO… I am useing Ubuntu 16.04 that is mangeded by serverpilot which installs apache 2.4 with nginx… mysql 5.7 and php7… as for the error this is what I got… I also installed Redis as a memcache and enabled file locking

locked",“Exception”:“OCA\\DAV\\Connector\\Sabre\\Exception\\FileLocked”,“Code”:0,“Trace”:"#0 \/srv\/users\/serverpilot\/apps\/nextcloud\/public\/apps\/dav\/lib\/connector\/sabre\/file.php(490): OCA\\DAV\\Connector\\Sabre\\File->convertToSabreException(Object(OCP\\Lock\\LockedException))\n#1 \/srv\/users\/serverpilot\/apps\/nextcloud\/public\/apps\/dav\/lib\/connector\/sabre\/file.php(103): OCA\\DAV\\Connector\\Sabre\\File->createFileChunked(Resource id #40)\n#2 \/srv\/users\/serverpilot\/apps\/nextcloud\/public\/apps\/dav\/lib\/connector\/sabre\/directory.php(134): OCA\\DAV\\Connector\\Sabre\\File->put(Resource id #40)\n#3 \/srv\/users\/serverpilot\/apps\/nextcloud\/public\/3rdparty\/sabre\/dav\/lib\/DAV\/Server.php(1036): OCA\\DAV\\Connector\\Sabre\\Directory->createFile(‘Dell Win7 Pro 6…’, Resource id #40)\n#4 \/srv\/users\/serverpilot\/apps\/nextcloud\/public\/3rdparty\/sabre\/dav\/lib\/DAV\/CorePlugin.php(523): Sabre\\DAV\\Server->createFile(‘software\/window…’, Resource id #40, NULL)\n#5 [internal function]: Sabre\\DAV\\CorePlugin->httpPut(Object(Sabre\\HTTP\\Request), Object(Sabre\\HTTP\\Response))\n#6 \/srv\/users\/serverpilot\/apps\/nextcloud\/public\/3rdparty\/sabre\/event\/lib\/EventEmitterTrait.php(105): call_user_func_array(Array, Array)\n#7 \/srv\/users\/serverpilot\/apps\/nextcloud\/public\/3rdparty\/sabre\/dav\/lib\/DAV\/Server.php(459): Sabre\\Event\\EventEmitter->emit(‘method:PUT’, Array)\n#8 \/srv\/users\/serverpilot\/apps\/nextcloud\/public\/3rdparty\/sabre\/dav\/lib\/DAV\/Server.php(248): Sabre\\DAV\\Server->invokeMethod(Object(Sabre\\HTTP\\Request), Object(Sabre\\HTTP\\Response))\n#9 \/srv\/users\/serverpilot\/apps\/nextcloud\/public\/apps\/dav\/appinfo\/v1\/webdav.php(55): Sabre\\DAV\\Server->exec()\n#10 \/srv\/users\/serverpilot\/apps\/nextcloud\/public\/remote.php(138): require_once(’\/srv\/users\/serv…’)\n#11 {main}",“File”:"\/srv\/users\/serverpilot\/apps\/nextcloud\/public\/apps\/dav\/lib\/connector\/sabre\/file.php",“Line”:546,“User”:“stuminelli”}",“level”:4,“time”:“2016-07-17T11:01:17+00:00”,“method”:“PUT”,“url”:"/remote.php/webdav/software/windows-operating-systems/windows-7-all-versions/Dell%20Win7%20Pro%2064bit.ISO-chunking-3472690014-563-518",“user”:“stuminelli”}
{“reqId”:“WMVoS6r+jJh6o2VcTtsB”,“remoteAddr”:“127.0.0.1”,“app”:“PHP”,“message”:“filemtime(): stat failed for /nextcloud-data/nextcloud/data/stuminelli/cache/windows-7-ultimate-x64.iso-chunking-1769032044-257 at /srv/users/serverpilot/apps/nextcloud/public/lib/private/files/storage/local.php#159”,“level”:3,“time”:“2016-07-17T11:05:58+00:00”,“method”:“POST”,“url”:"/",“user”:“stuminelli”}