[Building Sakai] Question pool slowness

[George Pipkin]

I'm getting evidence that the slowdown here is not happening in mysql and is
not due to a database index issue. I looked at the database directly using
mysql, and there are three tables involved:

SAM_ITEMTEXT_T,SAM_QUESTIONPOOLITEM_T,SAM_QUESTIONPOOL_T

(I verified this on my laptop by adding a question pools and seeing what 
changed
in the database).

Now on prod, I ran this query which pulls out the 192 questions it 
currently takes
anywhere from 32 to 55 seconds to get at using collab  (sakai):

select SAM_ITEMTEXT_T.TEXT from 
SAM_ITEMTEXT_T,SAM_QUESTIONPOOLITEM_T,SAM_QUESTIONPOOL_T where 
SAM_ITEMTEXT_T.ITEMID=SAM_QUESTIONPOOLITEM_T.ITEMID and 
SAM_QUESTIONPOOLITEM_T.QUESTIONPOOLID=SAM_QUESTIONPOOL_T.QUESTIONPOOLID 
and SAM_QUESTIONPOOL_T.QUESTIONPOOLID = 455;

Mysql returned the rows in 0.04 seconds!

So either Hibernate is feeding Mysql a twisted query, or the code is 
taking it's time
to build the necessary objects and pass it along to the presentation.

Could anyone help be figure out:

    1) Where I should look to see how Hibernate is creating the quyeries
    2) What's happening with this data once it is pulled from the database




                            - George Pipkin
                               U.Va.






Will Humphries wrote:
> Hi George,
>
> It sounds like your users are having trouble viewing pools. This is a 
> little tangential but maybe it will be helpful anyway - maybe once users 
> start publishing quizzes which use those pools. We had performance 
> issues with users taking and checking grades on quizzes which pulled 
> from several large pools this summer. One particular quiz pulled 10 
> random questions each from 9 pools which ranged from a few dozen to a 
> few hundred questions. We're running 2.5.4 and have the code changes 
> from SAK-10053, SAK-14585, and SAK-10079 in our copy of samigo already.
>
> We worked with our db group to identify a few indexes which they thought 
> would improve performance. It turned out 4 of the 5 indices they 
> recommended were in 
> /sam/samigo-pack/src/sql/oracle/sakai_samigo_post_schema_update.sql . We 
> already had all but 4 of the indices in this file in our schema. Adding 
> the indices we didn't already have dramatically improved performance. We 
> didn't have, and ended up including, the following:
>
> create index SAM_PUBITEMTEXT_ITEM_I on SAM_PUBLISHEDITEMTEXT_T (ITEMID) 
> compute statistics;
> create index SAM_PUBANSWER_ITEMTEXT_I on SAM_PUBLISHEDANSWER_T 
> (ITEMTEXTID) compute statistics;
> create index SAM_PUBANSWERFB_ANSWER_I on SAM_PUBLISHEDANSWERFEEDBACK_T 
> (ANSWERID) compute statistics;
> create index SAM_PUBLISHEDASSESSMENT_I on SAM_ASSESSMENTGRADING_T 
> (PUBLISHEDASSESSMENTID) compute statistics;
>
> At the recommendation of our db group we also added this:
>
> create index VT_SAM_ITEMGRADING_AG_PUBITEM
>     on SAM_ITEMGRADING_T ( ASSESSMENTGRADINGID, PUBLISHEDITEMID )
>        compute statistics;
>
> I'm not sure if this will be as useful in MySQL as it was in Oracle. I 
> noticed 
> /sam/samigo-pack/src/sql/mysql/sakai_samigo_post_schema_update.sql 
> doesn't include most of these indexes...
>
> -Will
>
> George Pipkin wrote:
>   
>> We are receiving complaints from our users about how slow it
>> is to load question pools. In one case, a question pool has 192 
>> questions in it,
>> and takes 50 – 60 seconds to display after clicking on it in the question
>> pools page. Is there any way to improve the performance of this function?
>> Is there a practical limit on the size of question pools ?
>>
>>
>> - George Pipkin
>> U.Va.
>> _______________________________________________
>> sakai-dev mailing list
>> sakai-dev at collab.sakaiproject.org
>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>
>> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
>>
>>   
>>     
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
>
>