[Building Sakai] Question pool slowness

[Karen Tsao]

Hi Will,

Thanks. I just want to add a note about why these four sql are not in MySql
sakai_samigo_post_schema_update.sql script. These indices actually have been
defined in Hibernate hbm files. In MySql, these get created automatically,
but not in Oracle. So for Oracle, we decided to add them manually using
sakai_samigo_post_schema_update.sql.

Karen

On Wed, Sep 9, 2009 at 7:07 AM, Will Humphries <whumphri at vt.edu> 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"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20090909/a3eafc6a/attachment.html