Background
Between participating in a MySQLi group and telling others about PHP5’s new MySQLi (MySQL improved) extension, there always seems to be a redundant (though valid) question, “Why?” Simple enough right? Maybe not, so I attempted to come up with some good benchmarks. Now note, just because of the pure nature of PHP extensions, this is easier and harder in some ways than doing let’s say, “ATI X800 vs Nvidia 6800” type benchmarks.
With that said, I tried my best to keep the test scripts used identical down to the variables. There will obviously be some differences as conceptually some things are different between the two extensions.
The Environment
The benchmark hardware/software configuration is as follows:
Processor – Intel Pentium 4 (HT) 2.6 gHz/800mHz FSB
Memory – 1236836K (~1.2GB) PC3200 DDR
Hard Drive – Seagate 250GB (7200 RPM) SATA150
Operating System – FreeBSD
PHP – mod_php5 with Apache2 and Zend Optimizer
MySQL – mysql-server4.1.11
Hardware wise, it’s not the fastest server on the face of the planet, nor is it the slowest. In fact, I think it’s a mid-level system which makes it perfect for benchmarking. I used Apache2, as I believe more people are starting to use it. I may do a future benchmark with Apache1.3.x as I do have the binaries in place. Apache will not be accepting external connections. The MySQL server has networking off. FreeBSD is running with soft updates on.
The tests
I spent some time trying figuring out what would be a good suite of tests to run. Without saying, they’d all obviously have to be PHP scripts of some sort interacting with MySQL through the traditional MySQL extension and the new MySQLi extension. The real question was, how these scripts would be setup. I decided to take the procedural approach (though I recommend using MySQLi in the OO way), since the traditional MySQL has no OO support. Below is my attempt at it.
The MySQL table
Query caching is off.
The table structure is as follows:
DESCRIBE benchmark;
+------------+--------------+-----+----------------+
| Field | Type | Key | Extra |
+------------+--------------+-----+----------------+
| PRIMARY_ID | int(11) | PRI | auto_increment |
| FOO | varchar(255) | | |
| DUMMY1 | text | | |
| DUMMY2 | text | | |
| DUMMY3 | char(1) | | |
+------------+--------------+-----+----------------+
Note, the MySQL table structure should not matter for our benchmark, as we are simply executing SQL queries through different mediums.
The PHP scripts
The focus of the scripts will be around the INSERT clause. Why not SELECT you might say. Well it doesn’t matter quite frankly. We are here to benchmark the functions with identical SQL, the key phrase is “identical SQL”.
Insert SQL test – Executes an INSERT query 100,000 times on the `benchmark` table with a for loop. mysql_query and mysqli_query will be used.
Insert SQL test 2 – Builds 100,000 INSERT SQL queries into one string. mysqli_multi_query will be used.
`benchmark` is truncated after each test script runs.
How performance is guaged
Deciding how to guage performance was undoubtely the hardest part of this benchmark. I had really only 2 options. The first, to setup a simple timer in between the functions where the queries took place and calculate the difference. The other option, was to use a full out profiler. I decided to go with a profiler, as it would give me execution times for surrounding PHP code (for example, the for loop).
My two (considered) choices in the profiler category are xdebug and Zend Studio Debugger. Let me say right now, I use Zend Studio for all my PHP work and so I had a certain bias to use it’s debugger, but alas…this benchmark must do without bias! This is about performance! So I hit Google to try to make an unbias decision about this. I found that Zend Debugger had won the best debugger award from PHP Magazin and so I decided to use it for this benchmark. Now note, all profilers are pretty much the same, in that conceptually they do the same thing. So choosing one over the other for this benchmark would not have a made a difference.
Running of the tests
So with all the formalities out of the way, it’s finally time run the test scripts. Getting anxious are you? Don’t worry, so am I 🙂
- Insert SQL test (insertsql_mysqli.php)
Here is the run of the first profile
A list of 9 subsequent runs and the DoInsert() function’s “own time” results (in microseconds)
| |
DoInsert() own time
|
Total execution time
|
| |
24,670.14 |
26,075.74 |
| |
25,132.45 |
26,569.09 |
| |
25,232.18 |
26,547.64 |
| |
25,006.92 |
26,289.18 |
| |
25,401.54 |
26,710.59 |
| |
27,792.51 |
29,136.76 |
| |
25,386.73 |
26,814.82 |
| |
25,279.91 |
26,575.78 |
| Average |
25,504.32 |
26,859.87 |
| Standard Deviation |
849.09 |
846.19 |
The standard deviation is an acceptable figure, on average give or take 1 second between those runs. Then on average, DoInsert() takes about 25 seconds to run with mysqli_query. Now let’s try mysql_query.
- Insert SQL test (insertsql_mysql.php)
Here is the run of the first profile
A list of 9 subsequent runs and the DoInsert() function’s “own time” results (in microseconds)
| |
DoInsert() own time
|
Total execution time
|
| |
24,799.11 |
26,134.51 |
| |
25,075.20 |
26,426.23 |
| |
24,685.38 |
26,048.15 |
| |
24,891.70 |
26,245.90 |
| |
25,091.64 |
26,299.66 |
| |
24,713.04 |
26,138.62 |
| |
24,741.54 |
26,125.89 |
| |
24,596.36 |
25,970.21 |
| Average |
24,885.75 |
26,007.04 |
| Standard Deviation |
229.16 |
489.50 |
The standard deviation with mysql_query is much less. In fact, mysql_query is able to do our task about 1 second faster than with mysqli_query. Let’s try to do the same task withmysqli_multi_query.
- Insert SQL test (insertsql_mysqli_multi_query.php)
Here is the run of the profile
53 seconds, mysqli_multi_query definately is not the best choice here.
- Insert SQL test with MySQLi statements (insertsql_mysqli_stmt.php)
In this test, there is a source code change conceptually, so make sure you take a look.
Here is run of the first profile
A list of 9 subsequent runs and the DoInsert() function’s “own time” results (in microseconds)
| |
DoInsert() own time
|
Total execution time
|
| |
24,077.77 |
25,179.06 |
| |
24,535.49 |
25,687.64 |
| |
25,232.18 |
26,547.64 |
| |
24,476.72 |
25,552.14 |
| |
24,445.86 |
25,551.81 |
| |
24,586.98 |
25,703.18 |
| |
24,582.58 |
25,675.57 |
| |
24,783.44 |
25,931.73 |
| Average |
24,589.72 |
25,760.13 |
| Standard Deviation |
318.49 |
376.39 |
The standard deviation is an acceptable figure, on average give or take half a second between those runs. Then on average, DoInsert() takes about 24 seconds to run withmysqli_stmt. I think we have enough for now that we can come to a conclusion.
Conclusions
The data showed that mysql_query outperforms mysqli_query head to head. For the specific task of inserting 100,000 rows into a table, using mysqli_multi_query more than doubled our script execution time. At this point, you may start wondering what the point of using MySQLi is if the traditional MySQL extension is getting the upper hand on MySQLi.
However, using MySQLi statements we got better results, in fact it outperformed mysql_query by as much as mysql_query outperformed mysqli_query. Though this might not be enough reason for you to change existing code, remember that MySQLi statements are “pre-escaped”, meaning that there is no need for you to do mysql_real_escape_string on any of the variables. It’s apparent that given this fact about MySQLi statements, if we had made the extra function call of mysql_real_escape_string to the four inserted values, we would have greatly increased our execution time (good for character escaping user input and stopping SQL injection).
While the above may not be enough reason to change existing code to use MySQLi statements, they might be good reasons to use them in the future, for both security and performance.
[11/16/2005] Also just to mention, mysqli also implements ssh like security measures to make logins between the client and server much more secure.
In summary,
mysqli_stmt > mysql_query > mysqli_query > mysqli_multi_query
Tags: MySQL
