[Dovecot] Berkeley DB with Dovecot

[/dev/rob0]

On Mon, May 21, 2012 at 02:37:28PM +0200, Sven Hartge wrote:
> Jerry <jerry at 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-compared-to-sqlite
> > http://www.oracle.com/technetwork/database/berkeleydb/learnmore/bdbvssqlite-wp-186779.pdf
> 
> 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 at 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: