PDO quote bottleneck

[staabm]

Description

running https://github.com/druidfi/mysqldump-php and the following code:

<?php

error_reporting(E_ALL);

require_once __DIR__ . '/vendor/autoload.php';

use Druidfi\Mysqldump as IMysqldump;

$date = date('Ymd');
$dumpSettings = array(
    'no-data' => false,
    'add-drop-table' => true,
    'single-transaction' => true,
    'lock-tables' => false,
    'add-locks' => true,
    'extended-insert' => true,
    'disable-foreign-keys-check' => true,
    'skip-triggers' => false,
    'add-drop-trigger' => true,
    'databases' => true,
    'add-drop-database' => true,
    'hex-blob' => true,
    'include-tables' => array('my-table'), // add a table name here
);

$dump = new IMysqldump\Mysqldump("mysql:host=$hostname;dbname=$db", $username, $password, $dumpSettings); // add DB credentials
$dump->start("backup$date.sql");

Results in a blackfire profile graph which shows a bottleneck in calling PDO::quote:

I am using a db-table which contains 1.3 millions rows and contains 16 columns (some varchar, some int)

I guess its kind of expected when running a php tool to dump a db table in which we need to escape every column value which is stringy that we would bottleneck on the escaping function.

I am wondering whether there is some low hanging fruits in PDO::quote regarding use-cases which call the function excessively.

repro steps

• git clone https://github.com/druidfi/mysqldump-php
• run composer install
• put the above code into test.php file
• run blackfire run --ignore-exit-status php test.php

PHP Version

PHP 8.1.27

Operating System

ubuntu 22 lts

[Girgias]

For MySQL specifically, it might be possibly to optimize the quoter function provided by the driver: mysql_handle_quoter it does seem to be doing some reallocations.

[nielsdos]

It looks deceivingly simple, I'll give it a shot tonight.

[nielsdos]

deceivingly simple

These were the right words.

I played with it a bit and found a couple of interesting things. I have to do verifications and some cleanup though.

• The reallocation is not the bottleneck and has only a tiny impact on performance. Nevertheless it's a good idea to avoid it.
• The indirect calls in mysqlnd_cset_escape_slashes are expensive. If I'm not mistaken, we can at least skip the indirect call for cset->mb_valid when the character is in the ASCII range (but I'd have to double check that). That already improves performance quite a bit.
• The condition char_maxlen > 1 && cset->mb_charlen(*escapestr) > 1 looks to me like it can never be true, because we can only execute that if cset->mb_valid(escapestr, end) failed which would mean it's not valid multibyte to begin with... I'd have to double check this but I think this check is dead (codecov also has no coverage of that branch which gives a bit of a hint).
• It's possible to do the character writes in bulk to newstr by delaying writes until we have to escape. I use a similar technique in my HTML 5 serialization code. This has only a small performance benefit, or at least smaller than I hoped.

That being said, it seems the majority of the time in the sample script is spent doing IO (as expected).
When testing PDO::quote in isolation I got a performance boost with my experimental code of about 2.3x.

[staabm]

Thanks for looking into it. 2x faster would be awesome, as the script will be used for multi GB tables in big databases.

Regarding IO and other bottlenecks: I am already working on that

• Faster dump by less tiny writes() druidfi/mysqldump-php#37
• Remove unnecessary call_user_func() to be faster druidfi/mysqldump-php#36
• Inline escape() method to improve performance druidfi/mysqldump-php#34

[kamil-tekiela]

Is your PHP using mysqlnd or libmysql?

[staabm]

mysqlnd 8.1.27

[nielsdos]

Didn't get auto-closed, but this is fixed by the commit series ending in 3ddd341.