Page Comparison

...

Code Block

language	sql

select realm_key
from sakai_realm
where locate("/site/",realm_id)!=0 
and substring_index(substring_index(realm_id,'/site/',-1),'/group/',1) not in ("!admin","!error","!gateway","!site", "!urlError", "citationsAdmin", "mercury", "PortfolioAdmin","~admin")
and substring_index(substring_index(realm_id,'/site/',-1),'/group/',1) not in (select site_id
from sakai_site)
order by realm_key asc

4 - Limpieza de Message Center

Las tablas de Message Center, que incluyen las herramientas Foros y Mensajes Privados, no se vacían al ser borrados sus sitios padre. Este es un mecanismo muy útil para poder recuperar un sitio borrado y que tenga restablecidos automáticamente sus Foros y Mensajes previos, pero con el tiempo estas tablas pueden llenarse hasta provocar unos retardos inaceptables en la plataforma.

En la UPNA decidimos borrar todos los datos de Foros y Mensajes Privados cuyo sitio padre hubiera sido borrado y que no hubieran sido modificados en los dos últimos años (después del 1 de Enero del 2013). Aproximadamente borramos el 50-60% de los registros en una plataforma que lleva en activo desde 2009.

A continuación mostramos nuestras consultas para MySql, pero antes una imagen que muestra cómo funcionan las tablas de Message Center en un sitio de pruebas llamado "Limpieza Msgcntr" donde creamos varios foros y mensajes para ver sus relaciones.

Image Added

4.1 - Creación de tablas de Backup con constraints.

Para crear tablas de backup donde guardar los datos que tenemos actualmente, por si hubiera que recuperar algo, creamos copias de las tablas que respeten los índices y constraints presentes en las tablas originales.

En nuestro caso sacamos los scripts de creación de tablas con la herramienta DbVisualizer.

Code Block

language	sql

CREATE TABLE backup_mfr_area_t (ID bigint NOT NULL AUTO_INCREMENT, VERSION int NOT NULL, UUID varchar(36) NOT NULL, CREATED datetime NOT NULL, CREATED_BY varchar(36) NOT NULL, MODIFIED datetime NOT NULL, MODIFIED_BY varchar(36) NOT NULL, CONTEXT_ID varchar(255) NOT NULL, NAME varchar(255) NOT NULL, HIDDEN bit NOT NULL, TYPE_UUID varchar(36) NOT NULL, ENABLED bit NOT NULL, LOCKED bit NOT NULL, MODERATED bit NOT NULL, AUTO_MARK_THREADS_READ bit NOT NULL, SENDEMAILOUT bit NOT NULL, AVAILABILITY_RESTRICTED bit DEFAULT FALSE NOT NULL, AVAILABILITY bit DEFAULT TRUE NOT NULL, OPEN_DATE datetime, CLOSE_DATE datetime, POST_FIRST bit NOT NULL, PRIMARY KEY (ID), CONSTRAINT MFR_AREA_CONTEXT_UUID_UNIQUE UNIQUE (CONTEXT_ID, TYPE_UUID)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE backup_mfr_open_forum_t (ID bigint NOT NULL AUTO_INCREMENT, FORUM_DTYPE varchar(2) NOT NULL, VERSION int NOT NULL, UUID varchar(36) NOT NULL, CREATED datetime NOT NULL, CREATED_BY varchar(36) NOT NULL, MODIFIED datetime NOT NULL, MODIFIED_BY varchar(36) NOT NULL, DEFAULTASSIGNNAME varchar(255), TITLE varchar(255) NOT NULL, SHORT_DESCRIPTION varchar(255), EXTENDED_DESCRIPTION text, TYPE_UUID varchar(36) NOT NULL, SORT_INDEX int NOT NULL, LOCKED bit NOT NULL, DRAFT bit, surrogateKey bigint, MODERATED bit NOT NULL, AUTO_MARK_THREADS_READ bit NOT NULL, AVAILABILITY_RESTRICTED bit DEFAULT false NOT NULL, AVAILABILITY bit DEFAULT true NOT NULL, OPEN_DATE datetime, CLOSE_DATE datetime, POST_FIRST bit NOT NULL, PRIMARY KEY (ID), CONSTRAINT FKC17608478B5E2A2F_2 FOREIGN KEY (surrogateKey) REFERENCES backup_mfr_area_t (ID), INDEX FKC17608478B5E2A2F (surrogateKey)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE backup_mfr_private_forum_t (ID bigint NOT NULL AUTO_INCREMENT, VERSION int NOT NULL, UUID varchar(36) NOT NULL, CREATED datetime NOT NULL, CREATED_BY varchar(36) NOT NULL, MODIFIED datetime NOT NULL, MODIFIED_BY varchar(36) NOT NULL, TITLE varchar(255) NOT NULL, SHORT_DESCRIPTION varchar(255), EXTENDED_DESCRIPTION text, TYPE_UUID varchar(36) NOT NULL, SORT_INDEX int NOT NULL, OWNER varchar(255) NOT NULL, AUTO_FORWARD bit, AUTO_FORWARD_EMAIL varchar(255), PREVIEW_PANE_ENABLED bit, surrogateKey bigint, PRIMARY KEY (ID), CONSTRAINT FKA9EE57548B5E2A2F_2 FOREIGN KEY (surrogateKey) REFERENCES backup_mfr_area_t (ID), CONSTRAINT OWNER UNIQUE (OWNER, surrogateKey), INDEX FKA9EE57548B5E2A2F (surrogateKey)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE backup_mfr_topic_t (ID bigint NOT NULL AUTO_INCREMENT, TOPIC_DTYPE varchar(2) NOT NULL, VERSION int NOT NULL, UUID varchar(36) NOT NULL, CREATED datetime NOT NULL, CREATED_BY varchar(36) NOT NULL, MODIFIED datetime NOT NULL, MODIFIED_BY varchar(36) NOT NULL, DEFAULTASSIGNNAME varchar(255), TITLE varchar(255) NOT NULL, SHORT_DESCRIPTION varchar(255), EXTENDED_DESCRIPTION text, MODERATED bit NOT NULL, POST_FIRST bit NOT NULL, MUTABLE bit NOT NULL, SORT_INDEX int NOT NULL, TYPE_UUID varchar(36) NOT NULL, of_surrogateKey bigint, pf_surrogateKey bigint, USER_ID varchar(255), CONTEXT_ID varchar(255), pt_surrogateKey bigint, LOCKED bit, DRAFT bit, CONFIDENTIAL_RESPONSES bit, MUST_RESPOND_BEFORE_READING bit, HOUR_BEFORE_RESPONSES_VISIBLE int, AUTO_MARK_THREADS_READ bit NOT NULL, AVAILABILITY_RESTRICTED bit DEFAULT false NOT NULL, AVAILABILITY bit DEFAULT true NOT NULL, OPEN_DATE datetime, CLOSE_DATE datetime, PRIMARY KEY (ID), CONSTRAINT FK863DC0BE74C7E92B_2 FOREIGN KEY (of_surrogateKey) REFERENCES backup_mfr_open_forum_t (ID) , CONSTRAINT FK863DC0BE82FAB29_2 FOREIGN KEY (pf_surrogateKey) REFERENCES backup_mfr_private_forum_t (ID) , CONSTRAINT FK863DC0BEFF3B3AE9_2 FOREIGN KEY (pt_surrogateKey) REFERENCES backup_mfr_topic_t (ID), INDEX FK863DC0BE82FAB29 (pf_surrogateKey), INDEX FK863DC0BEFF3B3AE9 (pt_surrogateKey), INDEX FK863DC0BE74C7E92B (of_surrogateKey)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE backup_mfr_message_t (ID bigint NOT NULL AUTO_INCREMENT, MESSAGE_DTYPE varchar(2) NOT NULL, VERSION int NOT NULL, UUID varchar(36) NOT NULL, CREATED datetime NOT NULL, CREATED_BY varchar(36) NOT NULL, MODIFIED datetime NOT NULL, MODIFIED_BY varchar(36) NOT NULL, TITLE varchar(255) NOT NULL, BODY text, AUTHOR varchar(255) NOT NULL, HAS_ATTACHMENTS bit NOT NULL, GRADEASSIGNMENTNAME varchar(255), LABEL varchar(255), IN_REPLY_TO bigint, TYPE_UUID varchar(36) NOT NULL, APPROVED bit, DRAFT bit NOT NULL, DELETED bit NOT NULL, surrogateKey bigint, EXTERNAL_EMAIL bit, EXTERNAL_EMAIL_ADDRESS varchar(255), RECIPIENTS_AS_TEXT text, NUM_READERS int, THREADID bigint, LASTTHREADATE datetime, LASTTHREAPOST bigint, RECIPIENTS_AS_TEXT_BCC text, PRIMARY KEY (ID), CONSTRAINT FK80C1A31650339D56_2 FOREIGN KEY (IN_REPLY_TO) REFERENCES backup_mfr_message_t (ID) , CONSTRAINT FK80C1A316A2D0BE7B_2 FOREIGN KEY (surrogateKey) REFERENCES backup_mfr_topic_t (ID), INDEX FK80C1A31650339D56 (IN_REPLY_TO), INDEX FK80C1A316A2D0BE7B (surrogateKey)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE backup_mfr_unread_status_t (ID bigint NOT NULL AUTO_INCREMENT, VERSION int NOT NULL, TOPIC_C bigint NOT NULL, MESSAGE_C bigint NOT NULL, USER_C varchar(255) NOT NULL, READ_C bit NOT NULL, PRIMARY KEY (ID), CONSTRAINT TOPIC_C UNIQUE (TOPIC_C, MESSAGE_C, USER_C), INDEX MFR_UNREAD_STATUS_I2 (MESSAGE_C, USER_C, READ_C), INDEX MFR_UNREAD_STATUS_I1 (TOPIC_C, MESSAGE_C, USER_C, READ_C)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE backup_mfr_pvt_msg_usr_t (messageSurrogateKey bigint NOT NULL, USER_ID varchar(255) NOT NULL, TYPE_UUID varchar(255) NOT NULL, CONTEXT_ID varchar(255) NOT NULL, READ_STATUS bit NOT NULL, BCC bit DEFAULT false NOT NULL, user_index_col int NOT NULL, PRIMARY KEY (messageSurrogateKey, user_index_col), CONSTRAINT FKC4DE0E1473D286ED_2 FOREIGN KEY (messageSurrogateKey) REFERENCES mfr_message_t (ID), INDEX FKC4DE0E1473D286ED (messageSurrogateKey), INDEX user_type_context_idx (USER_ID(36), TYPE_UUID(36), CONTEXT_ID(36), READ_STATUS)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT BACKUP_MFR_AREA_T SELECT * FROM MFR_AREA_T;
//28280 row(s), 1,254 secs

INSERT BACKUP_MFR_OPEN_FORUM_T SELECT * FROM MFR_OPEN_FORUM_T
//12307 row(s), 0,721 secs

INSERT BACKUP_MFR_PRIVATE_FORUM_T SELECT * FROM MFR_PRIVATE_FORUM_T
//490951 row(s), 40,024 secs

INSERT BACKUP_MFR_TOPIC_T SELECT * FROM MFR_TOPIC_T
//1645754 row(s), 79,974 secs 

INSERT BACKUP_MFR_MESSAGE_T SELECT * FROM MFR_MESSAGE_T
//967616 row(s), 156,934 secs

INSERT BACKUP_MFR_UNREAD_STATUS_T SELECT * FROM MFR_UNREAD_STATUS_T
//1194218 row(s), 203.433 secs

INSERT BACKUP_MFR_PVT_MSG_USR_T SELECT * FROM MFR_PVT_MSG_USR_T
//4621630 row(s), 2082.666 secs

4.2 - Borrado de los datos.

4.2.0 - Desactivamos los constraints.

Code Block

language	sql

SET FOREIGN_KEY_CHECKS=0;

4.2.1 - Borrado de las areas sobrantes.

Borramos todas las areas que no tengan site asociado en SAKAI_SITE y que no hayan sido modificadas en 2013 o 2014.

Code Block

language	sql

DELETE FROM MFR_AREA_T WHERE CONTEXT_ID NOT IN (SELECT SITE_ID FROM SAKAI_SITE) AND MODIFIED < '2013-01-01'
//16737 row(s) affected, exec/fetch time: 1.412

4.2.2 - Borrado de los Foros sobrantes.

A partir de las areas que conservamos, vamos borrando las tablas relativas a Foros en cascada.

Code Block

language	sql

DELETE FROM MFR_OPEN_FORUM_T WHERE SURROGATEKEY NOT IN (SELECT ID FROM MFR_AREA_T) AND MODIFIED < '2013-01-01'
//5845 row(s), 0.385 secs

DELETE FROM MFR_OPEN_FORUM_T WHERE SURROGATEKEY IS NULL AND MODIFIED < '2013-01-01'
//405 row(s), 0.030 secs

DELETE FROM MFR_PRIVATE_FORUM_T WHERE SURROGATEKEY NOT IN (SELECT ID FROM MFR_AREA_T) AND MODIFIED < '2013-01-01'
//275232 row(s), 40.948 secs

DELETE FROM MFR_PRIVATE_FORUM_T WHERE SURROGATEKEY IS NULL AND MODIFIED < '2013-01-01'
//0 row(s)

DELETE FROM MFR_TOPIC_T WHERE OF_SURROGATEKEY IS NULL AND PF_SURROGATEKEY IS NULL AND MODIFIED < '2013-01-01'
//678 row(s), 0.667 secs

DELETE FROM MFR_TOPIC_T WHERE OF_SURROGATEKEY IS NULL AND PF_SURROGATEKEY NOT IN (SELECT ID FROM MFR_PRIVATE_FORUM_T) AND MODIFIED < '2013-01-01'
//826280 row(s), 140.670 secs

DELETE FROM MFR_TOPIC_T WHERE PF_SURROGATEKEY IS NULL AND OF_SURROGATEKEY NOT IN (SELECT ID FROM MFR_OPEN_FORUM_T) AND MODIFIED < '2013-01-01'
//7227 row(s), 1.951 secs

DELETE FROM MFR_MESSAGE_T WHERE SURROGATEKEY NOT IN (SELECT ID FROM MFR_TOPIC_T) AND MODIFIED < '2013-01-01'
//25905 row(s), 20.248 secs

DELETE FROM MFR_UNREAD_STATUS_T WHERE TOPIC_C NOT IN (SELECT ID FROM MFR_TOPIC_T) AND MESSAGE_C NOT IN (SELECT ID FROM MFR_MESSAGE_T)
//707693 row(s) affected, exec/fetch time: 298.525/0.000 sec

4.2.3 - Borrado de los Mensajes Privados sobrantes.

Los sitios borrados en Sakai no están en la tabla SAKAI_SITE pero pueden estar en la tabla MFR_AREA_T si sus foros o mensajes fueron modificados después de 2013.

A partir de los identificadores de sitio de la tabla MFR_AREA_T borramos los mensajes.

Code Block

language	sql

delete from mfr_pvt_msg_usr_t where context_id not in (select context_id from mfr_area_t)
//2321995 row(s), 1433.614 secs

delete from mfr_message_t where surrogatekey is null and id not in (select messagesurrogatekey from mfr_pvt_msg_usr_t) AND MODIFIED < '2013-01-01'
//453276 row(s), 150.506 secs

4.2.4 - Activamos los constraints.

Code Block

language	sql

SET FOREIGN_KEY_CHECKS=1;

4.3 - Optimizamos las tablas.

MySql tiene un comando que optimiza las tablas, pero en nuestro caso tenía efectos secundarios. Se puede lanzar en la base de datos de pruebas para ver si mejoran los tiempos.

Code Block

language	sql

OPTIMIZE TABLE MFR_PVT_MSG_PVT_USR_T;

OPTIMIZE TABLE MFR_MESSAGE_T;

(...)

Versions Compared

Old Version 26

New Version 27

Key

4 - Limpieza de Message Center

4.1 - Creación de tablas de Backup con constraints.

4.2 - Borrado de los datos.

4.2.0 - Desactivamos los constraints.

4.2.1 - Borrado de las areas sobrantes.

4.2.2 - Borrado de los Foros sobrantes.

4.2.3 - Borrado de los Mensajes Privados sobrantes.

4.2.4 - Activamos los constraints.

4.3 - Optimizamos las tablas.