PDO transaction timing out when it has 10 insert (to mysql) statements

Question

I'm fairly new to PDO - trying to optimize some code that previously updated a MySQL table one row at a time, and get it to instead do the update in a batch, because there are literally thousands of rows.

Long story short, the code needs to get a recursive directory listing, and based on that, update the table of file urls, indicating whether the table is found. That's the end goal... here are the basic steps that the code is performing:

clear the "directory" table
do a recursive scandir into a temporary array, thousands of items long
go through the array one by one, and insert into the "directory" table
run an update query on the main table setting filefoundstatus based on the content of the "directory" table

Step 3 is where I'm having a problem. I keep getting "PHP Fatal error: Maximum execution time of 90 seconds exceeded." I've decreased the transaction size to 10, and increased the max_execution_time to 90, and it does manage to insert 1720 rows before it crashes with the above error.

Here's the code for step 3 (including a bit of step 2) - $pdo is a PDO object. If anybody has any pointers, it would be much appreciated. :)

//get the dir list into a one-dimensional array
$dirlistall = scanAllDir($rootdirprefix . 'docs/sds');
$countDirsList = count($dirlistall);
//print_r($dirlistall);

//load the dir list into the Directory Listings table
$stmt = $pdo->prepare("INSERT INTO DirectoryListingResults (URL) VALUES (:url)");
$batchSize = 10;
$i = 0;
try {
    for ($idx=0; $idx*$batchSize < $countDirsList; $idx++) { 
        $dirsPartial = array_slice($dirlistall, $idx*$batchSize, $batchSize);

        $pdo->beginTransaction();
        $stmt->bindParam(':url', $url);
        foreach ($dirsPartial as $url)
        {
            $stmt->execute();
        }
        $pdo->commit();

        unset($dirsPartial);
    }
}catch (Exception $e){
    $pdo->rollback();
    throw $e->getMessage();
}
unset($dirlistall);

Running the code on my test machine, which is running Windows 10 with IIS, and PHP 5.6. The MySQL server is remote, version 5.6.43.

[Edit]: In case it's relevant, here are the PDO connection options:

$pdooptions = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

Have you tried without transactions? How many times is that inner foreach loop run? What about the outer for loop? — miken32
– miken32, Commented Jun 24, 2019 at 23:13
I can't really tell because it's a PHP crash, so I can't get it to throw any more information, but going from the math (10 rows per batch, and it successfully added 1720 rows before crashing), it seems like it ran 172 times. There should be 29,329 items total in $dirlistall. Haven't tried without transactions. Kind of scared to. :) — Katerine459
– Katerine459, Commented Jun 25, 2019 at 1:57
Update: without transactions, it successfully added 1882 rows before crashing with the same error. — Katerine459
– Katerine459, Commented Jun 25, 2019 at 2:00
This isn't a "crash" it's a timeout. So, increase your timeout and adjust your MySQL settings so it runs more efficiently. Set up the general log or the slow query log on the DB side so you can see how long these are taking. — miken32
– miken32, Commented Jun 25, 2019 at 14:36

Don't Panic · Accepted Answer · 2019-06-24 23:05:55Z

1

I'm curious if binding values instead of parameters could help, so instead of this

$stmt->bindParam(':url', $url);
foreach ($dirsPartial as $url)
{
    $stmt->execute();
}

this

foreach ($dirsPartial as $url)
{
    $stmt->execute([':url' => $url]);
}

I really don't know if that will help the performance, but I know since parameters are bound by reference that the redefinition of $url in the foreach loop could cause problems in general.

answered Jun 24, 2019 at 23:05

Don't Panic

41.9k11 gold badges70 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

miken32 Over a year ago

That was going to be my comment as well (complete with the "I don't know if this will help" caveat.)

Don't Panic Over a year ago

@miken32 I actually hesitated to add it as an answer, but I was having trouble figuring out how to state it clearly in a comment.

Katerine459 Over a year ago

Thanks for the advice - I didn't know that about bindParam not working well. Unfortunately, it didn't seem to make any difference (and may have made things a bit worse - this time, it managed to insert 1680 rows before crashing, while before it was 1720).

Collectives™ on Stack Overflow

PDO transaction timing out when it has 10 insert (to mysql) statements

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related