Resources.TestsPrepareStatements HistoryShow minor edits - Show changes to markup April 24, 2013, at 02:19 PM
by
- Changed lines 1-140 from:
Resources -> Performance Tests -> MySQL prepared statementsThis page has been visited 25467 times. (:toc-float Table of Content:) 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. The 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. 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/ . It is essential to understand that prepare statements do not bring only performance, but more security also! What are the theoretical gains ?Client side:
Server side:
Limitations ?Query cache does not work for prepared statements, but considering that the OpenSIPS data is very large and various, it is not something to affect the performance (as the below results shows). Prepared statements have a gain in speed only if used more than once - for this reason, only the queries which are repetitively used in OpenSIPS are converted to prepared statements ; initial load are still using standard queries. Performance testsThe tests were done using OpenSIPS 1.4.4 as reference point for DB query performance without prepared statements. OpenSIPS 1.5.0 is the candidate measurements with prepared statements. Notes:
DB Authentication testThis test (using auth_db module) was focus in measuring he gain of a simple query:
Scenario: REGISTER requests, challenged and authenticated. http://www.opensips.org/uploads/Resources/stmt_auth_char.gif | Chart 1 Results:
-> BOOST = 21 % See full size chart See OpenOffice SpreadSheet with all result values.
User location testThis test (using usrloc module) was focus in measuring he gain of more complex query:
Scenario: REGISTER requests, no authentication, http://www.opensips.org/uploads/Resources/stmt_usrloc_char.gif | Chart 2 Results:
-> BOOST = 25 % See full size chart See OpenOffice SpreadSheet with all result values.
SIPTrace testThis test (using siptrace module) was focus in measuring he gain of simple, but datafull query:
Scenario: REGISTER requests, no authentication, http://www.opensips.org/uploads/Resources/stmt_siptrace_char.gif | Chart 3 Results:
-> BOOST = 23 % See full size chart See OpenOffice SpreadSheet with all result values.
Sitthipong? — 19 August 2012, 18:24(:commentboxchrono:) to:
(:redirect About.PerformanceTests-PreparedStatements quiet=1:) August 22, 2012, at 12:33 PM
by
- Deleted lines 137-142:
August 22, 2012, at 08:19 AM
by
- Comment addedAdded lines 138-143:
August 21, 2012, at 11:08 AM
by
- Deleted lines 137-142:
August 20, 2012, at 10:05 AM
by
- Comment addedAdded lines 138-143:
August 19, 2012, at 07:24 PM
by
- Comment addedAdded lines 133-138:
(:nl:)>>messagehead<< Sitthipong? — 19 August 2012, 18:24December 08, 2011, at 11:36 AM
by
- Deleted lines 132-137:
(:nl:)>>messagehead<< Marylouise? — 07 December 2011, 16:50December 07, 2011, at 05:50 PM
by
- Comment addedAdded lines 133-138:
(:nl:)>>messagehead<< Marylouise? — 07 December 2011, 16:50May 12, 2011, at 12:30 PM
by
- Comments CleanupDeleted lines 132-152:
May 11, 2011, at 06:20 PM
by
- Comment addedAdded lines 147-152:
May 11, 2011, at 10:15 AM
by
- Comments CleanupChanged line 1 from:
Resources -> Performance Tests -> MySQL prepared statmentsto:
Resources -> Performance Tests -> MySQL prepared statementsChanged lines 139-140 from:
Good Result have been find. to:
Good Result have been found. Deleted lines 146-157:
May 11, 2011, at 12:41 AM
by
- Comment addedAdded lines 153-158:
May 10, 2011, at 11:15 AM
by
- Comment addedAdded lines 147-152:
October 25, 2009, at 06:06 AM
by
- Comment addedAdded lines 141-146:
(:nl:)>>messagehead<< Fahad Ibn omar Fajardo? — 25 October 2009, 05:06August 20, 2009, at 05:16 PM
by
- Comment addedAdded lines 134-141:
March 18, 2009, at 12:02 PM
by
- Added line 2:
This page has been visited 25467 times. March 02, 2009, at 08:24 PM
by
- Deleted lines 132-136:
(:nl:)>>messagehead<< bogdan — 02 March 2009, 18:55March 02, 2009, at 07:55 PM
by
- Comment addedAdded lines 132-137:
(:nl:)>>messagehead<< bogdan — 02 March 2009, 18:55March 02, 2009, at 07:50 PM
by
- Deleted lines 131-136:
(:nl:)>>messagehead<< bogdan — 02 March 2009, 18:49March 02, 2009, at 07:49 PM
by
- Comment addedAdded lines 132-137:
(:nl:)>>messagehead<< bogdan — 02 March 2009, 18:49March 02, 2009, at 07:49 PM
by
- Added line 131:
March 01, 2009, at 11:50 PM
by
- Changed line 132 from:
(:commentbox:) to:
(:commentboxchrono:) March 01, 2009, at 11:40 PM
by
- Added line 132:
(:commentbox:) February 05, 2009, at 10:52 PM
by
- Changed lines 125-127 from:
See full size chart See OpenOffice SpreadSheet with all result values. to:
See full size chart See OpenOffice SpreadSheet with all result values. February 05, 2009, at 10:47 PM
by
- Added line 50:
February 05, 2009, at 10:47 PM
by
- Changed line 76 from:
to:
Changed line 103 from:
to:
February 05, 2009, at 10:46 PM
by
- Changed line 85 from:
http://www.opensips.org/uploads/Resources/stmt_usrloc_char.gif | Chart 1 to:
http://www.opensips.org/uploads/Resources/stmt_usrloc_char.gif | Chart 2 Changed line 113 from:
http://www.opensips.org/uploads/Resources/stmt_siptrace_char.gif | Chart 1 to:
http://www.opensips.org/uploads/Resources/stmt_siptrace_char.gif | Chart 3 February 05, 2009, at 10:46 PM
by
- Added lines 107-130:
This test (using siptrace module) was focus in measuring he gain of simple, but datafull query:
Scenario: REGISTER requests, no authentication, http://www.opensips.org/uploads/Resources/stmt_siptrace_char.gif | Chart 1 Results:
-> BOOST = 23 % See full size chart See OpenOffice SpreadSheet with all result values.
February 05, 2009, at 10:41 PM
by
- Added lines 75-76:
Added lines 79-103:
This test (using usrloc module) was focus in measuring he gain of more complex query:
Scenario: REGISTER requests, no authentication, http://www.opensips.org/uploads/Resources/stmt_usrloc_char.gif | Chart 1 Results:
-> BOOST = 25 % See full size chart See OpenOffice SpreadSheet with all result values.
February 05, 2009, at 10:35 PM
by
- Changed lines 67-68 from:
to:
Changed lines 71-72 from:
See OpenOffice SpreadSheet with all result values. to:
See OpenOffice SpreadSheet with all result values. February 05, 2009, at 10:29 PM
by
- Added lines 68-71:
See full size chart See OpenOffice SpreadSheet with all result values. February 05, 2009, at 10:26 PM
by
- Changed line 58 from:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif | Chart 1 to:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif | Chart 1 Added lines 63-64:
February 05, 2009, at 10:24 PM
by
- Changed line 58 from:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif | Chart 1 to:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif | Chart 1 February 05, 2009, at 10:23 PM
by
- Changed line 58 from:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif | Chart 1 to:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif | Chart 1 Changed lines 66-67 from:
to:
February 05, 2009, at 10:22 PM
by
- Changed line 58 from:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif | Chart 1 to:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif | Chart 1 February 05, 2009, at 10:22 PM
by
- Changed line 58 from:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif | Chart 1 to:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif | Chart 1 Changed lines 67-68 from:
to:
February 05, 2009, at 10:19 PM
by
- Changed lines 67-68 from:
bgcolor=lightblue% to:
February 05, 2009, at 10:19 PM
by
- Added line 66:
February 05, 2009, at 10:18 PM
by
- Changed lines 66-67 from:
to:
bgcolor=lightblue% February 05, 2009, at 10:18 PM
by
- Changed line 58 from:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif to:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif | Chart 1 February 05, 2009, at 10:16 PM
by
- Added line 58:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif Changed lines 66-69 from:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif to:
February 05, 2009, at 10:15 PM
by
- Changed lines 66-68 from:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif to:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif February 05, 2009, at 10:14 PM
by
- Changed lines 66-68 from:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif to:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif February 05, 2009, at 10:13 PM
by
- Changed lines 66-68 from:
to:
http://www.opensips.org/uploads/Resources/stmt_auth_char.gif February 05, 2009, at 10:10 PM
by
- Changed lines 66-68 from:
to:
February 05, 2009, at 10:07 PM
by
- Changed lines 66-68 from:
to:
February 05, 2009, at 10:03 PM
by
- Changed lines 66-68 from:
Attach: stmt_auth_chart to:
February 05, 2009, at 10:02 PM
by
- Changed lines 66-68 from:
to:
Attach: stmt_auth_chart February 05, 2009, at 09:58 PM
by
- Changed line 58 from:
to:
Changed line 62 from:
to:
Changed lines 64-66 from:
to:
February 05, 2009, at 09:56 PM
by
- Changed lines 56-57 from:
Scenario: REGISTER requests, challenged and authenticated. to:
Scenario: REGISTER requests, challenged and authenticated. Results:
-> BOOST = 21 % February 05, 2009, at 09:27 PM
by
- Changed lines 47-48 from:
to:
Added lines 52-57:
This test (using auth_db module) was focus in measuring he gain of a simple query:
Scenario: REGISTER requests, challenged and authenticated. February 05, 2009, at 09:22 PM
by
- Changed lines 43-48 from:
to:
DB Authentication testUser location testSIPTrace testFebruary 05, 2009, at 09:19 PM
by
- Added lines 9-13:
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/ . It is essential to understand that prepare statements do not bring only performance, but more security also! Added line 29:
Added lines 38-48:
Performance testsThe tests were done using OpenSIPS 1.4.4 as reference point for DB query performance without prepared statements. OpenSIPS 1.5.0 is the candidate measurements with prepared statements. Notes:
February 05, 2009, at 09:09 PM
by
- Deleted line 8:
Added lines 10-31:
What are the theoretical gains ?Client side:
Server side:
Limitations ?Query cache does not work for prepared statements, but considering that the OpenSIPS data is very large and various, it is not something to affect the performance (as the below results shows). Prepared statements have a gain in speed only if used more than once - for this reason, only the queries which are repetitively used in OpenSIPS are converted to prepared statements ; initial load are still using standard queries. February 05, 2009, at 08:06 PM
by
- Added lines 1-10:
Resources -> Performance Tests -> MySQL prepared statments(:toc-float Table of Content:) 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. The 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. |