Re: [Owncloud] SQLite vs MariaDB/MySQL
Hello, 2014/1/12 Chris : >>I also tried to migrate to MariaDB but failed and posted my >>experiences at > http://fabianpeter.de/cloud/owncloud-migrating-from-sqlite-to-mysql/#comment-6325 > > see the warning/disclaimer at the top of this howto and > the forum thread here: > > https://forum.owncloud.org/viewtopic.php?f=23&t=18423 > > which contains the info that this script will break further upgrades > of your ownCloud instances. Yes I am aware of that but at there is no other option available either. An in fact for my OC6 installation this script does not even work. So AFAIK the current situation is that the OwnCloud project does not provide any official migration script and there is no official nor inofficial/unsupported way to switch from SQLite to MariaDB/MySQL when the database grows. Yet another argument why MariaDB should be default from the beginning... I just read Klaas mail and his suggestions are good. I've been looking at the browser interface with logging open, and I've noticed that in particular requests to http://example.org/owncloud/index.php/apps/files/ajax/list.php?dir=a-directory-with-lots-of-content&breadcrumb=true are slow, sometimes taking tens of seconds, but after the initial request some cache seems to kick in and subsequent requests don't have a delay issue. The any benchmark should not just be about webdav calls and PROPSFIND but also on the web ui. ___ Owncloud mailing list [email protected] https://mail.kde.org/mailman/listinfo/owncloud
Re: [Owncloud] SQLite vs MariaDB/MySQL
On 12.01.2014 15:42, Sebastian Kügler wrote: On Saturday, January 11, 2014 13:18:33 Jakub Moscicki wrote: Sorry I don't have benchmarks at hand but not sure if this real needs benchmarking ;-) Playing devil's advocate here: I really like the option to use sqlite instead of MySQL, for the following reasons: - It requires less memory when the installation is idle (no daemon running) - It's a lot easier to setup, no fiddling with a database daemon, creating a db user, password management, exposing passwords in the webroot - It's way easier to backup and migrate, no SQL dumping and importing, just copying of files in one directory We go through this discussion every once in a while, every time with the same result: Yes, a full grown database serves better than SQLite, but we will not drop SQLite for the reasons Sebas mentioned. That said, here are some thoughts: - An easy and reliable way to migrate from SQLite to MySQL or Postgres (and possibly the other dbs we support) would be great. I think that could either be done as ownCloud App or also as commandline tool. I added a proposal to the GSOC collection: https://github.com/owncloud/core/wiki/GSoC,-GCI-and-OPW#database-migration-appscript Please add your input on that. - There are still opportunities to improve ownClouds performance with SQLite in the server code. That would of course benefit all db backends, and thus ownClouds general performance. Needs debugging! - Some kind of reproduceable benchmarking of the server would be cool, at least to plug it into the CI we're doing for the server. That way we would detect new performance drops right away. Simple example: create a directory tree of 1000 files, upload it via webdav, do some propfinds and measure the time of all that. Break the test if time is higher than last time the test run. Any takers? - It is a good suggestion to think again if the linux packages shouldn't better default to mysql rather than sqlite. On todays x86 computer for which we mainly provide packages, mysql really does not hurt. Any input on this? regards, Klaas Sure, performance with MySQL/MariaDB might be a a lot better, but ease of maintenance and setup can also be important. In the end, it's up to the user to make an informed choice which storage backend to use in a given case, and to be more comfortable when making this decisions, benchmarks, or even rough guidelines would be very useful, maybe even a recommendation how to measure performance of different aspects (reaction time of the server, memory consumption, especially). Cheers, ___ Owncloud mailing list [email protected] https://mail.kde.org/mailman/listinfo/owncloud
Re: [Owncloud] SQLite vs MariaDB/MySQL
On Saturday, January 11, 2014 13:18:33 Jakub Moscicki wrote: > As a user I don't know the product strategy of Owncloud Inc. but technically > it is completely clear that sqlite could only be useful for small single > user installations (like on your NAS at home) or demos. > > For anything bigger you should go for a real database, gradually scaling the > setup (from db collocated with the web server, then dedicated db server > finally dedicated db cluster). The DB schema shipped with owncloud is not > optimized so you need to do that yourself (indices,partitions,…) according > to your usage. If you can install mysql./mariadb easily on your system then > I would actually recommend that you always do that rather than sqlite. This > may be collocated with the web server if you have just on web server > anyway… That's the minimal reasonable setup IMO. > > Sorry I don't have benchmarks at hand but not sure if this real needs > benchmarking ;-) Playing devil's advocate here: I really like the option to use sqlite instead of MySQL, for the following reasons: - It requires less memory when the installation is idle (no daemon running) - It's a lot easier to setup, no fiddling with a database daemon, creating a db user, password management, exposing passwords in the webroot - It's way easier to backup and migrate, no SQL dumping and importing, just copying of files in one directory Sure, performance with MySQL/MariaDB might be a a lot better, but ease of maintenance and setup can also be important. In the end, it's up to the user to make an informed choice which storage backend to use in a given case, and to be more comfortable when making this decisions, benchmarks, or even rough guidelines would be very useful, maybe even a recommendation how to measure performance of different aspects (reaction time of the server, memory consumption, especially). Cheers, -- sebas http://www.kde.org | http://vizZzion.org | GPG Key ID: 9119 0EF9 ___ Owncloud mailing list [email protected] https://mail.kde.org/mailman/listinfo/owncloud
Re: [Owncloud] SQLite vs MariaDB/MySQL
Hi, >I also tried to migrate to MariaDB but failed and posted my >experiences at http://fabianpeter.de/cloud/owncloud-migrating-from-sqlite-to-mysql/#comment-6325 see the warning/disclaimer at the top of this howto and the forum thread here: https://forum.owncloud.org/viewtopic.php?f=23&t=18423 which contains the info that this script will break further upgrades of your ownCloud instances. -- View this message in context: http://owncloud.10557.n7.nabble.com/SQLite-vs-MariaDB-MySQL-tp11706p11721.html Sent from the ownCloud mailing list archive at Nabble.com. ___ Owncloud mailing list [email protected] https://mail.kde.org/mailman/listinfo/owncloud
Re: [Owncloud] SQLite vs MariaDB/MySQL
2014/1/12 Bernhard Posselt : > https://github.com/owncloud/core/pull/3052 Thanks for the pointer. I replied in https://github.com/owncloud/core/pull/3052#issuecomment-32121561 I also tried to migrate to MariaDB but failed and posted my experiences at http://fabianpeter.de/cloud/owncloud-migrating-from-sqlite-to-mysql/#comment-6325 I tried some time to manually fix the .sql file so that it would be compatible, but there where so many issues that I gave up. Probably easier to reinstall all of OwnCloud and reconfigure and import the data. ___ Owncloud mailing list [email protected] https://mail.kde.org/mailman/listinfo/owncloud
Re: [Owncloud] SQLite vs MariaDB/MySQL
(replying back to list) Yes I know this. I am not interested in a solution that works only for me, I want a solution that is best for everybody thus I suggest the by default OwnCloud packages should install MariaDB and not sqlite. 2014/1/12 Bernhard Posselt : > Basically don't use sqlite. The only reason why it's the default it's because > it requires no database user and password > > Am 11.01.2014 19:21 schrieb =?ISO-8859-1?Q?Otto_Kek=E4l=E4inen?= > : ... >> Also since sqlite is probably not useful even for home users, if your >> home includes more than one family member and relatives and friends >> using shared files, I'd suggest that OwnCloud should be packaged so >> that the rpm/deb packages install it by default with MariaDB. ___ Owncloud mailing list [email protected] https://mail.kde.org/mailman/listinfo/owncloud
Re: [Owncloud] SQLite vs MariaDB/MySQL
2014/1/11 Jakub Moscicki : > At the same time the web navigation (via the browser) should be not > suffering at all (how to measure that?). > > Well, it's not trivial to define what's meaningful. The place where speed matters the most, is the UI. It must be quick so that users don't have to wait after each click. How fast the files syncing is in the background is also important, but not as much. There should be some set of standard benchmark contents of the database and files, and then the benchmark could browse the browser based UI and measure response times. At least now I've seen on multiple installations that the browser interface is very slow if there are more than just a trivial amount of files. I suspect it is related to the database locking in sqlite or something.. Therefore benchmarks with different database backends would be good. Also since sqlite is probably not useful even for home users, if your home includes more than one family member and relatives and friends using shared files, I'd suggest that OwnCloud should be packaged so that the rpm/deb packages install it by default with MariaDB. ___ Owncloud mailing list [email protected] https://mail.kde.org/mailman/listinfo/owncloud
Re: [Owncloud] SQLite vs MariaDB/MySQL
I also don't think, that this needs benchmarks. Nevertheless benchmarks to measure the performance of owncloud would be nice to have. With those we could see, how changes on the code improve or degrade the performance. Agreed. For me it is not clear WHAT to measure. Upload/download times in the function of the file size?Time to make an "idle" propfind (if the account hasn't changed since the last sync)? To make things worse, it also depends on the file/directory structure. For example, we know that OC5 and to some extent OC6 has a design bug which makes the upload time dependant on the number of files in the directory (https://github.com/owncloud/core/issues/5084). Some parameters for the benchmark that come to my mind (to produce meaningful results) are: - oc version - webserver - database - installed apps - number of simulated users (size of db, # concurrent connections, # and size of files, types of requests) - https / http - storage backend (local disk, NFS mounted disk,...) - hardware + network Also if the DB is co-hosted with the web server or not (assuming it is not a DB cluster). I would tend to think that the benchmark should simulate some realistic usage expected to be handled by a single web server box + single db box. For example: - N user accounts (e.g. 200) - K concurrent desktop clients per account (e.g. 1,2 or 3) - M new files created by each client every S seconds (e.g. M=1, S=60), etc… At the same time the web navigation (via the browser) should be not suffering at all (how to measure that?). Well, it's not trivial to define what's meaningful. If there are benchmarks like this I'd really like to hear about that. Also if someone is willing and capable of setting up these kind of benchmarks, please keep me informed ;) I am also interested :-) We are currently using the opensmashbox scripts (on github) to do something similar to validate our pilot service but we don't have definitive answers (yet ;-))…. kuba -- ___ Owncloud mailing list [email protected] https://mail.kde.org/mailman/listinfo/owncloud
Re: [Owncloud] SQLite vs MariaDB/MySQL
Hi, On 01/11/2014 02:18 PM, Jakub Moscicki wrote: > Sorry I don't have benchmarks at hand but not sure if this real needs > benchmarking ;-) I also don't think, that this needs benchmarks. Nevertheless benchmarks to measure the performance of owncloud would be nice to have. With those we could see, how changes on the code improve or degrade the performance. Some parameters for the benchmark that come to my mind (to produce meaningful results) are: - oc version - webserver - database - installed apps - number of simulated users (size of db, # concurrent connections, # and size of files, types of requests) If there are benchmarks like this I'd really like to hear about that. Also if someone is willing and capable of setting up these kind of benchmarks, please keep me informed ;) Cheers, Daniel ___ Owncloud mailing list [email protected] https://mail.kde.org/mailman/listinfo/owncloud
Re: [Owncloud] SQLite vs MariaDB/MySQL
Hello, As a user I don't know the product strategy of Owncloud Inc. but technically it is completely clear that sqlite could only be useful for small single user installations (like on your NAS at home) or demos. For anything bigger you should go for a real database, gradually scaling the setup (from db collocated with the web server, then dedicated db server finally dedicated db cluster). The DB schema shipped with owncloud is not optimized so you need to do that yourself (indices,partitions,…) according to your usage. If you can install mysql./mariadb easily on your system then I would actually recommend that you always do that rather than sqlite. This may be collocated with the web server if you have just on web server anyway… That's the minimal reasonable setup IMO. Sorry I don't have benchmarks at hand but not sure if this real needs benchmarking ;-) Regards, kuba -- On Jan 11, 2014, at 1:58 PM, Otto Kekäläinen wrote: > Hello, > > Have you done any benchmarks on OwnCloud with SQLite vs. > MariaDB/MySQL? How does it perform? At what stage is real SQL server > preferred? Why is SQLlite at all used, why not always default to > MariaDB/MySQL, don't they give superior performance? > > SQLite is easier to install, but anyway OwnCloud need Apache/Nginx+PHP > and other "big" stuff and MySQL installationis anyway highly automated > in all modern distros. What matters more is how *good' OwnCloud is, > and you don't want to have a crappy database to give users impression > that OwnCloud is slow/buggy (due to locks in DB)? > > Or do you have benchmarks that show that SQLite is only a little > behind in performance, and where are those benchmarks? > > - Otto > ___ > Owncloud mailing list > [email protected] > https://mail.kde.org/mailman/listinfo/owncloud ___ Owncloud mailing list [email protected] https://mail.kde.org/mailman/listinfo/owncloud
[Owncloud] SQLite vs MariaDB/MySQL
Hello, Have you done any benchmarks on OwnCloud with SQLite vs. MariaDB/MySQL? How does it perform? At what stage is real SQL server preferred? Why is SQLlite at all used, why not always default to MariaDB/MySQL, don't they give superior performance? SQLite is easier to install, but anyway OwnCloud need Apache/Nginx+PHP and other "big" stuff and MySQL installationis anyway highly automated in all modern distros. What matters more is how *good' OwnCloud is, and you don't want to have a crappy database to give users impression that OwnCloud is slow/buggy (due to locks in DB)? Or do you have benchmarks that show that SQLite is only a little behind in performance, and where are those benchmarks? - Otto ___ Owncloud mailing list [email protected] https://mail.kde.org/mailman/listinfo/owncloud
