[Using Sakai] Problem with messageforum
Daniel Merino
daniel.merino at unavarra.es
Thu Oct 11 03:10:24 PDT 2012
Hi, Bryan and Mike.
Thank you very much for your help. We have run your last queries and it
seems that, as you say, the issue is due to the high number of users in
that site. We are going to try reducing that number, and see if it goes
better. Also, we plan to migrate to 2.9 in a close future.
FYI, another issue that happens in that slow site is that, after a post
has been send, page frozens loading and then usually users click again
the post button, so the posts are continuosly repeated.
This issue has several patches that fix it at
https://jira.sakaiproject.org/browse/MSGCNTR-53 . IMHO, you should add
one of them to current code, as they are quite simple patches and it is
really important to avoid this issue.
Thanks a lot and best regards.
Mike Jennings escribió:
> We had this issue happening at UNC where the explain plan was
> returning things in the wrong order for messageforum. To help fix
> this issue, we changed our MySQL 5.5 settings for
> innodb_stats_sample_pages and innodb_stats_on_metadata to be the
> following values.
>
> +---------------------------+-------+
>
> | Variable_name | Value |
>
> +---------------------------+-------+
>
> | innodb_stats_sample_pages | 32 |
>
> +---------------------------+-------+
>
>
> The Innodb_stats_sample_pages was increased so that when the execution
> path for queries are generated it does more random dives in the
> tables. This makes it more likely that you will get the correct
> execution plan in MySQL.
>
>
> +--------------------------+-------+
>
> | Variable_name | Value |
>
> +--------------------------+-------+
>
> | innodb_stats_on_metadata | OFF |
>
> +--------------------------+-------+
>
> When this variable is enabled (which is the default, as before the
> variable was created), InnoDB updates statistics during metadata
> statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing
> the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are
> similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does
> not update statistics during these operations. Disabling this variable
> can improve access speed for schemas that have a large number of
> tables or indexes. It can also improve the stability of execution
> plans for queries that involve InnoDB tables.
>
> After we did this we did a analyze on the entire database, and the
> explain plan started returning values in the correct order.
>
> This is all supported in MySQL 5.1 but you will need the InnoDB Plugin
> for the innodb_stats_sample_pages to wrok in 5.1.x
>
> ==============================================================================
>
> Mike Jennings
> Teaching and Learning Developer
> University of North Carolina at Chapel Hill
>
> Office: (919) 843-5013
> Cell: (919) 698-3746
> E-mail: mike_jennings at unc.edu
>
> On 10/4/2012 8:56 AM, Bryan Holladay wrote:
>> The indexes selected in the query plan are correct, however the order
>> isn't. I'm assuming the index order isn't correct because of the
>> number of students in the class. There is an untested jira that took
>> another approach to this query
>> https://jira.sakaiproject.org/browse/MSGCNTR-575 which may be better
>> if cases of large classes like this. Essentially, it forces a
>> straight join to ignore the query join by adding "select straight_join
>> count(*)". However, I do not believe this is your issue anymore.
>> More than likely, the issue is coming from the size of students in
>> your class. 1900 is a lot. When a message is deleted, the synoptic
>> counts for each user must be checked and updated. It'd be interesting
>> to see you hook up YourKit to a test instance with the same dataset to
>> see where the true performance issue is coming from. You can try
>> testing this query:
>>
>> select forum.ID as FORUM_ID, topic.ID as TOPIC_ID, forum.DRAFT as
>> isForumDraft, topic.DRAFT as isTopicDraft, topic.MODERATED as
>> isTopicModerated, forum.LOCKED as isForumLocked, topic.LOCKED as
>> isTopicLocked, forum.CREATED_BY as forumCreatedBy, topic.CREATED_BY as
>> topicCreatedBy, forum.AVAILABILITY as forumAvailability,
>> topic.AVAILABILITY as topicAvailability
>> from MFR_AREA_T
>> area, MFR_OPEN_FORUM_T forum, MFR_TOPIC_T topic
>> Where area.ID =
>> forum.surrogateKey and forum.ID =
>> topic.of_surrogateKey
>> and
>> area.CONTEXT_ID = ? and forum.ID = ? and topic.ID = ?
>>
>> If this isn't slow, then the best bet would be YourKit to track down
>> where your issue is coming from. You can also run msgcntr 2.8.x in
>> Sakai 2.7.x with a few patches to Kernel & Reference (mainly jquery
>> latest & EntityTransfererRefMigrator) since Msgcntr 2.8.x has several
>> performance improvements.
>>
>> Thanks,
>> Bryan
>>
>>
>> On Thu, Oct 4, 2012 at 4:58 AM, Daniel Merino
>> <daniel.merino at unavarra.es> wrote:
>>> Hi everybody.
>>>
>>> As you adviced some time ago in this list, we have patched our
>>> Msgcntr 2.7.2
>>> with MSGCNTR-683. Besides, we have optimized all Msgcntr tables and
>>> we have
>>> executed all the indexes you suggested in your mail.
>>>
>>> While in our test database our current used indexes are exactly the
>>> same
>>> that you have in your database, in our production database the
>>> results are
>>> different:
>>>
>>> mysql> explain 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;
>>> +----+-------------+--------------+--------+--------------------------------------+------------------------------+---------+-------------------------------------+--------+-----------------------------------------------------------+
>>>
>>> | 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 | 1 | Using where; Using
>>> index;
>>> Using temporary; Using filesort |
>>> | 1 | SIMPLE | openforumi3_ | ref | PRIMARY,FKC17608478B5E2A2F
>>> | FKC17608478B5E2A2F | 9 | sakaiprod.areaimpl4_.ID
>>> | 1 | Using where; Using index |
>>> | 1 | SIMPLE | unreadstat1_ | index | MFR_UNREAD_STATUS_I2
>>> | MFR_UNREAD_STATUS_I2 | 776 | NULL
>>> | 808209 | Using where; Using index; Using join buffer |
>>> | 1 | SIMPLE | messageimp0_ | eq_ref | PRIMARY,FK80C1A316A2D0BE7B
>>> | PRIMARY | 8 |
>>> sakaiprod.unreadstat1_.MESSAGE_C
>>> | 1 | Using where |
>>> | 1 | SIMPLE | topicimpl2_ | eq_ref | PRIMARY,FK863DC0BE74C7E92B
>>> | PRIMARY | 8 |
>>> sakaiprod.messageimp0_.surrogateKey | 1 | Using where
>>> |
>>> +----+-------------+--------------+--------+--------------------------------------+------------------------------+---------+-------------------------------------+--------+-----------------------------------------------------------+
>>>
>>> 5 rows in set (0.00 sec)
>>>
>>>
>>> We still have a bad response when deleting posts in Forums in a site
>>> with
>>> 19000 users. In our production database, MFR_MESSAGES_T has about
>>> 549000
>>> rows and MFR_TOPIC_T has about 865000. I don't think this is too much.
>>>
>>> I'm not sure at all about this being due to a performance issue.
>>> When I try
>>> to delete a forum's post, browser goes to a non-stop loading state.
>>> However,
>>> if I refresh the tool after that, the post appears like it has been
>>> read.
>>> And then, when I try to read it, it appears as deleted instantly.
>>>
>>> Please, could you give your impressions about our database? Or maybe
>>> some
>>> JIRA performance patch that we could have missed?
>>>
>>> Thanks a lot.
>>> Best regards.
>>>
>>> Bryan Holladay escribió:
>>>>
>>>> Msgcntr 2.7.x is missing a lot of performance improvements that have
>>>> been added to 2.8.x+ and is known to have some issues. I would first
>>>> check your tables and indexes:
>>>>
>>>> Here are some things to try:
>>>>
>>>>
>>>> If using 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 make sure you have the correct indexes (and no additional indexes
>>>> that could result in the wrong indexes being used):
>>>>
>>>>
>>>> CREATE INDEX MFR_UNREAD_STATUS_I2 USING BTREE
>>>> ON MFR_UNREAD_STATUS_T(MESSAGE_C, USER_C, READ_C);
>>>>
>>>> CREATE INDEX MFR_UNREAD_STATUS_I1 USING BTREE
>>>> ON MFR_UNREAD_STATUS_T(TOPIC_C, MESSAGE_C, USER_C, READ_C);
>>>>
>>>> CREATE UNIQUE INDEX PRIMARY USING BTREE
>>>> ON MFR_UNREAD_STATUS_T(ID);
>>>>
>>>> ALTER TABLE MFR_UNREAD_STATUS_T
>>>> ADD CONSTRAINT TOPIC_C
>>>> UNIQUE (TOPIC_C, MESSAGE_C, USER_C);
>>>>
>>>> ALTER TABLE MFR_MESSAGE_T
>>>> ADD CONSTRAINT FK80C1A316A2D0BE7B
>>>> FOREIGN KEY(surrogateKey)
>>>> REFERENCES MFR_TOPIC_T(ID);
>>>>
>>>> ALTER TABLE MFR_TOPIC_T
>>>> ADD CONSTRAINT FK863DC0BE74C7E92B
>>>> FOREIGN KEY(of_surrogateKey)
>>>> REFERENCES MFR_OPEN_FORUM_T(ID);
>>>>
>>>> ALTER TABLE MFR_OPEN_FORUM_T
>>>> ADD CONSTRAINT FKC17608478B5E2A2F
>>>> FOREIGN KEY(surrogateKey)
>>>> REFERENCES MFR_AREA_T(ID);
>>>>
>>>>
>>>> ALTER TABLE MFR_AREA_T
>>>> ADD CONSTRAINT MFR_AREA_CONTEXT_UUID_UNIQUE
>>>> UNIQUE (CONTEXT_ID, TYPE_UUID);
>>>>
>>>>
>>>> Next you want to see how your queries are using the indexes:
>>>>
>>>> Use this query to see what indexes are being used:
>>>>
>>>> explain 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): (notice that there are not indexes returning
>>>> a ton of results)
>>>>
>>>> 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
>>>>
>>>>
>>>>
>>>> The other issues are in the code and would be harder to merge and fix.
>>>> Start with this and if the issue is still bad, then you'll need to
>>>> backport the performance jira's.
>>>>
>>>> -Bryan
>>>>
>>>>
>>>> On Thu, Sep 6, 2012 at 7:54 AM, Neal Caidin
>>>> <nealcaidin at sakaifoundation.org> wrote:
>>>>
>>>>>
>>>>> Hi Anders,
>>>>>
>>>>> I wonder if it could be related to this one -
>>>>> https://jira.sakaiproject.org/browse/MSGCNTR-683 ?
>>>>>
>>>>> Cheers,
>>>>>
>>>>> Neal Caidin
>>>>>
>>>>> Sakai CLE Community Coordinator
>>>>> nealcaidin at sakaifoundation.org
>>>>> Skype: nealkdin
>>>>> AIM: ncaidin at aol.com
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Sep 6, 2012, at 5:31 AM, Anders Nordkvist
>>>>> <anders.nordqvist at his.se>
>>>>> wrote:
>>>>>
>>>>> Hello,
>>>>>
>>>>> We at Skövde University in Sweden have a site that’s distributed
>>>>> to all
>>>>> our
>>>>> new students (The University Library site). The site is automatically
>>>>> added
>>>>> to all students when a teacher creates a site in our course creator
>>>>> program
>>>>> for Sakai. The problem is that this site becomes very slow and if an
>>>>> instructor, or a student deletes a post in the forum he gets an error
>>>>> message (pic1). I get the same error as an admin and I don’t have
>>>>> more
>>>>> than
>>>>> one tab of Sakai open. This error is somewhat mentioned in the
>>>>> somewhat
>>>>> old
>>>>> post https://jira.sakaiproject.org/browse/MSGCNTR-48 (its unresolved
>>>>> there)
>>>>> and also I suspect it somehow can have to do with the amount of
>>>>> students
>>>>> on
>>>>> the site but I don’t know how to solve it. I have looked through
>>>>> all the
>>>>> logs but cant find anything!
>>>>>
>>>>> Tested on both IE v.9 and Firefox 15.0 with the same error.
>>>>>
>>>>> We are using Sakai 2.7.x and msgcntr version 2.7.7.
>>>>>
>>>>>
>>>>> Regards
>>>>> Anders Nordkvist
>>>>> System administrator
>>>>> University Of Skövde
>>>>> Sweden
>>>>>
>>>>>
>>>>> <pic1.PNG>_______________________________________________
>>>>> sakai-user mailing list
>>>>> sakai-user at collab.sakaiproject.org
>>>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-user
>>>>>
>>>>> TO UNSUBSCRIBE: send email to
>>>>> sakai-user-unsubscribe at collab.sakaiproject.org
>>>>> with a subject of "unsubscribe"
>>>>>
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> sakai-user mailing list
>>>>> sakai-user at collab.sakaiproject.org
>>>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-user
>>>>>
>>>>> TO UNSUBSCRIBE: send email to
>>>>> sakai-user-unsubscribe at collab.sakaiproject.org
>>>>> with a subject of "unsubscribe"
>>>>>
>>>>
>>>> _______________________________________________
>>>> sakai-user mailing list
>>>> sakai-user at collab.sakaiproject.org
>>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-user
>>>>
>>>> TO UNSUBSCRIBE: send email to
>>>> sakai-user-unsubscribe at collab.sakaiproject.org with a subject of
>>>> "unsubscribe"
>>>>
>>>>
>>>
>>>
>>> --
>>> Daniel Merino Echeverría
>>> daniel.merino at unavarra.es
>>> Gestor de teleformación - Centro Superior de Innovación Educativa.
>>> Tfno: 948-168489 - Universidad Pública de Navarra.
>>> --
>>> Los políticos son como los libros. Los que están más alto son los más
>>> inútiles. (Anónimo)
>> _______________________________________________
>> sakai-user mailing list
>> sakai-user at collab.sakaiproject.org
>> http://collab.sakaiproject.org/mailman/listinfo/sakai-user
>>
>> TO UNSUBSCRIBE: send email to
>> sakai-user-unsubscribe at collab.sakaiproject.org with a subject of
>> "unsubscribe"
>>
>
--
Daniel Merino Echeverría
daniel.merino at unavarra.es
Gestor de teleformación - Centro Superior de Innovación Educativa.
Tfno: 948-168489 - Universidad Pública de Navarra.
More information about the sakai-user
mailing list