FreeTDS Development Group

[Frediano Ziglio <freddy77 AT gmail.com>]

2009/2/8 Federico Alves <sales AT minixel.com>:
> I am having discussion on the Asterisk Developer's List about a bug that
> forces me to use Perl and AGI and not the Connection-Pooling mechanism.
> Tilghman says that my interpretation of the issue is wrong. The facts are:
> using Perl I can open a connection, execute a query, close it and thus I can
> have dozens, possibly hundreds of simultaneous queries to the SQL. Using
> Asterisk's ODBC sharing mechanism, it chokes to death and after a few
> queries per second, Asterisk starts to slow down and "constipate", many
> queries are queued and just a few are executed. I blame Asterisk and
> Tilghman blames Freetds. Frediano Ziglio was involved in debugging this
> issue with Digium's Steve Murphy, but it never worked. It would be great if
> Frediano or somebody with advanced knowledge of the Asterisk code would
> intervene. Just send me your comments and I will post them to the List or
> kindly signup for the Asterisk Developer's List.
> Federico
>

Mmm... I reminds a coding for some prepared statements and a debug for
a core, but nothing for performance (beside the prepared
statements)... but I must admit my memory isn't so fine. I don't know
Asterisk's ODBC sharing mechanism but if sharing require the use of a
single connection you could have serialization which obviously lead to
slow down. Could you send me Asterisk version you are using??

> 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.
>
> --
> Tilghman
>

I don't know asterisk that much to support any opinion. There are some
fast way to check for locking; few cpu usage and few connection to
server.

freddy77