[Building Sakai] SAKAI_EVENT and SAKAI_SESSION table trimming

[Sanghyun Jeon]

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/20140120/7c731f46/attachment.html