[Building Sakai] Cleaning Msgcntr tables
Daniel Merino
daniel.merino at unavarra.es
Mon Nov 3 01:18:05 PST 2014
Hi again Bryan,
I am getting really lost... when I run the DESCRIBE query against backup
tables (a copy of the full data, with no rows removed), the index order
is OK and the weird high value of unreadstats does not happen:
describe select SQL_NO_CACHE unreadstat1_.USER_C as col_0_0_, count(*)
as col_1_0_
from BACKUP_MFR_MESSAGE_T messageimp0_
inner join BACKUP_MFR_TOPIC_T topicimpl2_ on
messageimp0_.surrogateKey=topicimpl2_.ID
inner join BACKUP_MFR_OPEN_FORUM_T openforumi3_ on
topicimpl2_.of_surrogateKey=openforumi3_.ID
inner join BACKUP_MFR_AREA_T areaimpl4_ on
openforumi3_.surrogateKey=areaimpl4_.ID, BACKUP_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;
id select_type table type possible_keys
key key_len ref rows Extra
-- ----------- ------------ ---- ------------------------------------
---------------------------- ------- ---------------------------- ----
---------------------------------------------------------
1 SIMPLE areaimpl4_ ref PRIMARY,MFR_AREA_CONTEXT_UUID_UNIQUE
MFR_AREA_CONTEXT_UUID_UNIQUE 767 const 2
Using where; Using index; Using temporary; Using filesort
1 SIMPLE openforumi3_ ref PRIMARY,FKC17608478B5E2A2F
FKC17608478B5E2A2F 9 prueba_jesus.areaimpl4_.ID 1 Using
where; Using index
1 SIMPLE topicimpl2_ ref PRIMARY,FK863DC0BE74C7E92B
FK863DC0BE74C7E92B 9 prueba_jesus.openforumi3_.ID 1 Using
where; Using index
1 SIMPLE messageimp0_ ref PRIMARY,FK80C1A316A2D0BE7B
FK80C1A316A2D0BE7B 9 prueba_jesus.topicimpl2_.ID 1 Using where
1 SIMPLE unreadstat1_ ref MFR_UNREAD_STATUS_I2
MFR_UNREAD_STATUS_I2 8 prueba_jesus.messageimp0_.ID 1 Using
where; Using index
I have ran optimize queries in all cleaned tables and the incorrect
index order and the high rows value are still happening. Any other idea
to fix them? I can send you any extra data you need.
Thanks in advance.
Best regards.
El 29/10/14 a las 15:58, Bryan Holladay escribió:
> 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 <mailto: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 <mailto:daniel.merino at unavarra.es>
> Gestor de E-learning - Centro Superior de Innovación Educativa.
> Tfno: 948-168489 - Universidad Pública de Navarra.
>
>
--
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.
--
Una vez terminada la partida, el rey y el peón vuelven a la misma caja.
(Proverbio italiano)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20141103/c846a265/attachment.html
More information about the sakai-dev
mailing list