[Building Sakai] Question pool slowness
Karen Tsao
ktsao at stanford.edu
Fri Sep 11 14:07:38 PDT 2009
Ok. I see. Can you double check if you have SAK-14585? Unless you have
patched it yourself, I believe this is in 2.5.4.
Thanks,
Karen
On Fri, Sep 11, 2009 at 1:29 PM, George Pipkin <gpp8p at virginia.edu> wrote:
> 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"
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20090911/5fe2c39e/attachment.html
More information about the sakai-dev
mailing list