1. Because log messages are stored in the MySQL log table, a massive amount of data has accumulated in the production environment, with the data size of the log table reaching hundreds of GB. Therefore, I decided to implement scheduled deletion of log messages via a console command. In the development environment, the data size of the log table is 14.64 GB, as shown in Figure 1.
2. Edit \console\controllers\LogController.php to get the server’s current time and check if it falls within the range [00:00, 01:00). If it is not within this range, delay execution for 10 * 60 seconds and then exit.
<?php
/**
* Created by PhpStorm.
* User: Qiang Wang
* Date: 2019/10/08
* Time: 13:36
*/
namespace console\controllers;
use Yii;
use console\models\Log;
use yii\console\Controller;
use yii\console\ExitCode;
use yii\helpers\ArrayHelper;
use yii\web\HttpException;
use yii\web\ServerErrorHttpException;
/**
* 日志
*
* @author Qiang Wang <shuijingwanwq@163.com>
* @since 1.0
*/
class LogController extends Controller
{
const SLEEP_TIME = 10 * 60; // 延缓执行 10 * 60 秒
/**
* 日志的删除
*
*/
public function actionDelete()
{
/* 获取服务器当前时间，判断范围([0点，1点))，如果不在范围内，则延缓执行 10 * 60 秒，再退出 */
$time = time();
$zeroTime = strtotime("today"); // 0点
$oneTime = strtotime("today +1 hours"); // 1点
echo $time . " ";
echo date('Y-m-d H:i:s', $time) . " ";
echo $zeroTime . " ";
echo date('Y-m-d H:i:s', $zeroTime) . " ";
echo $oneTime . " ";
echo date('Y-m-d H:i:s', $oneTime) . " ";
exit;
if (!($time >= $zeroTime && $time < $oneTime)) {
// 延缓执行 10 * 60 秒
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
echo 1;
exit;
}
}PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1570518464 2019-10-08 15:29:17 1570464000 2019-10-08 00:00:00 1570467600 2019-10-08 01:00:00
3. Query the log time ($logTime) of 1 record, sorted by log time in ascending order. If no record exists, delay execution for 10 * 60 seconds and then exit.
/* 查询 1 条记录的日志时间($logTime)，基于 日志时间 升序排列，如果不存在，则延缓执行 10 * 60 秒，再退出 */
$logItem = Log::find()->select(['log_time'])->orderBy(['log_time' => SORT_ASC])->limit(1)->one();
if (!isset($logItem)) {
// 延缓执行 10 * 60 秒
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
$logTime = $logItem->log_time;
4. Get the value ($reservedTime: retention time, at 00:00) for 15 days prior to the server’s current time (default value, customizable). If $logTime is greater than or equal to $reservedTime, delay execution for 10 * 60 seconds and then exit.
/* 获取服务器当前时间的前 15 天(默认值，可自定义)的值($reservedTime：保留时间，0点)，如果 $logTime 大于等于 $reservedTime，则延缓执行 10 * 60 秒，再退出 */
$reservedTime = strtotime("today -15 days"); // 保留时间，0点
echo $logTime . " ";
echo date('Y-m-d H:i:s', $logTime) . " ";
echo $reservedTime . " ";
echo date('Y-m-d H:i:s', $reservedTime) . " ";
if ($logTime >= $reservedTime) {
echo 0;
exit;
// 延缓执行 10 * 60 秒
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
echo 1;
exit;
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1567046168.2703 2019-08-29 10:36:08 1569168000 2019-09-23 00:00:00 1
5. Get the value ($deadlineTime: deadline time, at 00:00) for 1 day after the log time ($logTime) of the 1 record. If $deadlineTime is greater than $reservedTime, assign $reservedTime to $deadlineTime (when the value is for 1 day later, this condition is never met; when it is for more than 1 day later, this condition may be met).
/* 获取 1 条记录的日志时间($logTime)的后 1 天的值($deadlineTime：截止时间，0点)，如果 $deadlineTime 大于 $reservedTime，则将 $reservedTime 赋值给 $deadlineTime(后 1 天的值时，此条件永远不满足，大于 1 天时，此条件可能满足) */
$logDate = date('Y-m-d', $logTime);
$deadlineTime = strtotime("$logDate +1 days"); // 截止时间，0点
echo $logDate . " ";
echo date('Y-m-d H:i:s', $deadlineTime) . " ";
if ($deadlineTime > $reservedTime) {
$deadlineTime = $reservedTime;
echo 1;
exit;
}
echo date('Y-m-d H:i:s', $deadlineTime) . " ";
echo 2;
exit;
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
2019-08-29 2019-08-30 00:00:00 2019-08-30 00:00:00 2
6. Execute the delete SQL(), with the condition (log time less than $deadlineTime), meaning a single run of the command line only deletes 1 day’s worth of log messages. Delay execution for 60 seconds and then exit.
Note: When executing the delete SQL(), DELETE FROM `cpa_log` WHERE `log_time` < 1570590733, which deletes over 260,000 records (1 day’s worth of log messages), I found that the execution time lasted over 1 minute, as shown in Figure 2.
<?php
/**
* Created by PhpStorm.
* User: Qiang Wang
* Date: 2019/10/08
* Time: 13:36
*/
namespace console\controllers;
use Yii;
use console\models\Log;
use yii\console\Controller;
use yii\console\ExitCode;
use yii\helpers\ArrayHelper;
use yii\web\HttpException;
use yii\web\ServerErrorHttpException;
/**
* 日志
*
* @author Qiang Wang <shuijingwanwq@163.com>
* @since 1.0
*/
class LogController extends Controller
{
const SLEEP_TIME = 10 * 60; // 延缓执行 10 * 60 秒
/**
* 日志的删除
*
*/
public function actionDelete()
{
/* 获取服务器当前时间，判断范围([0点，1点])，如果不在范围内，则延缓执行 10 * 60 秒，再退出 */
$time = time(); // 当前时间
$zeroTime = strtotime("today"); // 0点
$oneTime = strtotime("today +1 hours"); // 1点
if (($time >= $zeroTime && $time <= $oneTime)) {
// 延缓执行 10 * 60 秒
echo 0;
exit;
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
/* 查询 1 条记录的日志时间($logTime)，基于 日志时间 升序排列，如果不存在，则延缓执行 10 * 60 秒，再退出 */
$logItem = Log::find()->select(['log_time'])->orderBy(['log_time' => SORT_ASC])->limit(1)->one();
if (!isset($logItem)) {
// 延缓执行 10 * 60 秒
echo 1;
exit;
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
$logTime = $logItem->log_time;
/* 获取服务器当前时间的前 15 天(默认值，可自定义)的值($reservedTime：保留时间，0点)，如果 $logTime 大于等于 $reservedTime，则延缓执行 10 * 60 秒，再退出 */
$reservedTime = strtotime("today -15 days"); // 保留时间，0点
echo $logTime . " ";
echo date('Y-m-d H:i:s', $logTime) . " ";
echo $reservedTime . " ";
echo date('Y-m-d H:i:s', $reservedTime) . " ";
if ($logTime >= $reservedTime) {
echo 2;
exit;
// 延缓执行 10 * 60 秒
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
/* 获取 1 条记录的日志时间($logTime)的后 1 天的值($deadlineTime：截止时间，0点)，如果 $deadlineTime 大于 $reservedTime，则将 $reservedTime 赋值给 $deadlineTime(后 1 天的值时，此条件永远不满足，大于 1 天时，此条件可能满足) */
$logDate = date('Y-m-d', $logTime);
$deadlineTime = strtotime("$logDate +1 days"); // 截止时间，0点
echo $logDate . " ";
echo date('Y-m-d H:i:s', $deadlineTime) . " ";
if ($deadlineTime > $reservedTime) {
$deadlineTime = $reservedTime;
}
echo date('Y-m-d H:i:s', $deadlineTime) . " ";
/* 执行删除 SQL()，条件(日志时间 小于 $deadlineTime)，即命令行 1 次运行仅删除 1 天的日志消息，延缓执行 60 秒，再退出 */
Log::deleteAll(['<', 'log_time', $deadlineTime]);
// 延缓执行 60 秒
//sleep(60);
//return ExitCode::OK;
echo 3;
exit;
}
}
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1567046168.2703 2019-08-29 10:36:08 1569168000 2019-09-23 00:00:00 2019-08-29 2019-08-30 00:00:00 2019-08-30 00:00:00 3
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1567094579.432 2019-08-30 00:02:59 1569168000 2019-09-23 00:00:00 2019-08-30 2019-08-31 00:00:00 2019-08-31 00:00:00 3
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1567180800.2962 2019-08-31 00:00:00 1569168000 2019-09-23 00:00:00 2019-08-31 2019-09-01 00:00:00 2019-09-01 00:00:00 3
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1567267282.2226 2019-09-01 00:01:22 1569168000 2019-09-23 00:00:00 2019-09-01 2019-09-02 00:00:00 2019-09-02 00:00:00 3
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1568958887.1824 2019-09-20 13:54:47 1569168000 2019-09-23 00:00:00 2019-09-20 2019-09-21 00:00:00 2019-09-21 00:00:00
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1569832928.1543 2019-09-30 16:42:08 1569168000 2019-09-23 00:00:00 2
PS E:\wwwroot\github-shuijingwan-yii2-app-advanced> ./yii log/delete
1569832928.1543 2019-09-30 16:42:08 1569168000 2019-09-23 00:00:00 2
7. Set the parameter for the scheduled log deletion retention time (the past 15 days), with the unit in days. Edit \console\config\params-local.php.
<?php
return [
'logDeleteReservedTime' => 15, // 日志的定时删除的保留时间(前 15 天)，单位为天
];8. With the final code implementation, the data size of the log table in the development environment is 2.36 GB, which meets expectations, as shown in Figure 3.
<?php
/**
* Created by PhpStorm.
* User: Qiang Wang
* Date: 2019/10/08
* Time: 13:36
*/
namespace console\controllers;
use Yii;
use console\models\Log;
use yii\console\Controller;
use yii\console\ExitCode;
/**
* 日志
*
* @author Qiang Wang <shuijingwanwq@163.com>
* @since 1.0
*/
class LogController extends Controller
{
const SLEEP_TIME = 10 * 60; // 延缓执行 10 * 60 秒
/**
* 日志的删除
*/
public function actionDelete()
{
/* 获取服务器当前时间，判断范围([0点，1点])，如果不在范围内，则延缓执行 10 * 60 秒，再退出 */
$time = time(); // 当前时间
$zeroTime = strtotime("today"); // 0点
$oneTime = strtotime("today +1 hours"); // 1点
if (!($time >= $zeroTime && $time < $oneTime)) {
// 延缓执行 10 * 60 秒
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
/* 查询 1 条记录的日志时间($logTime)，基于 日志时间 升序排列，如果不存在，则延缓执行 10 * 60 秒，再退出 */
$logItem = Log::find()->select(['log_time'])->orderBy(['log_time' => SORT_ASC])->limit(1)->one();
if (!isset($logItem)) {
// 延缓执行 10 * 60 秒
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
$logTime = $logItem->log_time;
/* 获取服务器当前时间的前 15 天(默认值，可自定义)的值($reservedTime：保留时间，0点)，如果 $logTime 大于等于 $reservedTime，则延缓执行 10 * 60 秒，再退出 */
$logDeleteReservedTime = Yii::$app->params['logDeleteReservedTime'];
$reservedTime = strtotime("today -$logDeleteReservedTime days"); // 保留时间，0点
if ($logTime >= $reservedTime) {
// 延缓执行 10 * 60 秒
sleep(static::SLEEP_TIME);
return ExitCode::OK;
}
/* 获取 1 条记录的日志时间($logTime)的后 1 天的值($deadlineTime：截止时间，0点)，如果 $deadlineTime 大于 $reservedTime，则将 $reservedTime 赋值给 $deadlineTime(后 1 天的值时，此条件永远不满足，大于 1 天时，此条件可能满足) */
$logDate = date('Y-m-d', $logTime);
$deadlineTime = strtotime("$logDate +1 days"); // 截止时间，0点
if ($deadlineTime > $reservedTime) {
$deadlineTime = $reservedTime;
}
/* 执行删除 SQL()，条件(日志时间 小于 $deadlineTime)，即命令行 1 次运行仅删除 1 天的日志消息，延缓执行 60 秒，再退出 */
Log::deleteAll(['<', 'log_time', $deadlineTime]);
// 延缓执行 60 秒
sleep(60);
return ExitCode::OK;
}
}