Magento 2.3.6 - shop no response when aggregate cronjobs running

[DarkWolf1981]

Hello,

we have some problems with our store. Since switching our AWS RDS Aurora DB from MySQL 5.6 to 5.7 (needed for 2.4 Upgrade) we have some issues with the shop. The biggest problem is, when the aggregate cronjobs run at night, the shop is not reachable for 5 - 10 minutes. The cronjob takes very long. We have ~600 - 1200 orders per day.

Is there a problem with the cronjob itself (table locks), or something with the new mysql version? This runs without problems for 6 months, with the Aurora 5.6 DB. I read about some problems with deadlocks in MySQL 5.7. Would MariaDB be better for Magento 2.3/2.4?
Has someone an idea how to fix this? Or could that be fixed in Magento 2.4?

Best Regards,

Andreas

[gaurav_harsh1]

Hello @DarkWolf1981 ,

Can you share what are the cronjob commands which are being executed ?

Also it could be mysql server connectivity timeout issue.

You can run those commands manually on your staging or dev environment to check which specifically command makes site unreachable.

If the command is custom, try to optimise it if you found exact command which is breaking your site.

It would be most probably because of either PHP max execution time limit or Mysql execution time limit.

Hope it helps !

[DarkWolf1981]

Hello,

i switched the aggregate cronjobs from 0 to 1 AM, and the store has problems at 1 AM. The problem is, not every night. Sometimes it runs 2, 3 days till it happens again.

The Jobs executed are:
aggregate_sales_report_bestsellers_data
aggregate_sales_report_coupons_data
aggregate_sales_report_invoiced_data
aggregate_sales_report_order_data
aggregate_sales_report_refunded_data
aggregate_sales_report_tax_data

The longest with 5 Minutes is aggregate_sales_report_order_data, bestsellers took 3 minutes. I tried them separately and nothing crashes. Maybe a problem when all running together. Database did autoscaling when running all jobs together, but i found no errors in the database.

PHP via cronjob and MySQL have no execution time limits.

Regards,

Andreas