About purging admin_ngrest_log table, database maintenance

I have a very big admin_ngrest_log table and I am wondering how it is save to just truncate it ?
I am doing synchronization often and I need to clean this table…
And a more generic question, is there any recommended database maintenance / optimization for say a more than two years running production system ?

Hi @rochdi

There are commands you might use to cleanup and optimize the system:

Maybe we should introduce an ngrest log file cleanup process indeed, should be done very quickly we just need to defined what should be deleted, i would recommend something like this:

  • if there more then XXX (5’000?) rows, delete those which are older then YYY (1 year?) this ensures log files won’t be deleted if there just a few log entries.

How many log entries does your log table have?

If you don’t need those log informations , yes its save to truncate those data. If there are multiple editors using the admin area you will lose evidence of who did what.

Hi @nadar
Thanks for the info. I will try using the suggested optimizations.
My admin log is showing around 20K entries… So I believe I will just keep one year of history

So we could say:

  • 5000 ROWS
  • 2 Years

As parameters for the ngrest log files cleanup command?

As far as I am concerned, I believe 2 years is a good choice but I suggest to reduce the minimum rows to 3000. This will keeps the logs cleaner.

1 Like

Hi @nadar
Had a quick implementatio for the ngrest_log entries cleanup…

<?php

namespace luya\admin\commands;

use Yii;
use luya\console\Command;

/**
 * LUYA Admin Log command.
 *
 * Cleanup ngrest and cms log data.
 *
 * @author 
 * @since 
 */
class LogController extends Command
{
    
    /**
     * @var integer The minimum number of rows to keep.
     */
    public $rows = 5000;
    
    /**
     * @var integer The minimum age of log entries (in years) to keep.
     */
    public $years = 2;
    
    /**
     * @inheritdoc
     */
    public function options($actionID)
    {
        return ['rows', 'years', 'interactive'];
    }
    
    /**
     * Clean up ngrest logs older than a given threshold.
     */
    public function actionNgrestCleanup()
    {

        // check entries count
        $totalRowsCount = Yii::$app->db->createCommand('SELECT count(*) as count FROM {{%admin_ngrest_log}}')->queryScalar();
        $this->output(sprintf("Total entries found : $totalRowsCount (minimum to keep %s)",  $this->rows));
        
        if ($totalRowsCount < $this->rows) {
            return $this->outputInfo("Log entries do not execeed minium to keep.");
        }
                
        //check entries age
        $referenceTimestamp = strtotime(sprintf("-%s year", $this->years));
        $oldRowsCount = Yii::$app->db->createCommand('SELECT count(*) as count FROM {{%admin_ngrest_log}}WHERE timestamp_create < :timestampLimit', [
            ':timestampLimit' => $referenceTimestamp,
        ])->queryScalar();

        $this->output(sprintf("Total old entries : $oldRowsCount (reference date %s)",  date('d-M-Y H:i:s', $referenceTimestamp)));
        
        if ($oldRowsCount == 0) {
            return $this->outputInfo("Log entries are  not old enough to delete.");
        }        
        
        $this->outputInfo("extra ngrest log entries found");
        
        $confirmed = true;
        if ($this->interactive) {
            $confirmed = $this->confirm("Do you want to delete the extra entries from admin_ngrest_log table?");
        }

        if ($confirmed) {
            $removed = Yii::$app->db->createCommand()->delete('{{%admin_ngrest_log}}', 'timestamp_create < :timestampLimit', [
                ':timestampLimit' => $referenceTimestamp,
            ])->execute();
            
            if ($removed) {
                $this->outputSuccess(sprintf("%s entries removed", $removed));
                return $this->outputSuccess("Log entries cleaned.");                
            }
            return $this->outputInfo("No log entries renoved.");                
        }
        return $this->outputError("Log entries clean-up aborted.");
        
    }
    
}

And the execution gives this :

$ ./luya  admin/log/ngrest-cleanup --years=1 --rows=2000
Total entries found : 3803 (minimum to keep 2000)
Total old entries : 546 (reference date 05-Mar-2020 12:03:25)
extra ngrest log entries found
Do you want to delete the extra entries from admin_ngrest_log table? (yes|no) [no]:yes
546 entries removed
Log entries cleaned.
$
$
$./luya  admin/log/ngrest-cleanup --years=1 --rows=2000
Total entries found : 3257 (minimum to keep 2000)
Total old entries : 0 (reference date 05-Mar-2020 12:03:29)
Log entries are  not old enough to delete.
$
1 Like

Hey @rochdi that looks perfect i would say! Could you please send me a PR with this code? (Pull requests · luyadev/luya-module-admin · GitHub) Ensure maintainers are allowed to modify your code, so i can adjust small things. Thank you very much for the effort!

Pull request created Add new command to clean old log entries, fixes issue #622 by rochdi80tn · Pull Request #623 · luyadev/luya-module-admin · GitHub
I’ve made a more generic version that can be used for both admin_ngrest_log and cms_log…
Please feel free to adjust.

1 Like

Hey @rochdi thank you very much for the PR, i will review them asap.

Hi @nadar, I suggest to add also admin_logger table to the cleanup process. What do you think?

yes, sounds good to me! I am not sure, but are the “max age” and “number of files” should be AND conditions? because i think the logger can have less data, but important informations, therefore data should only be deleted when there to many AND they are to old.

Current implementation is as soon as we reach the number of files threshold (say 5000) script will remove ALL those too old… so it may happen that we clean 4000 entries if those are old…
so, this could be added to the same command but it might be trigged separately with a bigger age threshold

it would be nice to define whether both conditions must be met or just one.

‘admin_logger’ => self::BOTH_CONDITIONS,
‘admin_ngrest_log’ => self::FIST_CONDITION

something like this maybe?