About -> Performance Tests -> MySQL prepared statements

This page has been visited 34259 times.

---

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!

---

1.  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

---

2.  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.

---

3.  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

---

3.1  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.

---

3.2  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,

---

3.3  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,

---