PHP PDO Mysql Insert Performance

Question

I normally don't use a lot of SQL stuff in PHP but recently have been forced into it by a friend as to help him debug something.

I am using PDO with PHP to insert some data but the insert performance is abysmal. A simple loop of 151 inserts takes nearly 6 seconds! and I am lost as to why.

Here is my code:

<?php

$database='XXXXXX';
$username='XXXXXX';
$password='XXXXXX';
$hostname='127.0.0.1';

$inserted=0;
$counted=0;
$dsn = 'mysql:dbname='.$database.';host='.$hostname.'';
$start=microtime(true);
try {
    $data = new PDO($dsn, $username, $password, array(PDO::ATTR_EMULATE_PREPARES => false));
} catch (PDOException $e) {
    echo('Connection failed: ' . $e->getMessage());
}
for($i=1;$i<=150;$i++) {
    $time=time();
    $query=$data->prepare("INSERT INTO `tbl_temp` (aid, bid) VALUES (?, ?)");
    $query->execute(array($i, $time));
}
$data=null;
print "Took: ".(microtime(true)-$start)." S to insert $i records\n";

// Took: 5.569482088089 S to insert 151 records <--- result
?>

I have tried the same code using bindParam also and the speed is roughly the same. The server has an 8 core Xeon processor and 64gb of RAM. The script is being run from the command line (php-cgi) and the database and table are new and empty. The database type is InnoDB. Can anyone point me in the right direction of where to look as to why it's so slow? Because I am sure MySQL never used to be this slow!

Preparing your sql outside the loop will help a little. I think the problem is more likely in your table structure. What's your table structure and current content? — Simon Brahan, Sep 30 '14 at 07:03
Hi. My structure is this... `code` id -> AI Primary Key, aid -> Int, bid -> Int My current content is ZERO rows That's it :) — user3169851, Sep 30 '14 at 07:12
just a suggestion do an explain for the above query and observe where it is a bottleneck. may be it is helpful or not.... — Maz I, Sep 30 '14 at 07:26
possible duplicate of [Mysql insert,updates very slow](http://stackoverflow.com/questions/16476243/mysql-insert-updates-very-slow) — Maz I, Sep 30 '14 at 07:27
@ThinkingWeb. It's not helpful, I've already tried it. An insert query really doesn't get any more simple than this. Also this is nowhere near a possible duplicate of the link you put so I'm not sure why you put it there. — user3169851, Sep 30 '14 at 07:38
@user3169851 I searched and thought may be its helpful in your case as well. — Maz I, Sep 30 '14 at 07:40

score 4 · Answer 1 · edited May 23 '17 at 12:05

4

found this one. Performance in PDO / PHP / MySQL: transaction versus direct execution. so try this one.

$query = $data->prepare("INSERT INTO `tbl_temp` (aid, bid) VALUES (?, ?)");
try {
        $data->beginTransaction();
        for($i=0; $i < 150; $i++) {
            $time = time();
            $query->bindValue(1, $i, PDO::PARAM_INT);
            $query->bindValue(2, $time, PDO::PARAM_STR);
            $query->execute();
        }
        $data->commit();
    } catch(PDOException $e) {
            $data->rollBack();
    }

edited May 23 '17 at 12:05

Community

1
1

answered Sep 30 '14 at 07:51

blitzen12

1,350
3
24
33

What version of PHP was this example written for? In PHP 7 `mysqli` objects use `begin_transaction()`, `bind_param()` and `rollback`, not the function names that you used. – Kenny83 Apr 21 '20 at 10:10
@Kenny83 The whole post is about using PDO and prepared statements. Why are you talking about `mysqli`? – dmuensterer Sep 27 '20 at 14:59

score 1 · Answer 2 · answered Sep 30 '14 at 07:48

Turns out that a setting inside the SQL server is what was causing it. As far as I can tell the transaction commit was set to flush to disk on every write:

innodb_flush_log_at_trx_commit=0

This is the default ACID compliant settings of a new install.

I changed my setting to this

innodb_flush_log_at_trx_commit=2

This allows ONLY power outage or OS crash to erase the transaction buffer/log and not a mysqld crash.

For some people who need the "D" of ACID to be 100% true then you should leave this setting alone.

...or use faster storage..or don't use implicit transactions. — symcbean, Sep 30 '14 at 12:14

score 0 · Answer 3 · answered Sep 30 '14 at 07:14

As Simon stated in a comment, there is no use of doing the preparation within the loop. Doing so will cause 300 queries to be sent to the database, each time one for the preparation and on for the actual insert. Using the prepare-statement before will cause only 151 queries:

$query = $data->prepare("INSERT INTO `tbl_temp` (aid, bid) VALUES (?, ?)");
for ($i = 1; $i <= 150; $i++) {
    $time = time();
    $query->execute(array($i, $time));
}

Another idea could be to use instead a combined multi insert statement. I guess it could have a better performance, but I'm not quite sure:

$query = 'INSERT INTO `tbl_temp` (aid, bid) VALUES';
for ($i = 1; $i <= 150; $i++) {
    if ($i == 1) {
        $query .= ' ('.$i.', '.time().')';
    } else {
        $query .= ', ('.$i.', '.time().')';
    }
}
$data->exec($query);

Ok. Tried the first one and the performance is the same. The second one I can't try as it's not doing bound parametrization. My example is a cut down version of what's actually being done though the performance is roughly the same and the number of inserts is roughly the same. — user3169851, Sep 30 '14 at 07:18

PHP PDO Mysql Insert Performance

3 Answers3

Linked

Related