On Mon, May 21, 2012 at 02:37:28PM +0200, Sven Hartge wrote:
Jerry jerry@seibercom.net wrote:
On Mon, 21 May 2012 06:14:10 -0400 Charles Marcus articulated:
Out of curiousity...
How is the performance of SQLite? I'm assuming it is only recommended for servers that are not under heavy load...
SQLite support in Postfix is fairly young. I'm not sure about Dovecot, but again I doubt it has been around as long as real RDB systems. It hasn't existed as long as they have! 2000 May 29 was the initial Alpha release. In contrast, MySQL appeared in 1995, and PostgreSQL evolved in 1996 from a much older project.
SQLite.org has a very old article where someone did some read and write benchmarking against MySQL and PostgreSQL. In those tests it did very well.
This is what I'd expect, since there is nothing between the application and the database. The SQLite file will be cached in system RAM if it is frequently accessed, which in a moderate mail server, it certainly would be.
I don't have a busy system, but I expect it could handle anything one might throw at it.
What are the main advantages/disadvantages of using SQLite over MySQL?
At this point there is no easy frontend for SQLite-managed mail servers. If you want to give a non-technical user the ability to manage accounts, at this point, you'd probably have to write a GUI interface. (That should not be a problem for a competent programmer in just about any language.)
Being younger code, it is less well tested. If you recall, I stumbled upon a potentially serious bug in Postfix, fixed in 2.8.9 and 2.9.0. Poorly managed file permissions could have led to SQL injection attacks. (But Postfix does complain about it if the logs is not owned by and only writable by root.)
SQLite.org has a good "when to use SQLite" page: http://sqlite.org/whentouse.html
I found numerous links for just that sort of information on Google & Bing. These two seem rather informative.
http://stackoverflow.com/questions/2824135/how-fast-is-berkeley-db-sql-compa... http://www.oracle.com/technetwork/database/berkeleydb/learnmore/bdbvssqlite-...
Hmm.
Those documenets only talk about heavy writing to the database which is not involved in the Dovecot scenario discussed here, where the database is used as a data storage for the configuration which is mostly read.
Note that the same is true of BDB. I wouldn't expect SQLite to beat BDB on raw read speed, but I'd expect it to show respectably, as did the Oracle author (as he stated in the stackoverflow page.)
SQLite might vary widely depending on the schema and SQL queries. BDB, OTOH, is not going to vary in that regard: it's only a Key-to- Value mapping.
In comparing SQLite to BDB, I did optimize my own setup to reduce some of the multiple queries Postfix does. If a lookup of "user@domain" has no result, my queries also check for "domain", and that value would be returned. So it's possible that 3 simple BDB lookups are going to be beaten by 1-2 complex SQL queries in SQLite.
So the question, how fast SQLite is during read operations compared to BDB is still unanswered.
Indeed it is not. We need someone to do the testing. :)
http://rob0.nodns4.us/ -- system administration and consulting Offlist GMX mail is seen only if "/dev/rob0" is in the Subject: