MySQL performance differences when using cron

Question

I have a number of PHP maintenance scripts running on a shared hosting environment using cPanel. Most of the scripts need to run every 3-4 hours and to simplify their execution, I wrote a scheduler script that checks which (if any) of these scripts needs to be run and executes them as necessary. I set the scheduler script to run every 5 minutes in cron. If the script finds no maintenance tasks are currently due, it does nothing. The overhead of invoking the scheduler results in about 5 short SQL queries being executed (so these are performed every 5 minutes).

Everything was working fine until my host complained about high MySQL usage, claiming I was far over the allowed limit. After disabling the scheduler cron job, my resource usage went down to 0.

• Before disabling cron job: Number of MySQL procs (average) - 0.97
• After disabling cron job: Number of MySQL procs (average) - 0.00

The strange thing is that after removing the cron job, I still continued to activate the scheduler script manually through a browser every few hours. Since the script was still being run, I was very surprised by how drastically the average number of MySQL processes had fallen. Before disabling the cron job, I began logging how many SQL queries were executed by the scheduler and maintenance scripts.

• Before disabling cron job: 9899 queries a day (average)
• After disabling cron job: 9552 queries a day (average)

So when I called the scheduler manually, it still performed nearly as many SQL queries as it did as a cron, but somehow my MySQL usage still dropped to basically nothing.

Are there any performance-related differences between executing a PHP script via a cron job using the php command than with calling it through a browser? I do not explicitly close the database connection in my script since it was my understanding that this happens automatically at the end of execution. Is it possible this connection remains open when the script is run via cron? What other explanations could there be for the substantial performance differences when using cron?

Transpadane

Bet you weren't running the jobs from your browser every 5 minutes. – Narrate

No I wasn't - but like I said above the overall number of queries executed per day remained about the same. Perhaps there is a significant overhead involved when creating the DB connection every 5 minutes? – Transpadane

Maybe the 347 queries you didn't run are the ones responsible for the majority of the load? – Narrate

If there are no other differences between running the script via cron and running it through a browser request, then you are probably right. It's just that for the most part, the only additional queries getting executed by the scheduler script are simple select statements on a table with under 100 rows so they are not long-running queries. I'm also wondering if the statistics provided by cPanel are just not very accurate. – Transpadane

have you tried an explain on the queries ? and also average over what period ? 5 mins ? 7 hours ? – Psychosurgery

Interesting question. Did your cron run things in the console environment proper, or does it just kick off curl to run the scheduler via the web server? If it does the former, then try the latter - as Tom says, PHP/Apache may be configured to run queries much more efficiently. – Glycogenesis

Answer 1

Normally, two php.ini files are installed alongside PHP:

• One for Apache
• One for CLI (Command Line Interface)

Typically, cron jobs will execute php -f yourfile.php, which uses the CLI php.ini file. Likewise, when you call the script over the network and through apache, it will use the apache php.ini file. Additionally, apache allows using php_value or php_flag in your virtual-host configuration or .htaccess files.

You most probably have a configuration difference between apache and CLI.

The first thing that pops into my mind would be persistent DB connections. Persistent connections will use a pool of connections to your DB server that will persist between requests i.e. be left open, in order to avoid TCP handshakes. This is a performance optimization but can top your MySQL server's connection limit.

On my system (Ubuntu) the paths for the two php.ini files are as follows:

• /etc/php5/apache2/php.ini
• /etc/php5/cli/php.ini

You can specify the php.ini file that you want to use when running php over the command line through the -c flag.

Labrum

I don't have access to the default php.ini files on the shared hosting environment but I was able to create my own and configure the cron job to use that instead using the -c flag as you said. Running the job every 5 minutes still seems to overload my MySQL resource usage but adding mysql.allow_persistent = "0" to my custom php.ini file has definitely made a difference. Thanks! – Transpadane