Allowed memory size of 134217728 bytes exhausted after many Model::findFirst() - Phalcon 2.0

Question

Allowed memory size of 134217728 bytes exhausted after many Model::findFirst() - Phalcon 2.0

Lincoln Dec '16

Created Dec '16	Last Reply Dec '16	Replies 11	Views 975	Votes 0

Lincoln 6.2k

Dec '16

I am implementing a routine where in need update or insert many registers.. But after many Model::findFirst I got the error Allowed memory size of 134217728 bytes exhausted...

There are a better way to resolve this?

Follow the code:

protected function categoryMacs($establishment, $macs, $categoryId){
    $memoryLimit = ini_get("memory_limit");
    foreach ($macs as $mac => $data) {
      $this->logger->log("M: " . $this->formatBytes(memory_get_usage()) . " de " . $memoryLimit);
      $establishmentCM = EstablishmentCategoryMac::findFirst("establishment_id = " . $establishment["id"] . " and mac = " . $mac);
      if (! $establishmentCM) {
        $establishmentCM = new EstablishmentCategoryMac();
        $establishmentCM->setEstablishment_id($establishment["id"]);
        $establishmentCM->setMac($mac);
      }
      $establishmentCM->setCategory_mac_id($categoryId);
      $establishmentCM->save();
      unset($establishmentCM);
    }
  }

LOG:

[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.25 mb de 128M
[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.26 mb de 128M
[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.28 mb de 128M
[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.29 mb de 128M
[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.31 mb de 128M
[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.32 mb de 128M
[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.34 mb de 128M
[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.35 mb de 128M
[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.37 mb de 128M
[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.38 mb de 128M
[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.39 mb de 128M
[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.41 mb de 128M
[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.42 mb de 128M
[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.44 mb de 128M
[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.45 mb de 128M
[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.47 mb de 128M
[Wed, 14 Dec 16 11:14:40 -0200][DEBUG] M: 51.48 mb de 128M

Wojciech Ślawski
145.0k

Accepted
answer

edited Dec '16
Dec '16

The problem is this:

$establishmentCM = EstablishmentCategoryMac::findFirst("establishment_id = " . $establishment["id"] . " and mac = " . $mac);

Use binding here. Phalcon is caching all phql queries with real sql so there is no need to recreate it.

Lajos Bencz · Answer 1 · 2016-12-14T07:09:44-07:00

Is findFirst returning a single, large result, or are the results piling up in memory? (based on the log, t's the latter) Unset is not instantenous, you could try calling gc_collect_cycles() at the end of each iteration

Lincoln · Answer 2 · 2016-12-14T07:27:47-07:00

Lincoln
6.2k

in reply to

Lajos Bencz

Dec '16

Thanks for your help, but the result was the same.

Lajos Bencz · Answer 3 · 2016-12-14T07:39:05-07:00

Lajos Bencz
77.7k

Dec '16

Do we know exactly which line causes the memory exhaustion? (findfirst/save?)

Jonathan Aaron Steel · Answer 4 · 2016-12-14T08:42:28-07:00

Jonathan Aaron Steel
79.0k

Dec '16

Instead of:

      unset($establishmentCM);

do this:

      $establishmentCM = null;

Lincoln · Answer 5 · 2016-12-14T09:04:38-07:00

Lincoln
6.2k

in reply to

Lajos Bencz

Dec '16

findFisrt, the error "Memory..." happen in this line.

Lincoln · Answer 6 · 2016-12-14T09:11:20-07:00

Lincoln
6.2k

in reply to

Jonathan Aaron Steel

Dec '16

Instead of:

     unset($establishmentCM);

do this:

     $establishmentCM = null;

Same problem.

Lincoln · Answer 7 · 2016-12-14T09:20:16-07:00

So guys, I know that this is not the problem solution.. But i found a workaround.. I will usal SQL RAW....


    $sql = "INSERT INTO establishment_category_macs (establishment_id, category_mac_id, mac, mac_int)
        VALUES ('" . $establishment["id"] . "', '" . $categoryId . "', '" . $mac . "', '" . $macInt . "')
        ON DUPLICATE KEY UPDATE category_mac_id = '" . $categoryId . "'";
      $this->db->query($sql);

LOG:

[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M
[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M
[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M
[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M
[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M
[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M
[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M
[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M
[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M
[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M
[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M
[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M
[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M
[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M
[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M
[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M
[Wed, 14 Dec 16 14:13:42 -0200][DEBUG] M: 8.28 mb de 128M

Jonathan Aaron Steel · Answer 8 · 2016-12-14T09:21:43-07:00

null on a given variable should do the job at the same runtime, while unset() is expected to fail in this memory hog scenario as it will only mark variable (memory pointer) for GC process to handle it after execution/runtime.

You're saying that findFirst() (ORM) fails, but exactly same data set works with PDO?

Lincoln · Answer 9 · 2016-12-14T09:53:39-07:00

null on a given variable should do the job at the same runtime, while unset() is expected to fail in this memory hog scenario as it will only mark variable (memory pointer) for GC process to handle it after execution/runtime.

You're saying that findFirst() (ORM) fails, but exactly same data set works with PDO?

The findFirst works.. the problem is that when a use it the memory in this caso dont stop to grow, until the error occurrence.

Lincoln · Answer 10 · 2016-12-14T10:07:18-07:00

The problem is this:

$establishmentCM = EstablishmentCategoryMac::findFirst("establishment_id = " . $establishment["id"] . " and mac = " . $mac);

Use binding here. Phalcon is caching all phql queries with real sql so there is no need to recreate it.

Show.. works here.

     $establishmentCM = EstablishmentCategoryMac::findFirst(array(
        "conditions" => "establishment_id = ?1 and mac = ?2",
        "bind" => array(
          1 => $establishment["id"],
          2 => $mac
        )
      ));

LOG:

[Wed, 14 Dec 16 15:05:38 -0200][DEBUG] M: 8.3 mb de 128M
[Wed, 14 Dec 16 15:05:38 -0200][DEBUG] M: 8.3 mb de 128M
[Wed, 14 Dec 16 15:05:38 -0200][DEBUG] M: 8.3 mb de 128M
[Wed, 14 Dec 16 15:05:38 -0200][DEBUG] M: 8.3 mb de 128M
[Wed, 14 Dec 16 15:05:38 -0200][DEBUG] M: 8.3 mb de 128M
[Wed, 14 Dec 16 15:05:38 -0200][DEBUG] M: 8.3 mb de 128M
[Wed, 14 Dec 16 15:05:38 -0200][DEBUG] M: 8.3 mb de 128M
[Wed, 14 Dec 16 15:05:38 -0200][DEBUG] M: 8.3 mb de 128M
[Wed, 14 Dec 16 15:05:38 -0200][DEBUG] M: 8.3 mb de 128M
[Wed, 14 Dec 16 15:05:38 -0200][DEBUG] M: 8.3 mb de 128M
[Wed, 14 Dec 16 15:05:38 -0200][DEBUG] M: 8.3 mb de 128M
[Wed, 14 Dec 16 15:05:38 -0200][DEBUG] M: 8.3 mb de 128M
[Wed, 14 Dec 16 15:05:38 -0200][DEBUG] M: 8.3 mb de 128M
[Wed, 14 Dec 16 15:05:38 -0200][DEBUG] M: 8.3 mb de 128M
[Wed, 14 Dec 16 15:05:38 -0200][DEBUG] M: 8.3 mb de 128M
[Wed, 14 Dec 16 15:05:38 -0200][DEBUG] M: 8.3 mb de 128M

Jonathan Aaron Steel · Answer 11 · 2016-12-14T10:42:01-07:00

Ohh, seriouslly?! What is the difference in binding and passing search criteria as a string in terms of this issue? It's internal ORM shit then, and one more reason for me to keep using my own ORM implementation :)

At least this should be documented / advertised as a potential memory hog if developers are not binding params.

Kudos to @Jurigag for this solution.

Wojciech Ślawski · Answer 12 · 2016-12-14T14:06:27-07:00

It is documented https://docs.phalcon.io/en/3.0.1/reference/models-cache.html#caching-of-phql-planning

Just read docs next time :)

le51 · Answer 13 · 2016-12-15T03:35:57-07:00

le51
43.9k

in reply to

Wojciech Ślawski

edited Dec '16
Dec '16

really good ! I will you use that.

It is documented https://docs.phalcon.io/en/3.0.1/reference/models-cache.html#caching-of-phql-planning

Just read docs next time :)

Jonathan Aaron Steel · Answer 14 · 2016-12-15T05:18:23-07:00

I'm not using ORM that often. But kudos to Phalcon docs too as it is documented afterall.

Still, it's just crazy behaviour.

It is documented https://docs.phalcon.io/en/3.0.1/reference/models-cache.html#caching-of-phql-planning

Just read docs next time :)

Lajos Bencz · Answer 15 · 2016-12-15T05:27:06-07:00

It sounds perfectly reasonable... parameter binding has been around for what, 15 years now? It's a good practice to prevent SQL injection, so why not pair it up with performance optimization?:]

Jonathan Aaron Steel · Answer 16 · 2016-12-15T05:28:54-07:00

Well, if go with plain PDO that's another story. I would always use prepared statements for sure.

But with framework, you expect some things are wrapped behind the scenes to be optimized by default, and not to kill your app completely.

In any case it's good to know this.