[Building Sakai] Cleaning Msgcntr tables
Bryan Holladay
holladay at longsight.com
Mon Nov 3 05:35:25 PST 2014
I don't have any other ideas on how to fix this. The optimize queries
always resolves it for me. Maybe it's a mysql version issue or setup issue?
On Mon, Nov 3, 2014 at 4:18 AM, Daniel Merino <daniel.merino at unavarra.es>
wrote:
> 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>
> 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.
>>
>
>
> --
> 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/3389948e/attachment.html
More information about the sakai-dev
mailing list