使用cron时MySQL的性能差异

时间:2021-08-22 06:48:16

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

我有一些使用cPanel在共享托管环境中运行的PHP维护脚本。大多数脚本需要每3-4小时运行一次,为了简化执行,我编写了一个调度程序脚本,用于检查需要运行哪些脚本(如果有的话)并在必要时执行它们。我将调度程序脚本设置为在cron中每5分钟运行一次。如果脚本发现当前没有维护任务,则不会执行任何操作。调用调度程序的开销导致执行大约5个短SQL查询(因此每5分钟执行一次)。

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.

一切都工作正常,直到我的主人抱怨MySQL使用率很高,声称我远远超过允许的限制。禁用调度程序cron作业后,我的资源使用率降至0。

  • Before disabling cron job: Number of MySQL procs (average) - 0.97
  • 在禁用cron job之前:MySQL procs的数量(平均值) - 0.97

  • After disabling cron job: Number of MySQL procs (average) - 0.00
  • 禁用cron job后:MySQL procs数(平均值) - 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.

奇怪的是,在删除cron作业后,我仍然每隔几个小时通过浏览器手动激活调度程序脚本。由于脚本仍在运行,我对MySQL进程的平均数量大幅下降感到非常惊讶。在禁用cron作业之前,我开始记录调度程序和维护脚本执行了多少SQL查询。

  • Before disabling cron job: 9899 queries a day (average)
  • 在禁用cron作业之前:每天9899次查询(平均)

  • After disabling cron job: 9552 queries a day (average)
  • 禁用cron作业后:每天9552个查询(平均)

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.

因此,当我手动调用调度程序时,它仍然执行与cron一样多的SQL查询,但不知怎的,我的MySQL使用率仍然基本没有下降。

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?

使用php命令通过cron作业执行PHP脚本与通过浏览器调用它之间是否存在性能相关的差异?我没有在我的脚本中显式关闭数据库连接,因为我的理解是这在执行结束时自动发生。当脚本通过cron运行时,此连接是否可以保持打开状态?使用cron时可能存在哪些实质性性差异的解释?

1 个解决方案

#1


3  

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

通常,两个php.ini文件与PHP一起安装:

  • One for Apache
  • 一个用于Apache

  • One for CLI (Command Line Interface)
  • 一个用于CLI(命令行界面)

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.

通常,cron作业将执行php -f yourfile.php,它使用CLI php.ini文件。同样,当您通过网络和apache调用脚本时,它将使用apache php.ini文件。此外,apache允许在虚拟主机配置或.htaccess文件中使用php_value或php_flag。

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

您很可能在apache和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.

弹出我脑海中的第一件事就是持久的数据库连接。持久连接将使用与数据库服务器的连接池,这些连接将在请求之间保持不变,即保持打开状态,以避免TCP握手。这是性能优化,但可以超越MySQL服务器的连接限制。

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

在我的系统(Ubuntu)上,两个php.ini文件的路径如下:

  • /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.

您可以通过-c标志指定在命令行上运行php时要使用的php.ini文件。

#1


3  

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

通常,两个php.ini文件与PHP一起安装:

  • One for Apache
  • 一个用于Apache

  • One for CLI (Command Line Interface)
  • 一个用于CLI(命令行界面)

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.

通常,cron作业将执行php -f yourfile.php,它使用CLI php.ini文件。同样,当您通过网络和apache调用脚本时,它将使用apache php.ini文件。此外,apache允许在虚拟主机配置或.htaccess文件中使用php_value或php_flag。

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

您很可能在apache和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.

弹出我脑海中的第一件事就是持久的数据库连接。持久连接将使用与数据库服务器的连接池,这些连接将在请求之间保持不变,即保持打开状态,以避免TCP握手。这是性能优化,但可以超越MySQL服务器的连接限制。

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

在我的系统(Ubuntu)上,两个php.ini文件的路径如下:

  • /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.

您可以通过-c标志指定在命令行上运行php时要使用的php.ini文件。