Can not load more than 1000 items using Phalcon-3.0.x + PHP-7.0 + PostgreSQL

[tembem]

I can confirm this is working with Ubuntu 16.04 + nginx + PHP-5.6 + Phalcon-2.1.x + PostgreSQL-9.5

Yesterday, I decided to upgrade to PHP-7.0 + Phalcon-3.0.0 and found this code is not working anymore.

public static function findAllDetails($id) {
    $params = array('id' => $id,);
    $sql = "SELECT * FROM mytable WHERE id = :id";
    $item = new MyModel();  
    return new Resultset(null, $item, $item->getReadConnection()->query($sql, $params));
}

My result is more than 1000 items (actually ~ 4000 items), this code is working without any problem using Phalcon 2.1.x and showing in view as expected.

If the result got ~ 700 items, the code is working again.

No change in nginx.conf nor php.ini nor postgresql.conf in my installation.

Is this bug? Or I need to edit some "config" in my PHP-7.0 installation?

Ubuntu-16.04.1 LTS
PHP 7.0.8-0ubuntu0.16.04.1
Phalcon-3.0.x latest build #a48bdbb
PostgreSQL-9.5
nginx-1.10

[tembem]

I tested with this sample code using PDO PHP-7.0 directly (with same configuration)

public static function findAllDetails($id) {
    $user = 'postgres';
    $pass = 'mypassword';

    $conn = new \PDO('pgsql:host=127.0.0.1; dbname=mydb;', $user, $pass);

    $mainSQL = "SELECT * FROM mytable";
    $sth = $conn->prepare($mainSQL);
    $sth->setFetchMode(\PDO::FETCH_ASSOC);
    $sth->execute();
    $retRows = $sth->fetchAll();
    var_dump($retRows);
    unset($sth);
    unset($conn);
}

And it works showing all ~4000 items. And showing another resultset ~32000 items too without any problems.

[tembem]

SOLVED

Increase php.ini memory_limit solving my problem

[Jurigag]

Actually as far as i know there is still small memory leak on php 7 and phalcon 3.0.0 which can happen especially on big results.

[tembem]

@Jurigag yes i guess so

@sergeyklay @andresgutierrez I hope it won't become a bigger problem

[andresgutierrez]

Yeah, what was your memory_limit and what is now after being increased?

[tembem]

@andresgutierrez my initial memory_limit was 128M (default setting from php ubuntu repo installation). I increased to 2048M, and it was working then.

Initial memory_limit 128M was enough using PHP-5.6 + Phalcon-2.1.x (latest build) to run my function.

[andresgutierrez]

That's strange, I'll check if there are memory leaks we haven't aware of

[tembem]

@andresgutierrez

I set 128M not working
I set 256M not working
I set 512M not working
I set 1024M working
I set 2048M working

Thank you. That's quite big memory.

[Jurigag]

Were you trying what i posted many times - Adding getters and setters to models as well all properties to them ?

[tembem]

@Jurigag wew you're right. I add a new custom model with properties match with my resultset columns, and it's working now.

Maybe #12131 solving a different problem?

[Jurigag]

But you checked this latest committ to 3.0.x ? Well the best could be if you could check valgrind for memory leaks with latest 3.0.x and post log from it here.

[tembem]

@Jurigag Yes, i checked this with latest commit. Built phalcon few hours ago. Commit 7fd54ba for fixed memory leak was there.

[Jurigag]

Well i guess i need to check this myself

[Jurigag]

For me there is no memory leak on this latest commit using phalcon_test and personnes table.

==2766== LEAK SUMMARY:
==2766==    definitely lost: 520 bytes in 2 blocks
==2766==    indirectly lost: 0 bytes in 0 blocks
==2766==      possibly lost: 464 bytes in 5 blocks
==2766==    still reachable: 119,120 bytes in 369 blocks
==2766==         suppressed: 0 bytes in 0 blocks
==2766== Reachable blocks (those to which a pointer was found) are not shown.
==2766== To see them, rerun with: --leak-check=full --show-leak-kinds=all

Script to reproduce:

$di = new Phalcon\DI\FactoryDefault();

$di->setShared('db', function () {
    $connection = new Phalcon\Db\Adapter\Pdo\Mysql([
        'adapter'  => 'Mysql',
        'host'     => 'localhost',
        'port'     => 3306,
        'dbname'   => 'phalcon_test',
        'username' => 'root'
    ]);

    return $connection;
});
Model::setup([
   'notNullValidations'=>false
]);

class Personnes extends Model
{
}

$test = Personnes::find();
foreach($test as $t)
{
}

Can you post your script to reproduce and database ? @tembem OR AT LEAST valgrind log

[tembem]

@Jurigag Sorry, I don't know how to use valgrind, never use it before. I try to learn valgrind this weekend.

Could you please help test it with Postgresql and Phalcon\Mvc\Model\Resultset\Simple? Maybe something like:

$di = new Phalcon\DI\FactoryDefault();

$di->setShared('db', function () {
    $connection = new Phalcon\Db\Adapter\Pdo\Postgresql([
        'adapter'  => 'Postgresql',
        'host'     => 'localhost',
        'port'     => 5432,
        'dbname'   => 'phalcon_test',
        'username' => 'postgres'
    ]);

    return $connection;
});

class Personnes extends Model
{
}

$sql = "SELECT * FROM bigtable";
$item = new Personnes();  
$test = new Phalcon\Mvc\Model\Resultset\Simple(null, $item, $item->getReadConnection()->query($sql));
foreach($test as $t)
{
}

Thank you for your time

[sergeyklay]

SELECT * FROM bigtable

It will always be bad. In any framework

[tembem]

@sergeyklay
That's only an example.

In my real sql example there were explicit columns and some several joins, which was ran nicely with 2.1.x, not 3.0.x, unless I add memory_limit. That's the point I think.

If you think there's no problem with phalcon. It's fine. I just report what I found and already close this issue by adding memory_limit and using plain pdo instead.

[sergeyklay]

@tembem Could you please provide a tables schema and models definition?

[Jurigag]

Make sure you are doing this on this working "sample", that there will be no out of memory. Eventually try to limit your query or something

@tembem :

1. Install valgrind using apt-get install valgrind
2. Disable your apache2/nginx
3. If you are using apache2 do source /etc/apache2/envvars
4. Do valgrind -v --log-file=memleak.log --trace-children=yes --leak-check=full --read-var-info=yes --track-origins=yes apache2 -X
5. Access website causing problem - it will load more time than always, after loading just exit valgrind - using ctrl+c
6. Post a log from memleak.log - you don't to post whole file, just leak summary and stack which is causing a problem like having something xxx bytes in y blocks are possibly/defintely lost where xxx should be a big number.

You need to provide script to reproduce this issue, otherwise we can't deal with this.

[tembem]

@sergeyklay okay, will do
@Jurigag I'm using apt php7, should I reconfigure --enable-debug for using valgrind and php?

[Jurigag]

@tembem don't need to do this. --enable-debug is just php option - it will cause to php detect itself memory leaks. With valgrind you can just detect them without --enable-debug.

[Jurigag]

Checked this on postgresql, no problem with personnes table and Personnes model from phalcon which has 2180 records.

[tembem]

@Jurigag @sergeyklay Sorry, I can't run valgrind work, it's always crash :(

Interesting that #12144 that happens to me as well! Maybe root of the problems is same. I create sample:

<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);

$di = new Phalcon\DI\FactoryDefault();

$di->setShared('db', function () {
    $connection = new Phalcon\Db\Adapter\Pdo\Postgresql([
        'host'     => 'localhost',
        'port'     => 5432,
        'dbname'   => 'somedb',
        'username' => 'postgres',
        'password' => 'passwordhere'
    ]);

    return $connection;
});

class Roll extends Phalcon\Mvc\Model
{
    public $id;
}

function bytes($size)
{
    $units = [' B', ' KB', ' MB', ' GB', ' TB'];
    for ($i = 0; $size >= 1024 && $i < 4; $i++)
        $size /= 1024;
    return round($size, 2) . $units[$i];
}

$sql = "SELECT
        id,
        roll_date
        FROM roll
        ";
$item = new Roll();  
$result = new Phalcon\Mvc\Model\Resultset\Simple(null, $item, $item->getReadConnection()->query($sql));
$counter = 0;
foreach ($result as $r) {
    if ($counter % 1000 == 0) {
        echo bytes(memory_get_usage()) . "\n";
    }
    if ($counter > 10000) {
        die();
    }
    $counter++;
}

PHP-7.0 + Phalcon-3.0.x 649f35c (without define all columns)

369.23 KB
5.56 MB
10.76 MB
15.97 MB
21.17 MB
26.39 MB
31.59 MB
36.78 MB
41.98 MB
47.24 MB
52.43 MB

PHP-5.6 + Phalcon-2.1.x 95ef0db (without define all columns)

270.03 KB
270.06 KB
270.06 KB
270.06 KB
270.06 KB
270.06 KB
270.06 KB
270.06 KB
270.06 KB
270.06 KB
270.06 KB

PHP-7.0 + Phalcon-3.0.x 649f35c (define all columns)

class Roll extends Phalcon\Mvc\Model
{
    public $id;
    public $roll_date;
}

363.26 KB
363.26 KB
363.26 KB
363.26 KB
363.26 KB
363.26 KB
363.26 KB
363.26 KB
363.26 KB
363.26 KB
363.26 KB

I think @Jurigag was correct, without define properties properly, script may consume memory even bigger.

For bigger tables it shown Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4096 bytes) in /var/www/test/index.php on line 40

[tembem]

I think I better close this issues, and keeping my eyes on #12144

[Jurigag]

Are you sure for 100% you compiled latest phalcon ? I don't have issue which you are posting and #12144 is other issue.

Your issue was fixed by 7fd54ba for sure. I checked it and it's fixing it. #12144 is whole different thing and not related to your example.

$di = new Phalcon\DI\FactoryDefault();

$di->setShared('db', function () {
    $connection = new Phalcon\Db\Adapter\Pdo\Mysql([
        'host'     => 'localhost',
        'port'     => 3306,
        'dbname'   => 'phalcon_test',
        'username' => 'root',
    ]);

    return $connection;
});
Model::setup([
   'notNullValidations'=>false
]);
function bytes($size)
{
    $units = [' B', ' KB', ' MB', ' GB', ' TB'];
    for ($i = 0; $size >= 1024 && $i < 4; $i++)
        $size /= 1024;
    return round($size, 2) . $units[$i];
}

class Personnes extends Model
{
    public $email;
}
$sql = "SELECT email,cedula FROM personnes";
$item = new Personnes();
$test = new Phalcon\Mvc\Model\Resultset\Simple(null, $item, $item->getReadConnection()->query($sql));
$counter = 0;
foreach($test as $t)
{
    if ($counter % 1000 == 0) {
        echo bytes(memory_get_usage()) . "\n";
    }
    if ($counter > 10000) {
        die();
    }
    $counter++;
}

Is returning for me 788.24 KB 788.24 KB 788.21 KB WITHOUT FIXING #12144

[tembem]

Yes. I'm sure. Latest commit 649f35c.

It's weird. Have you tried my sample code?

[Jurigag]

But it's the same sample code, just different table, on postgresql result is the same.

How you sure ? How you compiled it again exactly ? To use 3.0.x you need to use zephir i think, build folder wasn't regenerated yet so it won't work using:

cd cphalcon/build
sudo ./install

Because there is just old build from 3.0 right now. Only master repository i supposed to working with default build process. To making sure you have latest version with latest changes you need to use zephir for building it.

[tembem]

oh my bad

I built using

cd cphalcon/build
sudo ./install

Thank you for mentioning this. I'll try again.

[tembem]

@Jurigag you're right. Using zephir build for latest phalcon. Problem is solved

Thanks a lot