Cleaning older activity log data

There’s a table named OKM_ACTIVITY where OpenKM saves a log of the actions performed by the users on all nodes. This table can easily have several milions of rows which can affect the perfomance of the database. You should save only the most used activity log data - for example last year’s - and store the rest of the data outside the database. For this reason it is a good practice to perfom a cleaning process, exporting the oldest activity log data and then deleting it from database.

Tip

Taking control of the OKM_ACTIVITY log table size allows you to improve your database perfomance and reduce the total amount of your database backup size and the time it takes to perform database actions.

Doing backups of OKM_ACTIVITY by a range and deleting the backed up information from the database allows you to keep your activity log information safe without affecting database performance.

You can always restore the OKM_ACTIVITY logs from other databases for forensic analytics.

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

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 “postfix” service in 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 at 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 configuring periodic tasks.

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. Production MySQL server (A) and backup MySQL server (B).

First of all federated tables must be enabled on your production server. Stop MySQL production service service and edit the MySQL configuration file (my.cnf or my.ini depending on your OS). Go to [mysqld] section and add the setting below:

[mysqld]
# To enable the federated table feature
federated

Start your production MySQL service and execute from your preferred MySQL client tool 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_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 (see below where we have changed the table name, the ENGINE=innodb for ENGINE=federated and we have 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_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;