Limpieza de la base de datos

Owned by Former user (Deleted)
Last updated: Dec 10, 2015 by Former user (Deleted)
9 min read

Conforme las instalaciones de Sakai crecen en usuarios y sitios, el volumen de datos manejado en las tablas de la base de datos puede llegar a ser brutal. Esto se ve empeorado por el hecho de que no hay ningún mecanismo automático que "limpie" los datos obsoletos. En instalaciones con tablas muy pobladas, el rendimiento global del sistema puede verse reducido drásticamente.

Las dos tablas de Sakai que más volumen de datos manejan, con gran diferencia, son las tablas SAKAI_EVENT y SAKAI_SESSION. Estas tablas registran la actividad de los usuarios y se utilizan para poblar, tras ser procesadas, las tablas de Estadísticas. Sin embargo, tras ese procesado no vuelven a utilizarse por parte de la plataforma y se van acumulando. Si bien es muy útil guardar estos datos para posibles investigaciones futuras (por ejemplo, quién borró un archivo o si en un examen hubo un acceso no autorizado), es necesario un mecanismo que limpie estas tablas y las mantenga en unos volúmenes aceptables, aunque sea moviendo los datos periódicamente a tablas auxiliares donde puedan ser recuperados en el futuro si se necesitan.

Por otro lado, en Sakai el borrado de un sitio de la tabla SAKAI_SITE no implica que se borren todos los datos de las herramientas asociadas a ese sitio. Algunos de estos datos pueden ocupar muchas filas en las tablas, como por ejemplo los datos de la herramienta Estadísticas (Sitestats), o los datos de la herramienta Foros & Mensajes Privados (Message Center), por poner unos pocos ejemplos.

 

El objetivo de esta página es hacer una recopilación de los scripts de limpieza que utilizamos las organizaciones del S2G para limpiar nuestras tablas, con objeto de que estos scripts estén accesibles y puedan ser utilizados por todo el mundo.

 

DISCLAIMER: El uso de estos scripts NO ha sido probado exhaustivamente y no está oficialmente validado por la comunidad Sakai, ni por los usuarios que los publicamos en esta página. Cada organización debe hacerse responsable del correcto uso de los mismos y debe extremar las precauciones, ya que podrían derivar en incongruencias en la base de datos, pérdidas de datos e inestabilidad del sistema.

1 - Mover eventos y sesiones anteriores a una fecha determinada.

El script utiliza una semana, pero no recomendamos este tiempo por ningún motivo especial. De hecho la organización en la que trabaja el autor de estos scripts mueve los datos anteriores a los dos últimos meses. Cambiar la fecha es tan sencillo como modificar el parámetro interval de la query.

En el caso de estas tablas, si tienen un gran volumen de datos, las consultas pueden tardar muchísimo tiempo en ser ejecutadas, pueden ralentizar el sistema mientras esto sucede y puede que incluso dar timeouts. Estos problemas podrían causar inconsistencias y pérdidas de datos en la base de datos. En producción recomendamos utilizarlas con mucho cuidado y bajo la supervisión de un DBA.

1.1 - MySql

CREATE TABLE OLD_SAKAI_EVENT LIKE SAKAI_EVENT;
CREATE TABLE OLD_SAKAI_SESSION LIKE SAKAI_SESSION;
 
START TRANSACTION;
INSERT INTO OLD_SAKAI_EVENT SELECT * FROM SAKAI_EVENT WHERE EVENT_DATE < CURDATE() - INTERVAL 7 DAY;
DELETE FROM SAKAI_EVENT WHERE EVENT_DATE < CURDATE() - INTERVAL 7 DAY;
COMMIT;
 
START TRANSACTION;
INSERT INTO OLD_SAKAI_SESSION SELECT * FROM SAKAI_SESSION WHERE SESSION_END < CURDATE() - INTERVAL 7 DAY;
DELETE FROM SAKAI_SESSION WHERE SESSION_END < CURDATE() - INTERVAL 7 DAY;
COMMIT;

1.2 - Oracle

TO-DO.

2 - Limpieza de las tablas de Estadísticas (Sitestats)

La herramienta de Estadísticas hace un uso intensivo de la base de datos, de hecho en las propias instrucciones de la herramienta se ofrece como consejo el colocarla en una instancia de la base de datos independiente de la de Sakai.

Tras las tablas de eventos y sesiones, estas tablas son las que más pérdida de rendimiento pueden ocasionar si los datos se acumulan sin control.

En el caso de estas tablas, si tienen un gran volumen de datos, las consultas pueden tardar muchísimo tiempo en ser ejecutadas, pueden ralentizar el sistema mientras esto sucede y puede que incluso dar timeouts. Estos problemas podrían causar inconsistencias y pérdidas de datos en la base de datos. En producción recomendamos utilizarlas con mucho cuidado y bajo la supervisión de un DBA.

2.1 - MySql

CREATE TABLE OLD_SST_EVENTS LIKE SST_EVENTS;
START TRANSACTION;
INSERT INTO OLD_SST_EVENTS SELECT * FROM SST_EVENTS WHERE SITE_ID NOT IN (SELECT DISTINCT SITE_ID FROM SAKAI_SITE);
DELETE FROM SST_EVENTS WHERE SITE_ID NOT IN (SELECT DISTINCT SITE_ID FROM SAKAI_SITE);
COMMIT;


CREATE TABLE OLD_SST_RESOURCES LIKE SST_RESOURCES;
START TRANSACTION;
INSERT INTO OLD_SST_RESOURCES SELECT * FROM SST_RESOURCES WHERE SITE_ID NOT IN (SELECT DISTINCT SITE_ID FROM SAKAI_SITE);
DELETE FROM SST_RESOURCES WHERE SITE_ID NOT IN (SELECT DISTINCT SITE_ID FROM SAKAI_SITE);
COMMIT;


CREATE TABLE OLD_SST_REPORTS LIKE SST_REPORTS;
START TRANSACTION;
INSERT INTO OLD_SST_REPORTS SELECT * FROM SST_REPORTS WHERE SITE_ID NOT IN (SELECT DISTINCT SITE_ID FROM SAKAI_SITE);
DELETE FROM SST_REPORTS WHERE SITE_ID NOT IN (SELECT DISTINCT SITE_ID FROM SAKAI_SITE);
COMMIT;


CREATE TABLE OLD_SST_SITEVISITS LIKE SST_SITEVISITS;
START TRANSACTION;
INSERT INTO OLD_SST_SITEVISITS SELECT * FROM SST_SITEVISITS WHERE SITE_ID NOT IN (SELECT DISTINCT SITE_ID FROM SAKAI_SITE);
DELETE FROM SST_SITEVISITS WHERE SITE_ID NOT IN (SELECT DISTINCT SITE_ID FROM SAKAI_SITE);
COMMIT;


CREATE TABLE OLD_SST_SITEACTIVITY LIKE SST_SITEACTIVITY;
START TRANSACTION;
INSERT INTO OLD_SST_SITEACTIVITY SELECT * FROM SST_SITEACTIVITY WHERE SITE_ID NOT IN (SELECT DISTINCT SITE_ID FROM SAKAI_SITE);
DELETE FROM SST_SITEACTIVITY WHERE SITE_ID NOT IN (SELECT DISTINCT SITE_ID FROM SAKAI_SITE);
COMMIT;

2.2 - Oracle.

TO-DO.

3 - Comprobación de Realms

Los realms sí que son borrados al borrarse el site asociado. Para comprobar si tenemos incongruencias (realms asociados a sites que ya no existen), podemos utilizar la siguiente select, que si todo está correcto debería dar 0 resultados.

3.1 - MySql

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

3.2 - Oracle

select realm_key
from sakai_realm
where instr(realm_id,'/site/',1,1)!=0
and substr(substr(realm_id,instr(realm_id,'/site/')+length('/site/')),1,
instr(substr(realm_id,instr(realm_id,'/site/')+length('/site/')),'/group/',1)-1) not in ('!admin','!error','!gateway','!site', '!urlError', 'citationsAdmin', 'mercury', 'PortfolioAdmin','~admin')
and not exists ( select 'x' from sakai_site where site_id=substr(substr(realm_id,instr(realm_id,'/site/')+length('/site/')),1,
instr(substr(realm_id,instr(realm_id,'/site/')+length('/site/')),'/group/',1)-1))
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.

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.

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.

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.

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.

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.

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.

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.

OPTIMIZE TABLE MFR_PVT_MSG_PVT_USR_T;

OPTIMIZE TABLE MFR_MESSAGE_T;

(...)