Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:111694
To: PHP internals <internals@lists.php.net>
Message-ID: <08b959f5-7652-5e8a-562a-c2659facec8c@voxdiversa.hr>
Date: Wed, 26 Aug 2020 15:02:44 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:80.0) Gecko/20100101
 Thunderbird/80.0
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="------------1A59B12C26E8F957F120522E"
Content-Language: en-US
Subject: PDO fetch performance problems with many bind parameters
From: dino.pejakovic@voxdiversa.hr (=?UTF-8?Q?Dino_Pejakovi=c4=87?=)

--------------1A59B12C26E8F957F120522E
Content-Type: multipart/alternative;
 boundary="------------E4D9AC3C93DB72AEAA49E019"


--------------E4D9AC3C93DB72AEAA49E019
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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.


--------------E4D9AC3C93DB72AEAA49E019
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p> </p>
    <div class="moz-text-html" lang="x-unicode">
      <p>Hi,</p>
      <p><br>
      </p>
      <p>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.</p>
      <p><br>
      </p>
      <p>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.<br>
      </p>
      <p>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.</p>
      <p>I'm sending the benchmark script and postgres database schema
        used.<br>
      </p>
      <p><br>
      </p>
      <p>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.<br>
      </p>
      <p>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.</p>
      <p><br>
      </p>
      <p>As a test I commented out the lines dispatching those events in
        ext/pdo/pdo_stmt.c:</p>
      <p><code>diff --git a/ext/pdo/pdo_stmt.c b/ext/pdo/pdo_stmt.c</code><code><br>
        </code><code>index 96f7574638..49703a7d68 100644</code><code><br>
        </code><code>--- a/ext/pdo/pdo_stmt.c</code><code><br>
        </code><code>+++ b/ext/pdo/pdo_stmt.c</code><code><br>
        </code><code>@@ -592,9 +592,9 @@ static int
          do_fetch_common(pdo_stmt_t *stmt, enum pdo_fetch_orientation
          ori, zen</code><code><br>
        </code><code>                return 0;</code><code><br>
        </code><code>        }</code><code><br>
        </code><code> </code><code><br>
        </code><code>-       if (!dispatch_param_event(stmt,
          PDO_PARAM_EVT_FETCH_PRE)) {</code><code><br>
        </code><code>-               return 0;</code><code><br>
        </code><code>-       }</code><code><br>
        </code><code>+       // if (!dispatch_param_event(stmt,
          PDO_PARAM_EVT_FETCH_PRE)) {</code><code><br>
        </code><code>+       //      return 0;</code><code><br>
        </code><code>+       // }</code><code><br>
        </code><code> </code><code><br>
        </code><code>        if (!stmt-&gt;methods-&gt;fetcher(stmt,
          ori, offset)) {</code><code><br>
        </code><code>                return 0;</code><code><br>
        </code><code>@@ -605,9 +605,9 @@ static int
          do_fetch_common(pdo_stmt_t *stmt, enum pdo_fetch_orientation
          ori, zen</code><code><br>
        </code><code>                return 0;</code><code><br>
        </code><code>        }</code><code><br>
        </code><code> </code><code><br>
        </code><code>-       if (!dispatch_param_event(stmt,
          PDO_PARAM_EVT_FETCH_POST)) {</code><code><br>
        </code><code>-               return 0;</code><code><br>
        </code><code>-       }</code><code><br>
        </code><code>+       // if (!dispatch_param_event(stmt,
          PDO_PARAM_EVT_FETCH_POST)) {</code><code><br>
        </code><code>+       //      return 0;</code><code><br>
        </code><code>+       // }</code><code><br>
        </code><code> </code><code><br>
        </code><code>        if (do_bind &amp;&amp;
          stmt-&gt;bound_columns) {</code><code><br>
        </code><code>                /* update those bound column
          variables now */</code></p>
      <p><code><br>
        </code></p>
      <p>After this change, fetching takes ~5ms and nothing seems
        broken, but the whole INSERT/RETURNING is now 10 times faster.</p>
      <p><br>
      </p>
      <p>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 <code>PDO_PARAM_EVT_FREE
          | PDO_PARAM_EVT_NORMALIZE | PDO_PARAM_EVT_ALLOC |
          PDO_PARAM_EVT_EXEC_PRE</code>, as other case statements seem
        empty.<br>
      </p>
    </div>
  </body>
</html>

--------------E4D9AC3C93DB72AEAA49E019--

--------------1A59B12C26E8F957F120522E
Content-Type: application/x-php;
 name="bench_pdo.php"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="bench_pdo.php"

PD9waHAKCmRlY2xhcmUoc3RyaWN0X3R5cGVzPTEpOwoKZnVuY3Rpb24gZ2VuRGF0YShpbnQg
JGNudCk6IGFycmF5IHsKCSRkYXRhID0gW107CgoJZm9yKCRpID0gMDsgJGkgPCAkY250OyAr
KyRpKSB7CgkJJGRhdGFbXSA9IFsKCQkJJ3N0cjEnID0+IHVuaXFpZCgpLAoJCQknc3RyMicg
PT4gdW5pcWlkKCksCgkJCSdudW0nID0+IHJhbmQoKSwKCQldOwoJfQoKCXJldHVybiAkZGF0
YTsKfQoKJGRhdGEgPSBnZW5EYXRhKDgwMDApOwoKJGRiID0gbmV3IFBETygncGdzcWw6aG9z
dD1sb2NhbGhvc3Q7ZGJuYW1lPXRlc3QnLCAndGVzdCcsICd0ZXN0Jyk7CiRkYi0+c2V0QXR0
cmlidXRlKFBETzo6QVRUUl9FUlJNT0RFLCBQRE86OkVSUk1PREVfRVhDRVBUSU9OKTsKCiR2
YWxzID0gW107CmZvcmVhY2goJGRhdGEgYXMgJHJvdykgewoJJHZhbHNbXSA9ICRyb3dbJ3N0
cjEnXTsKCSR2YWxzW10gPSAkcm93WydzdHIyJ107CgkkdmFsc1tdID0gJHJvd1snbnVtJ107
Cn0KJHZhbFNxbCA9IFxpbXBsb2RlKCcsICcsIFxhcnJheV9maWxsKDAsIFxjb3VudCgkZGF0
YSksICcoPywgPywgPyknKSk7CiRzcWwgPSA8PDxTUUwKCUlOU0VSVCBJTlRPIHRoaW5naWVz
IChzdHIxLCBzdHIyLCBudW0pCglWQUxVRVMgeyR2YWxTcWx9CglSRVRVUk5JTkcgaWQsIHN0
cjIKU1FMOwoKJHByZXBTdGFydCA9IFxtaWNyb3RpbWUodHJ1ZSk7CiRxdWVyeSA9ICRkYi0+
cHJlcGFyZSgkc3FsKTsKJGV4ZWNTdGFydCA9IFxtaWNyb3RpbWUodHJ1ZSk7CiRxdWVyeS0+
ZXhlY3V0ZSgkdmFscyk7CiRmZXRjaFN0YXJ0ID0gXG1pY3JvdGltZSh0cnVlKTsKJHJldCA9
ICRxdWVyeS0+ZmV0Y2hBbGwoKTsKZWNobyAnR290IHJvd3M6ICcuXGNvdW50KCRyZXQpLlBI
UF9FT0w7CiRlbmQgPSBcbWljcm90aW1lKHRydWUpOwoKJHByZXBEdXJhdGlvbiA9IFxudW1i
ZXJfZm9ybWF0KCRleGVjU3RhcnQgLSAkcHJlcFN0YXJ0LCAzKTsKJGV4ZWNEdXJhdGlvbiA9
IFxudW1iZXJfZm9ybWF0KCRmZXRjaFN0YXJ0IC0gJGV4ZWNTdGFydCwgMyk7CiRmZXRjaER1
cmF0aW9uID0gXG51bWJlcl9mb3JtYXQoJGVuZCAtICRmZXRjaFN0YXJ0LCAzKTsKCi8vIHZh
cl9kdW1wKCRyZXQpOwoKZWNobyAiW1RpbWluZ3NdIHByZXA6IHskcHJlcER1cmF0aW9ufSwg
ZXhlYzogeyRleGVjRHVyYXRpb259LCBmZXRjaDogeyRmZXRjaER1cmF0aW9ufSIuUEhQX0VP
TDsK
--------------1A59B12C26E8F957F120522E
Content-Type: application/sql;
 name="test-schema.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="test-schema.sql"

Q1JFQVRFIFRBQkxFIHRoaW5naWVzICgKCWlkIFNFUklBTCBQUklNQVJZIEtFWSwKCXN0cjEg
VEVYVCBOT1QgTlVMTCwKCXN0cjIgVEVYVCBOT1QgTlVMTCwKCW51bSBJTlRFR0VSIE5PVCBO
VUxMCik7Cg==
--------------1A59B12C26E8F957F120522E--