[Building Sakai] SAKAI_EVENT and SAKAI_SESSION table trimming

[Sanghyun Jeon]

Hi Steve,

This  looks like what you suggest:

CREATE TABLE SAKAI_SESSION_KEEP AS

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


RENAME TABLE SAKAI_SESSION TO SAKAI_SESSION_OLD;


RENAME TABLE SAKAI_SESSION_KEEP TO SAKAI_SESSION;


DROP SAKAI_SESSION_OLD;



I am wondering why two rename statements are necessary.

I am also wondering whether this procedure will not disturb our current
sakai users' activities as well as the current indexes on SAKAI_SESSION.


I truly appreciate your time and efforts.




On Mon, Jan 20, 2014 at 4:38 PM, Steve Swinsburg
<steve.swinsburg at gmail.com>wrote:

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