使用cron时MySQL的性能差异

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?

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.