[asterisk-dev] ODBC improvements: Community opinions requested

[Marek Červenka]

+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
=======================================