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 the nodes. This table can easily contain several millions of rows, which can affect the performance of the database. 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 a cleaning process: export the oldest activity log data and then delete it from the database.

Tip

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

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

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

MySQL

Linux

Create a file named clean_okm_activity.sh at /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 at /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

And add these 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 mail, 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 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 add the following:

[mysqld]
# To enable federated feature
federated

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

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, replaced ENGINE=innodb with 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 one 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;