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

[Venefax]

I always write all my selects "select * from table with (nolock)". That is
the correct syntax. I also keept a record or when the stored procedure
started to execute, in a tracetable, and in fact, the execution never
arrived in SQL server until several seconds after it reached func_odbc,
which proves the "constipation" problem.
Federico

-----Original Message-----
From: asterisk-dev-bounces at lists.digium.com
[mailto:asterisk-dev-bounces at lists.digium.com] On Behalf Of Sebastian
Sent: Monday, February 09, 2009 2:50 PM
To: 'Asterisk Developers Mailing List'
Subject: Re: [asterisk-dev] 1.4 and CDRs -- The Breaking Point

It could be the select locking the tables in mssql, try for example select *
from table (nolock)



-----Original Message-----
From: asterisk-dev-bounces at lists.digium.com
[mailto:asterisk-dev-bounces at lists.digium.com] On Behalf Of Kaloyan Kovachev
Sent: lunes, 09 de febrero de 2009 05:02 p.m.
To: Asterisk Developers Mailing List
Subject: Re: [asterisk-dev] 1.4 and CDRs -- The Breaking Point

> 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



_______________________________________________
--Bandwidth and Colocation Provided by http://www.api-digital.com--

asterisk-dev mailing list
To UNSUBSCRIBE or update options visit:
   http://lists.digium.com/mailman/listinfo/asterisk-dev
 

__________ Information from ESET Smart Security, version of virus signature
database 3838 (20090209) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
 

__________ Information from ESET Smart Security, version of virus signature
database 3838 (20090209) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 


_______________________________________________
--Bandwidth and Colocation Provided by http://www.api-digital.com--

asterisk-dev mailing list
To UNSUBSCRIBE or update options visit:
   http://lists.digium.com/mailman/listinfo/asterisk-dev