Command times out when executing

Question

Laravel version:
8.x

PHP version:
8.0

MySQL version:
8.0

Server:
Larave Forge

I'm running a command on one of my websites

php artisan command:here

after a few minutes I get this status "Timed Out", is there anything that can be optimized here to prevent the time out?

subscriptions table has 40,000 records and incomes table has 8,000,000. Every subscription has a maximum of 200 records in the incomes table.

To explain what this command does, this is fixing (by inserting) the lacking of income of every ACTIVE subscription, which will be identified based on the hour difference from the last income that has been inserted. Every subscription has a max of 200 records in the incomes table, so if the script detected that a subscription has already reached the 200 income records, it will update the status to COMPLETED.

Subscription.php (model)

public function latestIncome()
{
    return $this->hasOne(Income::class)->latestOfMany();
}

Income.php (model)

public function subscription()
{
    return $this->belongsTo(Subscription::class);
}

namespace App\Console\Commands;

class SomeCommand extends Command
{
    protected $signature = 'command:here';

    public function handle()
    {
        ini_set('max_execution_time', 0);

        foreach (Subscription::with('latestIncome')->withCount('income')->where('status', 'ACTIVE')->lazy() as $subscription) {
            $count_earnings = $subscription->income_count;
            $recent_bonus = $subscription->latestIncome;

            if ($recent_bonus) {
                if ($count_earnings < 200) {
                    $hour_difference = now()->diffInHours($recent_bonus->created_at);

                    if ($hour_difference > 1) {
                        $to_insert = 200 - $count_earnings;
                        $max = $hour_difference;

                        if ($hour_difference > $to_insert) {
                            $max = $to_insert;
                        }

                        for ($i = 0; $i < $max; $i++) {
                            $income = new Income;
                            $income->user_id = $subscription->user_id;
                            $income->subscription_id = $subscription->id;
                            $income->amount = (100 * 0.002) * 100;
                            $income->save();
                        }

                        if (($count_earnings + $max) >= 200) {
                            $subscription->update(['status' => 'COMPLETED']);
                        }

                        Log::info('Fix for:'.$subscription->id.' | User:'.$subscription->user_id.' | Total:'.$max);
                    }
                } else {
                    $subscription->update(['status' => 'COMPLETED']);
                }
            }
        }
    }
}

Answer 1

• I think the subquery is the main sluggish part.

• This is not "sargable": AND Date(created_at) >= '2022-02-04'. If possible, say

  AND created_at >= '2022-02-04'
  AND created_at  < '2022-02-04' + INTERVAL 1 DAY
  

• After the above, this index on subscriptions will be beneficial:

  INDEX(status, deleted_at, created_at)
  

• Oh, I found this hidden in a Comment; did you want that optimized?

  update  `subscriptions`
       set status = COMPLETED, 
           subscriptions.updated_at = 2022-02-14 17:53:10
    where  
    (
        SELECT  count(*)
            from  incomes
            where  subscriptions.id = incomes.subscription_id
              and  incomes.deleted_at is null
    ) >= 200
      and  status != COMPLETED
      and  subscriptions.deleted_at is null)
  

• It may benefit from these indexes:

  subscriptions:  INDEX(deleted_at, status)
  incomes:  INDEX(subscription_id, deleted_at)
  

• This will NOT work: updated_at = 2022-02-14 17:53:10; the datetime needs quotes around it. You might consider using NOW() instead of building the string.

• More importantly, that Update will, when there are more than 200, check each of the 200+ one at a time and update them -- 200+ times each!! Work on rearranging the UPDATE to be a "Multitable" update where one table is a subquery something like this:

    SELECT subscription_id
        FROM incomes
        WHERE deleted_at IS NULL
        GROUP BY subscription_id
        HAVING COUNT(*) >= 200
  

That should make the Update run hundred(s) of times faster.

Answer 2

You might gain some performance if you do your updates in bulk:

namespace App\Console\Commands;

class SomeCommand extends Command
{
    protected $signature = 'command:here';

    public function handle()
    {
        ini_set('max_execution_time', 0);

        // Update everything you can before the loop
        Subscription::has('income', '>=', 200)
                ->where('status', '!=' 'COMPLETED')
                ->update([ 'status' => 'COMPLETED' ]);

        foreach (Subscription::with('latestIncome')->withCount('income')->where('status', 'ACTIVE')->lazy() as $subscription) {
            $count_earnings = $subscription->income_count;
            $recent_bonus = $subscription->latestIncome;

            if ($recent_bonus) {
              $hour_difference = now()->diffInHours($recent_bonus->created_at);

              if ($hour_difference > 1) {
                   $to_insert = 200 - $count_earnings;
                   $max = $hour_difference;

                   if ($hour_difference > $to_insert) {
                       $max = $to_insert;
                   }
                        
                   // Some performance gain here possibly
                   Income::insert(collect()->pad($max, [ 
                         'user_id' => $subscription->user_id,
                         'subscription_id' => $subscription->id,
                         'amount' => (100 * 0.002) * 100,
                   ])->all());

                   Log::info('Fix for:'.$subscription->id.' | User:'.$subscription->user_id.' | Total:'.$max);
                }
            }
        }
       // Update anything that got pushed over that threshold
       Subscription::has('income', '>=', 200)
            ->where('status', '!=' 'COMPLETED')
            ->update([ 'status' => 'COMPLETED' ]);
    }
}

Answer 3

You can define a time limit in the __construct function or in your index controller if you want a large time restriction.

    public function __construct()
    {
        set_time_limit(8000000);
    }