Should 2 of the standard cron always be running?

Question

My question comes down to, should multiple magento cron:run -vvv processes always be running and hitting MySql constantly.

I am setting up Magento 2.2.1 thru Google Cloud and I have the 3 standard cron jobs that were pre-setup thru Google's 1 click install of Magento.

*/1 * * * * /opt/bitnami/php/bin/php /opt/bitnami/apps/magento/htdocs/bin/magento cron:run -vvv 2>&1

*/1 * * * * /opt/bitnami/php/bin/php /opt/bitnami/apps/magento/htdocs/update/cron.php 2>&1

*/1 * * * * /opt/bitnami/php/bin/php /opt/bitnami/apps/magento/htdocs/bin/magento setup:cron:run -vvv 2>&1

Looking at top -c there is always 2 php.bin processes running, which are hitting MySql constantly and causing it to use around 50% - 70% CPU all the time. Here is a snapshot of what it normally looks like.

 PID USER      PR  NI    VIRT    RES    SHR  S   %CPU   %MEM 
19327 mysql     20   0 3872884 332876  19172 S  60.8  3.4 332:42.45 /opt/bitnami/mysql/bin/mysqld.bin --defaults-file=/opt/bitnami/mysql/my.cnf --basedir=/opt/bitnami+
26458 bitnami   20   0  679516 476444  64492 S  24.6  4.9   0:24.85 /opt/bitnami/php/bin/php.bin /opt/bitnami/apps/magento/htdocs/bin/magento cron:run -vvv
26415 bitnami   20   0  677532 475672  64588 R  23.6  4.9   1:36.11 /opt/bitnami/php/bin/php.bin /opt/bitnami/apps/magento/htdocs/bin/magento cron:run -vvv

I've also changed the crons to run every 5 minutes, instead of the default every minute but the behavior stays the same.

My latest change was alternating every 7 minutes and 8 minutes with the 2 cron:run jobs starting 3 and 4 minutes apart, and with that only 1 cron job is running at a time with 30% - 40% CPU from MySQL.

My site also has no traffic right now because I haven't launched it yet. Is this behavior normal from Magento since there is nothing going on with the site? I let it sit for 12 hours without doing anything at all and when i look at top the cron is still running and hammering MySQL.

UPDATE: It's now clear the problem is just first cron:run process that is causing problems. I changed the 2nd and 3rd items back to every minute and left the first at 8 minutes and there is only a single running cron:run process at a time. From the comment below it could be an issue with Bitnami Magento installations, but this is my first experience with Magento so I don't know if this is expected behavior (I really hope it's not).

Answer 1

Providing at least a temporary work around to the problem, to avoid hammering your MySQL server. Git issue describing the problem

At least part of the problem comes down to the cron_schedule table. I'd recommend doing a select count(*) from cron_schedule; and if that returns more than a few hundred, then you have the issue. For me, that query returned 208,046 on a server that has been running for only a few weeks.

If you have the problem then run this query to delete everything in that table, except the recent rows delete from cron_schedule where scheduled_at < date_sub(now(), interval 1 hour);

Then run the count query again and it should be waaaay lower. Mine went from 208k to 252.

After running that query I set all 3 standard crons back to the standard once per minute, and like magic they all 3 run almost instantly. Back to normal as far as i can tell.

In the github issue, another user suggests adding that query to crontab to prevent the table from growing again.

0 * * * * <path_to_mysql_bin_dir>/mysql <magento_db_name> -e "delete from cron_schedule where scheduled_at < date_sub(now(), interval 1 hour)"

The last response from the Magento team on the issue was in September saying they cannot reproduce the issue, but a number of users have followed up saying they experience the same thing, including myself. So hopefully they fix this so this hack can be removed.

EDIT: To use that command on the crontab you'll need to pass your credentials to MySQL in some way. See my comment below if you're on a dedicated VM or server and want to put your user/pass on crontab, otherwise you'll need to set up a MySQL credentials file. And you can use which mysql to find the path to your mysql binary directory.