[Building Sakai] SAKAI_EVENT and SAKAI_SESSION table trimming

[Steve Swinsburg]

Why not just do an INSERT INTO SELECT FROM as of some date, then delete
from the live tables as of the same date? Make the date a week in the past
or ensure that everyone's sessions are ended.

Some pseudo SQL as I don't have the schema here:

INSERT INTO sakai_session_backup (col1,cold3,col3)
SELECT col1, col2, col3 FROM sakai_session
WHERE date < now - 7 days;

DELETE FROM sakai_session WHERE date < now - 7 days;

Repeat for sakai_event table.





On Tue, Jan 21, 2014 at 10:15 AM, Sanghyun Jeon <euksa99 at gmail.com> wrote:

> 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.
>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to
> sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of
> "unsubscribe"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20140121/f8d185e2/attachment.html