[Building Sakai] Cleaning messages tables in database

[Daniel Merino]

Wow. Thanks a lot, Bryan!

About the missing index, I will take a look but what I call "slowness" 
is about 10-12 seconds to load.

I definitely will do this cleaning in our next migration. If it is 
already documented somewhere, I think that these instructions would be 
really useful in the Migration Guide.

Best regards.

El 17/10/14 a las #4, Bryan Holladay escribió:
> 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
>
> On Fri, Oct 17, 2014 at 9:01 AM, Daniel Merino 
> <daniel.merino at unavarra.es <mailto:daniel.merino at unavarra.es>> wrote:
>
>     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.
>     -- 
>     Daniel Merino Echeverría
>     daniel.merino at unavarra.es <mailto:daniel.merino at unavarra.es>
>     Gestor de E-learning - Centro Superior de Innovación Educativa.
>     Tfno: 948-168489 - Universidad Pública de Navarra.
>
>     _______________________________________________
>     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"
>
>

-- 
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.
--
Felices aquellos que pueden reírse de sí mismos, porque ellos tendrán 
una razón para reír toda la vida (Prelado Daniel)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20141020/992ea067/attachment.html