Search the Asterisk Blog

Asterisk, ODBC, and Connections

By Kevin Harwell

As mentioned in a previous post there have been some fundamental changes made to the Asterisk ODBC resource module, res_odbc. Specifically, the single connection bottle neck was removed in favor of a reusable connection pool model implemented within unixODBC itself.

Upgrade ODBC for Stability

Unfortunately these changes, while beneficial, have caused a few problems. These occurred mainly on connection or disconnection attempts for people using the new res_odbc changes. What was discovered was that upgrading the unixODBC driver as well as the database specific ODBC driver (PostgreSQL, MySQL, etc. ODBC driver) would resolve these issues. With that said, it is highly recommended to upgrade both the unixODBC and the relevant ODBC database drivers. Doing so should help avoid any bugs or problems due to running outdated versions of those libraries.

Pooling for Efficiency

Even after installing the latest ODBC drivers some setups experienced a bit of a “slow down”. After a bit of digging it was found that the current version of the unixODBC library (2.3.4) has a problem with its connection configuration caching. The most efficient way to work around this was to just have Asterisk handle the connection pooling itself. This made it so Asterisk is no longer dependent upon unixODBC for this functionality, thus essentially bypassing the problem.

Systems wishing to fully utilize this new connection pooling mechanism within Asterisk can set the following configuration option within a respective database connection section in their res_odbc.conf file, for example:

This represents the maximum number of connections that can be open at a given time. If not set it defaults to one, or using a single connection. However, for those using a version of unixODBC greater than or equal to 2.3.2 you can set the value higher. This should then result in a significant performance improvement with regards to database access.

Note, since Asterisk is now managing the connection pool the “Pooling” option in unixODBC is now inconsequential. It can be left at, or set to, its default value of “No”.

What’s the maximum number of connections I should use for my system? In order to help figure that out the current connection count and limit can now be viewed when using the Asterisk “odbc show” CLI command:

The Asterisk database connection pool functionality can be found in the soon to be released Asterisk 13.10, or greater.

In summary, by making sure you are using the latest database drivers for your system, and by increasing the maximum number of connections the pool can maintain you should find Asterisk realtime to be both stable and fast.

There Are 3 Comments

  • Todd Ingarfield says:

    It appears that is should be set in context name section, not general.

    • Kevin Harwell says:

      Todd,

      You are correct. I’ve updated the post to reflect that the option should be specified beneath a database connection section and not the general section.

  • Carl Fortin says:

    Here’s a small procedure for anyone that want to update UnixODBC under CentOS 6.
    Yum will not offer you anything above version 2.2.14 so it has to be done from source:

    yum remove mysql-connector-odbc.x86_64
    yum remove unixODBC.x86_64

    wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.4.tar.gz
    tar zxvf unixODBC-2.3.4.tar.gz
    cd unixODBC-2.3.4
    ./configure –prefix=/usr –sysconfdir=/etc/unixODBC
    make
    make install

    wget https://dev.mysql.com/get/Downloads/Connector-ODBC/5.3/mysql-connector-odbc-5.3.7-1.x86_64.rpm
    rpm -i –nodeps –noscripts mysql-connector-odbc-5.3.7-1.x86_64.rpm

    nano /etc/unixODBC/odbcinst.ini
    [MySQL]
    Description = ODBC for MySQL
    Driver64 = /usr/lib64/libmyodbc5w.so
    FileUsage = 1
    Pooling = Yes

    nano /etc/unixODBC/odbc.ini
    [asterisk-connector]
    Description = MySQL connection to ‘asterisk’ database
    Driver = MySQL
    Database = asterisk
    Server = localhost
    Port = 3306
    Socket = /var/lib/mysql/mysql.sock

    ldconfig

    isql –version
    unixODBC 2.3.4

Add to the Discussion

Your email address will not be published. Required fields are marked *

About the Author

Kevin Harwell

Kevin is a Software Developer at Digium. He has a diverse background in the software industry and has worked on an assortment of projects. Since joining the Asterisk team a few years ago he has been a frequent contributor to a variety of areas within the project. He also can usually be seen with a cup of hot tea.

See All of Kevin's Articles

More From
The Digium Blog

  • No items