[Building Sakai] Cleaning messages tables in database

Wed Oct 22 05:27:56 PDT 2014

If a Forum or Topic or Message doesn't have a parent (either all surrogates
are null or missing) then just delete them. Msgcntr never deletes anything,
it just orphans the data. So when you "delete" a topic, you are just
removing the topic's surrogate key and leaving it orphaned. This way you
can add it back and all the messages will still exist.

-Bryan

On Wed, Oct 22, 2014 at 8:18 AM, Daniel Merino <daniel.merino at unavarra.es>
wrote:

>  Hi Bryan,
>
> I have started to work in this, as it seems that the slowness is quite
> worse in other daytimes. We plan to do a maintenance stop in a low usage
> period to launch the cleanup and to avoid issues.
>
> I have found rows in MFR_TOPIC_T which have:
>
>      -OF_SURROGATEKEY with a value that does not exist in MFR_OPEN_FORUM_T
> and PF_SURROGATEKEY null
>     -PF_SURROGATEKEY with a value that does not exist in
> MFR_PRIVATE_FORUM_T and OF_SURROGATEKEY null
>     -Both OF_SURROGATEKEY and PF_SURROGATEKEY with null values.
>
> Could you tell me which rows should I remove from the table?
>
> As soon as I have finished the queries I will share them.
>
> Thanks again.
> Best regards.
>
> El 20/10/14 a las #4, Bryan Holladay escribió:
>
> 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"
>>
>
>
> --
> 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.
> --
> La verdad no cambia según nuestra habilidad para digerirla. (Flannery
> O'Connor)
>
> _______________________________________________
> 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/20141022/95716b05/attachment.html 