How to reduce PHP/MySQL memory consumption?

Question

PHP 7, mysqli, Reference: Example of how to use bind_result vs get_result

I am using unbuffered fetching (I hope) and wonder about the memory consumption $m. As I just fetch (test case) I would expect the memory $m to be almost constant. But it is not, depending on how many rows I fetch it increases. I would expect that fetch result works like a cursor only getting 1 row at a time.

How would I achieve that (reading 1 row at a time)?

Remark: Here https://stackoverflow.com/a/14260423/356726 they use

$uresult = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT);

but I have not found a way to pass MYSQLI_USE_RESULT somewhere in a prepared statement.

$result = $stmt->get_result(); // not stored I hope
$i = 0;
while ($row = $result->fetch_assoc()) {
    $i++;
    if ($i > 20000) {
        break;
    }
}
$m = memory_get_usage(); // see values between 10-50MB here, depending on i
$this->freeAndCloseStatement($stmt);

How is the memory consumption if you use [`bind_result()`](http://php.net/manual/en/mysqli-stmt.bind-result.php) and [`fetch()`](http://php.net/manual/en/mysqli-stmt.fetch.php) instead of `get_result()` and `fetch_assoc()`? — simon, Sep 26 '18 at 16:06
around the same size, also not const, but increasing with the size of the fetched rows. When I free the SQL resources (close) I see a dramatic drop in memory. Means somehow the result consumes memory, which is exactly what I should not see with unbuffered results (at least this is my understanding why it is called unbuffered). — Horst Walter, Sep 26 '18 at 16:11
Well I'm at a loss then. While prepared statements are unbuffered by default, `get_result()` returns a buffered result set. So I thought that using `bind_result()` instead would solve the issue. — simon, Sep 26 '18 at 16:28
The main problem is not the buffering of that one row, but of all rows already read (unwanted behavior). The next problem is that I hit my 128MB script memory limit, although `memory_get_usage` only shows 80MB. All very odd. — Horst Walter, Sep 26 '18 at 18:42

score 0 · Answer 1 · answered Sep 26 '18 at 15:08

There is an example on Mysql Documentation, so you don't put as parameter, just run a method.

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

/* execute multi query */
if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->use_result()) { //<----------- THIS LINE
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->close();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

/* close connection */
$mysqli->close();
?>

https://dev.mysql.com/doc/apis-php/en/apis-php-mysqli.use-result.html

For prepared statement you could set attribute as false.

$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

I have tried `$this->_connection->use_result();` in 3 places, before `while`, before the `get_result()` and before the query. The memory consumption does not change, same value in all 3 scenarios. — Horst Walter, Sep 26 '18 at 15:50

How to reduce PHP/MySQL memory consumption?

1 Answers1