[Building Sakai] Cleaning Msgcntr tables
Bryan Holladay
holladay at longsight.com
Wed Oct 29 07:58:11 PDT 2014
Those queries look good to me (disclaimer: I didn't actually run or test
them). You could even add additional tables if you want, like the
attachments for messages. But like I said before, the tables you have are
the main tables that would effect lookup performance. As for your query
explain plan, it isn't correct. Strangely, it's selecting the correct
indices, but not in the correct order. This is how it should be:
area: MFR_AREA_CONTEXT_UUID_UNIQUE
openforum: FKC17608478B5E2A2F
topic: FK863DC0BE74C7E92B
message: FK80C1A316A2D0BE7B
unread: MFR_UNREAD_STATUS_I2
When I see that situation, it's usually fixed by running the optimize
queries. Did you run that on all 5 of those tables? As you can see in the
explain, you're queries are hitting a bottle neck in unreadstats. The
number 847724 is way too high. The total of all rows read should be no more
than 100 (adding all 5 tables together). This is what you need to fix. You
could have extra indices somewhere that's throwing MySql's query plan off.
-Bryan
On Wed, Oct 29, 2014 at 7:58 AM, Daniel Merino <daniel.merino at unavarra.es>
wrote:
> Hi Bryan,
>
> I have created a plan to clean Msgcntr tables in our 2.9.2 database. As I
> want to be sure that nothing is lost, I have set it up to delete any
> non-referenced value that has not been modified after 2013/01/01. Removing
> this date limit should be trivial.
>
> I would be very grateful if you could take a look and tell me if the
> queries seem correct to you.
>
> First at all, I have done a diagram of tables & constraints, also drawing
> with a dotted line the relationships between fields that don't have a
> constraint. I send it as attachment.
>
> My steps:
>
> 1) I have created backup tables exporting the schemas with the constraints
> and indexes, importing them after changing the constraints names and
> copying all the rows into them. As I think that everyone knows how to do
> this with a tool like DbVisualizer or PHPMyAdmin, I don't write these
> queries here to avoid noise.
>
>
> 2) I have deleted every non-referenced row from the tables with these
> queries:
>
> SET FOREIGN_KEY_CHECKS=0;
>
> //MFR_AREA_T
>
> SELECT COUNT(*) FROM MFR_AREA_T
> 28280
> DELETE FROM MFR_AREA_T WHERE CONTEXT_ID NOT IN (SELECT SITE_ID
> FROM SAKAI_SITE) AND MODIFIED < '2013-01-01'
> 16737 row(s) affected, exec/fetch time: 1.412
>
> //MFR_OPEN_FORUM_T
>
> SELECT COUNT(*) FROM MFR_OPEN_FORUM_T
> 12307
>
> DELETE FROM MFR_OPEN_FORUM_T WHERE SURROGATEKEY NOT IN (SELECT ID
> FROM MFR_AREA_T) AND MODIFIED < '2013-01-01'
> 5845 row(s), 0.385 secs
>
> DELETE FROM MFR_OPEN_FORUM_T WHERE SURROGATEKEY IS NULL AND
> MODIFIED < '2013-01-01'
> 405 row(s), 0.030 secs
>
> //MFR_PRIVATE_FORUM_T
>
> SELECT COUNT(*) FROM MFR_PRIVATE_FORUM_T
> 490951
>
> DELETE FROM MFR_PRIVATE_FORUM_T WHERE SURROGATEKEY NOT IN (SELECT
> ID FROM MFR_AREA_T) AND MODIFIED < '2013-01-01'
> 275232 row(s), 40.948 secs
>
> DELETE FROM MFR_PRIVATE_FORUM_T WHERE SURROGATEKEY IS NULL AND
> MODIFIED < '2013-01-01'
> 0 row(s)
>
> //MFR_TOPIC_T
>
> SELECT COUNT(*) FROM MFR_TOPIC_T
> 1645754
>
> DELETE FROM MFR_TOPIC_T WHERE PF_SURROGATEKEY IS NULL AND
> OF_SURROGATEKEY IS NULL AND MODIFIED < '2013-01-01'
> 678 row(s), 6.148 secs
>
> DELETE FROM MFR_TOPIC_T WHERE OF_SURROGATEKEY IS NULL AND
> PF_SURROGATEKEY NOT IN (SELECT SURROGATEKEY FROM MFR_PRIVATE_FORUM_T) AND
> MODIFIED < '2013-01-01'
> 859764 row(s), 163.956 secs
>
> DELETE FROM MFR_TOPIC_T WHERE PF_SURROGATEKEY IS NULL AND
> OF_SURROGATEKEY NOT IN (SELECT SURROGATEKEY FROM MFR_OPEN_FORUM_T) AND
> MODIFIED < '2013-01-01'
> 0 row(s), 1.281 secs
>
> //MFR_MESSAGE_T
>
> SELECT COUNT(*) FROM MFR_MESSAGE_T
> 967616
>
> DELETE FROM MFR_MESSAGE_T WHERE SURROGATEKEY NOT IN (SELECT ID
> FROM MFR_TOPIC_T) AND MODIFIED < '2013-01-01'
> 2251 row(s), 17.829 secs
>
> DELETE FROM MFR_MESSAGE_T WHERE SURROGATEKEY IS NULL AND MODIFIED
> < '2013-01-01'
> 476371 row(s), 67.170 secs
>
> //MFR_UNREAD_STATUS_T
>
> SELECT COUNT(*) FROM MFR_UNREAD_STATUS_T
> 1194218
>
> DELETE FROM MFR_UNREAD_STATUS_T WHERE TOPIC_C NOT IN (SELECT ID
> FROM MFR_TOPIC_T) AND MESSAGE_C NOT IN (SELECT ID FROM MFR_MESSAGE_T)
> 346863 row(s) affected, exec/fetch time: 133.392/0.000 sec
>
> //MFR_PVT_MSG_USR_T
>
> select count(*) from mfr_pvt_msg_usr_t
> 4621630
>
> DELETE FROM MFR_PVT_MSG_USR_T WHERE MESSAGESURROGATEKEY NOT IN
> (SELECT ID FROM MFR_MESSAGE_T) AND CONTEXT_ID NOT IN (SELECT SITE_ID FROM
> SAKAI_SITE)
> 2144021 row(s), 1242.050 secs -> 20 minutos y 42 segundos.
>
> SET FOREIGN_KEY_CHECKS=1;
>
>
> 3) I have optimized the seven tables.
>
> optimize table MFR_MESSAGE_T
>
> prueba_jesus.mfr_message_t optimize note Table does not
> support optimize, doing recreate + analyze instead
> prueba_jesus.mfr_message_t optimize status OK
>
> optimize table MFR_AREA_T
>
> prueba_jesus.mfr_area_t optimize note Table does not
> support optimize, doing recreate + analyze instead
> prueba_jesus.mfr_area_t optimize status OK
>
> (...)
>
> 4) I have checked the query plan (and I hope that is OK, because I am not
> able to understand it as I am not MySql DBA...)
>
> 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
>
> 2035 9013c6ab-09bb-40e3-98f1-40a0d6106818
>
> 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='9013c6ab-09bb-40e3-98f1-40a0d6106818'
> and unreadstat1_.READ_C=1 and messageimp0_.DRAFT=0 and
> messageimp0_.DELETED=0
> group by unreadstat1_.USER_C;
>
> 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 2 Using where; Using
> index; Using temporary; Using filesort
> openforumi3_ 1 SIMPLE ref PRIMARY,FKC17608478B5E2A2F
> FKC17608478B5E2A2F 9 prueba_jesus.areaimpl4_.ID 1 Using where;
> Using index
> unreadstat1_ 1 SIMPLE index MFR_UNREAD_STATUS_I2
> MFR_UNREAD_STATUS_I2 776 (null) 847724 Using where; Using index;
> Using join buffer
> messageimp0_ 1 SIMPLE eq_ref PRIMARY,FK80C1A316A2D0BE7B
> PRIMARY 8 prueba_jesus.unreadstat1_.MESSAGE_C 1 Using where
> topicimpl2_ 1 SIMPLE eq_ref PRIMARY,FK863DC0BE74C7E92B
> PRIMARY 8 prueba_jesus.messageimp0_.surrogateKey 1 Using where
>
>
> I hope that this is helpful for anybody else. If you think that these
> instructions are OK, I think that I could write them in some Confluence
> space.
>
> 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.
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20141029/45e1e93a/attachment.html
More information about the sakai-dev
mailing list