Link To MySQL Connection

Home » Asterisk Users » Link To MySQL Connection
Asterisk Users 6 Comments

I’m making changes to an Asterisk IVR designed by someone else.

The application uses both func_odbc.conf and php agi to access an external MySQL database.

In the php routines, I would like to use the persistent connection that is established in the dialplan, rather than creating a new connection each time they run. How can I do this?

In res_odbc.conf, the context “asterisk” is established, successfully referencing “asterisk-connector” in odbc.ini.

pre-connect => yes resulting in a persistent connection at startup.

Opening a connection in the php routines takes about 5 seconds, which is unacceptable.

–Don

6 thoughts on - Link To MySQL Connection

  • Then you should analyze why it takes 5s. Opening and closing a mysql connection should take at most a fraction of a second on a local net.

    BTW, classical web sites (plain PHP and HTML) do not maintain state, so keeping the mysql connection open may not be at all possible. I forgot whether open db connections get closed automatically after the web page has been rendered, but I think so. You could test this.

    For data processing you do not want to got through Asterisk. You could actually write your own db proxy, but I still think it makes more sense to find out why opening the db connection takes so much time.

    jg

  • Then you should analyze why it takes 5s. Opening and closing a mysql connection should take at most a fraction of a second on a local net.

    BTW, classical web sites (plain PHP and HTML) do not maintain state, so keeping the mysql connection open may not be at all possible. I forgot whether open db connections get closed automatically after the web page has been rendered, but I think so. You could test this.

    For data processing you do not want to got through Asterisk. You could actually write your own db proxy, but I still think it makes more sense to find out why opening the db connection takes so much time.

    jg

    I’ll look into why the connection takes so long.

    I’m looking for a quick fix for the problem 🙂

    Long term, I think a web service would be better for what this routine is doing. It doesn’t do any telephony stuff and only requires one variable from the dialplan.

    –Don

  • You can’t, they are completely separate processes and code. Joshua Colp

    Thanks–that’s not the answer I wanted, but it sure was quick. 🙂 Is there anything that would enable me to use a persistent connection in the agi?
    –Don

    Yes. Use func_odbc in your PHP AGI. In Asterisk dialplan functions are treated like dialplan variables so you can get and set them just like you would other dialplan variables.

    If it takes 5 seconds to open a PDO DB connection inside PHP you have some OTHER problem. Eric Wieling

    Thanks, Eric. If I can’t figure out why the connection takes so long, I’ll try the func_ODBC approach.

    –Don

  • One of my AGIs (written in C) reads 3 arguments from the command line, gets 4 channel variables, reads a row from the database, and sets 3
    channel variables.

    On an AMD Athlon BE-2300 (a pretty low end processor), CentOS 4.9, Asterisk 1.2.37, MySQL 5.0.95 running on a separate host; 1,000 executions of this AGI takes about 16 seconds or about 0.016 seconds for a single execution. (60 per second.)

    On a VirtualBox VM (2 cores) on an Intel i7-3770, CentOS 5.9, Asterisk
    1.2.40, MySQL 5.0.95 running on the same VM, 25,000 executions takes about
    40 seconds or about 0.0016 seconds for a single execution. (625 per second.)

    Why does your AGI take 5 seconds?

  • look into DNS problems. Something may be trying to do an reverse DNS
    lookup and taking too long to either give up or get an answer.