[TYPO3-dev] Quick speed test mysql vs mysqli, for now a win for mysql

[ries van Twisk]

I forgot to mention

the result where done with 15 concurent users with one laptop (core  
duo 2GHz 2GB ram and i had free RAM during the tests).
Sub-optimal but well, better then nothing for now...


Ries


On Oct 8, 2007, at 10:46 PM, ries van Twisk wrote:

> Hey Guys,
>
> today I did a very quick speedtest for mysql native and mysqli with
> prepared statements.
>
> I filled a simpel table with 1000 records and each records has one
> test field with 1Kbye of random textual content.
>
>
> THis is the table DDL (MyISAM):
>
> CREATE TABLE `test` (
>    `uid` int(11) NOT NULL auto_increment,
>    `pid` int(11) NOT NULL,
>    `text` text collate utf8_unicode_ci NOT NULL,
>    PRIMARY KEY  (`uid`),
>    KEY `pid` (`pid`)
> ) ;
>
>
> This was how I filled the table
>
> <?php
>
> $connection = mysql_connect('localhost', 'root', '');
> mysql_select_db('speedtest', $connection);
>
>
> function Random_Password($length) {
>      srand(microtime(true)*1000000);
>      $possible_charactors =
> "abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ";
>      $string = "";
>      while(strlen($string)<$length) {
>          $string .= substr($possible_charactors, mt_rand(0, strlen
> ($possible_charactors)),1);
>      }
>      return($string);
> }
>
>
>
> for ($i=0; $i<1000;$i++) {
> 	$str = "insert into test (pid, text) VALUES (".($i % 25).",'".
> (Random_Password(1024))."')";
> 	mysql_query($str, $connection);
> }
>
> mysql_close($connection);
>
> ?>
>
>
> mysqli version
> <?php
>
> $mysqli = mysqli_connect('localhost', 'root', '', 'speedtest');
>
> $stmt = $mysqli->prepare("SELECT * FROM test WHERE uid = ?");
> for ($i=0; $i<100;$i++) {
> 	$stmt->bind_param("i", $rnd);
> 	$rnd = rand(0, 10) * 100;
> 	$stmt->execute();
> 	$stmt->bind_result($uid, $pid, $text);
> 	$stmt->fetch();	
> }
>
> mysqli_close($mysqli);
>
> ?>
>
> mysql version
> <?php
>
> $connection = mysql_connect('localhost', 'root', '');
> mysql_select_db('speedtest', $connection);
>
>
>
> for ($i=0; $i<100;$i++) {
> 	$str = "SELECT * FROM test WHERE uid = " .rand(0, 10)*100;
> 	$res = mysql_query($str, $connection);	
> 	$row = mysql_fetch_assoc($res);
> }
>
> mysql_close($connection);
>
> ?>
>
>
>
> The goal of using rnd(0, 10)*100 is to make sure the
> records are cached using the mysql version and will mimic
> a bit better what happens on the website, means only a part of the
> records
> get's retrieved many times
>
>
> As of the current state the mysql version is 5% faster
> then the mysqli version with prepared statements using
> the above code. This is properly because my version of mysql is 5.0.x
> and prepared statements are not cached.
>
> When I set my random string to rand(0, 50) * 20; the difference
> is even bigger at 10% win for mysql (interesting, I don't know why  
> yet)
>
>
> If anybody is interested in my jmeter config then let me know
> and I can send it over.
>
>
> I hope that somebody can confirm and check my results (specially
> the conform part :D )
>
>
> I can publish the jMeter graphs somewhere upon request if you wish...
>
> Ries
>
>
>
> --
> Ries van Twisk
> Freelance TYPO3 Developer
> email: ries at vantwisk.nl
> web:   http://www.rvantwisk.nl/
> skype: callto://r.vantwisk
> Phone: + 1 810-476-4193
>