Resources.TestsPrepareStatements History

Hide minor edits - Show changes to markup

April 24, 2013, at 01:19 PM by 109.99.235.212 -
Changed lines 1-140 from:

Resources -> Performance Tests -> MySQL prepared statements

This page has been visited 22443 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:

  1. save on building the string query
  2. save on converting to string all types of data provided by DB API
  3. no need for string / blob escaping
  4. eliminates the risk of SQL injections
  5. save memory for buffering strings, blobs, other data

Server side:

  1. save parsing the query each time
  2. no need to unescape data

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 tests

The 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:

  1. to simulate as much as possible a real world setup, were server query caching has no impact (due data volume), the mysql server that was used had the query cache disabled;
  2. OpenSIPS was located on the same machine with mysql server, in order to eliminate any network anomalies;
  3. SIPP was used to generate REGISTER traffic (scenario will be shortly uploaded); SIPP was locate on a different machine, to avoid any interferences with OpenSIPS
  4. what was measured was the response time at application level (at the level of modules using the DB API); so, the measured time includes the time to prepared the query, to send and wait the query from server and to fetch results.
  5. tests were composed of 10 000 measurements per test

DB Authentication test

This test (using auth_db module) was focus in measuring he gain of a simple query:

  • low complexity query (as simple select with 2 fields)
  • low volume of data pushed to mysql (2 short strings - username and domain)

Scenario: REGISTER requests, challenged and authenticated.

http://www.opensips.org/uploads/Resources/stmt_auth_char.gif | Chart 1

Results:

  • no prepared statements (OpenSIPS 1.4.4) = 254 micro secs (avg per query)
  • with prepared statements (OpenSIPS 1.5.0) = 202 micro secs (avg per query)

-> BOOST = 21 %

See full size chart

See OpenOffice SpreadSheet with all result values.



User location test

This test (using usrloc module) was focus in measuring he gain of more complex query:

  • highy complexity query (an insert with 13 fields and 2 "where" values)
  • low volume of data pushed to mysql (only short strings, integers, datatime values)

Scenario: REGISTER requests, no authentication,

http://www.opensips.org/uploads/Resources/stmt_usrloc_char.gif | Chart 2

Results:

  • no prepared statements (OpenSIPS 1.4.4) = 477 micro secs (avg per query)
  • with prepared statements (OpenSIPS 1.5.0) = 358 micro secs (avg per query)

-> BOOST = 25 %

See full size chart

See OpenOffice SpreadSheet with all result values.



SIPTrace test

This test (using siptrace module) was focus in measuring he gain of simple, but datafull query:

  • low complexity query (an insert with 6 fields)
  • large volume of data pushed to mysql (BLOBs containing a whole SIP message is inserted)

Scenario: REGISTER requests, no authentication,

http://www.opensips.org/uploads/Resources/stmt_siptrace_char.gif | Chart 3

Results:

  • no prepared statements (OpenSIPS 1.4.4) = 556 micro secs (avg per query)
  • with prepared statements (OpenSIPS 1.5.0) = 428 micro secs (avg per query)

-> BOOST = 23 %

See full size chart

See OpenOffice SpreadSheet with all result values.



(:nl:)>>messagehead<<

Sitthipong?19 August 2012, 18:24

Technically, CSV should wrap fidles that contain a comma in simple double quotes. That should take care of situations like that. The only complication is when you have a text field with double quotes inside them. Although scripts that read in CSVs should again, according to the presumptive standard of RFC 4180 -pay close attention so as to only recognize as delimer , the reality is many importing scripts just grab the first double quote they see and run with it as a delimiter. That's why I like TSVs; tab-separated text files, with the .txt extension and not .csv so as to avoid MS Excel reading problems with UTF-8 and multi-byte characters (when you open or import a .txt file in Excel it'll accept non-ASCII / ANSI files and read them as such). And with the combo quote-tab-quote the chance of picking on a field with that combo in it are rather slim, much more so than the quote-comma-quote sequence.If you're interested at all, there's currently a decent . You'll see it's very complicated from a programmer's POV (e.g. the intelligent placement of quotes). I suppose the quick solution is to just plop quotes around every field, i.e. not just the first or header row. I've also suggested the good people at YM to consider adding TSV but I'm not optimistic about support for that @#$% serialized usermeta crud.My kingdom for a usermeta cleaner!

(:commentboxchrono:)

to:

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

August 22, 2012, at 11:33 AM by bogdan -
Deleted lines 137-142:

(:nl:)>>messagehead<<

Eliana?22 August 2012, 07:19

Hi Dutch,That update does not. (No one asked for it yet and while I try (at the risk of ever eanipdxng scope) no way do I think of every thing up front, so it is really good if people give feedback and put some requests in.Now it is on the list and may make it into the next update.Thanks for noting it.

August 22, 2012, at 07:19 AM by Eliana - Comment added
Added lines 138-143:

(:nl:)>>messagehead<<

Eliana?22 August 2012, 07:19

Hi Dutch,That update does not. (No one asked for it yet and while I try (at the risk of ever eanipdxng scope) no way do I think of every thing up front, so it is really good if people give feedback and put some requests in.Now it is on the list and may make it into the next update.Thanks for noting it.

August 21, 2012, at 10:08 AM by bogdan -
Deleted lines 137-142:

(:nl:)>>messagehead<<

Yuko?20 August 2012, 09:05

The only solace is that these folks reap what they sow, but at what a hoirrble cost. Both my parents (nine children) were estranged from the family. Male parent died alone. Mother is left alone to deal with her memories. All of us kids left as soon as we possibly could, at 14 in my case. Nothing on the street was worse than what was at home, a fact that few people realize when pontificating about 'runaways' or 'throwaways'. Luckily, those inclined to perpetuate the abuse were deprived of children. One dreams of the special hell reserved for these individuals. Keep doing your good work.

August 20, 2012, at 09:05 AM by Yuko - Comment added
Added lines 138-143:

(:nl:)>>messagehead<<

Yuko?20 August 2012, 09:05

The only solace is that these folks reap what they sow, but at what a hoirrble cost. Both my parents (nine children) were estranged from the family. Male parent died alone. Mother is left alone to deal with her memories. All of us kids left as soon as we possibly could, at 14 in my case. Nothing on the street was worse than what was at home, a fact that few people realize when pontificating about 'runaways' or 'throwaways'. Luckily, those inclined to perpetuate the abuse were deprived of children. One dreams of the special hell reserved for these individuals. Keep doing your good work.

August 19, 2012, at 06:24 PM by Sitthipong - Comment added
Added lines 133-138:

(:nl:)>>messagehead<<

Sitthipong?19 August 2012, 18:24

Technically, CSV should wrap fidles that contain a comma in simple double quotes. That should take care of situations like that. The only complication is when you have a text field with double quotes inside them. Although scripts that read in CSVs should again, according to the presumptive standard of RFC 4180 -pay close attention so as to only recognize as delimer , the reality is many importing scripts just grab the first double quote they see and run with it as a delimiter. That's why I like TSVs; tab-separated text files, with the .txt extension and not .csv so as to avoid MS Excel reading problems with UTF-8 and multi-byte characters (when you open or import a .txt file in Excel it'll accept non-ASCII / ANSI files and read them as such). And with the combo quote-tab-quote the chance of picking on a field with that combo in it are rather slim, much more so than the quote-comma-quote sequence.If you're interested at all, there's currently a decent . You'll see it's very complicated from a programmer's POV (e.g. the intelligent placement of quotes). I suppose the quick solution is to just plop quotes around every field, i.e. not just the first or header row. I've also suggested the good people at YM to consider adding TSV but I'm not optimistic about support for that @#$% serialized usermeta crud.My kingdom for a usermeta cleaner!

December 08, 2011, at 10:36 AM by bogdan -
Deleted lines 132-137:

(:nl:)>>messagehead<<

Marylouise?07 December 2011, 16:50

That's the best aneswr of all time! JMHO

December 07, 2011, at 04:50 PM by Marylouise - Comment added
Added lines 133-138:

(:nl:)>>messagehead<<

Marylouise?07 December 2011, 16:50

That's the best aneswr of all time! JMHO

May 12, 2011, at 11:30 AM by bogdan - Comments Cleanup
Deleted lines 132-152:

(:nl:)>>messagehead<<

Shreyas?20 August 2009, 16:16

Great!!!!!!!!! Good Result have been found.

(:nl:)>>messagehead<<

Fahad Ibn omar Fajardo?25 October 2009, 05:06

What a great I tested it! I got a more better result using a high spec system.

(:nl:)>>messagehead<<

Demelza?11 May 2011, 17:20

In awe of that awnser! Really cool!

May 11, 2011, at 05:20 PM by Demelza - Comment added
Added lines 147-152:

(:nl:)>>messagehead<<

Demelza?11 May 2011, 17:20

In awe of that awnser! Really cool!

May 11, 2011, at 09:15 AM by bogdan - Comments Cleanup
Changed line 1 from:

Resources -> Performance Tests -> MySQL prepared statments

to:

Resources -> Performance Tests -> MySQL prepared statements

Changed lines 139-140 from:

Good Result have been find.

to:

Good Result have been found.

Deleted lines 146-157:

(:nl:)>>messagehead<<

Josie?10 May 2011, 10:15

No more s***. All posts of this qluaity from now on

(:nl:)>>messagehead<<

Sailor?10 May 2011, 23:41

THX that's a great anwser!

May 10, 2011, at 11:41 PM by Sailor - Comment added
Added lines 153-158:

(:nl:)>>messagehead<<

Sailor?10 May 2011, 23:41

THX that's a great anwser!

May 10, 2011, at 10:15 AM by Josie - Comment added
Added lines 147-152:

(:nl:)>>messagehead<<

Josie?10 May 2011, 10:15

No more s***. All posts of this qluaity from now on

October 25, 2009, at 05:06 AM by Fahad Ibn omar Fajardo - Comment added
Added lines 141-146:

(:nl:)>>messagehead<<

Fahad Ibn omar Fajardo?25 October 2009, 05:06

What a great I tested it! I got a more better result using a high spec system.

August 20, 2009, at 04:16 PM by Shreyas - Comment added
Added lines 134-141:

(:nl:)>>messagehead<<

Shreyas?20 August 2009, 16:16

Great!!!!!!!!! Good Result have been find.

March 18, 2009, at 11:02 AM by bogdan -
Added line 2:

This page has been visited 22443 times.

March 02, 2009, at 07:24 PM by bogdan -
Deleted lines 132-136:

(:nl:)>>messagehead<<

bogdan02 March 2009, 18:55

test again

March 02, 2009, at 06:55 PM by bogdan - Comment added
Added lines 132-137:

(:nl:)>>messagehead<<

bogdan02 March 2009, 18:55

test again

March 02, 2009, at 06:50 PM by bogdan -
Deleted lines 131-136:

(:nl:)>>messagehead<<

bogdan02 March 2009, 18:49

test

March 02, 2009, at 06:49 PM by bogdan - Comment added
Added lines 132-137:

(:nl:)>>messagehead<<

bogdan02 March 2009, 18:49

test

March 02, 2009, at 06:49 PM by bogdan -
Added line 131:

March 01, 2009, at 10:50 PM by bogdan10 -
Changed line 132 from:

(:commentbox:)

to:

(:commentboxchrono:)

March 01, 2009, at 10:40 PM by bogdan10 -
Added line 132:

(:commentbox:)

February 05, 2009, at 09:52 PM by 92.81.68.225 -
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 09:47 PM by 92.81.68.225 -
Added line 50:

February 05, 2009, at 09:47 PM by 92.81.68.225 -
Changed line 76 from:
to:

Changed line 103 from:
to:

February 05, 2009, at 09:46 PM by 92.81.68.225 -
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 09:46 PM by 92.81.68.225 -
Added lines 107-130:

This test (using siptrace module) was focus in measuring he gain of simple, but datafull query:

  • low complexity query (an insert with 6 fields)
  • large volume of data pushed to mysql (BLOBs containing a whole SIP message is inserted)

Scenario: REGISTER requests, no authentication,

http://www.opensips.org/uploads/Resources/stmt_siptrace_char.gif | Chart 1

Results:

  • no prepared statements (OpenSIPS 1.4.4) = 556 micro secs (avg per query)
  • with prepared statements (OpenSIPS 1.5.0) = 428 micro secs (avg per query)

-> BOOST = 23 %

See full size chart

See OpenOffice SpreadSheet with all result values.


February 05, 2009, at 09:41 PM by 92.81.68.225 -
Added lines 75-76:
Added lines 79-103:

This test (using usrloc module) was focus in measuring he gain of more complex query:

  • highy complexity query (an insert with 13 fields and 2 "where" values)
  • low volume of data pushed to mysql (only short strings, integers, datatime values)

Scenario: REGISTER requests, no authentication,

http://www.opensips.org/uploads/Resources/stmt_usrloc_char.gif | Chart 1

Results:

  • no prepared statements (OpenSIPS 1.4.4) = 477 micro secs (avg per query)
  • with prepared statements (OpenSIPS 1.5.0) = 358 micro secs (avg per query)

-> BOOST = 25 %

See full size chart

See OpenOffice SpreadSheet with all result values.


February 05, 2009, at 09:35 PM by 92.81.68.225 -
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 09:29 PM by 92.81.68.225 -
Added lines 68-71:

See full size chart

See OpenOffice SpreadSheet with all result values.

February 05, 2009, at 09:26 PM by 92.81.68.225 -
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 09:24 PM by 92.81.68.225 -
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 09:23 PM by 92.81.68.225 -
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 09:22 PM by 92.81.68.225 -
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 09:22 PM by 92.81.68.225 -
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 09:19 PM by 92.81.68.225 -
Changed lines 67-68 from:

bgcolor=lightblue%

to:

February 05, 2009, at 09:19 PM by 92.81.68.225 -
Added line 66:
February 05, 2009, at 09:18 PM by 92.81.68.225 -
Changed lines 66-67 from:

to:

bgcolor=lightblue%

February 05, 2009, at 09:18 PM by 92.81.68.225 -
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 09:16 PM by 92.81.68.225 -
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 09:15 PM by 92.81.68.225 -
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 09:14 PM by 92.81.68.225 -
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 09:13 PM by 92.81.68.225 -
Changed lines 66-68 from:
to:

http://www.opensips.org/uploads/Resources/stmt_auth_char.gif

February 05, 2009, at 09:10 PM by 92.81.68.225 -
Changed lines 66-68 from:
to:
February 05, 2009, at 09:07 PM by 92.81.68.225 -
Changed lines 66-68 from:
to:
February 05, 2009, at 09:03 PM by 92.81.68.225 -
Changed lines 66-68 from:

Attach: stmt_auth_chart

to:
February 05, 2009, at 09:02 PM by 92.81.68.225 -
Changed lines 66-68 from:
to:

Attach: stmt_auth_chart

February 05, 2009, at 08:58 PM by 92.81.68.225 -
Changed line 58 from:

to:
Changed line 62 from:

to:
Changed lines 64-66 from:

to:
February 05, 2009, at 08:56 PM by 92.81.68.225 -
Changed lines 56-57 from:

Scenario: REGISTER requests, challenged and authenticated.

to:

Scenario: REGISTER requests, challenged and authenticated.

Results:

  • no prepared statements (OpenSIPS 1.4.4) = 254 micro secs (avg per query)
  • with prepared statements (OpenSIPS 1.5.0) = 202 micro secs (avg per query)

-> BOOST = 21 %

February 05, 2009, at 08:27 PM by 92.81.68.225 -
Changed lines 47-48 from:
to:
  1. tests were composed of 10 000 measurements per test
Added lines 52-57:

This test (using auth_db module) was focus in measuring he gain of a simple query:

  • low complexity query (as simple select with 2 fields)
  • low volume of data pushed to mysql (2 short strings - username and domain)

Scenario: REGISTER requests, challenged and authenticated.

February 05, 2009, at 08:22 PM by 92.81.68.225 -
Changed lines 43-48 from:
  1. to simulate as much as possible a real world setup, were server query caching has no impact (due data volume), the mysql server that was used had the cache disabled;
  2. OpenSIPS was located on the same machine with mysql server, in order to eliminate any network anomalies;
  3. SIPP was used to generate REGISTER traffic (scenario will be shortly uploaded); SIPP was locate on a different machine, to avoid any interferences with OpenSIPS
  4. what was measured was the response time at application level (at the level of modules using the DB API); so, the measured time includes the time to prepared the query, to send and wait the query from server and to fetch results.
to:
  1. to simulate as much as possible a real world setup, were server query caching has no impact (due data volume), the mysql server that was used had the query cache disabled;
  2. OpenSIPS was located on the same machine with mysql server, in order to eliminate any network anomalies;
  3. SIPP was used to generate REGISTER traffic (scenario will be shortly uploaded); SIPP was locate on a different machine, to avoid any interferences with OpenSIPS
  4. what was measured was the response time at application level (at the level of modules using the DB API); so, the measured time includes the time to prepared the query, to send and wait the query from server and to fetch results.

DB Authentication test

User location test

SIPTrace test

February 05, 2009, at 08:19 PM by 92.81.68.225 -
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 tests

The 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:

  1. to simulate as much as possible a real world setup, were server query caching has no impact (due data volume), the mysql server that was used had the cache disabled;
  2. OpenSIPS was located on the same machine with mysql server, in order to eliminate any network anomalies;
  3. SIPP was used to generate REGISTER traffic (scenario will be shortly uploaded); SIPP was locate on a different machine, to avoid any interferences with OpenSIPS
  4. what was measured was the response time at application level (at the level of modules using the DB API); so, the measured time includes the time to prepared the query, to send and wait the query from server and to fetch results.
February 05, 2009, at 08:09 PM by 92.81.68.225 -
Deleted line 8:
Added lines 10-31:

What are the theoretical gains ?

Client side:

  1. save on building the string query
  2. save on converting to string all types of data provided by DB API
  3. no need for string / blob escaping
  4. eliminates the risk of SQL injections
  5. save memory for buffering strings, blobs, other data

Server side:

  1. save parsing the query each time
  2. no need to unescape data

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 07:06 PM by 81.180.102.217 -
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.



Page last modified on April 24, 2013, at 01:19 PM