Allowed memory size of 8589934592 bytes exhausted

Question

I encountering issue on my schedule on Laravel.

I see that there is duplicate questions about this error but these don't fix my issue

root@trafficshield:/var/www/vhosts/trafficshield.tools/httpdocs# /opt/plesk/php/7.1/bin/php artisan schedule:run Running scheduled command: Closure ^[[15~PHP Fatal error: Allowed memory size of 8589934592 bytes exhausted (tried to allocate 4096 bytes) in /var/www/vhosts/trafficshield.tools/httpdocs/vendor/laravel/framework/src/Illum inate/Database/Eloquent/Model.php on line 279 PHP

This schedule aim to count all the visits of all campains on our service.

    $schedule->call(function () {
        $campaigns = Campaign::all();
        foreach ($campaigns as $campaign) {
            $campaign->denied_visits = $campaign->visitsDenied->count();
            $campaign->allowed_visits = $campaign->visitsAllowed->count();
            $campaign->save();
        }
    })->everyFiveMinutes();

How can I change the PHP code the avoid this issue ?

Config : memory_limit : 8G

Thank's in advance for your help.

To change the memory_limit would be a temporally fix when the ammount of data increase the problem come back so I would suggest you to avoid using Elloquent to do this job since Elloquent has poor memory efficiency. I would give you an approach hold on — aaron0207
Just a food for thought: You often run out of memory if trapped in an infinite loop... — Greg

aaron0207 aaron0207 · Accepted Answer · 2017-09-15T10:40:51

Allowed memory size of 8589934592 bytes exhausted

This kind of errors are caused by a big amount of data in memory, so the way to fix it is write a less heavy memory script. By changing memory_limit we only get a temporally fix because when our data grows it comes back.

    $campaigns = Campaign::all(); //at this point you are pulling the whole Campaigns table to memory.. and you pull some extra rows after that increasing even more the memory use

As I said, Elloquent is poor efficient doing this kind of task so let's fetch the database rows as mysqli used to do, one by one:

  $db = DB::connection()->getPdo(); //get a database connection instance

  $main_query_sql = "SELECT * FROM Campaigns"; //write our query 
  $main_query = $db->prepare($main_query_sql); //prepare it to get executed
  $main_query->execute(); //execute our query


  $visits_denied_sql = "SELECT COUNT(*) as total FROM VISITS WHERE DENIED = 1 AND CAMPAIGN_ID ="; //let's prepare our aggregate queries

  $visits_allowed_sql = "SELECT COUNT(*) as total FROM VISITS WHERE DENIED = 0 AND CAMPAIGN_ID ="; //I just assumed them so change it as you need

  $visits_denied = null;
  $visits_allowed = null;

  while($campaign = $main_query->fetch()){ //fetch our rows one by one
      //now we are getting an associative array from the db instead of a model so we cannot use it as a Laravel Model (we can't use ->save() method or eager loading)
      $visits_denied = $db->prepare($visits_denied_sql.$campaign['id']);
      $visits_denied = $visits_denied->execute();
      $denied_visits = $visits_denied->fetch();

      $visits_allowed= $db->prepare($visits_allowed_sql.$campaign['id']);
      $visits_allowed= $visits_allowed->execute();
      $allowed_visits = $visits_allowed->fetch();

      $campaign['denied_visits'] = $denied_visits['total'];
      $campaign['allowed_visits'] = $allowed_visits['total'] ;

      //edit with the correct update sentence:
      $insert_query_sql = "UPDATE CAMPAIGNS SET allowed_visits = :visits_allowed, denied_visits = :visits_denied WHERE id = :id";
        $insert_query = $db->prepare($insert_query_sql);
        $insert_query->bindParam(':visits_allowed', $campaign['allowed_visits']);
        $insert_query->bindParam(':visits_denied', $campaign['denied_visits']);
        $insert_query->bindParam(':id', $campaign['id']);
        $insert_query->execute();

  }

Try this inside your schedule and let me know if worked!

Allowed memory size of 8589934592 bytes exhausted

1 Answers