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

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

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

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



6.2k

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

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

Instead of:

      unset($establishmentCM);

do this:

      $establishmentCM = null;


6.2k

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



6.2k

Instead of:

     unset($establishmentCM);

do this:

     $establishmentCM = null;

Same problem.



6.2k

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
edited Dec '16

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?



145.0k
Accepted
answer
edited 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.



6.2k

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.



6.2k

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

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.



43.9k
edited 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 :)

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 :)

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?:]

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.