Re: [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 voy...@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'

Re: [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 patric...@patrickdk.comwrote:

 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 voy...@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'

Re: [Dovecot] mysql lookup on another host performance q?

[Voytek Eymont]

On Sat, April 9, 2011 1:52 pm, Francisco Wagner C. Freire wrote:
 http://wiki2.dovecot.org/Authentication/Caching

thanks, Francisco

so what's  good starting values... ?

 grep _cache_ *.conf
dovecot.conf:#mail_cache_min_mail_count = 0
dovecot.conf:auth_cache_size = 1024
dovecot.conf:auth_cache_ttl = 3600
dovecot.conf:auth_cache_negative_ttl = 3600



-- 
Voytek