[Building Sakai] Question pool slowness

[Adams, David]

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"