[Building Sakai] Cleaning Msgcntr tables
Daniel Merino
daniel.merino at unavarra.es
Tue Nov 4 03:05:20 PST 2014
Well, I have discovered that in fact is the optimize query the guilty of
this issue.
After I created the backup tables and copied the rows to them, the
describe query runs fine on them and the results are the expected.
After executing optimize queries over these backup tables, the describe
query's results have changed to worse.
I am using MySql 5.5.27 and all the tables are InnoDB. I don't know too
much about MySql, if somebody can give me a hint I would be very grateful.
Best regards.
El 03/11/14 a las 14:35, Bryan Holladay escribió:
> 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 <mailto: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 <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 <mailto: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)
>
>
--
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.
--
Malgasté mi tiempo, ahora el tiempo me malgasta a mí. (Shakespeare)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20141104/f1926209/attachment.html
More information about the sakai-dev
mailing list