[Building Sakai] Question pool slowness
George Pipkin
gpp8p at Virginia.EDU
Fri Sep 11 13:29:19 PDT 2009
No - we don't run Oracle at all. It's taking 0.04 seconds to run the
query from
command line mysql. It's taking 35-55 seconds to show the same information
in sakai's tests and quizzes tool.
- George
Karen Tsao wrote:
> 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
> <mailto: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
> <mailto: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>
> [mailto:sakai-dev- <mailto:sakai-dev->
> >> bounces at collab.sakaiproject.org
> <mailto: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
> <mailto: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
> <mailto: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
> <mailto:unsubscribe at collab.sakaiproject.org> with a subject of
> "unsubscribe"
> >> >>
> >> >>
> >> >>
> >> > _______________________________________________
> >> > sakai-dev mailing list
> >> > sakai-dev at collab.sakaiproject.org
> <mailto: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
> <mailto:unsubscribe at collab.sakaiproject.org> with a subject of
> "unsubscribe"
> >> >
> >> >
> >>
> >> _______________________________________________
> >> sakai-dev mailing list
> >> sakai-dev at collab.sakaiproject.org
> <mailto: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
> <mailto:unsubscribe at collab.sakaiproject.org> with a subject of
> "unsubscribe"
> > _______________________________________________
> > sakai-dev mailing list
> > sakai-dev at collab.sakaiproject.org
> <mailto: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
> <mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a
> subject of "unsubscribe"
> >
>
>
>
> --
> Aaron Zeckoski (azeckoski (at) vt.edu <http://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
> <mailto: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
> <mailto: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"
More information about the sakai-dev
mailing list