[Building Sakai] SAKAI_EVENT and SAKAI_SESSION table trimming
Daniel Merino
daniel.merino at unavarra.es
Thu Jan 23 01:53:58 PST 2014
Hi everybody.
FYI after several performance issues we have made a cleaning of our
database and I have documented our SQL queries in Confluence.
https://confluence.sakaiproject.org/display/SPANISH/Limpieza+de+la+base+de+datos
The page is in spanish, but the SQL queries are pretty clear.
Please, if you have any other query to clean up database or if you see
any error in our queries, don't hesitate to tell to me. I will update
that document with them.
Best regards.
PD: You can see in the page several warnings in spanish. Just advising
to use it with precaution and some DBA supervising it.
El 21/01/14 03:21, Steve Swinsburg escribió:
> 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
> <mailto: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 <mailto: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 <mailto: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
> <mailto: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 <mailto: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
> <mailto: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
> <mailto:sakai-dev-unsubscribe at collab.sakaiproject.org>
> with a subject of "unsubscribe"
>
>
>
>
>
>
>
>
> _______________________________________________
> 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"
--
Daniel Merino Echeverría
daniel.merino at unavarra.es
Gestor de teleformación - Centro Superior de Innovación Educativa.
Tfno: 948-168489 - Universidad Pública de Navarra.
--
Vivimos en un mundo donde nos escondemos para hacer el amor, mientras la
violencia se practica a plena luz del día. (John Lennon)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20140123/909e8045/attachment.html
More information about the sakai-dev
mailing list