Cleaning older activity log data

There’s a table named OKM_ACTIVITY where openkm saves a log will the actions done by the users on all the nodes. In this table can be easily several milions of rows what can affect the perfomance of the database. Really you should be interested on most actual activity log data - for example last year - and keep the other data outside the database. For it is a good practice to perfom some cleaning process, exporting oldest activity log data and then deleting it from database.

Tip

Taking control of OKM_ACTIVITY log table size you can improve your database perfomance and reduce the total amount of your database backup size and time for doing it.

Doing backups of OKM_ACTIVITY by range and deleting from database you keep your activity log information safe without affecting database performance.

You always can restore the OKM_ACTIVITY logs in other database for doing forensic analytics.

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 as 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 “postfix” service in your server.

Windows

Create a file named clean_okm_activity.bat at 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 for configuring periodically your task.

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 must enable federated tables on 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 set below:

[mysqld]
# To enable federated 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 ( take a look 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 an 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;