[Building Sakai] Cleaning messages tables in database

[Neal Caidin]

This looks like it would make a great Stack Exchange question. I *think* 
we are still making this effort (?)

Snippet from an earlier email from Steve Swinsburg:

"Just a reminder that the trial for the Stack Exchange site(s) is still 
open and we still need dozens more people before we hit our quota. It 
would be great if you could sign up to both the Apereo and Sakai sites here:
http://area51.stackexchange.com/proposals/70749/sakai
http://area51.stackexchange.com/proposals/71959/apereo-projects "

Cheers,
Neal


> Bryan Holladay <mailto:holladay at longsight.com>
> October 17, 2014 at 9:42 AM
> I'm assuming you probably have a missing (or conflicting) index. We 
> have clients that have more rows than your mfr_pvt_msg_usr_t. However, 
> if you want to clean up the MFR tables, this is how they work:
>
> `MFR_AREA_T`.`CONTEXT_ID` = Site Id
>
> `MFR_OPEN_FORUM_T`.`surrogateKey` = `MFR_AREA_T`.`ID`
>
> `MFR_PRIVATE_FORUM_T`.`surrogateKey` = `MFR_AREA_T`.`ID`
>
> `MFR_OPEN_FORUM_T`.`ID` `MFR_TOPIC_T`.`of_surrogateKey`
>
> `MFR_PRIVATE_FORUM_T`.`ID` = `MFR_TOPIC_T`.`pf_surrogateKey`
>
> `MFR_MESSAGE_T`.`surrogateKey` = `MFR_TOPIC_T`.`ID`
>
> Then the rest should be based on the `MFR_MESSAGE_T`.`ID` column. Make 
> sure to clean out all those tables above plus MFR_MESSAGE_T and 
> MFR_UNREAD_STATUS_T. Those tables will help you out the most with 
> performance.
>
> You can also work on, in addition or in lieu of, tuning your tables:
>
> If running MySql, run this:
>
> optimize table MFR_MESSAGE_T
>
> optimize table MFR_AREA_T
>
> optimize table MFR_OPEN_FORUM_T
>
> optimize table MFR_TOPIC_T
>
> optimize table MFR_UNREAD_STATUS_T
>
>
> Next see which indices are being selected when you run this query:
>
>
> Find Largest Forum usage site:
>
> 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
>
>
> Then grab that site id and place it in this query:
>
>
> 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='554a0549-03b1-4c1b-82a9-75d7fc5ac2d5'
> and unreadstat1_.READ_C=1 and messageimp0_.DRAFT=0 and 
> messageimp0_.DELETED=0
> group by unreadstat1_.USER_C;
>
>
>
> a good query plan should look like (taken from a DB with 5+ million 
> mfr_unread_status rows):
>
> 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 1 Using where; Using index; 
> Using temporary; Using filesort
>
> openforumi3_ 1 SIMPLE ref PRIMARY,FKC17608478B5E2A2F 
> FKC17608478B5E2A2F 9 sakai28.areaimpl4_.ID 1 Using where; Using index
>
> topicimpl2_ 1 SIMPLE ref PRIMARY,FK863DC0BE74C7E92B FK863DC0BE74C7E92B 
> 9 sakai28.openforumi3_.ID 31 Using where; Using index
>
> messageimp0_ 1 SIMPLE ref PRIMARY,FK80C1A316A2D0BE7B 
> FK80C1A316A2D0BE7B 9 sakai28.topicimpl2_.ID 35 Using where
>
> unreadstat1_ 1 SIMPLE ref MFR_UNREAD_STATUS_I2 MFR_UNREAD_STATUS_I2 8 
> sakai28.messageimp0_.ID 5 Using where; Using index
>
>
>
> Thanks,
> Bryan
>
>
> _______________________________________________
> 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 <mailto:daniel.merino at unavarra.es>
> October 17, 2014 at 9:01 AM
> Hi everybody.
>
> We are experiencing some slowness in Message Center tool. Taking a 
> look on database, our table mfr_pvt_msg_usr_t has about 4,5 millions 
> of rows.
>
> A simple query shows that a big percent of these messages are related 
> to old sites that don't exist anymore:
>
> /select count(messageSurrogateKey) from mfr_pvt_msg_usr_t where 
> context_id not in (select site_id from sakai_site)//
> /
> I don't know if CONTEXT_ID field in MFR_PVT_MSG_USR_T table can be 
> related to any other entity apart from sites.
>
> We would like to remove these rows and I wonder if somebody has 
> documented in the past how to do this or any possible issue that we 
> could find.
>
> Thanks in advance.
> Best regards.
> _______________________________________________
> 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"

-- 
Neal Caidin
Sakai Community Coordinator
Apereo Foundation
neal.caidin at apereo.org
Skype me! (but let me know in advance for the first interaction) - nealkdin

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20141017/0d1a870b/attachment.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: compose-unknown-contact.jpg
Type: image/jpeg
Size: 770 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20141017/0d1a870b/attachment.jpg