php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #80761 PDO uses too much memory
Submitted: 2021-02-17 09:30 UTC Modified: 2021-02-22 14:48 UTC
From: anton at 4blk dot com Assigned:
Status: Closed Package: PDO MySQL
PHP Version: 7.3.27 OS: Ubuntu 16.04
Private report: No CVE-ID: None
 [2021-02-17 09:30 UTC] 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

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-02-18 00:54 UTC] 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-22 14:34 UTC] 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-22 14:48 UTC] 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-23 10:17 UTC] nikic@php.net
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
 [2021-02-23 10:17 UTC] nikic@php.net
-Status: Open +Status: Closed
 [2021-05-27 14:00 UTC] git@php.net
Automatic comment on behalf of nikic
Revision: https://github.com/php/php-src/commit/635303aec511f74130d029e29956841d88253a9a
Log: Fixed bug #80761
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Oct 04 18:01:29 2024 UTC