[asterisk-users] Lookup ${EXTEN} in database, update context/route if found... AGI?

[Steve Edwards]

> ----- "Doug Lytle" <support at drdos.info> wrote:

>> I use the mysql add-on, I'd create a subroutine that gets called at 
>> dial

On Thu, 3 Jun 2010, Tim Nelson wrote:

> Thank you for the example. I'm using something very similar to this now, 
> looking up the destination ${EXTEN}, and using GotoIf if the number of 
> results from the query are > 0. It seems to be working quite well.
>
> I do have a few additional questions however:
>
> -How reliable is the MYSQL application?

Very reliable. I have a MySQL server serving a couple of Asterisk servers 
that has been running for almost 1,000 days -- not a record I'm sure, just 
a single example.

> -In the event of a failed database query (load too high), what happens 
> to the call?

> -Also, what about slow queries? If a query takes a few seconds to 
> complete, does the call wait for the query to complete or are there 
> timeouts for the query that could result in dropped calls?

(I prefer to call MySQL from an AGI instead of the dialplan. I find 
"dialplan MySQL" ugly and limiting. I also don't like exposing a bunch of 
code in an environment where someone can "fat-finger" a difficult to find 
error.)

Back to your question, what ever you want to happen. If you don't code for 
the eventuality, a slow query will introduce an annoying delay in the 
execution of the call. If the delay is annoying enough, your customers 
will "drop" the call. If you have adequate resources and monitor the MySQL 
slow query log, this is a non-issue.

> -Are there any problems with concurrency? What if there are 50 
> simultaneous calls that need to perform this database lookup? Providing 
> the MySQL server is fine with that number of simultaneous queries, will 
> the MYSQL app and/or Asterisk handle it gracefully?

This is where a database really shines. Concurrency is a non-issue. 50 
calls accessing the database is peanuts. If they are executing the same 
query it's even less since MySQL can cache the query and the result set.

> -Does it make sense to open, disconnect, and clear every connection for 
> every call? Is there a way to have a persistent connection for better 
> performance?

I don't see this as an issue in this environment. This system handles 
about 300 simultaneous calls (again, not a record, just my example) with 
every call executing at least a dozen AGIs per call -- 3 AGIs before the 
caller hears "first audio." Each AGI creates a new process, a connection, 
a query or two, and a disconnection. Performance has never been an issue. 
I do write all my AGIs in C though. You can execute hundreds of AGIs 
written in C in the time (not counting "database" time) it takes to load a 
single interpreter (PHP or Perl) and parse your script.

A persistent connection for the life of a call would make sense. In my 
environment, merging several of the AGIs into a single AGI would make 
sense (from a performance standpoint), but the client is happy with the 
performance and won't spend money for no perceivable benefit.

-- 
Thanks in advance,
-------------------------------------------------------------------------
Steve Edwards       sedwards at sedwards.com      Voice: +1-760-468-3867 PST
Newline                                              Fax: +1-760-731-3000