The old saying “Need is the father of innovation” (or something like that) held true this weekend. I was looking for an easy way to benchmark MySQL for some RAM drive InnoDB experimentation but couldn’t find anything cross platform, user friendly, and created after 2005. So I built an early version of what I was looking for.
This is a very synthetic benchmark for now. In some instances InnoDB is much faster than MyISAM (simultaneous reads/writes) but that doesn’t come across in these results. I’m planning on beefing up the benchmark options in later versions. This tool is currently useful in benchmarking hard drive / RAID performance when using InnoDB. It’s also good for basic my.cnf tweaking.
So here is the PHP MySQL Benchmark Tool (PMBT) version 0.2 (download). You’ll need InnoDB (optional) and MySQLi (might change this) support for now. Tested with MySQL 5.0.51b-community.
Current Features:
- Easy to use: No compiling, complicated config files, or OS requirements. Web based.
- Specify your table type (MyISAM, InnoDB, MEMORY)
- Insert, Select, and Update testing (CRUd)
- Specify number of records to test
- Results displayed in operations per second (instead of total seconds)
Future Features:
- Custom benchmarks on existing tables
- Aesthetic Rejuvenation
- Support for non mysqli connections (do a find/replace on the code for now)
- Full text benchmarking for MyISAM
- Less Syntheticish Testing (simultaneous reads/writes (AJAX?))
Instructions:
- Unzip to a web accessible folder.
- Change the DB settings in dbconnect.inc.php to match your setup.
Let me know in the comments if you find this useful.
And in case you’re curious InnoDB inserts went from 100 per second to 9,955 per second on the RAM drive. MyISAM tables saw virtually no change in performance. The InnoDB log was also on the RAM drive for that benchmark. Full ACID compliance at half the speed of a Memory/Heap table ain’t bad. I’m writing up a post on how I made it happen.
In other news, I added the LastFM widget over to the right, it plays songs based on my preferences from my music library.
Why mysqli? Why not standard mysql?
Doesnt work for me. What a pitty.
You just need to change a couple of lines of code to make it work with MySQL. Maybe I’ll make that an option in the next version.
Thanks! Just what I was looking for ;o)
Thanks ! Bookmarked :-)
Very handy drop-in benchmarking tool. Glad it still exists, and thank you for making it.
Its a good idea to make this PHP script also you can add some charts and optional select for MySQL or MySQLi. Also, I have a question for you. Is it possible to benchmark MySQL with existent data? For example, i have a Web application and i want to test MySQL with those data? Thanks.
If you want to use mysql instead on mysqli functions, just change the dbconnect.inc.php file as follows.
Michel
dbconnect.inc.php :
If you want to use mysql instead on mysqli functions, just change the dbconnect.inc.php file as follows.
Michel
dbconnect.inc.php :
<?php
$linkDBTest = mysqli_connect(
‘localhost’, /* The host to connect to */
‘testbench’, /* The user to connect as */
‘BenchCheck4U’ ); //, /* The password to use */^M
// ‘kirktest’); /* The default database to query */^M
if (!$linkDBTest) {
printf(“Can’t connect to MySQL Server. Errorcode: %s\n”, mysqli_connect_error());
exit;
}
function mysqli_connect($host,$username,$password){
return mysql_connect($host, $username, $password);
}
function mysqli_query($link,$query){
return mysql_query($query, $link);
}
function mysqli_free_result($result){
return mysql_free_result($result);
}
function mysqli_fetch_assoc($result){
return mysql_fetch_assoc($result);
}
?>
Hi i have big problem wit php script. I install Zend Server CE it has MySQL Server, PHP and Apache. Well I put 100 test rows, for insert operation its not a prblem but in updates it says: Query Failed. Update work up to 90 test rows but after that it failed. I change Resource limits in php.ini file but its a same. After taht I used WAMP server and it works up to 2000 test rows i checked every single line of code in php.ini or other files and its a same. In my corporation I must use Zend Server CE. Please I need help and fast!!!!
Hi,
The ZIP file is missing in the the link .. File not found error.
Can you reupload and provide a new link – this looks like a really useful piece of work.
Regards,
H
Good web site you have here.. It’s hard to find good quality writing like yours these days. I honestly appreciate individuals like you! Take care!!