[ejabberd] MySQL delay/lock

Jeffrey Rogiers jeffrey.rogiers at gmail.com
Mon Sep 28 20:36:18 MSD 2009


Hello All,

I have been having noticeable delays with MySQL queries during mass  
reconnects (server restarts/network fluctuations). The problem being  
that each login results in about 6-8 queries (login + custom internal  
modules). Most queries result in nothing being returned because we do  
timestamp checks, but of the ~1% of queries that return data, this  
results in 1 to 20k of data being returned. In order to deal with the  
collisions rate we configured ejabberd to use 200 MySQL connections,  
and this helps substantially.

However even with a high connection count we do get noticeable delays  
when a collision occurs. Queries are distributed to MySQL threads  
based on ejabberd_odbc_sup:get_random_pid(Host), which means  
collisions are possible even when not saturated. So during mass  
reconnects whenever these collisions occur and even when no data is  
being returned it can take between 15-50 seconds for a response to  
return.

Below is the code I've narrowed our issue down to.
ejabberd_odbc.erl (lines 69-71):
	sql_query(Host, Query) ->
		gen_server:call(ejabberd_odbc_sup:get_random_pid(Host),
			{sql_query, Query}, 60000).

I am curious if anyone has an idea on how to remedy this situation.  
I'd like to minimize collisions as well as lower the delay time. I am  
curious how gen_server:call() affects queued items. Do newer queued  
items go to the front of the line, thus pushing items that have been  
waiting back further? And would lowering the timeout (60000) time help  
long delayed queries?

Thanks,
Jeffrey Rogiers


More information about the ejabberd mailing list