[asterisk-dev] ODBC improvements: Community opinions requested

[Mark Michelson]

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