All things Linux

[Argent Stonecutter]

Why would anyone use anything but SQLite for an embedded SQL server?

 

[Essence Lumin]

Yeah, Kmail is still there and getting love. For me a little bit strange is that they are using an embedded MySQL server for mail storage as default, and that this technology layer for it called Akonadi is always a little bit shaky.

That seems a bit off if this page is correct.

Why not use MySQL/Embedded?
We tried that as well, there are two reasons for not using it: No support for the InnoDB engine (which we need for transaction support) and poor availability (only OpenSUSE provided usable packages, needed a patched QSQL driver).

Do I need a running MySQL server?
No. Akonadi starts its own local MySQL server (unless configured otherwise, see next question). All you need is having the 'mysqld' binary installed at runtime (usually found in the mysql-server package of your distribution).

KDE PIM/Akonadi - KDE TechBase

My general question is why anyone would use mysql when there is postgresql. Often the answer is because that's all my hosting provider has to which I would say, find a different provider.

 

[Argent Stonecutter]

My general question is why anyone would use mysql when there is postgresql.

Another good option, though (like MySQL) a bit heavyweight for this application. We use both SQLite and PostgreSQL at work, depending on the problem area, which is particularly easy because SQLite follows PostgreSQL syntax as precedent where possible.

 

[Bartholomew Gallacher]

According to this FAQ Sqlite is bad at handling the numbers of concurrent connections seen in daily use.

 

[Argent Stonecutter]

According to this FAQ Sqlite is bad at handling the numbers of concurrent connections seen in daily use.

I don't know what you mean by that because you didn't link to the FAQ so I don't know what particular limitation of SQLite you're referring to, or whether it's relevant to an embedded database in a mail program. It's an embedded database, with a completely different profile to a network database. You don't connect to it over a socket or network port, each process or thread using it opens the database as a file and uses shared memory to synchronize access. This gives it a completely different performance profile than say PostgreSQL. We use it as a front end to cut the load on PostgreSQL from apps that mostly hit read-only tables, loading tables and materialized views into a local SQLite database that serves most local programs. Queries are checked before being passed on to PostgreSQL and are served from SQLite if they can be.

Also, Apple Mail use SQLite as its embedded database, which seems close to the use case in question.

 

[Bartholomew Gallacher]

I don't know what you mean by that because you didn't link to the FAQ so I don't know what particular limitation of SQLite you're referring to, or whether it's relevant to an embedded database in a mail program. It's an embedded database, with a completely different profile to a network database. You don't connect to it over a socket or network port, each process or thread using it opens the database as a file and uses shared memory to synchronize access. This gives it a completely different performance profile than say PostgreSQL. We use it as a front end to cut the load on PostgreSQL from apps that mostly hit read-only tables, loading tables and materialized views into a local SQLite database that serves most local programs. Queries are checked before being passed on to PostgreSQL and are served from SQLite if they can be.

Also, Apple Mail use SQLite as its embedded database, which seems close to the use case in question.

Essence Lumin already linked to that document, so here it is: KDE PIM/Akonadi - KDE TechBase

Why not use sqlite?
We tried. Really. It just can't handle the concurrent access very well.
Please refer to [2] for more information on this subject.

The referred link is already dead, but the Web Archive has still a copy from 2015 around:

Sqlite
Status: working with limitations, default backend for mobile systems

Supported modes: Embedded

Known issues:

• Requires newer version than the default Qt one.
• Requires patched QtSql driver to fix concurrency issues (Akonadi ships a fork of the driver with the necessary changes)
• Slower than MySQL (see e.g. http://bertjan.broeksemaatjes.nl/node/53), based on our measurments about 2-5x on the query level, but still in the milliseconds range, so that's probably not a big issue.
• Mutex-based transaction serialization in Akonadi (required because SQlite's support of concurrency might not be enough) can block clients while background operations such as a IMAP sync are going on. This is the real killer here, the more clients (including background processes like indexing) are accessing the data, the more blocking you experience. Background http://www.sqlite.org/faq.html#q5 , http://www.somacon.com/p369.php , http://www.sqlite.org/lockingv3.html , http://www.mail-archive.com/sqlite-users%40sqlite.org/msg43873.html

 

[Argent Stonecutter]

Those documents make it sound like they their work predated WAL (write-ahead log) mode, which minimizes locking and doesn't block reads while writes are going on. WAL mode has been available since 2010, but those links reference the older locking mode, and the mailing list post (I assume involving one of the developers) was from 2009.

 

[Lance Corrimal]

Why would anyone use anything but SQLite for an embedded SQL server?

Because sqlite (and mysql, for that matter) perform REALLY bad when the datafiles are on a NFS share. In that case you actually have to stick all that akonadi stuff into a database via tcp...

 

[Bartholomew Gallacher]

Well putting Sqlite files on NFS shares sounds more like an edge case for me, which most people would not use. Anyway... this design decision was made long ago, and since then they seem to have stuck by it.

 

[Argent Stonecutter]

Because sqlite (and mysql, for that matter) perform REALLY bad when the datafiles are on a NFS share.

Or any other embedded database. You don't run a database over a network file system using file layer operations, whether it's NFS, CIFS, OpenNet, or Netware, because you're trying to do locking over a file system that doesn't even support ordering of events let alone reliable locking. You don't want to use a network file system in anything but a local-writer remote-reader model (eg, pure log datastore), or a whole-file atomic operations model.

Basically, if your database is not on a local file system or in memory, you shouldn't be using an embedded database.

I spent the last fifteen years, on and off, maintaining and extending an in-memory shared-memory key-value data store, and then converting the application to SQLite because their current (last five years) query optimizer spanked my query optimizer by enough that my faster raw performance didn't matter any more.

We never even considered running a database over NFS.

Well putting Sqlite files on NFS shares sounds more like an edge case for me

It's one of those edgy teenager edge cases that end badly.

In that case you actually have to stick all that akonadi stuff into a database via tcp...

In this case the question is why you are using MySQL instead of PostgreSQL.

 

[Bartholomew Gallacher]

It's one of those edgy teenager edge cases that end badly.

There's a reason why all major RDBMS engines have TCP/IP access, yes. And one of the reasons why they are using MySQL might be that during the time they implemented that Postgres was still busy playing catch up games on speed terms with MySQL, who knows.

 

[Essence Lumin]

There's a reason why all major RDBMS engines have TCP/IP access, yes. And one of the reasons why they are using MySQL might be that during the time they implemented that Postgres was still busy playing catch up games on speed terms with MySQL, who knows.

My understanding is the speed thing was always a myth. Mysql was faster because it didn't support transactions at the time.

 

[Lance Corrimal]

Well putting Sqlite files on NFS shares sounds more like an edge case for me, which most people would not use. Anyway... this design decision was made long ago, and since then they seem to have stuck by it.

..unless your $HOME is on nfs which is quite a common setup in larger networks with unix workstations.

 

[Lance Corrimal]

In this case the question is why you are using MySQL instead of PostgreSQL.

because adding another database to an already set up database server is easier than setting up another, different databse server on the same host. or in short, because i already had a running MySQL server (and postgres support in akonadi wasn't official back then)

 

[Bartholomew Gallacher]

I guess it is quite safe to say that Postgres is feature wise far more advanced than MySQL is. But for this use case probably both RDBMS engines just can do this job, so the question whichever to choose depended more on the preference of the programmers than technical merits.

 

[Argent Stonecutter]

There's a reason why all major RDBMS engines have TCP/IP access, yes. And one of the reasons why they are using MySQL might be that during the time they implemented that Postgres was still busy playing catch up games on speed terms with MySQL, who knows.

MySQL had some backing stores that had better performance than PostgreSQL, but they did it by abandoning some part of ACID. I'm not sure they even had an ACID backing store to start with. (What Essence Lumin said)

..unless your $HOME is on nfs which is quite a common setup in larger networks with unix workstations.

I've administered a couple of networks like that and not running embedded databases is one of many compromises you make with that configuration. Particularly if you're using something like Sun's CacheFS layer .

It was becoming less common though by the time SQLite became really solid... it's only about 20 years old, remember.

You have to be real careful how your mail delivery agent works, too, even with simpler file operations. We had a bit of a debacle with UW-IMAP configured to put files in ~/Mail instead of /var/spool/something/$USER. It only ever appended to the file but didn't happen to be using a file lock that worked properly on NFS so you could corrupt incoming mail while writing out your mailbox from your mail client.

 

[Bartholomew Gallacher]

No, MySQL at its beginnings was just MyISAM. InnoDB was quite much later added to it, which was fine at the beginning. MySQL AB by the way, the company behind this database engine, was a Swedish.

InnoDB was though not a development by MySQL AB, but of Finnish software company called Innobase Oy Inc. InnoDB though was in 2005 bought by Oracle, meaning that Oracle controlled an important part of MySQL through this and many feared the worst that Oracle would try to eliminate through this graps its competitor. 2008 MySQL AB was acquired by Sun Microsystems, which later were 2010 bought by Oracle.

Since Sun/Oracle was giving MySQL quite long enough not much love, MariaDB was created 2009 by Monty Wirzenius, MySQL's original programmer, as dropin replacement for it with advanced features which MySQL not has.

 

[Lance Corrimal]

One week with openSUSE Leap 15.2, so far i had two not so fun things happen.

1. with kernel 5.7.3 much bigger kernel images - and my firewall/server at home only has 150MB in /boot. gparted to the rescue.
2. NetworkManager 1.24.2 disables wake-on-lan by default. Can be switched to enable it instead.

On the plus side: NVIDIA Prime Render offloading now works out of the box. On 15.1 you had to install inofficial X11 upgrades.

 

[Argent Stonecutter]

You had me at NetworkManager.

When I was supporting a RH network we always disabled Network Mangler unless on a laptop because it was kind of needed for Wifi. But that was several years ago and I hear it's harder to do that now.

PS: #^&*@^ cmake

 

[Noodles]

I started moving back to using Linux full time but at this point the Windows Terminal is good enough that I am not really seeing the point.

If I really need to do something Pure Linux, I can SSH to my project machine, which is just an old desktop sitting under a desk. I do need to update it though. It's running 32bit hardware and that occasionally becomes a dead stopping point on projects.