We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Paginator

Paginator exceeds memory_limit

Hey guys. I'm having a very strange problem. The issue consists of the Paginator exceeding PHP's memory limit since, I guess, it tries to retrieve the entire database table instead of getting only the 50 "current" rows as instructed by the 'limit' value. And here comes the intriguing part. It works just fine on PHP 5.6 but exceeds the memory limit on PHP 7. The installed PHP extensions as well as the configuration of 5.6 and 7 are all the same. I used EasyApache 4 to install and configure everything. The version of Phalcon is 3.2.2 for both PHP 5.6 and 7.

    $Paginator = new \Phalcon\Paginator\Adapter\Model([
        'data' => \ControlPanel\Models\Projects::find([
            'order' => 'id DESC'
        ]),
        'limit' => 50,
        'page' => $page
    ]);

Any idea why this is happening and what I can do to prevent this behaviour?

P.S. Just to let you know, the memory limit for both PHP 5.6 and 7 is set to the healthy 32 MB. The database table I'm trying to paginate is nearly 140 MB, and if I increase the memory limit, the issue disappears. However, increasing the memory limit is not a solution by itself, so I'd like to get to the root cause of this.

1


32.2k
Accepted
answer

You are using the pagination adapter in a less optimal way. Try the following

use Phalcon\Paginator\Factory;

$builder = $this->modelsManager->createBuilder()
                ->from(\ControlPanel\Models\Projects::class)
                ->orderBy('id DESC');

$options = [
    'builder' => $builder,
    'limit'   => 50,
    'page'    => $page,
    'adapter' => 'queryBuilder',
];

$paginator = Factory::load($options);

Good luck



2.9k
edited Mar '18

Thank you! Never in this application have I used the Query Builder adapter, I have only used just plain Models for making any DB calls. That being said, now that I tried your suggestion, that Query Builder seems a lot more efficient, at least in this scenario. I'll look into substituting the Models I've been using everywhere with a faster and better adapter if there is such. I wrote this app 2-3 years ago, I should probably read the Phalcon documentation again and revise all the code at this point.

My original pagination script:

PHP 5.6: Page loaded in 326 ms., Peak memory usage was 12.51 MB    // not sure why Phalcon needs this much memory to paginate 1-2 MB of data
PHP 7.1: Page loaded in 376 ms., Peak memory usage was 214.00 MB   // probably retrieved the entire database table

The pagination script you suggested:

PHP 5.6: Page loaded in 211 ms., Peak memory usage was 1.23 MB
PHP 7.1: Page loaded in 179 ms., Peak memory usage was 1.92 MB

One page of Paginator results should equal to 1-2 MB worth of database data. As you can see, the method you suggested uses only as much memory as it absolutely needs (1.23 or 1.92 MB), while my method uses obscene amounts of memory depending on the version of PHP. I'm not sure why this is happening, maybe it's a bug in Phalcon.



32.2k

your problem in the first example is this

'data' => \ControlPanel\Models\Projects::find([
    'order' => 'id DESC'
]),

The find() get all row and map to model, no problem if you have few elements, but if you have hundreds you have a big problem.

The query builder adapter allows you to make a right query and then Phalcon only get the range (offset/limit) required



2.9k
edited Mar '18

Thanks. I understand that, but I was just surprised to see the Paginator getting less memory efficient with newer versions of PHP. I'm sure there's a good reason for that though. Anyway, thanks again for helping me out. Now everything is working like a charm.



22.6k

This adapter has an issue with cursors, it will have memory issues with large data sets

new \Phalcon\Paginator\Adapter\Model

Querybuilder works fine getting count and loading with large data

 new \Phalcon\Paginator\Adapter\QueryBuilder