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

Question

Hi we have a site on magento 2.2 version , the 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 . when we dig in matter in database

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

, there are almost more than thousands of pending requests in status fields , 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 execute after 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;

Than

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