[OpenSIPS-Users] [NEW] Database Optimisation - multi-row insert DB ops

[Vlad Paiu]

Hello all,

As we have seen from previous performance tests, database operations are 
very costly, and have a serious impact on the performance of the proxy, 
so, whenever possible, we should try to decrease the number of DB 
queries that OpenSIPS has to do.

The solution is to merge multiple rows into a single query - instead of 
inserting rows one by one into the database (one row per insert query), 
OpenSIPS will now cache rows in memory, and only insert them to DB when 
a certain number of rows have piled up in cache. The flushing of the 
rows will be done within a single SQL command.

So, as the existing design does not allow asynchronous DB queries (so to 
optimize the  query itself), this new features tries to reduce the 
number of queries by combining several DB ops in a single query (for 
insert operations).

Two new parameters have been added to the OpenSIPS core :
     query_buffer_size - number of rows that will be flushed at once to 
the database. [1]
     query_flush_time - if a query is being held in memory longer that 
this number of seconds, it will be forcefully flushed to the database by 
a timer. This is done to preserve real-time synchronization with the DB. [2]

The feature has been currently integrated with accounting, siptrace and 
avpops, as well as with the write-back timers in the dialog and usrloc 
modules.

For more in detail explanations of how this works and how much does this 
new feature influence performance, see [3].

[1] http://www.opensips.org/Resources/DocsCoreFcn#toc67
[2] http://www.opensips.org/Resources/DocsCoreFcn#toc68
[3] http://www.opensips.org/Resources/InsertBuffering


Regards,

-- 
Vlad Paiu
OpenSIPS Developer