Repository navigation
PDO quote bottleneck #13440
Description
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.phpfile - run
blackfire run --ignore-exit-status php test.php
PHP Version
PHP 8.1.27
Operating System
ubuntu 22 lts
Activity
Girgias commentedon Feb 20, 2024
For MySQL specifically, it might be possibly to optimize the quoter function provided by the driver:
mysql_handle_quoterit does seem to be doing some reallocations.ndossche commentedon Feb 20, 2024
It looks deceivingly simple, I'll give it a shot tonight.
ndossche commentedon Feb 20, 2024
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.
mysqlnd_cset_escape_slashesare expensive. If I'm not mistaken, we can at least skip the indirect call forcset->mb_validwhen the character is in the ASCII range (but I'd have to double check that). That already improves performance quite a bit.char_maxlen > 1 && cset->mb_charlen(*escapestr) > 1looks to me like it can never be true, because we can only execute that ifcset->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).newstrby 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::quotein isolation I got a performance boost with my experimental code of about 2.3x.staabm commentedon Feb 21, 2024
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
kamil-tekiela commentedon Feb 22, 2024
Is your PHP using mysqlnd or libmysql?
staabm commentedon Feb 22, 2024
mysqlnd 8.1.27
ndossche commentedon Mar 7, 2024
Didn't get auto-closed, but this is fixed by the commit series ending in 3ddd341.