Cleaning older activity log data

There’s a table named OKM_ACTIVITY where OpenKM saves a log with the actions performed by users on all nodes. This table can easily contain several millions of rows, which can affect database performance. In practice, you should be interested only in the most recent activity log data - for example, the last year - and keep the other data outside the database. Therefore, it is good practice to perform some cleaning processes: export the oldest activity log data and then delete it from the database.

Tip

By controlling the OKM_ACTIVITY log table size, you can improve your database performance and reduce the total size of your database backups and the time required to create them.

By backing up OKM_ACTIVITY by range and deleting it from the database, you keep your activity log information safe without affecting database performance.

You can always restore the OKM_ACTIVITY logs in another database for forensic analysis.

MySQL

Linux

Create a file named clean_okm_activity.sh in the /root/ folder:

#!/bin/sh
## BEGIN CONFIG ##
DATABASE_PASS="*secret*"
BACKUP_DIR="/mnt/backup/okm_activity"
## END CONFIG ##

mysqldump -h localhost -u root -p$DATABASE_PASS -w"ACT_DATE < (NOW() - INTERVAL 365 DAY)" --no-create-info okmdb OKM_ACTIVITY > $BACKUP_DIR/okm_activity_$(date +\%Y.\%m.\%d_\%H.\%M.\%S).sql
mysql -h localhost -u root -p$DATABASE_PASS okmdb < /root/delete.sql

Make clean_okm_activity.sh an executable file:

$chmod +x clean_okm_activity.sh

Create a file named delete.sql in the /root/ folder:

DELETE FROM OKM_ACTIVITY WHERE ACT_DATE < (NOW() - INTERVAL 365 DAY);
ANALYZE TABLE OKM_ACTIVITY;

Configure crontab

To install the cron job, run:

$ sudo mkdir /root/logs
$ sudo crontab -e

Add the following lines according to your personal configuration:

MAILTO=nomail@openkm.com
@daily /root/clean_okm_activity.sh | tee /root/logs/okm_activity.$(date +\%Y.\%m.\%d_\%H.\%M.\%S).log

Note

More information at Crontab quick reference

If you want to be notified by email, you should install the postfix service on your server.

Windows

Create a file named clean_okm_activity.bat in the c:\backup folder:

set SQLFILE_DATE=%DATE:~6,4%.%DATE:~3,2%.%DATE:~0,2%
set SQLFILE_TIME=%TIME:~0,2%.%TIME:~3,2%
set SQLFILE=c:\backup\okm_activity-%SQLFILE_DATE%-%SQLFILE_TIME%.sql
set DATABASE_PASS=*secret*

mysqldump -h localhost -u root -p%DATABASE_PASS% -w"ACT_DATE < (NOW() - INTERVAL 365 DAY)" --no-create-info okmdb OKM_ACTIVITY > %SQLFILE%
mysql -h localhost -u root -p%DATABASE_PASS% okmdb < delete.sql

Create a file named delete.sql in the c:\backup folder:

DELETE FROM OKM_ACTIVITY WHERE ACT_DATE < (NOW() - INTERVAL 365 DAY);
ANALYZE TABLE OKM_ACTIVITY;

Note

You can use the Windows Task Scheduler to schedule your task periodically.

Database replication with federated tables

The procedure for using FEDERATED tables is very simple. Normally, you have two servers running, either both on the same host or on different hosts. (It is possible for a FEDERATED table to use another table that is managed by the same server, although there is little point in doing so.)

Note

You should have two database servers: a production MySQL server (A) and a backup MySQL server (B).

First of all, you must enable federated tables on the production server. Stop the MySQL production service and edit the MySQL configuration file (my.cnf or my.ini depending on your OS). Go to the [mysqld] section and set the following:

[mysqld]
# To enable the federated feature
federated

Start your production MySQL service and, from your preferred MySQL client tool, execute the command:

SHOW CREATE TABLE okm_activity:

You will get something like:

CREATE TABLE `okm_activity` (
  `ACT_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `ACT_ACTION` varchar(127) COLLATE utf8_bin DEFAULT NULL,
  `ACT_DATE` datetime DEFAULT NULL,  `ACT_IP` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `ACT_ITEM` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `ACT_PARAMS` varchar(4000) COLLATE utf8_bin DEFAULT NULL,
  `ACT_PATH` longtext COLLATE utf8_bin,
  `ACT_TENANT` bigint(20) DEFAULT NULL,
  `ACT_USER` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ACT_ID`),
  KEY `IDX_ACTIVITY_TENANT` (`ACT_TENANT`),
  KEY `IDX_ACTIVITY_DATE` (`ACT_DATE`),
  KEY `IDX_ACTIVITY_TNTUSRACT` (`ACT_TENANT`,`ACT_USER`,`ACT_ACTION`),
  KEY `IDX_ACTIVITY_TNTDATACT` (`ACT_TENANT`,`ACT_DATE`,`ACT_ACTION`)
  ) ENGINE=innodb DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Create the table “okm_activity” in your backup MySQL server.

Create the federated table in your production MySQL server (note that we have changed the table name, changed the ENGINE=innodb to ENGINE=federated, and added the connection link):

CREATE TABLE `okm_activity_remote` (
  `ACT_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `ACT_ACTION` varchar(127) COLLATE utf8_bin DEFAULT NULL,
  `ACT_DATE` datetime DEFAULT NULL,  `ACT_IP` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `ACT_ITEM` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `ACT_PARAMS` varchar(4000) COLLATE utf8_bin DEFAULT NULL,
  `ACT_PATH` longtext COLLATE utf8_bin,
  `ACT_TENANT` bigint(20) DEFAULT NULL,
  `ACT_USER` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ACT_ID`),
  KEY `IDX_ACTIVITY_TENANT` (`ACT_TENANT`),
  KEY `IDX_ACTIVITY_DATE` (`ACT_DATE`),
  KEY `IDX_ACTIVITY_TNTUSRACT` (`ACT_TENANT`,`ACT_USER`,`ACT_ACTION`),
  KEY `IDX_ACTIVITY_TNTDATACT` (`ACT_TENANT`,`ACT_DATE`,`ACT_ACTION`)
  ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 COLLATE=utf8_bin
  CONNECTION='mysql://user:poassword@server_b:3306/okmdb/okm_activity';

Finally, you can periodically execute a script like this from your MySQL production server:

select (NOW() - INTERVAL 1 DAY) into @dump_date;
insert into okm_activity_remote select * from okm_activity where act_date < @dump_date;
delete from okm_activity where act_date < @dump_date;
analyze table okm_activity;