[Building Sakai] Question pool slowness

Fri Sep 11 09:50:35 PDT 2009

Hi George,

So you have the same data in both Oracle and MySql, but Oracle takes 32 to
55 seconds and MySql takes only 0.04 seconds? You said you have the same
indexes in both, right? Do you have the up to date Oracle db statistics?

Thanks,
Karen

On Fri, Sep 11, 2009 at 9:25 AM, Aaron Zeckoski <aaronz at vt.edu> wrote:

> 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
> _______________________________________________
> 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/20090911/4a662949/attachment.html 