[Dovecot] mysql lookup on another host performance q?

[Patrick Domack]

For my small setups of less than a few thousand users, I make a small  
script that will dump the db tables to flatfiles.

This goes for postfix, and dovecot, with dovecot using the passwd file  
backend.

I did this for a few reasons at first, mainly cause I had to split my  
mysql and mail servers across datacenters and didn't want a connection  
issue to cause everything to halt. And after that, it just worked  
well. I didn't notice a lower load on mysql though, seems dovecot and  
postfix already cached good.


Quoting Voytek Eymont <voytek at sbt.net.au>:

> Hi, new to dovecot
>
> I'm setting a small postfix/dovecot server for virtual domain/users in mysql
>
> dovecot is on one host, mysql on another
>
> seems to be working, but I'm concerned over performance overhead of this
> setup;
>
> dumb Q, is there any 'proxy mysql' or cacheing option I can use ?
>
> any suggestions appreciated
>
> also, my mail users as set as 5000/5000
> virtual_gid_maps = static:5000
> virtual_uid_maps = static:5000
>
> so, I should use uid/gid 5000/5000 in query below, yes ?
>
> thanks
>
> --
> Voytek
>
> ---------------------------------------------------------
> # dovecot --version
> 1.2.9
>
> Linux 2.6.32-21-generic-pae #32-Ubuntu SMP Fri Apr 16 09:39:35 UTC 2010
> i686 GNU/Linux
>
> # grep -v '^ *\(#.*\)\?$' dovecot-sql.conf
> driver = mysql
> connect = host=host.tld dbname=ddd user=ccc password=xxx
>
> user_query = SELECT concat('/var/mail/vhosts/', maildir) as home,
> concat('maildir:/var/mail/vhosts/', maildir) as mail, 5000 AS uid, 5000 AS
> gid, concat('maildir:storage=', quota) AS quota FROM mailbox WHERE
> username = '%u' AND active = '1'
> default_pass_scheme = MD5
> user_query = SELECT '/var/mail/vhosts/%d/%n@%d' as home,
> 'maildir:/var/mail/vhosts/%d/%n@%d' as mail, 5000 AS uid, 5000 AS gid,
> concat('dirsize:storage=', quota) AS quota FROM mailbox WHERE username =
> '%u' AND active = '1'
> password_query = SELECT username as user, password,
> '/var/mail/vhosts/%d/%n@%d' as userdb_home,
> 'maildir:/var/mail/vhosts/%d/%n@%d' as userdb_mail, 5000 as userdb_uid,
> 5000 as userdb_gid FROM mailbox WHERE username = '%u' AND active = '1'