[Building Sakai] Cleaning messages tables in database
Daniel Merino
daniel.merino at unavarra.es
Wed Oct 22 05:18:26 PDT 2014
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 <mailto: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 <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 <mailto: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
> <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.
--
La verdad no cambia según nuestra habilidad para digerirla. (Flannery
O'Connor)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20141022/c7138109/attachment.html
More information about the sakai-dev
mailing list