Hi,
First time messing around with PHP internal code and mailing lists, so
sorry if I'm in the wrong place or doing something wrong in general.
I recently noticed some weird performance issues while doing bulk
inserts with prepared statements (single INSERT with a lot of VALUES)
and using RETURNING clause to get back IDs and other columns.
So I wrote a little benchmark to insert 8000 random rows (3 columns
each) into a table and spent some time tracking down why it's slow.
Suprisingly it seems that INSERT itself takes 100-200ms, but
fetch/fetchAll returning id and one of the columns takes 2-3 seconds.
I'm sending the benchmark script and postgres database schema used.
After digging around PHP source code (pulled master branch), the problem
seems to be in PDO calling param_hook with PDO_PARAM_EVT_FETCH_PRE and
again PDO_PARAM_EVT_FETCH_POST for each fetched row, which causes
param_hook to be executed for each row x each param twice. In my little
benchmark inserting 8000 rows with 3 columns and returning 2 columns for
each row that means param_hook is called 8000x3x8000x2 = 384 000 000
times! So I took a look at pgsql_stmt_param_hook in
ext/pdo_pgsql/pgsql_statement.c and it doesn't seem to do anything for
PDO_PARAM_EVT_FETCH_PRE or PDO_PARAM_EVT_FETCH_POST. So if my
understanding is correct, it's calling a function that does nothing
meaningful 384 000 000 times, and this number grows exponentially with
the number of rows and columns.
I'm using postgres, but looking at ext/pdo_mysql code this seems to also
be the case for mysql's drivers, didn't benchmark it though.
As a test I commented out the lines dispatching those events in
ext/pdo/pdo_stmt.c:
|diff --git a/ext/pdo/pdo_stmt.c b/ext/pdo/pdo_stmt.c||
||index 96f7574638..49703a7d68 100644||
||--- a/ext/pdo/pdo_stmt.c||
||+++ b/ext/pdo/pdo_stmt.c||
||@@ -592,9 +592,9 @@ static int do_fetch_common(pdo_stmt_t *stmt, enum
pdo_fetch_orientation ori, zen||
|| return 0;||
|| }||
||||
||- if (!dispatch_param_event(stmt, PDO_PARAM_EVT_FETCH_PRE)) {||
||- return 0;||
||- }||
||+ // if (!dispatch_param_event(stmt, PDO_PARAM_EVT_FETCH_PRE)) {||
||+ // return 0;||
||+ // }||
||||
|| if (!stmt->methods->fetcher(stmt, ori, offset)) {||
|| return 0;||
||@@ -605,9 +605,9 @@ static int do_fetch_common(pdo_stmt_t *stmt, enum
pdo_fetch_orientation ori, zen||
|| return 0;||
|| }||
||||
||- if (!dispatch_param_event(stmt, PDO_PARAM_EVT_FETCH_POST)) {||
||- return 0;||
||- }||
||+ // if (!dispatch_param_event(stmt, PDO_PARAM_EVT_FETCH_POST)) {||
||+ // return 0;||
||+ // }||
||||
|| if (do_bind && stmt->bound_columns) {||
|| /* update those bound column variables now */|
|
|
After this change, fetching takes ~5ms and nothing seems broken, but the
whole INSERT/RETURNING is now 10 times faster.
Am I understanding this right? Could this be solved by letting each
pdo_* driver set some kind of a flag (bitmask?) telling PDO which hooks
it wants called from dispatch_param_event? For example for pdo_pgsql
that flag would be |PDO_PARAM_EVT_FREE | PDO_PARAM_EVT_NORMALIZE |
PDO_PARAM_EVT_ALLOC | PDO_PARAM_EVT_EXEC_PRE|, as other case statements
seem empty.
