About -> News -> DB optimization

Multi-row insert operations

As 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 (added in 1.7.) 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.
• 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.

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.