MySQLi vs MySQL speed

[ezekiel]

Hey all.

I've read lots of docs on how much faster mysqli is than mysql. I've also read about how using mysqli in OO mode is much faster than using PEAR:😃B. So I ran a test of my own with an app that I'm building now.

For background purposes, my PHP application (running PHP 5.0.4) is taking in text files and processing the contents into a MySQL 4.1 db. Some of these files are small 16K and result in a 70 or so records. Some are large (1M😎 and result in about 17K in db records. I'm running this app as a PHP CLI script.

Using mysqli I was able to cut out a lot of redundant code and utilize the chained queries feature using mysqli_multi_query() function. Both a PEAR:😃B and mysqli version of my app run. However, the mysqli version is WAY slower. My average speeds of the apps is:

PEAR:😃B (using mysqli) at 17seconds
mysqli OO at 55 seconds

So, one problem I was getting using mysqli OO was "Error: MySQL server has gone away". I fixed this by enabling the option in the PHP-CLI php.ini to mysqli.reconnect = On. I've also adjusted my my.cnf file to handle bigger datasets. So the file runs, but takes upto 53-54 seconds to run. I think this is due to a timeout and then reconnection. I can't see why PEAR:😃B would be faster than OO mysqli.

Suggestions? Thanks in advance!

[MarkR]

One possibility is that prepare / execute involves more server round trips than a simple query.

Why not have a look at your MySQL server's log to see if there's anything obviously different about the queries?

It makes sense that mysqli is better than PEAR DB, because it involves less overhead in PHP itself (although of course, this effect might be masked by something else).

Test just one thing at a time and repeat it a lot in the same (CLI) script.

"MySQL server has gone away" should not happen under normal circumstances. particularly in a CLI script which simply uses a single connection.

Are you sure that you're only connecting to the database once?

Mark

[ezekiel]

Mark,

Thanks for the response. There is nothing in the log. Just says when I started last. Also I am not using prepare/execute statements. A sample of what Im doing is:

$db = new mysqli(host, user, pass, db);

$sql = "select * from sometable";
$res = $db->query($sql) or die($db->error);
$num = $res->num_rows;

if($num == 0){
$sql = "insert into table ( var1, var2) values ($meh, $bleh)";
$db->query($sql);
$meh_id = $db->insert_id;
}

$res->close();
$db->close();

[MarkR]

You should of course, enable query logging in Mysql to see what they're both doing in the log.

If you're executing the above code in a loop, that is clearly wrong - the right way is to just create one connection object and reuse it.

Moreover, if you're doing it in a loop with many inserts, prepared queries may be better (you should compare them).

If you're NOT doing it in a loop, i.e. a single query with no prepare, just connect, query, disconnect, measuring the performance difference will be difficult and error-prone.

Mark

[ezekiel]

No I'm not executing this in a loop. I won't deny I could be doing it wrong, but here is a generic example of how I'm doing this.

class DoSomething{

        private $db;
        private $fileName;

        public function __construct( $fileName ){
               $this->db = new mysqli( DSN);

               $this->processFile();

               $this->db->close();
        }

        private function processFile(){
                $sql = "select * from sometable";
                $res = $this->db->query($sql) or die($this->db->error);
                $num = $res->num_rows;

                if($num == 0){
                     $sql = "insert into table ( var1, var2) values ($meh, $bleh)";
                     $this->db->query($sql);
                     $meh_id = $db->insert_id;
                }

                $res->close();
        }

}

[MarkR]

You can't reasonably benchmark a single run of a single insert. As you're just calling it just once, it will execute very quickly:

• It will be too quick to time accurately
• Its run time will be adversely affected by the previous state of the system hence very variable
• It won't benefit from various types of caching.

In order to do a reasonable benchmark, you should carry out AT LEAST 10,000 inserts. I would expect it to take only a few seconds.

Mark

[ezekiel]

Mark, this is only an example. My real script parses my sample file that is 1MB. That one file generates 17,000+ entries in the database based on its logic of parsing the file. I was using that code example above to demonstrate how I was essentially accessing the mysqli functions.

Is this way of accessing the methods/fields in mysqli correct? or maybe this is an issue with php 5.0.4 and mysql 4.1.

[ezekiel]

Ok, So I tried my script on a server running MySQL 5.1.9 and PHP 5.1.4. I get the same problem. HELLA SLOW. First problem I found is I get the:

"MySQL server has gone away".

I fix this by editing my php.ini entry for mysqli.reconnect and setting it to On. But wow! This last time it took 99 seconds to run. I am making Blob versions of my data and storing it in a longblob column. Could that be part of my problem? MySQL isn't able to handle the longblob object i'm passing in?

Seems like maybe I'm not utilizing the mysqli functions properly. Any suggestions per the conversation so far?