Resources.InsertBuffering History

Hide minor edits - Show changes to markup

April 24, 2013, at 11:58 AM by 109.99.235.212 -
Changed lines 1-64 from:

Resources -> Performance Tests -> Insert buffering performance

This page has been visited 1845 times. (:toc-float Table of Content:)


There are quite a few OpenSIPS modules that need to store information in the database ( acc, siptrace, etc ), or that need to synchronize information with the DB from time to time, so in case of a failure, the info can be retrieved from the database ( dialog, usrloc, etc ).

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.


What was the used approach ?

Instead of flushing inserted rows one by one into the database, OpenSIPS will now store rows in memory, and only flush them to DB when a certain number of rows have piled up in memory. The flushing of the rows will be done within a single SQL command.

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.

What are the theoretical gains ?

With fewer database operations, we should gain the following

  • a better response time from the proxy
  • less load overall on the server ( OpenSIPS and mysql daemon )
  • ultimately, a higher number of CPS that can be handled by the proxy

Performance test

The tests were done using OpenSIPS 1.6.4 as reference point. OpenSIPS 1.7.0 (devel) is the candidate whose performance was measured.

The test were performed with a simple script scenario, where the proxy would only do accounting ( old type of accounting, not CDR ). The database back-end used was MYSQL, and the database server was located on a different machine than the main proxy, but in the same LAN.


Results

1. On a constant rate of 1500 CPS pushed through the proxy, the load on the mysql daemon was :

  • OpenSIPS 1.6.4 : 71.5 %
  • OpenSIPS 1.7.0 : 35.1 %

-> BOOST = ~50 % lower load on mysql

2. In terms of raw CPS, the maximum number of calls per second that the proxy could handle was :

  • OpenSIPS 1.6.4 : 1700 CPS
  • OpenSIPS 1.7.0 : 3000 CPS

-> BOOST = ~75 % higher CPS

3. In terms of wait time on the OpenSIPS server, with a total of 10 SIP listening children, the average number of children sleeping, waiting for an event was :

  • OpenSIPS 1.6.4 : 8.6 children sleeping at any given time
  • OpenSIPS 1.7.0 : 7.2 children sleeping at any given time

Conclusions

The new insert buffering feature introduced in OpenSIPS 1.7 offers great advantages, because you eventually end up with less DB queries done by the proxy, which translates into more time to handle the SIP related work.

to:

(:redirect About.PerformanceTests-InsertBuffering quiet=1 :)

June 22, 2011, at 04:43 PM by vlad_paiu -
Changed line 53 from:

-> BOOST = ~60 % higher CPS

to:

-> BOOST = ~75 % higher CPS

June 22, 2011, at 04:23 PM by vlad_paiu -
Changed line 64 from:

The new insert buffering feature introduced in OpenSIPS 1.7 offers great advantages, because you eventually end up with less DB queries done by the proxy, which translates into more time to handle the SIP work.

to:

The new insert buffering feature introduced in OpenSIPS 1.7 offers great advantages, because you eventually end up with less DB queries done by the proxy, which translates into more time to handle the SIP related work.

June 22, 2011, at 04:22 PM by vlad_paiu -
Changed line 64 from:

The new insert buffering feature introduced in OpenSIPS 1.7 offers great advantages, because you eventually end up with less DB queries done by the proxy.

to:

The new insert buffering feature introduced in OpenSIPS 1.7 offers great advantages, because you eventually end up with less DB queries done by the proxy, which translates into more time to handle the SIP work.

June 22, 2011, at 04:22 PM by vlad_paiu -
June 22, 2011, at 04:21 PM by vlad_paiu -
Changed lines 8-9 from:

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 fewer database queries is something that we should always desire.

to:

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.

Changed line 13 from:

Instead of flushing rows one by one into the database, OpenSIPS will now store rows in memory, and only flush them to DB when a certain number of rows have piled up in memory. The flushing of the rows will be done within a single SQL command.

to:

Instead of flushing inserted rows one by one into the database, OpenSIPS will now store rows in memory, and only flush them to DB when a certain number of rows have piled up in memory. The flushing of the rows will be done within a single SQL command.

June 22, 2011, at 04:20 PM by vlad_paiu -
Changed line 8 from:

As we have seen from previous performance tests, database operations are very costly, and have a serious impact on the performance of the proxy.

to:

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 fewer database queries is something that we should always desire.

June 22, 2011, at 04:18 PM by vlad_paiu -
Changed lines 40-43 from:
  1. On a constant rate of 1500 CPS pushed through the proxy, the load on the mysql daemon was :
 OpenSIPS 1.6.4 : 71.5 %
 OpenSIPS 1.7.0 : 35.1 %
to:

1. On a constant rate of 1500 CPS pushed through the proxy, the load on the mysql daemon was :

  • OpenSIPS 1.6.4 : 71.5 %
  • OpenSIPS 1.7.0 : 35.1 %
Changed lines 48-51 from:
  1. In terms of raw CPS, the maximum number of calls per second that the proxy could handle was :
 OpenSIPS 1.6.4 : 1700 CPS
 OpenSIPS 1.7.0 : 3000 CPS
to:

2. In terms of raw CPS, the maximum number of calls per second that the proxy could handle was :

  • OpenSIPS 1.6.4 : 1700 CPS
  • OpenSIPS 1.7.0 : 3000 CPS
Changed lines 56-59 from:
  1. In terms of wait time on the OpenSIPS server, with a total of 10 SIP listening children, the average number of children sleeping, waiting for an event was :
 OpenSIPS 1.6.4 : 8.6 children sleeping
 OpenSIPS 1.7.0 : 7.2 children sleeping
to:

3. In terms of wait time on the OpenSIPS server, with a total of 10 SIP listening children, the average number of children sleeping, waiting for an event was :

  • OpenSIPS 1.6.4 : 8.6 children sleeping at any given time
  • OpenSIPS 1.7.0 : 7.2 children sleeping at any given time
June 22, 2011, at 04:17 PM by vlad_paiu -
Changed lines 41-43 from:
  • OpenSIPS 1.6.4 : 71.5 %
  • OpenSIPS 1.7.0 : 35.1 %
to:
 OpenSIPS 1.6.4 : 71.5 %
 OpenSIPS 1.7.0 : 35.1 %
Changed lines 49-51 from:
  • OpenSIPS 1.6.4 : 1700 CPS
  • OpenSIPS 1.7.0 : 3000 CPS
to:
 OpenSIPS 1.6.4 : 1700 CPS
 OpenSIPS 1.7.0 : 3000 CPS
Changed lines 58-59 from:
  • OpenSIPS 1.6.4 : 8.6 children sleeping
  • OpenSIPS 1.7.0 : 7.2 children sleeping
to:
 OpenSIPS 1.6.4 : 8.6 children sleeping
 OpenSIPS 1.7.0 : 7.2 children sleeping
June 22, 2011, at 04:16 PM by vlad_paiu -
Deleted lines 59-62:

-> BOOST = ~15 % less wait time

June 22, 2011, at 04:15 PM by vlad_paiu -
Changed line 19 from:
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 preserver real-time synchronization with the DB.
to:
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.
June 22, 2011, at 04:15 PM by vlad_paiu -
Changed lines 65-66 from:

Conclusions

to:

Conclusions

June 22, 2011, at 04:14 PM by vlad_paiu -
June 22, 2011, at 04:14 PM by vlad_paiu -
Changed line 45 from:

-> BOOST = 50 % lower load on mysql

to:

-> BOOST = ~50 % lower load on mysql

Added lines 48-67:
  1. In terms of raw CPS, the maximum number of calls per second that the proxy could handle was :
  • OpenSIPS 1.6.4 : 1700 CPS
  • OpenSIPS 1.7.0 : 3000 CPS

-> BOOST = ~60 % higher CPS

  1. In terms of wait time on the OpenSIPS server, with a total of 10 SIP listening children, the average number of children sleeping, waiting for an event was :
  • OpenSIPS 1.6.4 : 8.6 children sleeping
  • OpenSIPS 1.7.0 : 7.2 children sleeping

-> BOOST = ~15 % less wait time

Conclusions

The new insert buffering feature introduced in OpenSIPS 1.7 offers great advantages, because you eventually end up with less DB queries done by the proxy.

June 22, 2011, at 03:43 PM by vlad_paiu -
Added lines 29-46:

Performance test

The tests were done using OpenSIPS 1.6.4 as reference point. OpenSIPS 1.7.0 (devel) is the candidate whose performance was measured.

The test were performed with a simple script scenario, where the proxy would only do accounting ( old type of accounting, not CDR ). The database back-end used was MYSQL, and the database server was located on a different machine than the main proxy, but in the same LAN.


Results

  1. On a constant rate of 1500 CPS pushed through the proxy, the load on the mysql daemon was :
  • OpenSIPS 1.6.4 : 71.5 %
  • OpenSIPS 1.7.0 : 35.1 %

-> BOOST = 50 % lower load on mysql

June 22, 2011, at 03:37 PM by vlad_paiu -
Added lines 20-29:

What are the theoretical gains ?

With fewer database operations, we should gain the following

  • a better response time from the proxy
  • less load overall on the server ( OpenSIPS and mysql daemon )
  • ultimately, a higher number of CPS that can be handled by the proxy
June 22, 2011, at 03:31 PM by vlad_paiu -
Changed lines 8-10 from:

As we have seen from previous performance tests, database operations are very costly, and have a serious impact on the performance of the proxy.

The tests were conducted in order to determine the real gain of the balanced search trees in cases with many registered users.

to:

As we have seen from previous performance tests, database operations are very costly, and have a serious impact on the performance of the proxy.

June 22, 2011, at 03:30 PM by vlad_paiu -
Changed lines 4-21 from:

to:

There are quite a few OpenSIPS modules that need to store information in the database ( acc, siptrace, etc ), or that need to synchronize information with the DB from time to time, so in case of a failure, the info can be retrieved from the database ( dialog, usrloc, etc ).

As we have seen from previous performance tests, database operations are very costly, and have a serious impact on the performance of the proxy.

The tests were conducted in order to determine the real gain of the balanced search trees in cases with many registered users.


What was the used approach ?

Instead of flushing rows one by one into the database, OpenSIPS will now store rows in memory, and only flush them to DB when a certain number of rows have piled up in memory. The flushing of the rows will be done within a single SQL command.

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 preserver real-time synchronization with the DB.
June 22, 2011, at 03:21 PM by vlad_paiu -
Added lines 1-4:

Resources -> Performance Tests -> Insert buffering performance

This page has been visited 1845 times. (:toc-float Table of Content:)



Page last modified on April 24, 2013, at 11:58 AM