Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Upload of many Files really slow #331

Closed
myzinsky opened this issue Feb 12, 2013 · 160 comments

Comments

@myzinsky
Copy link

commented Feb 12, 2013

Expected behaviour

Upload of a lot of MP3 Files (4GB) should be fast.

Actual behaviour

Upload Only 6 Files (ca 40mb) in 4 hours!

Server configuration

Operating system: Debian Stable

Web server: Apache2

Database: Mysql

PHP version: PHP Version 5.3.3-7+squeeze14

ownCloud version: 4.5.6

Client configuration

Client version: 1.2

Operating system: Debian Unstable

OS language: German

Installation path of client: /usr/bin/owncloud

Logs

ownclouds://xxx/Blue Moon
02-12 14:18:20:533 oc_module: owncloud_stat ownclouds://xxx/Blue Moon called
02-12 14:18:20:678 oc_module: Simple propfind result code 207.
02-12 14:18:20:678 oc_module: Server Date from HTTP header value: Tue, 12 Feb 2013 13:18:20 GMT
02-12 14:18:20:679 oc_module: Ok: Time delta remained (almost) the same: 0.
02-12 14:18:20:679 oc_module: => Errno after fetch resource list for ownclouds://xxx/Blue Moon: 0
02-12 14:18:20:679 oc_module: Working on file Blue Moon
02-12 14:18:20:679 oc_module: :> Subtracting 0 from modtime 1360675100
02-12 14:18:20:679 oc_module: STAT result from propfind: Blue Moon, mtime: 1360675100
02-12 14:18:20:679 oc_module: Directory of file to open exists.
02-12 14:18:20:679 oc_module: PUT request on //xxx/Blue%20Moon/Robben%20Ford%20-%20My%20Everything.mp3!
02-12 14:18:20:679 oc_module: Sendfile handling request type PUT.
02-12 14:18:20:679 oc_module: Put file size: 6858752, variable sizeof: 8

This is an example, it waits a really loooong time :( (Paths shortened with xxx)

Web server error log

Deprecated: Directive 'register_long_arrays' is deprecated in PHP 5.3 and greater in Unknown on line 0

There is a lot of this stuff, but i'm not shure if this is realted to owncloud

ownCloud log (data/owncloud.log)

{"app":"media","message":"error reading title tag in '/var/www/owncloud/xxx.mp3'","level":2,"time":1360575380}### Expected behaviour
Upload of a lot of MP3 Files (4GB) should be fast.

@danimo

This comment has been minimized.

Copy link
Contributor

commented Feb 12, 2013

Please provide details about your network setup (speed/hubs/switches/wifi).

@myzinsky

This comment has been minimized.

Copy link
Author

commented Feb 12, 2013

I tried it from my home, where i have a standard dsl connection. And from University where I'm directly connected to DFN (http://www.dfn.de/fileadmin/1Dienstleistungen/XWIN/Netzentwurf_X-WiN.pdf)

At home I have a Fritzbox as router and I use Wifi there. At university im behind at least two switches and a firewall and I use a cable connection.

@danimo

This comment has been minimized.

Copy link
Contributor

commented Feb 12, 2013

Are you seeing the same data rate regardless of the connection?

@myzinsky

This comment has been minimized.

Copy link
Author

commented Feb 12, 2013

From home it was actually a little bit faster. bit still very slow... someone in the IRC channel suggested that this behavior could be due to a php time-out bug in 4.5.x.

@dragotin

This comment has been minimized.

Copy link
Contributor

commented Feb 12, 2013

On 12.02.2013 21:51, myzinsky wrote:

From home it was actually a little bit faster. bit still very slow... someone in the IRC channel suggested that this behavior could be due to a php time-out bug in 4.5.x.

Hmm. I am not sure.

I think we could do two things:

  1. We could use the new upload progress API in csync to write a specifc
    performance log file that gives us concrete data.
  2. based on that we need to investigate where speed loss happens, ie. in
    client, on network transport or on the server side.

The client does a lot of requests in a row. Is it be possible that this
is missunderstood as DOS attack by any part of the network
infrastructure and because of that speed limited? I don't know.

@myzinsky

This comment has been minimized.

Copy link
Author

commented Feb 12, 2013

sounds like a plan, what to do?

@dragotin

This comment has been minimized.

Copy link
Contributor

commented Feb 12, 2013

@myzinsky: Maybe you want to try to capture some http traffic that can be investigated. http://justniffer.sourceforge.net/ might be an interesting tool for that.

@myzinsky

This comment has been minimized.

Copy link
Author

commented Feb 13, 2013

i tried the tool, and i tried wireshark! But I dont see anything because its ssl encrypted!

@myzinsky

This comment has been minimized.

Copy link
Author

commented Feb 13, 2013

Every 20s one data packet is transmitted, and sometimes i got this:

8230 365.037659000 172.xx.xx.xx 62.xx.xx.xx ICMP 590 Destination unreachable (Port unreachable)

Maybe that is the problem

@myzinsky

This comment has been minimized.

Copy link
Author

commented Feb 13, 2013

So I think the problem is not realted to mirral, it is something with my connection or the apache server.
If i upload files from KDE's dolphin to ownlcoud its also very very slow!

@myzinsky

This comment has been minimized.

Copy link
Author

commented Feb 14, 2013

i tried uploading 200 mbs with my mac fom the university, its running very very fast only some seconds!
So the problem is not the server, its mit linux laptop! Is mirral using the same library for webdav access as kde's dolphin?

@danimo

This comment has been minimized.

Copy link
Contributor

commented Feb 14, 2013

@myzinsky No, different implementations. Port unreachable is odd. Is it possible that apache runs out of available workers or that there is some kind of rate-limiting firewall in place? It could be that those behave different depending on the implementation.

@mkudro

This comment has been minimized.

Copy link

commented Feb 27, 2013

I guys, i will jump into conversation since i do have similar issues as myzinsky. If i try to upload large amounts of files..well..it's just impossible. I have tried to sync my pictures directory +/- 30Gb large, containing some 20000 photos. Sync-client (linux version 1.2) couldn't handle it - it seemed that sync was in progress, but progress was excruciatingly slow, some 2Gigs in 8 hours.
And since my ISP was having some network quality issues for awhile (stability, millisecond drops...i think they are heavily using deep package inspections on their clients :) ) I naturally assumed it was network problem.
Next day i went to my father house where my server is stationed (he has fiberglass 100MB up/down connection) and tried to sync files through LAN - same effect.
It seems impossible for client to sync large amounts of files at once. I did synced documents directory (some 100MB +/- 300 files) and it was done within a minute or so, i think less.
And i do not think it's a apache issue either, when i rsynced (photos) files to server, then refreshed my owncloud "files" page whole pictures directory (30GB) was parsed by apache within couple of minutes.
Their are one other things could be relevant to this. Whenever i start sync client on my desktop http daemon throws following errors in the endless (and constant) loop:
PROPFIND /files/webdav.php/my_sync_folder HTTP/1.1" 401 291
PROPFIND /remote.php/webdav/my_sync_folder HTTP/1.1" 207 13797
Which is very strange, considering that authentication went well - sync is started, i see files showing up - very slowly but it shouldn't through 401 errors at me.
And i know that version 1.2 (which i'm using) should talk to server through remote.php and not webdav.
Same errors are generated when akonadi owncloud service is configured to sync calendar and contacts with KDE desktop.

PS some version facts of applications i'm using:
Linux (Fedora 17) with kernel 3.7.9-101
KDE 4.9.5
Sync-client/mirall 1.2
csync 0.7

@Deltachaos

This comment has been minimized.

Copy link

commented Feb 27, 2013

Same for me

@tytgatlieven

This comment has been minimized.

Copy link

commented Mar 14, 2013

Hi guys

I'm having the same issue. The owncloud client log looks like this:

03-14 13:10:20:519 oc_module: MKdir on /owncloud/remote.php/webdav/Documents/PhD/documentation/
03-14 13:10:21:055 oc_module: => open called for owncloud://tv/owncloud/remote.php/webdav/Documents/PhD/documentation/Modeling Cognitive Radio to Improve the Performance of Communications Part3.pdf
03-14 13:10:21:055 oc_module: Stating directory owncloud://tv/owncloud/remote.php/webdav/Documents/PhD/documentation
03-14 13:10:21:055 oc_module: owncloud_stat owncloud://tv/owncloud/remote.php/webdav/Documents/PhD/documentation called
03-14 13:10:21:236 oc_module: Simple propfind result code 207.
03-14 13:10:21:236 oc_module: Server Date from HTTP header value: Thu, 14 Mar 2013 12:10:21 GMT
03-14 13:10:21:236 oc_module: Ok: Time delta remained (almost) the same: 0.
03-14 13:10:21:236 oc_module: => Errno after fetch resource list for owncloud://tv/owncloud/remote.php/webdav/Documents/PhD/documentation: 0
03-14 13:10:21:236 oc_module: Working on file documentation
03-14 13:10:21:236 oc_module:   :> Subtracting 0 from modtime 1363263020
03-14 13:10:21:236 oc_module: STAT result from propfind: documentation, mtime: 1363263020
03-14 13:10:21:236 oc_module: Directory of file to open exists.
03-14 13:10:21:236 oc_module: PUT request on /owncloud/remote.php/webdav/Documents/PhD/documentation/Modeling%20Cognitive%20Radio%20to%20Improve%20the%20Performance%20of%20Communications%20Part3.pdf!
03-14 13:10:21:236 oc_module: Sendfile handling request type PUT.
03-14 13:10:21:236 oc_module: Put file size: 158574, variable sizeof: 8
03-14 13:10:22:861 oc_module: http request all cool, result code 201
03-14 13:10:22:862 oc_module: Add a time delta to modtime 1269854080: 0
03-14 13:10:22:862 oc_module: Setting LastModified of /owncloud/remote.php/webdav/Documents/PhD/documentation/Modeling%20Cognitive%20Radio%20to%20Improve%20the%20Performance%20of%20Communications%20Part3.pdf to 1269854080
03-14 13:10:24:512 oc_module: owncloud_stat owncloud://tv/owncloud/remote.php/webdav/Documents/PhD/documentation/Modeling Cognitive Radio to Improve the Performance of Communications Part3.pdf called
03-14 13:10:25:283 oc_module: Simple propfind result code 207.
03-14 13:10:25:283 oc_module: Server Date from HTTP header value: Thu, 14 Mar 2013 12:10:24 GMT
03-14 13:10:25:283 oc_module: Ok: Time delta remained (almost) the same: -1.
03-14 13:10:25:283 oc_module: => Errno after fetch resource list for owncloud://tv/owncloud/remote.php/webdav/Documents/PhD/documentation/Modeling Cognitive Radio to Improve the Performance of Communications Part3.pdf: 0
03-14 13:10:25:283 oc_module: Working on file Modeling Cognitive Radio to Improve the Performance of Communications Part3.pdf
03-14 13:10:25:283 oc_module:   :> Subtracting -1 from modtime 1269854080
03-14 13:10:25:283 oc_module: STAT result from propfind: Modeling Cognitive Radio to Improve the Performance of Communications Part3.pdf, mtime: 1269854081
03-14 13:10:25:283 oc_module: Get file ID for owncloud://tv/owncloud/remote.php/webdav/Documents/PhD/documentation/Modeling Cognitive Radio to Improve the Performance of Communications Part3.pdf: 5141be301cd00
03-14 13:10:25:283 _get_md5: MD5 for owncloud://tv/owncloud/remote.php/webdav/Documents/PhD/documentation/Modeling Cognitive Radio to Improve the Performance of Communications Part3.pdf: 5141be301cd00
03-14 13:10:25:283 _csync_push_file: FINAL MD5: 5141be301cd00
03-14 13:10:25:283 _csync_push_file: PUSHED  file: owncloud://tv/owncloud/remote.php/webdav/Documents/PhD/documentation/Modeling Cognitive Radio to Improve the Performance of Communications Part3.pdf
03-14 13:10:25:283 _store_id_update: SYNCED remember  dir: PhD/documentation/Modeling Cognitive Radio to Improve the Performance of Communications Part3.pdf

So basically the slow syncing originates from:

  1. The PUT of the file: 158K in 1.6s
  2. Setting LastModified: 1.65s
  3. Owncloud_stat: 0.7s

Thus a total processing time of around 4s.

My setup:
100Mbps link between server and pc. No DNS resolution problems (0.4m average ping time both sides)
server: dual core amd 5050e, ubuntu raring amd64, MYSQL owncloud backend
client: owncloud 1.2.1

I have noticed a relatively high load on the mysql process on the server. Perhaps it is a slow query for the lastmodified and owncloud_stat calls?

kind regards

Lieven

@MTRichards

This comment has been minimized.

Copy link

commented Mar 14, 2013

@tytgatlieven Have you tried to create an index on the database table oc_properties? Reports of performance increases when this is done.

alter table oc_properties add index(userid);
alter table oc_properties add index(propertypath);
alter table oc_properties add index(propertyname);

@tytgatlieven

This comment has been minimized.

Copy link

commented Mar 14, 2013

Hi MTRichards

I have executed your queries.
The log:

03-14 13:41:48:545 oc_module: PUT request on /owncloud/remote.php/webdav/Documents/CaTy/code/sensor/codesourcery/share/doc/arm-arm-none-eabi/html/gccint/Fragments.html!
03-14 13:41:48:545 oc_module: Sendfile handling request type PUT.
03-14 13:41:48:545 oc_module: Put file size: 4336, variable sizeof: 8
03-14 13:41:51:702 oc_module: http request all cool, result code 201
03-14 13:41:51:702 oc_module: Add a time delta to modtime 1313994618: 0
03-14 13:41:51:702 oc_module: Setting LastModified of /owncloud/remote.php/webdav/Documents/CaTy/code/sensor/codesourcery/share/doc/arm-arm-none-eabi/html/gccint/Fragments.html to 1313994618
03-14 13:41:55:344 oc_module: owncloud_stat owncloud://tv/owncloud/remote.php/webdav/Documents/CaTy/code/sensor/codesourcery/share/doc/arm-arm-none-eabi/html/gccint/Fragments.html called
03-14 13:41:57:565 oc_module: Simple propfind result code 207.
03-14 13:41:57:565 oc_module: Server Date from HTTP header value: Thu, 14 Mar 2013 12:41:55 GMT
03-14 13:41:57:565 oc_module: Ok: Time delta remained (almost) the same: -2.

So it seems this still takes long.
Is the add index executed immediately, or should I wait for the index to be built in background and then review the values?

Forgot to mention: I'm on owncloud 5.0 rc2

@MTRichards

This comment has been minimized.

Copy link

commented Mar 14, 2013

Hi! How big is your installation? I have been a lot faster than you seem to be, I think - do you have a lot of files or folders in your root ownCloud folder on the desktop?

@bartv2 which tables are queried in an eTag request? Any other tables touched when the server is asked for an eTag?

@tytgatlieven

This comment has been minimized.

Copy link

commented Mar 14, 2013

Hi

I'm currently syncing some 75GB of files in something around 350000 files

Owncloud web interface mentions atm: 825.7 MB

Server has 8GB memory, about 1GB used, almost solely running apache and mysql atm.

@bartv2

This comment has been minimized.

Copy link
Member

commented Mar 14, 2013

@MTRichards for etag the filecache table is queried, this has been changed in OC5.
@icewind1991 do you have more information?

@tytgatlieven

This comment has been minimized.

Copy link

commented Mar 16, 2013

Hi all

I've been doing som performance verification, and these are my conclusions/thoughts up to now:

  1. The mysql queries themselves, called from core: lib/files/cache/cache.php are all pretty fast (around 1ms for the get and getFolder calls), so this does not explain the large delays

  2. I assume the client side does not introduce large delays.

  3. So the delay should be inside the php code itself

So I looked into speedup of php, and found some info here:
http://docs.joomla.org/How_do_you_tune_for_speed_with_PHP5_and_MySQL5%3F

I did step 1: php cache thorugh APC, howto can be found here:
http://www.howtoforge.com/apc-php5-apache2-debian-etch

measured the delays again, and noticed significant improvements:

  1. The PUT of the file: avg around 4s, independent of file size
  2. Setting LastModified: 0.5s
  3. Owncloud_stat: 60ms

The load on apache has dropped significantly due to the caching mechanism.
However, the put files request is still very long, and I assume this can be due to the mysql queries, in which I did not yet check the time needed for a file put.

BTW: I currently have indexes as follows:
oc_filecache: path_hash, etag, fileid
oc_permissions: fileid

So I would at least recommend to enable APC, since this already gives a nice performance boost.

Hope this helps you guys forwards somewhat

@tytgatlieven

This comment has been minimized.

Copy link

commented Mar 17, 2013

Hi all

Did some more logging / analyzing of what is going on.
You can find the mysql log and the client log here: https://gist.github.com/tytgatlieven/5183718

My observations:

  1. around 600 queries are needed for a single file upload (put, proppatch and propfind), which seems a bit much imo. (I'm certainly no expert in PHP + MYSQL, so I might be wrong)

  2. When I issue a few of the queries myself, I noticed that the SELECT queries on oc_filecache take around 0.5 - 1ms each, so relatively fast. However, the UPDATE statements to oc_filecache take a lot longer, namely 45ms. As there are 80 UPDATE statements during a single file upload, then this already takes around 3.6s, which is a major part of the total processing time I have noticed. Add to this value the 500 SELECT queries taking 0.75ms average and we have a MYSQL processing time of around 4s out of a total of 4.7s on my system. The remainder seems to be PHP, networking and client overhead, but is at this moment negligible imo.

So my advise for increased owncloud client performance is the following (on top of enabling APC):

  1. Reduce the amount of UPDATE statements issued, or significantly improve their performance. (Indexing reduces the performance of these statements I believe)

  2. Reduce the amount of SELECT statements only when 1) is improved

I won't have as much time in the coming weeks as I have put in in the last few days, but I'm certainly willing to test updated code or give additional information.

@bartv2, @MTRichards, @icewind1991 : I believe you guys know a lot more about this than I do. Especially since this turned out to be a core problem, and not a client problem. I looked into the core code somewhat, but it takes me way to long to grasp everything since I'm no php expert.
I hope someone of you could bring this to the attention of the core developers. I'm certainly willing to help testing, but I'm not able to make sufficient time to write code myself.

Hope this is of help

Lieven

@mkudro

This comment has been minimized.

Copy link

commented Mar 18, 2013

Lieven, thnx for you detailed explanation and your time you have put in this ticket...makes things bit clearer now. Hopefully devs look in to this soon, it's hard to believe that we are the only ones having problem with this. Are we only ones who trying upload/sync large amount of files with owncloud server? I really wondering what are experiences of others who try to use owncloud to full extend.

@tafinho

This comment has been minimized.

Copy link

commented Mar 18, 2013

Hi,

You're not certainly the only one seeing this behavior.
On my test case, there is around 200.000 files weighting around 300GB. The
last time I tested, it took around 3 full days to synchronize.
Although this is far from optimal, I'm more concerned with possible data
loss than performance at this point. There still exist some data loss bugs
withstanding. After those are solved, people can take care of performance,
IMHO.

No dia Segunda-feira, 18 de Março de 2013,
mkudronotifications@github.comescreveu:

Lieven, thnx for you detailed explanation and your time you have put in
this ticket...makes things bit clearer now. Hopefully devs look in to this
soon, it's hard to believe that we are the only ones having problem with
this. Are we only ones who trying upload/sync large amount of files with
owncloud server? I really wondering what are experiences of others who try
to use owncloud to full extend.


Reply to this email directly or view it on GitHubhttps://github.com//issues/331#issuecomment-15043207
.

@happyreacer

This comment has been minimized.

Copy link

commented Apr 4, 2013

I use OC on a NAS. How I like to monitor the harware and see the following when copying a large file.
In a WEBDAV connection in a directory on the NAS and connecting via the OC diret remoute.php LAN is busy. When copying many small files, I see a big difference. Directly over the LAN utilization WEBDAV is also at maximum but the OC remoute.php the NAS processor goes to its maximum and it will be 4-6 apache services launched parallel. The transmission is then in the b / s range.
No difference whether Mirall and dolphin.
I think it's a problem of remote.php?!

ownCloud 5.0.3
PHP 5.3.14
MySQL 5.1.36
apache 2
only LAN-connection
More questions?

@munialabs

This comment has been minimized.

Copy link

commented Apr 4, 2013

ok, i had also extremely slow speed with my setup, tested with three different computers on two different local networks (i only do local stuff). all configurations were running on a debian/mint server, and when switchin to archlinux, the problem was gone. so in case you have a raspberry pi, try loading the debian version (current one mid march i think), install apache via apt-get, d/l owncloud and test speeds, i believe its reproducible. the issue creator also had debian installed, thats maybe a hint. i think there is something in the debian/mint apache default configuration that makes things incredible slow (10kb/s max).
as said, configuring archlinux with owncloud on the very same network, very same clients, very same hardware did produce normal results.

@munialabs

This comment has been minimized.

Copy link

commented Apr 4, 2013

sorry, i think i have to take that last comment back: it is true that bigger files (images of 2-3 mb each) did sync very well, but as soon as i come back to my usual data (7507 directories, 92250 files in roughly 11 GB) it seems to be slow again.
is it the raspberry pi not providing enough performance??

@tytgatlieven

This comment has been minimized.

Copy link

commented Apr 5, 2013

@munialabs : It's indeed the case. In essence it comes down to this:
The file upload itself is relatively fast, even on a slower device. However, There is a lot of processing overhead per file. Thus a 1M or a 10M file take about equal time to be processed in a local network.

@happyreacer: Its indeed due to the php code, but not in remote.php. I only did a quick check, but I believe it is due to the underlying sabredav. Again, not the transfer of the file takes time, it is the updating of the file information in the SQL database which takes time. So it's most likely not sabredav itself, but the way the link is made to update the state information. It does to many requests for identical queries to the database. (See my earlier comment including the mysql logfile for a single file upload by a client)

@bartv2

This comment has been minimized.

Copy link
Member

commented Apr 5, 2013

@tytgatlieven you have a couple of good points, i looked at your sql, owncloud/core#2747 will reduce the select queries quiet a bit, and you're right reducing the update queries will also make it faster. Could you create issues with your suggestions in the core repo? And also for the documentation?

@danger89

This comment has been minimized.

Copy link

commented Nov 20, 2015

WebDav is a HTTP protocol and has nothing to do with MySQL.

@scolebrook

This comment has been minimized.

Copy link
Contributor

commented Nov 20, 2015

@danger89 ownCloud isn't just webDAV. Try running it without a database of some kind. SQLite is a low performance database backend. If you want speed, use MySQL. If you have more than one person or sync client using ownCloud at the same time. SQLite is really only good as a data store for a single application which is why the sync client uses it for the journal.

You can see the point @moscicki makes from the stats I supplied further up this thread. While I was uploading a folder of about 1000 small files there was a query rate increase of approximately 1000 queries a second. This lasted for a 6 minute period. That's an average of about 360 queries to upload each file. Now there's folder structure in there to consider too. It had to discover that directories didn't exist, make them and so on. But 300+ queries is a massive amount for a single file. With the network round trip between the web and database servers running at about 0.8ms (there's a load balancer in there too) a very large percentage of the 6 minutes for my test is clearly network latency due to the massive amount of communication to the db.

This has been an area of continuous improvement with each major version. But it's clear that there still room for more.

@aronovgj

This comment has been minimized.

Copy link

commented Nov 22, 2015

It is still innodb_flush_log_at_trx_commit on the server side. set it to 0 or 2.

@aronovgj

This comment has been minimized.

Copy link

commented Nov 22, 2015

Also nobody could explain to me what this means:
"Beware that it means that the actual IO flush is only done every one second and no more at each commit (see [1]), you may lose data so don't use this for critical systems"

What can be lost during this second and can this corrupt my files if mysql has a properly working recovery?

Want to add that I set the variable to 2 a month ago and I did not lose any data until now.

@scolebrook

This comment has been minimized.

Copy link
Contributor

commented Nov 25, 2015

@aronovgj By setting innodb_flush_log_at_trx_commit to 0 you are turning off MySQL's properly working recovery mechanism and relying solely on the capabilities of your storage backend (battery protected write cache, etc). Better have some good power management in place.

Setting it to 2 results in the log being written for every write query but the other write operations happening once a second at most. This is not ACID compliant unless you're in a galera cluster where the cluster as a whole is still ACID compliant.

There is only a marginal performance improvement between 2 and 1. 1 is where everything is flushed to disk for every write query and is the only option that is ACID compliant for a stand alone MySQL server.

But 0 doesn't change the results of the test I described above. While there is less disk io on our database nodes, it takes the same time to sync the same folder. This reinforces my opinion that in my case, the time is a function of network latency. The latency is very, very small in our environment, about as small as you can possibly make it, but there are literally hundreds of queries for each file and it really adds up.

I have no idea what the content of all these queries is but I think it's a pretty safe bet that many of them are selects for exactly the same information over and over again by different parts of the code. It would be good if that information could be cached to avoid hitting the db over and over.

@ghost

This comment has been minimized.

Copy link

commented Dec 6, 2015

now we need to catch up on server side. Please track in the mentioned server bugs.

from #331 (comment)

@danger89

This comment has been minimized.

Copy link

commented Dec 7, 2015

If it's a server side issue, indeed we need to take a look at the 'core' issues instead. Give this issue more credits for example:
owncloud/core#20967

@Croydon

This comment has been minimized.

Copy link

commented Dec 12, 2015

14.329 files, 557 directories, 91,8 MB
and it runned for serveral days multiple hours. I can't even say exactly how long.
However, it's obviously a problem with small files.

@aronovgj

This comment has been minimized.

Copy link

commented Dec 12, 2015

@scolebrook Although setting innodb_flush_log_at_trx_commit = 2 may not be ACID compliant I strongly disagree that the performance improvement is marginal compared to setting it to 1, reducing HDD load from 100% to about 5% and reducing the sync time from several months to a few hours.
Had about 70000 files, about 30GB altogether but also a lot of small files which took several seconds per file.

@scolebrook

This comment has been minimized.

Copy link
Contributor

commented Dec 12, 2015

@aronovgj You aren't seeing the difference between 1 and 2 specifically. You're seeing how the requirements of the two impact storage that is constrained on iops. 2 does an append write to the log for every write type query. A very quick and simple write operation. Even slow storage can accomplish it quickly. It then writes the actual tables once per second these are insert type writes are take significantly longer than an append as some data needs to be re-arranged. Grouping these operations together and writing once a second allows for significant optimization. 1 on the other hand doesn't allow that optimization because there is no grouping of work.

If you have battery backup for your systems such that you're satisfied that the risk of data loss is sufficiently mitigated, 2 will give better performance. The level of performance difference between 2 and 1 will be a function of how slow the storage is. Where storage isn't the bottle neck, the difference, while still obvious, is not that significant. Changing os level settings or other settings in MySQL can have a bigger impact. This is our situation. Network latency is the primary constraint for us with our storage never getting above 10% utilization for any configuration in MySQL.

We actually run with 0 which is an order of magnitude faster than 2 because our infrastructure is backed up and protected to the extent where the it'd require the loss of the data center to lose that one second of data between writes. In that event we'd be falling back to our most recent offsite tape which would be at least 24 hours old. And that is exactly the same level of risk for using either 1 or 2 because loss of the data center includes loss of the disks.

Each situation is different. Knowing the details about these options is important to make sure your configuration is appropriate for your environment and your needs. Everything in IT is a compromise in one way or another.

Something else that will improve performance is spending some time tuning io at the operating system level. Our MySQL nodes are virtualized so we use the noop scheduler and let our storage environment do the optimization of write operations to it's disks rather than have Linux spend time optimizing only to have the storage backend re-optimize the MySQL traffic together with the traffic from other systems. For hardware servers it's probably best to measure performance with your workload and try cfq and deadline to find the right one for your situation.

At the end of the day, the issue is the number of queries that are required. 300+ per file must be far more than what is actually needed. If this can be optimized further as it has in previous versions, then it improves network and disk use.

@aronovgj

This comment has been minimized.

Copy link

commented Dec 12, 2015

Thanks for the clarification @scolebrook. So seems like we are talking about different problems in different environments. I have a home server with two HDDs, one of which is only for backups every six hours. And I actually can live with data loss of up to six hours. However I'd rather live without the risk and with proper writing speeds. I haven't found another solution for this until now.

@ghost

This comment has been minimized.

Copy link

commented Dec 12, 2015

Instead of discussing this here in the client tracker you can also have a look at the core issue tracker as already suggested twice. If the amount of sql queries can be reduced maybe the change of innodb_flush_log_at_trx_commit is not needed at all.

@scolebrook

This comment has been minimized.

Copy link
Contributor

commented Dec 12, 2015

@aronovgj Sounds like you're making informed decisions to suit your needs and your equipment. Excellent.

@RealRancor is right. Talking about tuning MySQL or other things doesn't get the job of improving ownCloud done. It's helpful information but it's still a workaround.

@TheManOfTheMAn

This comment has been minimized.

Copy link

commented Jan 23, 2016

I am using Owncloud 8.2 on a Ubuntu 14.4.3 LTS Virtual set up including MySql and I also faced this issue.

I can confirm that this is caused mainly due the database overhead for each new file what will be uploaded to the server. While tweaking cache and lock with APCu and Redis just gave me a very little improvements an also tweeking Php, Apache and MySql configs did not make a big difference. It was absolutely clear as I moved the MySql database from the HDDs to a SSD in my system.
Upload time per file took in about 10 to 15 seconds in minimum (even for very small files).
After moving db to SSD for MySql its in about 1 second in minimum.

So this problem just really counts if you shift many little files (as mentioned before). Because the database overhead takes much more time than the physical upload and storing time on the local disk.

While the new desktop sync client performs multiple uploads and this in combination with the SSDs gave the upload speed a push so that is somehow acceptable now. But since we need to use Webdav a lot, the upload speed is still a pain in the ass for a large amount of little files.

Because most Webdav mounts just upload each file one by one, same problem while deleting folders. Deleting each file one after the other. This was absolutely unusable with 15 to 20 seconds per file and is still very bad with 1 to 2 seconds per file in minimum for small files (a few KB or less per file for example).

Well the problem for us is, that we need to use Webdav because we can not sync our whole external Owncloud storage to the mainly used Apple Airs with very few disk space. So that we can work with it we use Cyberduck mounting the Webdav. Download, dir listing, upload of large files, everything is perfect. Except the problem with small files.

So a possible solution would be a dramatical increase of database action going along with every uploaded file. Maybe it would be better to move as much actions to a regularly working crone job and to improve the Mysql overhead.

Another possibility would be to expand the desktop client to use methods like folder sync. like DropBox and as second option just a file browser for some folder with multi upload ability without local syncing somehow like the apple or android app. So that you see all your files remotely and that you could edit them directly without the need of syncing and even much faster than Webdav.

Or is there a possibility to make Webdav faster?

@butonic

This comment has been minimized.

Copy link
Member

commented Jan 25, 2016

Have a look at https://www.percona.com/blog/2013/09/20/innodb-performance-optimization-basics-updated/ especially innodb_flush_log_at_trx_commit if you can live with loosing 2 seconds of transactions (IMO like a failed upload on power outage). Roughly speaking, setting it to 2 will only cause data loss when the OS crashes. Setting it to 0 will make you loose data when mysql crashes. You decide. Would be interesting to see how the performance changes in your case. Let us know!

@TheManOfTheMAn

This comment has been minimized.

Copy link

commented Jan 25, 2016

Well thanks for the tip. I was scared of setting it to 2 as I just downgraded from replication mode of the database to periodical dumps. But to be honest the replication was not primary targeted to Owncloud but to a resource database what is hardly used but where data lose would be terrible, but that is not related to this discussion.

Just to inform you: Yes I moved away from replication master slave mode due performance issues way before I tested the Sql performance and posted the comment above :) – but anyway I might could live with the standard OS I/O cache (innodb_flush_log_at_trx_commit = 2) update time if you can answer me one question please:

If a users is uploading files into hies Owncloud private place, or if for example a system admin is moving files just right to a users Owncloud "files" folder, will Owncloud detected the changes and set all necessary DB entries or will this cause problems in some circumstances?

I know that the crone checks external drives for changes periodically and that there is a config setting to activate file system changes in general.

The point is, I do daily backups of the MySql database. So even if there would be a bad crash and corruption, it would be okay if all uploaded files get clean redetceted after importing an older DB dump while the files are physically present. If yes, I would try to move to "2" in this case :)

@jospoortvliet

This comment has been minimized.

Copy link

commented Feb 7, 2016

@TheManOfTheMAn have you tried it since then? Would be interesting to hear if this helped, if it did, it is something we could document.

guruz added a commit that referenced this issue Mar 2, 2016
Propagator: Pump in more requests if we think current ones are quick
Helps with small file sync #331
When I benchmarked this, it went up to 6 parallelism and
was about 1/3 faster than the previous fixed 3 parallelism.
Doing more than 6 is dangerous because QNAM limits to 6 TCP
connections and also the server might become a bottleneck.

Should also help for #4081
@guruz

This comment has been minimized.

Copy link
Collaborator

commented Mar 3, 2016

FYI, the 2.2 client will try to do more parallelism for small files. This will at least help to use the network better even if the server could become more the bottleneck..
You can try with a nightly build from this morning.. http://download.owncloud.com/desktop/daily/
(3rd of March)

@TheManOfTheMAn

This comment has been minimized.

Copy link

commented Mar 12, 2016

WARNING
Pardon me for my late answer!

I would NOT RECOMMEND setting the innodb_flush_log_at_trx_commit to 2.

I tested it and there was very less up to no increase in the speed performance while uploading files.
So there might be a little or no effort at all, depending on your system, using innodb_flush_log_at_trx_commit = 2 but a total increase of system instability.

Just one day after I set it to 2 my system got a hard reset by an admin (should'nt happen normally but just imaging a power outage or system freeze). However the Owncloud database was corrupted and it was not possible to repair or restore it. Even if it happened at 3 o'Clock am with no user operations running on the database, it was enough for completely corrupting it. So we could get it back to work dropping all tables and pushing an database backup to the server!

Now, to be clear: innodb_flush_log_at_trx_commit = 2 will not only cause a little loose of data if the system goes down unexpected or crashes for some reasons. It will possibly corrupt your database and leave it unusable at all. So in my opinion this isn’t a valid option and it won't give you a significant performance increase!

My final solution: I moved the database on SSDs to get a acceptable upload speed.

@aronovgj

This comment has been minimized.

Copy link

commented Mar 12, 2016

I did some hard restards to my system having it set to 2 and nothing happened. But that is finally a proper answer to my question. I wanted to know what can happen.

@mckaygerhard

This comment has been minimized.

Copy link

commented Jan 11, 2017

my 50c apport:

i have some instalations used for backup and, noted that those with postgres dont have so many problems like those with mysql, i'm not a mysql-like user due i think its a poor database and in those instalations that have mysql with owncloud i must restart the database to property get working afeter that kind of problems

mckaygerhard added a commit to hypery2k/owncloud that referenced this issue Jan 12, 2017
clarify cpu load if used poor engine db as mysql
clarify cpu load if used poor engine db as mysql, see similar issues like:
* owncloud/client#331
* owncloud/core#21915
in productin env i used postgresql and no cpu load has noted! loading of main page are faster rather than mysql engine, inclusively using sqlite are more faster!
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
You can’t perform that action at this time.