[OpenSIPS-Users] Asynchronous DB queries in OpenSIPS 1.x

[Vlad Paiu]

Hello all,

Problem :
     In the current 1.6.x OpenSIPS design, database queries are one of 
the main performance issues. Running DB queries is blocking, and in case 
the DB back-end is not answering as fast as expected, for reasons like 
high load, table locks etc, the  SIP processing children of OpenSIPS 
remain blocked until they receive a reply from the DB and cannot further 
process SIP traffic.

Solution :
     The current 1.6.x OpenSIPS design does not have an asynchronous 
reactor ( will only be present from 2.x on ) so for queries returning a 
result, like selects, we can not address this problem.

      What we can try to speed up are queries that do not return any 
results, like inserts, updates, deletes.
So far we have identified two potential solutions that would allow 
OpenSIPS not to block on these particular types of queries :

1) Extend the OpenSIPS DB core. Add extra core processes that would only 
handle queries that return no results.
     For example : The accounting module need to insert an entry in the 
DB. The module calls the insert() function. Behind the scene, this 
triggers passing all the arguments to the new core processes, via IPC 
mechanisms. The insert() then exists and the SIP children continues 
execution as if the entry has been inserted in the DB. Meanwhile, the DB 
core processes receive the new parameters, build and send the query, 
blocking if necessary.

     Advantages of this solution         : General solution, works for 
all DB drivers. Transparent for modules.
     Disadvantages of this solution    : Not an easy task. IPC 
communication for all queries.

2) Use the asynchronous functions exported by each particular DB driver.
     For example : The accounting module need to insert an entry in the 
DB. The module calls the insert() function. The particular DB module 
used has been modified to call the asynchronous functions whenever it 
can ( inserts, updates, deletes ). The function exists immediately and 
the actual operation on the DB back-end will be applied in the future.

     Advantages of this solution        : Less code to write, no extra 
overhead for IPC
     Disadvantages of this solution   : Not a general solution. While 
Postgres API fully supports asynchronous queries, Mysql only allows 
regular queries to be run asynchronously out of the box, while prepared 
statements can be ran asynchronously with some quite ugly hacks. Other 
modules that deal with an effective DB backend ( db_berkeley, db_oracle 
) would need extra documentation, and eventually, if they do not support 
asynchronous queries, inserts will block until completed for those 
particular modules.


What solution do you find is more appropriate ? Or if you have better 
solutions, please share them.

Regards,

-- 
Vlad Paiu
OpenSIPS Developer

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.opensips.org/pipermail/users/attachments/20110316/cb9a2862/attachment.htm>