[Building Sakai] Cleaning messages tables in database

[Bryan Holladay]

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