Use mysql instead of sqlite?


#1

I’m having some trouble with sync speed with ownCloud, and I’ve been reading this can be caused by using sqlite for the backend rather than mysql. What is the reason for using sqlite over a standalone server? Would a PR to migrate at least ownCloud to mysql be considered?

EDIT: Migrating didn’t fix it, but I’m still curious as to why a more robust database solution isn’t being used, and whether that’s open to change.


#2

The goal was to keep things as simple as possible. Unless there’s an actual problem with Sqlite, why would we use something more complex?


#3

Fair enough, I’ll keep working on figuring out the ownCloud issue.


#4

how about performance? if someone use it for thousands of email addresses with hundreds of different domains, this may be a problem?


#5

Try it and let me know!


#6

Yep i wanna do it.
I’m using MIAB for business (mail server of a small multinational company: around 125 users and a terabyte of mail in the server) so i need to scale it up.
I’m making a fork to work on this upgrade


#7

I found it incredibly fast … My girlfriend send an email to me from her phone (thru the Internetz to Mailinabox server, then thru either polling or push request to my Android phone)…

Literally from when she hit send on her phone … til my phone made the new mail ping sound … it was literally about 6 to 8 seconds, tops.

That said, if I was using mailinabox for work, i’d probably want to use it with Mysql / Maria as well , since SqlLite doesn’t scale out as well as Mysql with lots of random 4K writes. Then again, this would matter less if you have SSD or RAID on your server.

I think the current SqlLite setup would be plenty fast for like 1-10 users for sure. It probably scales to like 25-50 users I’d think depending on the hardware and load.

But yeah for 125 users off one server, I’d want a mysql databse as well. But for that use-case the admin probably knows what they are doing anyway.


#8

I know that this is a very, very old thread, but I thought I’d chip in.

We use SQLIte as a main database for our apps, currently the largest database we host is 75GB. it grows around 500MB per week. We’re by no means experts but we know a reasonable amount of SQL and how to keep databases going.

For what we are using this DB for, SQLite is more than adequate. For 125 users on one server, it won’t even break sweat for what its used for. I looked at the schema and its trivial, it’s also not optimal as there are no indexes at all, however even if it table scanned every single entry, I can’t imagine SQLite being a bottle neck. Postfix and the anti virus software will be the bottleneck on any e-mail.

The other advantage of SQLite is its simplicity. Its easy to setup and run. Don’t mistake simplicity for loss of power though, we do a analytics run through 75GB of data once a day to generate stats. We have tuned the database and we can process 75GB of data in 90 secs. To be fair we make sure that all the data is in the indexes so it never actually hits the database so there is zero IO :slight_smile:

For a backend database like this, SQLIte is quick and easy and has very low calling latency, indeed we have used SQLite in preference to certain file IO ops.

So just in case anybody is thinking about pulling SQLIte and putting in MySQL (or any derivative), I’d be very careful as it may well be slower. The issues that are talked about with random 4K writes are not an issue for a database of this size. Putting a simple index on the two tables will give you immediate response rates as it’ll all be in memory. Opening and closing files on SQLite database is not a massive overhead.

Just my 2p worth and apologies for resurrecting an old thread,


#9

For 125 users, even a CSV file should be sufficient. The only problem, you will see is that the webinterface isn’t handy for hundreds of users. But that is was- and is not the intention of MiAB.

Most of the SQL actions are read and that is cached. if there is enough memory.


#10

@sander-schippers

Agree, A CSV would work fine, or use the postmap functions. We have map files with hundreds of email addresses in and its simply not an issue at all.

The only reason I commented onto this very old thread was to stop somebody actually trying to do a modification to move to MySQL and finding that the database slows things down, is vastly more complicated and nothing works anyway. Use the right tool for the job, SQLite is just as good a tool here as anything else and is simple. We love simple :slight_smile:

Also I just wanted to point out the SQLite is a very, very good SQL database. It is not client server (and was not designed to be) but for this case that’s absolutely fine. If anybody has any performance issues at all with SQLIte, I’d love to know because I reckon we could tune the hell of it and make it as fast as anything.

As an aside its probably the most widely used database in the world as it ships with every Apple and Android phone.

Rob


#11

Hey guys, just wanted to add that an option to have MySQL instead would be wonderful simply because it would allow one to have a WordPress site because PHP is already set up! Imagine that 30% of the web runs on Wordpress, so having the possibility of installing this on a MailInABox instance would be amazing!