[Building Sakai] SAKAI_EVENT and SAKAI_SESSION table trimming

[Steve Swinsburg]

A few strategies:

1. You could batch up the delete and delete a few thousand records at a
time.
2. Within a transaction you could copy the records that are NOT to be
deleted over to a new table (sakai_session_keep) using INSERT INTO SELECT
FROM or a CREATE TABLE AS SELECT pattern, RENAME the original table to
sakai_session_old, rename the new table (sakai_session_keep) back to the
original value (sakai_session), then drop sakai_session_old table.

Going forward once you have deleted the data, a nightly/weekly process
could just use a straight delete.

cheers,
Steve





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

> Thank you very much for your suggestion.
> Since we have too many rows for SAKAI_SESSION and it is a huge table, the
> delete query takes too long and exceeds our timeout.
>
> Could you teach me a way to do it without delete statement step by step?
>
> I also have one more question: when our current storage engine is innodb
> but we never set "innodb_file_per_table=1" in my.cnf
> I am wondering what happens without this setting.
>
> Thank you in advance.
>
>
>
>
> On Mon, Jan 20, 2014 at 4:02 PM, Steve Swinsburg <
> steve.swinsburg at gmail.com> wrote:
>
>> 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/d84131d1/attachment.html