[OpenSIPS-Users] [NEW] Database enhancements, a 25% performance boost

[Bogdan-Andrei Iancu]

Hi,

Part of the work to address the most problematic (read "slow") parts of 
OpenSIPS, several improvements were done on the DB areas (performing DB 
queries).

The end result is a 25% boost in DB queries - a 25% less time spent in 
running DB queries.


1. Prepared statements
=======================

Starting with OpenSIPS 1.5.0 the internal DB API provides support for 
prepared statements. First DB module to implement prepared statements is 
DB_MYSQL module. See more about the concept of prepared statements in 
MySQL : 
http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

A very good overview on Prepared Statements, that may help you in 
understating the advantages and disadvantages of prepared statements can 
be found at 
http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepared-statements/.

The current implementation is based on an original patch submited by 
Victor Gamov; the patch was completely reworked, re-designed and improved.


2. Memory Fragmentation
========================

A complete rework of the structures from internal DB API was done in 
order to reduce the number of memory fragments used by a DB result:
    previous number = 1 + 2 + no_cols + 1 + no_rows
    current number = 3
The number of memory fragments is now constant and what is more 
important is the fact that we avoid keep allocating very small chunks 
(like 8 bytes).

This will reduce the fragmentation of the private memory due of 
continues DB queries -> OpenSIPS is now a real long distance runner, 
with no side effect or performance decrease in time.


TESTS AND MEASUREMENTS
========================

A set of tests were conducted in order to determine the real gain of 
prepared statements. The tests tried to cover various case - 
combinations between complexity of a query and the amount of data 
transfered by a query.

Following the measurement with the new prepared statements a 25 % 
increase in performance. See the entire set of tests as well as the 
result interpretation:

                
http://www.opensips.org/pmwiki.php?n=Resources.TestsPrepareStatements


Best regards,
Bogdan