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).
Best Answer
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.
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.