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
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
MUST BE VALID
Solve the problem:
13 - 4 = ?
Subscribe to this entry?

 
 [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

Add a Patch

Pull Requests

Add a Pull Request

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-2021 The PHP Group
All rights reserved.
Last updated: Tue Dec 07 04:03:41 2021 UTC