[Building Sakai] Question pool slowness

Fri Sep 11 09:25:23 PDT 2009

That's the sakai.properties file in case that was not obvious.
hibernate.show_sql=true
:-)
-AZ

On Fri, Sep 11, 2009 at 5:22 PM, Adams, David <da1 at vt.edu> wrote:
> Hey George,
> You can set hibernate.show_sql=true in your properties which should print it out (along with a bunch of other stuff; not recommended for production).
>
> -dave
>
>> -----Original Message-----
>> From: sakai-dev-bounces at collab.sakaiproject.org [mailto:sakai-dev-
>> bounces at collab.sakaiproject.org] On Behalf Of George Pipkin
>> Sent: Friday, September 11, 2009 12:12 PM
>> Cc: sakai-dev at collab.sakaiproject.org
>> Subject: Re: [Building Sakai] Question pool slowness
>>
>> 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"
>> >
>> >
>>
>> _______________________________________________
>> 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"
>


-- 
Aaron Zeckoski (azeckoski (at) vt.edu)
Senior Research Engineer - CARET - University of Cambridge
https://twitter.com/azeckoski - http://www.linkedin.com/in/azeckoski
http://aaronz-sakai.blogspot.com/ - http://tinyurl.com/azprofile