[Dovecot] SQLite dovecot query caching
Hi,
I am wondering if Dovecot caches SQLite queries, and how well it works in high performance setups. I am particularly interested because in the below thread SQLite has been suggested as a means of Dovecot proxying connections to different ports.
http://old.nabble.com/director%3A-non-standart-ports-at-backends-td33991991....
Cheers, Dominic.
On Sun, 2012-06-24 at 03:36 +1000, Dominic Malolepszy wrote:
Hi,
I am wondering if Dovecot caches SQLite queries, and how well it works in high performance setups. I am particularly interested because in the below thread SQLite has been suggested as a means of Dovecot proxying connections to different ports.
You can enable auth cache: http://wiki2.dovecot.org/Authentication/Caching
On 24/06/12 3:39 AM, Timo Sirainen wrote:
On Sun, 2012-06-24 at 03:36 +1000, Dominic Malolepszy wrote:
Hi,
I am wondering if Dovecot caches SQLite queries, and how well it works in high performance setups. I am particularly interested because in the below thread SQLite has been suggested as a means of Dovecot proxying connections to different ports. You can enable auth cache: http://wiki2.dovecot.org/Authentication/Caching
This is a per user caching though, it will still have to perform a sql look up each time a unique user authenticates to determine what port the proxy should forward each connection. Is that accurate?
On Sun, 2012-06-24 at 04:20 +1000, Dominic Malolepszy wrote:
On 24/06/12 3:39 AM, Timo Sirainen wrote:
On Sun, 2012-06-24 at 03:36 +1000, Dominic Malolepszy wrote:
Hi,
I am wondering if Dovecot caches SQLite queries, and how well it works in high performance setups. I am particularly interested because in the below thread SQLite has been suggested as a means of Dovecot proxying connections to different ports. You can enable auth cache: http://wiki2.dovecot.org/Authentication/Caching
This is a per user caching though, it will still have to perform a sql look up each time a unique user authenticates to determine what port the proxy should forward each connection. Is that accurate?
It caches the passdb lookup. The cache key consists of the given % variables in the SQL query. So if your SQL query doesn't contain %n/%u then the cache doesn't add per-user entries.
On 24/06/12 4:57 AM, Timo Sirainen wrote:
On 24/06/12 3:39 AM, Timo Sirainen wrote:
On Sun, 2012-06-24 at 03:36 +1000, Dominic Malolepszy wrote:
Hi,
I am wondering if Dovecot caches SQLite queries, and how well it works in high performance setups. I am particularly interested because in the below thread SQLite has been suggested as a means of Dovecot proxying connections to different ports. You can enable auth cache: http://wiki2.dovecot.org/Authentication/Caching
This is a per user caching though, it will still have to perform a sql look up each time a unique user authenticates to determine what port the proxy should forward each connection. Is that accurate? It caches the passdb lookup. The cache key consists of the given % variables in the SQL query. So if your SQL query doesn't contain %n/%u
On Sun, 2012-06-24 at 04:20 +1000, Dominic Malolepszy wrote: then the cache doesn't add per-user entries.
Thanks Timo, I re-read the link you sent me, and it makes a lot more sense now. I will play around with the different variables (especially the port related ones), to get the desired result. Gah its late I should go to sleep!
On 24/06/12 04:57, Timo Sirainen wrote:
On Sun, 2012-06-24 at 04:20 +1000, Dominic Malolepszy wrote:
On 24/06/12 3:39 AM, Timo Sirainen wrote:
On Sun, 2012-06-24 at 03:36 +1000, Dominic Malolepszy wrote:
Hi,
I am wondering if Dovecot caches SQLite queries, and how well it works in high performance setups. I am particularly interested because in the below thread SQLite has been suggested as a means of Dovecot proxying connections to different ports. You can enable auth cache: http://wiki2.dovecot.org/Authentication/Caching
This is a per user caching though, it will still have to perform a sql look up each time a unique user authenticates to determine what port the proxy should forward each connection. Is that accurate?
It caches the passdb lookup. The cache key consists of the given % variables in the SQL query. So if your SQL query doesn't contain %n/%u then the cache doesn't add per-user entries.
Hi,
I had a chance to play around with this in the lab. The cache hit appears to be returning values that should actually be ignored particularly the username for the previous cached entry. This is causing it to rewrite the username using what is in the cache. The passdb config is as follows:
passdb { driver = sql args = /etc/dovecot-director/dovecot-sql.conf }
The dovecot-sql.conf config contains:
driver = sqlite connect = /tmp/dovecot-sqlite.db
password_query = select 'y' as proxy,
NULL AS password, 'y' as nopassword,
NULL AS user, NULL AS username,
case '%a'
when '110' then '9110'
when '995' then '9995'
when '143' then '9143'
when '993' then '9993' end
as port;
To demonstrate I log into POP3 first as user 'dmmailtest250' which populates the cache with (#011proxy#011port=9110#011user=dmmailtest250@mailtest.com.au#011nopassword=y). Than I log into POP3 as non existent user 'blahtest01229', which causes the passdb to return user=dmmailtest250. Below is what is captured in the logs:
Jul 13 14:16:59 mail01 dovecot: auth: Debug: client in: AUTH#0111#011PLAIN#011service=pop3#011secured#011lip=127.0.0.1#011rip=127.0.0.1#011lport=110#011rport=52873#011resp=AGJsYWh0ZXN0MDEyMjkAcGFzc3dvcmQ= Jul 13 14:16:59 mail01 dovecot: auth: Debug: cache(blahtest01229@mailtest.com.au,127.0.0.1): hit: #011proxy#011port=9110#011user=dmmailtest250@mailtest.com.au#011nopassword=y Jul 13 14:16:59 mail01 dovecot: auth: cache(blahtest01229@mailtest.com.au,127.0.0.1): NULL password access Jul 13 14:16:59 mail01 dovecot: auth: Debug: auth(blahtest01229@mailtest.com.au,127.0.0.1): username changed blahtest01229@mailtest.com.au -> dmmailtest250@mailtest.com.au Jul 13 14:16:59 mail01 dovecot: auth: Debug: client out: OK#0111#011user=dmmailtest250@mailtest.com.au#011proxy#011port=9110#011pass=password Jul 13 14:16:59 mail01 dovecot: auth: Debug: client in: AUTH#0111#011PLAIN#011service=pop3#011secured#011lip=192.168.1.151#011rip=192.168.1.151#011lport=9110#011rport=60096#011resp=AGRtb2llbWFpbHRlc3QyNTBAb3B0dXNuZXQuY29tLmF1AHBhc3N3b3Jk Jul 13 14:16:59 mail01 dovecot: auth: Debug: cache(dmmailtest250@mailtest.com.au,192.168.1.151): hit: {crypt}$1$eKFvOhwW$PYfPhY05SNUIRD439USMH/#011user=dmmailtest250@mailtest.com.au#011userdb_home=/m5/dm/dmmailtest250#011userdb_quota_rule=*:backend=52428800S
I have not specified any sort of cache_key explicitly, because according to http://wiki2.dovecot.org/Authentication/Caching "For SQL and LDAP lookups Dovecot figures this out automatically by using all the used %variables as the cache key", which means that %a is the key ie the lport number which is 110 in this case. I tried specifying a NULL user and username, but that does not help.
Cheers, Dominic.
On 13.7.2012, at 8.30, Dominic Malolepszy wrote:
It caches the passdb lookup. The cache key consists of the given % variables in the SQL query. So if your SQL query doesn't contain %n/%u then the cache doesn't add per-user entries.
I had a chance to play around with this in the lab. The cache hit appears to be returning values that should actually be ignored particularly the username for the previous cached entry. This is causing it to rewrite the username using what is in the cache.
The simplest solution would be to just not put username to cache, but I'm worried it might break something else.. So for now I don't think I'm going to change it.
On 17/07/12 21:28, Timo Sirainen wrote:
The simplest solution would be to just not put username to cache, but I'm worried it might break something else.. So for now I don't think I'm going to change it.
Fair enough, last thing I want is you breaking Dovecot, I was just hoping I was doing something wrong. I have it working by including the username in the sqlite query so it is included part of the cache key, and will just up the cache ttl to something like 24 hours and see how things go, thanks again Timo!
participants (2)
-
Dominic Malolepszy
-
Timo Sirainen