[Dovecot] mysql lookup on another host performance q?

[Francisco Wagner C. Freire]

http://wiki2.dovecot.org/Authentication/Caching



On Fri, Apr 8, 2011 at 10:33 PM, Patrick Domack <patrickdk at patrickdk.com>wrote:

> 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'
>>
>
>
>
>