[Building Sakai] SAKAI_EVENT and SAKAI_SESSION table trimming
Steve Swinsburg
steve.swinsburg at gmail.com
Mon Jan 20 18:21:06 PST 2014
You could probably get away with:
CREATE TABLE sakai_session_keep AS
SELECT * FROM sakai-session WHERE session_start > DATE_SUB(NOW(), INTERVAL
1 MONTH);
DROP sakai_session;
RENAME TABLE sakai_session_keep TO sakai_session;
You will need to recreate any indexes that were present.
Users may have a slight issue in between the DROP and RENAME since they are
DDL and will be immediately committed.
Another way might just be to create a new index on the column you want to
use as the lookup, and see if the delete statement runs faster based on
that. You'll need to run some explain plans to see how it compares.
cheers,
Steve
On Tue, Jan 21, 2014 at 12:44 PM, Sanghyun Jeon <euksa99 at gmail.com> wrote:
> 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/20140121/d8199934/attachment.html
More information about the sakai-dev
mailing list