+1 to unixodbc connection pooling (but i'm not using it in production)
googled for "unixodbc connection pooling issues". few results
Dne 1.12.2015 v 0:14 Mark Michelson napsal(a):
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
--
---------------------------------------
Marek Cervenka
=======================================
--
_____________________________________________________________________
-- 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
