[asterisk-dev] ODBC improvements: Community opinions requested

[John Fawcett]

Hi

it might be worthwhile contacting the unixODBC authors/maintainers to
check whether connection pooling is regarded as a mainstream or
experimental feature. They may also have more info about the level of
support and stability for connection pooling withn the drivers for the
most commonly used databases.

John

On 12/01/2015 12:14 AM, Mark Michelson 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
>