PDOProxy, memory isn't freed after query

[GuillaumeBrook]

1. What did you do? If possible, provide a simple script for reproducing the error.

<?php

declare(strict_types=1);

use Swoole\Constant;
use Swoole\Database\PDOConfig;
use Swoole\Http\Request;
use Swoole\Http\Response;
use Swoole\Http\Server;

ini_set('memory_limit', '2048m');

// 4 workers
$http = new Server('0.0.0.0', 9501);
$http->set(
     [
        Constant::OPTION_WORKER_NUM => 4
     ]
);

// A pool of 16 connections
$dbConnectionsPool = new Swoole\Database\PDOPool(
    (
        new PDOConfig())
        ->withHost('...')
        ->withDbname('...')
        ->withUsername('...')
        ->withPassword('...')
        ->withCharset('utf8mb4')
        ->withDriver('mysql'),
    16);
$http->on(
    Constant::EVENT_REQUEST,
    function (Request $request, Response $response) use (
        $http,
        $dbConnectionsPool
    ) {
        echo 'Started a request on worker : ' . $http->worker_pid . "\n";
        $dbConnection = $dbConnectionsPool->get();
        /** @var Swoole\Database\PDOStatementProxy $statement */
        $statement = $dbConnection->__call('query', ['select * from my_table']); // like 200 mb of data
        $results = $statement->__call('fetchAll', []);
        $dbConnectionsPool->put($dbConnection);
        $response->status(200);
        $response->end();
    }
);

$http->start();

To fill 1 worker: Call via postman / curl / browser the url : http://localhost:9501

To fill the 4 workers : wrk -t12 -c400 -d1s http://localhost:9501 // apache benchmark should be fine too On mysql write : show processlist multiple time until everything is done

2. What did you expect to see? The memory usage to be released when the query end.

3. What did you see instead? Each worker seems to have in memory the size of results

4. What version of Swoole are you using (show your php --ri swoole)?

Swoole => enabled
Author => Swoole Team <[email protected]>
Version => 5.1.0
Built => Oct 27 2023 17:47:19
coroutine => enabled with boost asm context
epoll => enabled
eventfd => enabled
signalfd => enabled
cpu_affinity => enabled
spinlock => enabled
rwlock => enabled
sockets => enabled
openssl => OpenSSL 3.0.11 19 Sep 2023
dtls => enabled
http2 => enabled
json => enabled
curl-native => enabled
zlib => 1.2.13
mutex_timedlock => enabled
pthread_barrier => enabled
futex => enabled
mysqlnd => enabled
async_redis => enabled
coroutine_pgsql => enabled

Directive => Local Value => Master Value
swoole.display_errors => On => On
swoole.enable_coroutine => On => On
swoole.enable_fiber_mock => Off => Off
swoole.enable_library => On => On
swoole.enable_preemptive_scheduler => Off => Off
swoole.unixsock_buffer_size => 8388608 => 8388608
swoole.use_shortname => On => On

5. What is your machine environment used (show your uname -a & php -v & gcc -v) ? I used docker image FROM phpswoole/swoole:5.1.0-php8.2

Linux 2f39e4041183 5.14.0-1054-oem #61-Ubuntu SMP Fri Oct 14 13:05:50 UTC 2022 x86_64 GNU/Linux

PHP 8.2.11 (cli) (built: Oct 12 2023 04:07:53) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.2.11, Copyright (c) Zend Technologies

[NathanFreeman]

Because a connection pool is used, the connections inside the pool will only be released when the program terminates.

[GuillaumeBrook]

Actually, this is how mysqlnd operates. Let's take the following example :

<?php
var_dump(memory_get_usage(true) / 1024 / 1024);

$pdoClient = new \PDO($dsn, $user, $password, $options);
$pdoClient->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); // doesn't matter anyway
$prepare = $pdoClient->prepare('select * from some_table'); // 400k entries
$prepare->execute();
$results = $prepare->fetchAll(\PDO::FETCH_ASSOC);

$prepare->closeCursor(); // doesn't matter anyway

var_dump(count($results));

unset($results, $prepare, $pdoClient);

var_dump(memory_get_usage(true) / 1024 / 1024);

Will print

int(2)
int(400000)
int(366)

In a php stateless app, that ok, we will retrieve our 366mb of memory at the end of the http request. In a php stateful app, I don't manage to retrieve it. Maybe the gc will do it's job sometimes but I doubt it.

I guess the only solution is to do the query outside the "Constant::EVENT_REQUEST" and say goodbye to my 366mb of memory... (it's better than saying goodbye to 366*number of workers though)

I didn't manage to find a solution yet to free this. So indeed, it's not really linked to Swoole internals.

[GuillaumeBrook]

Last update, it was not really PDO. It is just how PHP works when creating a big array. This allocated memory will be reserved by PHP even after unsetting ! However, PHP will re-use this memory.

Example :

<?php

echo 'Mem usage : ' . (memory_get_usage(true)) . "\n";
$results = forArray();
echo 'Mem usage : ' . (memory_get_usage(true)) . "\n";
var_dump(count($results));
unset($results);
echo 'Mem usage : ' . (memory_get_usage(true)) . "\n";

function forArray(): array
{
    $results = [];
    for ($i = 0; $i < 100000; $i++) {
        $results[] = 'example_' . $i;
    }

    return $results;
}

It will print

Mem usage : 2097152
Mem usage : 8392704
int(100000)
Mem usage : 6291456

Even after unsetting, we don't fallback to our 2MB of memory usage. PHP has reserved 6MB of memory usage.

However, if you run again the function multiple times in the same script, it will not increase exponentially. PHP re-uses the previous reserved memory.