cron Scheduler requests remains in pending , eventually site crashed why?

Question

Our a magento 2.2 site usually goes down for about 3 to 4 minutes after within every 4 or 5 hours, than goes up when cron scheduler finishes it job. In this database...

select count(status) from cron_schedule where status='pending'

...there are just over a thousand status pending records, the requests we are getting from our store server for pricing updates of products.

Following is the error comes on screen when site is down during execution of cron_scheduler .

Reason: Connection Timeout

Reason: HTTP 503 - Service Unavailable

Can anyone please assist us why it's happening and what will be best permanent solution of it, because right now we are using temporary solution by doing so...

 delete from cron_schedule where scheduled_at < date_sub(now(), interval 1 hour);

Thanks

Answer 1

You may create an event in database that executes every 3 hours automatically. i.e.

First of all you have to enable event scheduler open your mangeto database and run this query

SET GLOBAL event_scheduler = ON;

Then

CREATE EVENT `Delete_Old_Entries` ON
SCHEDULE EVERY 3 HOUR 
STARTS '2018-11-20 00:00:00' ON 
COMPLETION PRESERVE ENABLE DO DELETE from cron_schedule where scheduled_at < date_sub(now(), interval 3 hour)

You have to adjust start date time and hour in the query