[asterisk-dev] ODBC improvements: Community opinions requested

[Brad Watkins]

My preference, certainly, would be the removal of code and allowing
unixODBC to "do its job".  But I also can't speak with any authority about
its fitness for purpose.  I've used unixODBC connection pooling and never
had any issues, but that's merely anecdotal.  I'd definitely like to hear
from other folks about their experiences before saying it's the right way
forward.

Assuming there are no major blockers with using unixODBC's connection
pooling, there is still the issue of end user training.  But given the
potentially positive impact such a change would have, I think it's worth
it.  We'd want to ensure that CHANGES and other documentation was updated
to reflect the change, obviously.

Regards,
- Brad

On Mon, Nov 30, 2015 at 6:14 PM, Mark Michelson <mmichelson at digium.com>
wrote:

> Hi folks,
>
> I apologize if this ends up being a wall of text
>
> In Asterisk 13, we introduced two new features, the PJSIP channel driver
> and the sorcery configuration management tool, that have both made the
> stability and reliability of Asterisk better. However, when it comes to
> realtime configuration with ODBC, we've noticed that there is a bad trend
> of contention within res_odbc in Asterisk. This is because the PJSIP
> channel driver, unlike chan_sip, is multithreaded, meaning that multiple
> threads may be attempting to read or alter the database at the same time.
> Also, the use of sorcery, along with the use of different types of objects
> within the PJSIP channel driver, results in more database queries than were
> being performed with chan_sip.
>
> Part of this issue has been addressed by adding a memory cache to sorcery,
> and there will be work down the pipe to decrease the number of unneeded
> database queries. In the meantime, though, addressing the contention issue
> is something I want to get fixed.
>
> The big reason for the contention is that a default configuration of
> Asterisk results in a single connection to the database being created and
> shared between all threads. This connection is lock-protected, resulting in
> many threads potentially waiting on the lock in order to be able to use the
> connection. The res_odbc.conf file allows for an administrator to configure
> multiple connections for Asterisk to use, but this limitation is incredibly
> strict. If, for instance, you configure Asterisk to use five database
> connections, all five are in use, and a query is initiated, it will fail,
> rather than doing something graceful, like waiting for a connection to
> become available. Predicting the number of connections you'll need is
> incredibly difficult, and so you're likely to end up causing yourself big
> problems unless you set the limit to something absurdly high.
>
> The obvious solution to this problem is to use multiple database
> connections by default in Asterisk. One provision provided by unixODBC is a
> connection pooling option that can be enabled in odbcinst.ini. With this,
> any time that a connection is requested, it is pulled from a pool of
> connections. When disconnected, rather than being freed, the connection is
> added back to the pool. unixODBC has configuration options that allow for
> connections in the pool to eventually time out if they haven't been used in
> a while, and it has an option to allow for the connection to only be used a
> certain number of times before it is freed.
>
> The nice thing about this is that the connection pooling logic is provided
> completely under the hood, meaning all Asterisk ever has to do is request a
> connection and then disconnect it. A huge swath of code in res_odbc could
> be outright removed. There are two potential problems, though:
>
> * There is no way for us to enforce the use of connection pooling from our
> code. If connection pooling is not configured in odbcinst.ini, then
> Asterisk would end up creating and freeing database connections for each
> query, which could be costly in terms of performance (though admittedly it
> may still be better than the current situation).
> * The amount of data out there about connection pools is pretty limited. I
> had to figure out how it worked and what options were available from
> reading the unixODBC source code. More importantly, I don't have much data
> on the reliability/reputation of unixODBC connection pooling.
>
> I'm curious what opinions people have about what would be best for
> Asterisk with regards to using multiple ODBC connections. Do you have any
> experience using unixODBC connection pooling? Was the experience positive?
> Was the performance difference between using connection pooling vs. not
> using connection pooling noticeable?
>
> The alternative to using the connection pooling built into unixODBC would
> be to modify what currently exists in Asterisk to be less strict. The goal
> would be to work similar to how unixODBC works, in that multiple
> connections can be in use, connections can be re-used, and the limit on the
> number of open connections is "softer" than it currently is in Asterisk.
> The big disadvantage to this is having to maintain a connection pool within
> Asterisk instead of letting a lower level handle it for us. However, if
> connection pooling has a noticeable performance improvement over constantly
> opening/closing connections, then this may be a tempting alternative since
> we otherwise would not be able to enforce the use of connection pooling in
> unixODBC.
>
> I want to know what community opinion is with regards to how to proceed
> here. Do we write ODBC code in such a way that it has no knowledge of
> connection pooling and *strongly* urge users to enable it in odbcinst.ini,
> or do we modify the existing connection pooling code in res_odbc to be more
> user-friendly?
>
> Thanks in advance,
> Mark Michelson
>
> --
> _____________________________________________________________________
> -- Bandwidth and Colocation Provided by http://www.api-digital.com --
>
> asterisk-dev mailing list
> To UNSUBSCRIBE or update options visit:
>   http://lists.digium.com/mailman/listinfo/asterisk-dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.digium.com/pipermail/asterisk-dev/attachments/20151201/4e45447d/attachment.html>