[Owncloud] SQLite vs MariaDB/MySQL

[Klaas Freitag]

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,
>