[asterisk-dev] 1.4 and CDRs -- The Breaking Point

[Kaloyan Kovachev]

> On Sunday 08 February 2009, Tilghman Lesher wrote:
> > > I have the same problem with mysql. I use func_odbc (backport form 1.6)
> > > in dialplan and asterisk chokes when call rate reaches 20-25 calls per
> > > second. CPU usage on quad core mysql box never exceeds 100%, seems like
> > > queries on pooled connections are executed sequentially but not in
> > > parallel.
> >

looking at the res_odbc code there is one thing that may slowdown the things
(or can be improved): the list is locked and traversed each time from the
beginning, while the next free object in the pool is rarely at the beginning
when there are too many queries, but this should not cause the limit of 20-25
queries per second for sure (don't know how many are executed per call)

> > Are you using the pooling feature to request 
> > multiple connections, or are you using a single shared connection?
> 
> Connection pool is turned on in res_odbc.conf:
> 
> [a2billing]
> enabled => yes
> dsn => a2billing
> username => xxxxxx
> password => xxxxxx
> pre-connect => yes
> pooling => yes
> limit => 50
> idlecheck => 3600
> 

if the problem is the list traversing you may get higher limits by lowering
the pool limit and using more pools for different tasks even if you are using
the same database for registrations, dialplan and other realtime data.

in my apps i have a pool of mysql connections and a global
"last_used_instance" variable, which points somewhere in the list (as the name
says) and i start with the next connection in the pool, which has the highest
chance to be free - being unused for the longest time among other connections.

another thing that can be a bottleneck is if the tables are locked often or
for a long time from other database queries/threads, which can't be the case
for just SELECT queries, but may also be caused from your replication thread
if there are frequent updates - in that case you just don't have any
advantages from separating the reads and writes, but as you don't see the
problem with multiple dedicated connections (without pool) it seems not to be
the case