[Building Sakai] Cleaning messages tables in database

[Bryan Holladay]

10-12 sec isn't terrible, but not great. Msgcntr tables seems to need the
cleanup command ran on it often. We have our clients set up to run once
weekly. Make sure to do this during a low usage period since it will block
queries on those tables while it's running. I don't believe there is any
documentation with purging this data. We don't do this since the tables
work fine with large data when properly tuned. If you do end up doing this
work, could you document your exact steps?

Thanks,
Bryan

On Mon, Oct 20, 2014 at 3:36 AM, Daniel Merino <daniel.merino at unavarra.es>
wrote:

>  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>
> 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
>> 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
>> 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 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)
>
> _______________________________________________
> 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"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20141020/b509a8f2/attachment.html