[Building Sakai] Cleaning Msgcntr tables

[Daniel Merino]

Hi Bryan,

I have created a plan to clean Msgcntr tables in our 2.9.2 database. As 
I want to be sure that nothing is lost, I have set it up to delete any 
non-referenced value that has not been modified after 2013/01/01. 
Removing this date limit should be trivial.

I would be very grateful if you could take a look and tell me if the 
queries seem correct to you.

First at all, I have done a diagram of tables & constraints, also 
drawing with a dotted line the relationships between fields that don't 
have a constraint. I send it as attachment.

My steps:

1) I have created backup tables exporting the schemas with the 
constraints and indexes, importing them after changing the constraints 
names and copying all the rows into them. As I think that everyone knows 
how to do this with a tool like DbVisualizer or PHPMyAdmin, I don't 
write these queries here to avoid noise.


2) I have deleted every non-referenced row from the tables with these 
queries:

     SET FOREIGN_KEY_CHECKS=0;

     //MFR_AREA_T

         SELECT COUNT(*) FROM MFR_AREA_T
             28280
         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

     //MFR_OPEN_FORUM_T

         SELECT COUNT(*) FROM MFR_OPEN_FORUM_T
         12307

         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

     //MFR_PRIVATE_FORUM_T

         SELECT COUNT(*) FROM MFR_PRIVATE_FORUM_T
         490951

         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)

     //MFR_TOPIC_T

         SELECT COUNT(*) FROM MFR_TOPIC_T
         1645754

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

         DELETE FROM MFR_TOPIC_T WHERE OF_SURROGATEKEY IS NULL AND 
PF_SURROGATEKEY NOT IN (SELECT SURROGATEKEY FROM MFR_PRIVATE_FORUM_T) 
AND MODIFIED < '2013-01-01'
         859764 row(s), 163.956 secs

         DELETE FROM MFR_TOPIC_T WHERE PF_SURROGATEKEY IS NULL AND 
OF_SURROGATEKEY NOT IN (SELECT SURROGATEKEY FROM MFR_OPEN_FORUM_T) AND 
MODIFIED < '2013-01-01'
         0 row(s), 1.281 secs

     //MFR_MESSAGE_T

         SELECT COUNT(*) FROM MFR_MESSAGE_T
         967616

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

         DELETE FROM MFR_MESSAGE_T WHERE SURROGATEKEY IS NULL AND 
MODIFIED < '2013-01-01'
         476371 row(s), 67.170 secs

     //MFR_UNREAD_STATUS_T

         SELECT COUNT(*) FROM MFR_UNREAD_STATUS_T
         1194218

         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)
         346863 row(s) affected, exec/fetch time: 133.392/0.000 sec

     //MFR_PVT_MSG_USR_T

         select count(*) from mfr_pvt_msg_usr_t
         4621630

         DELETE FROM MFR_PVT_MSG_USR_T WHERE MESSAGESURROGATEKEY NOT IN 
(SELECT ID FROM MFR_MESSAGE_T) AND CONTEXT_ID NOT IN (SELECT SITE_ID 
FROM SAKAI_SITE)
         2144021 row(s), 1242.050 secs -> 20 minutos y 42 segundos.

     SET FOREIGN_KEY_CHECKS=1;


3) I have optimized the seven tables.

     optimize table MFR_MESSAGE_T

         prueba_jesus.mfr_message_t    optimize    note    Table does 
not support optimize, doing recreate + analyze instead
         prueba_jesus.mfr_message_t    optimize    status    OK

     optimize table MFR_AREA_T

         prueba_jesus.mfr_area_t    optimize    note    Table does not 
support optimize, doing recreate + analyze instead
         prueba_jesus.mfr_area_t    optimize    status    OK

     (...)

4) I have checked the query plan (and I hope that is OK, because I am 
not able to understand it as I am not MySql DBA...)

     select max(a), b from
     (select count(*) a, mat.CONTEXT_ID b from MFR_MESSAGE_T mmt
     left join MFR_TOPIC_T mtt on mtt.ID = mmt.surrogateKey
     left join MFR_OPEN_FORUM_T moft on moft.ID = mtt.of_surrogateKey
     left join MFR_AREA_T mat on mat.ID = moft.surrogateKey
     where mmt.surrogateKey is not null and moft.surrogateKey is not 
null and mtt.of_surrogateKey is not null
     Group By mat.CONTEXT_ID
     Order by a DESC) c

         2035    9013c6ab-09bb-40e3-98f1-40a0d6106818

     describe select SQL_NO_CACHE unreadstat1_.USER_C as col_0_0_, 
count(*) as col_1_0_
     from MFR_MESSAGE_T messageimp0_
     inner join MFR_TOPIC_T topicimpl2_ on 
messageimp0_.surrogateKey=topicimpl2_.ID
     inner join MFR_OPEN_FORUM_T openforumi3_ on 
topicimpl2_.of_surrogateKey=openforumi3_.ID
     inner join MFR_AREA_T areaimpl4_ on 
openforumi3_.surrogateKey=areaimpl4_.ID, MFR_UNREAD_STATUS_T unreadstat1_
     where messageimp0_.ID=unreadstat1_.MESSAGE_C and 
areaimpl4_.CONTEXT_ID='9013c6ab-09bb-40e3-98f1-40a0d6106818'
     and unreadstat1_.READ_C=1 and messageimp0_.DRAFT=0 and 
messageimp0_.DELETED=0
     group by unreadstat1_.USER_C;

         table    id    select_type    type    possible_keys key    
key_len    ref    rows    Extra

         areaimpl4_    1    SIMPLE    ref 
PRIMARY,MFR_AREA_CONTEXT_UUID_UNIQUE MFR_AREA_CONTEXT_UUID_UNIQUE    
767    const    2    Using where; Using index; Using temporary; Using 
filesort
         openforumi3_    1    SIMPLE    ref 
PRIMARY,FKC17608478B5E2A2F    FKC17608478B5E2A2F    9 
prueba_jesus.areaimpl4_.ID    1    Using where; Using index
         unreadstat1_    1    SIMPLE    index MFR_UNREAD_STATUS_I2    
MFR_UNREAD_STATUS_I2    776    (null) 847724    Using where; Using 
index; Using join buffer
         messageimp0_    1    SIMPLE    eq_ref 
PRIMARY,FK80C1A316A2D0BE7B    PRIMARY    8 
prueba_jesus.unreadstat1_.MESSAGE_C    1    Using where
         topicimpl2_    1    SIMPLE    eq_ref 
PRIMARY,FK863DC0BE74C7E92B    PRIMARY    8 
prueba_jesus.messageimp0_.surrogateKey    1    Using where


I hope that this is helpful for anybody else. If you think that these 
instructions are OK, I think that I could write them in some Confluence 
space.

Thanks in advance.
Best regards.
-- 
Daniel Merino Echeverría
daniel.merino at unavarra.es
Gestor de E-learning - Centro Superior de Innovación Educativa.
Tfno: 948-168489 - Universidad Pública de Navarra.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Msgcntr_tables.png
Type: image/png
Size: 303146 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20141029/6c944547/attachment.png