Bug #80761 [Opn->Csd]: PDO uses too much memory
Edit report at https://bugs.php.net/bug.php?id=80761&edit=1
ID: 80761
Updated by: nikic@php.net
Reported by: anton at 4blk dot com
Summary: PDO uses too much memory
-Status: Open
+Status: Closed
Type: Bug
Package: PDO MySQL
Operating System: Ubuntu 16.04
PHP Version: 7.3.27
Block user comment: N
Private report: N
New Comment:
Automatic comment on behalf of nikita.ppv@gmail.com
Revision: http://git.php.net/?p=php-src.git;a=commit;h=1fc4c89214c82fabbf997da58051a385d8fe50ab
Log: Fixed bug #80761
Previous Comments:
------------------------------------------------------------------------
[2021-02-22 14:48:02] nikic@php.net
The problem is that we receive the packet in 16MB chunks (with the total size unknown) and continue
reallocate the buffer for each chunk
(https://github.com/php/php-src/blob/7f8ea83ef438fbcfa1cbc636d701491d4e773245/ext/mysqlnd/mysqlnd_wireprotocol.c#L1409).
Because this uses an arena allocator, "realloc" effectively becomes "alloc" and
memory usage will grow quadratically with the payload size.
------------------------------------------------------------------------
[2021-02-22 14:34:23] nikic@php.net
<?php
ini_set('memory_limit', -1);
$mb = 1024**2;
$n = 100 * $mb;
$m = memory_get_usage();
$x = str_repeat('a', $n);
printf("%.2F\n", (memory_get_usage() - $m) / $mb);
$db = new pdo('mysql:host=127.0.0.1', 'root', '', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);
$m = memory_get_usage();
$q = $db->query("select repeat('a', $n)");
printf("%.2F\n", (memory_get_usage() - $m) / $mb);
$a = $q->fetchAll();
printf("%.2F\n", (memory_get_usage() - $m) / $mb);
PHP 8.0:
100.00
636.05
736.06
PHP 8.1:
100.00
536.05
636.05
Using PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = false:
100.00
0.02
100.02
I removed an unnecessary 100 MB copy in PHP 8.1, but the memory usage is still larger than it should
be. Of course, when working with large data using an unbuffered query is preferred, which removes
the memory overhead entirely.
------------------------------------------------------------------------
[2021-02-18 00:54:54] dharman@php.net
I don't think this is PDO specific. I believe this is just how mysqlnd allocates memory. It
would be worth looking into it in more detail to see if this could be improved.
------------------------------------------------------------------------
[2021-02-17 09:30:59] anton at 4blk dot com
Description:
------------
Fetching long strings or BLOBs from MySQL uses too much memory.
Creating 100 MB string in php uses ~100 MB memory.
Fetching 100 MB string from database uses ~600 MB memory.
Test script:
---------------
<?php
// docker run --net=host --name db -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d mysql:5
--max_allowed_packet=256M
ini_set('memory_limit', -1);
$mb = 1024**2;
$n = 100 * $mb;
// $x = str_repeat('a', $n);
$db = new pdo('mysql:host=127.0.0.1;port=3306', 'root', '',
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$x = $db->query("select repeat('a', $n)");
printf("%.2F\n", memory_get_peak_usage() / $mb);
Expected result:
----------------
~100 MB
Actual result:
--------------
~600 MB
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=80761&edit=1
Thread (5 messages)