Bug #80761 [Opn]: 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 Type: Bug Package: PDO MySQL Operating System: Ubuntu 16.04 PHP Version: 7.3.27 Block user comment: N Private report: N New Comment: 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. Previous Comments: ------------------------------------------------------------------------ [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