[Building Sakai] SAKAI_EVENT and SAKAI_SESSION table trimming

[Sanghyun Jeon]

Hello,



We have noticed warnings regarding SAKAI_EVENT and SAKAI_SESSION table as
follows when we migrate the db server on this January.



The SAKAI_EVENT table size (38805722) is approaching the point at which
performance will begin to degrade (20000000), we recommend you archive
older events over to another table, remove older rows, or truncate this
table before it reaches a size of 20000000

2014-01-12 11:20:01.779 [INFO]
org.sakaiproject.event.impl.ClusterEventTracking:244 - The SAKAI_SESSIONS
table size (7919471) is approaching the point at which performance will
begin to degrade (2000000), we recommend you archive older sessions over to
another table, remove older rows, or truncate this table before it reaches
a size of 2000000



When we searched our tomcat log it looks like they have been appeared since
last December. We planned to trim the two tables, but we were not sure how
to trim the table without disturbing the current users’ activities.



Now SAKAI_SESSION has 7783957 rows and the size of SAKAI_SESSION.ibd is
over 4 G, which tells us we need to trim our two tables.



We've just updated from mysql 5.1 to 5.5.34 and we also change our default
storage engine into innodb (default-storage-engine=InnoDB) on Jan 12, 2014.



We backup DB daily at 3 AM, saved for 2 weeks and then discarded. We have
one old DB server as of Jan 12, 2014 because we migrated from old server to
the new server and we plan to keep the old one for a while (not too long).



Our main concern is how to trim two tables without disturbing the current
users’ activates.

Below was the best we could come up with:



RENAME TABLE SAKAI_SESSION TO SAKAI_SESSION_NEW;



CREATE TABLE SAKAI_SESSION_BACKUP AS

  SELECT * FROM SAKAI_SESSION_NEW WHERE SESSION_START >
DATE_SUB(NOW(),INTERVAL 1 MONTH);



TRUNCATE SAKAI_SESSION_NEW;



LOCK TABLE SAKAI_SESSION_NEW WRITE, SAKAI_SESSION_BACKUP WRITE;



INSERT INTO SAKAI_SESSION_NEW SELECT * FROM SAKAI_SESSION_BACKUP;



UNLOCK TABLES;



RENAME TABLE SAKAI_SESSION_NEW TO SAKAI_SESSION;



DROP TABLE SAKAI_SESSION_BACKUP;



I am wondering whether you have any better suggestions?

Thank you for any insights you can share.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20140120/ba297c84/attachment.html