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

[Sergey Okhapkin]

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.

On Sunday 08 February 2009, Venefax wrote:
> I think that "pooling" the connections and reusing them is far more
> efficient than using Perl, and all the books I read from SQL programming
> say that what taxes SQL is opening and closing the connection. It should be
> left open and reused. The Perl alternative is just a workaround. However, I
> cannot explain why Asterisk chokes with many calls. And that is exactly
> what happens: the calls get stuck in the func_odbc code and they never
> arrive to the database except several seconds later, when the caller is
> already gone. Steve Murphy could not figure out the bottleneck. But I am
> 100% sure that Freetds has nothing to do with this, because I am using it
> successfully with Perl, except that the connections are closed and reopen.
> I cannot see clearly why Tilghman does not see a conceptual link between
> the facts and the explanation. Maybe my logic is flawed, but I cannot see
> where.
>
> -----Original Message-----
> From: asterisk-dev-bounces at lists.digium.com
> [mailto:asterisk-dev-bounces at lists.digium.com] On Behalf Of Tilghman Lesher
> Sent: Sunday, February 08, 2009 1:27 AM
> To: Asterisk Developers Mailing List
> Subject: Re: [asterisk-dev] 1.4 and CDRs -- The Breaking Point
>
> On Saturday 07 February 2009 14:25:30 Venefax wrote:
> > I did not test it. I don't think that the driver has anything to do with
> > this. If you read carefully what has been written you may achieve the
> > same conclusion.
> > a) Freetds allows me to execute 100+ simultaneous queries to the
> > database, on separate channels. Open Connection, Execute, Close
> > Connection. b) Asterisk slows down to a halt in the same scenario. The
> > difference is that the connections are never closed, are "pooled".
>
> If you believe that reconnecting for every single query would make a
> difference, that is a very easy change.  However, given the single-engine-
> multi-instance aspect, I doubt this will have a positive effect.  But I'd
> be happy to code it, if you're willing to test it.
>
> > If you compare a) to b), there is no way to blame the driver. This
> > absolutely absurd. The driver opens a connection and keeps a list of open
> > connections in its internal structure. It is a black box. This black box
> > does in fact work as expected, because at the same second I may have
>
> dozens
>
> > of independent queries being processed using Perl.
>
> I think you're revealing a fundamental lack of understanding about the
> codebase.  The only way that I could see a relevant comparison is if your
> Perl
> process was multi-threaded, running Fast AGI, and running multiple queries
> without ever shutting down.  As it stands, I believe that you're running
> multiple Perl instances, which means that you aren't testing FreeTDS for
> concurrent queries, the way Asterisk runs.  As a result, your determination
> that the driver is not at fault is sorely lacking.
>
> > I think that a portion of the sharing mechanism inside Asterisk's ODBC
> > technology is keeping a lock longer than necessary.
>
> You're free to think whatever you like; the difference here is that the
> entire
> codebase is open source, so all are welcome to look and determine for
> themselves if any part is holding any lock for longer than necessary.  I've
> reviewed the codebase multiple times, and I've found nothing to backup such
> a
> claim.