[asterisk-users] How to reuse mysql connection between AGI's

[Steve Edwards]

On Thu, 5 Aug 2010, Faheem wrote:

> Hey, Is there any way to share MySQL connection between different agi's.

No.

Each AGI is executed as a separate process. While debugging (not 
interacting with a "real" call), you can executed your AGI completely 
independent from Asterisk from the command line if you feed it appropriate 
cruft via stdin.

> Actually when call comes to asterisk box it executes various agi scripts 
> sequentially. Each script checks various values by making a 
> new MySQL connection and then execute query and then disconnects. 
> 
> So, Ideally there should be one connection, and it should be reused 
> between each agi and when a call is over it should be disconnected. Is 
> there any mechanism to reuse single MySQL connection between agi 
> scripts? The agi scripts are written in Perl

I suspect the issue is Perl, not MySQL.

If you are doing something often enough to be a performance issue, why did 
you write it in an interpreted script language instead of a compiled 
language?

In previous experiments, I demonstrated you can execute XXX AGIs written 
in c in the time you can load the interpreter, parse your script, and 
execute a single AGI written in Perl or PHP.

In that experiment, the "null-agi" only read the AGI environment and 
exited.

I added mysql_init(), mysql_real_connect(), and mysql_close() and...

On my wimpy (by current standards) 500MHz AMD Geode I can execute about 20 
"null-agi's" per second. On my almost as wimpy 1.1GHz AMD 8650 I can 
execute about 100 per second.

I'd suggest re-implementing your AGIs in c and "merging" a couple of the 
AGIs together if it makes sense for your environment.

As an alternative, you could go the fastagi() route. This means changing 
your code to execute as a daemon which means you can keep the same MySQL 
connection and you eliminate the AGI process creation overhead.

It also introduces complexity in handling simultaneous call execution, you 
become dependent on that process being available, and you lose some 
flexibility in being able to make changes to your AGIs without affecting 
calls in progress.

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