[Building Sakai] More on: Performance issue with group member items in gradebook
John Bush
john.bush at rsmart.com
Fri Mar 8 18:07:28 PST 2013
Unless something has changed the way I remember sakai's hibernate
stuff works is that the cache is only per the hibernate session. That
session is created per request. So I'm not sure what is invoking
these requests, but if they are coming from either different user
requests or background threads that start a new hibernate session I
wouldn't expect them to be cached across threads unless something
higher up the stack is doing some caching.
On Fri, Mar 8, 2013 at 2:15 PM, David Haines <dlhaines at umich.edu> wrote:
> We had a recent CTools production issue with a site with a grade book with
> 10 internal queries, 14 external ones and 300 students. When the instructor
> announced the grades were released, cpu and db usage started climbing
> immediately and within 1/2 hour the CTools servers needed to be restarted
> having maxed out CPU and database connections. Looking at the database
> queries we found 7 queries each with around 3,000,000 requests during this
> period. The queries are on the gradebook tables GB_GRADEBOOK_T,
> GB_GRADABLE_OBJECT_T etc. Each query ran quickly but there were too many.
> The queries were generated via Hibernate.
>
> Noah has addressed some of the algorithm issues so we should be in better
> shape in the future. My question is how could 20,000,000 actual database
> queries be generated in such a short period of time if caching was enabled
> in Hibernate? I suspect that Hibernate caching was not enabled or it was
> for some reason ineffective. It looks to me like it is enabled in the hbm
> files but I know very little about Hibernate. Does anyone know how to
> figure out what caching Hibernate actually ends up doing and why it does or
> does not use the cache?
>
> The queries are below.
>
> The hbm files are in
> ./gradebook-service/hibernate/src/hibernate/org/sakaiproject/tool/gradebook.
>
> Thanks - Dave
>
> SQL_ID SQL_FULLTEXT
> -------------
> --------------------------------------------------------------------------------
> 4fvuamr7c82vz
>
> select category0_.ID as ID64_4_,
> category0_.VERSION as VERSION64_4_,
> category0_.GRADEBOOK_ID as GRADEBOOK3_64_4_,
> category0_.NAME as NAME64_4_,
> category0_.WEIGHT as WEIGHT64_4_,
> category0_.DROP_LOWEST as DROP6_64_4_,
> category0_.REMOVED as REMOVED64_4_,
> category0_.IS_EXTRA_CREDIT as IS8_64_4_,
> category0_.IS_EQUAL_WEIGHT_ASSNS as IS9_64_4_,
> category0_.IS_UNWEIGHTED as IS10_64_4_,
> category0_.CATEGORY_ORDER as CATEGORY11_64_4_,
> category0_.ENFORCE_POINT_WEIGHTING as ENFORCE12_64_4_,
> category0_.DROP_HIGHEST as DROP13_64_4_,
> category0_.KEEP_HIGHEST as KEEP14_64_4_,
> gradebook1_.ID as ID52_0_,
> gradebook1_.VERSION as VERSION52_0_,
> gradebook1_.GRADEBOOK_UID as GRADEBOOK3_52_0_,
> gradebook1_.NAME asNAME52_0_,
> gradebook1_.SELECTED_GRADE_MAPPING_ID as SELECTED5_52_0_,
> gradebook1_.ASSIGNMENTS_DISPLAYED as ASSIGNME6_52_0_,
> gradebook1_.COURSE_GRADE_DISPLAYED asCOURSE7_52_0_,
> gradebook1_.ALL_ASSIGNMENTS_ENTERED as ALL8_52_0_,
> gradebook1_.LOCKED as LOCKED52_0_,
> gradebook1_.GRADE_TYPE as GRADE10_52_0_,
> gradebook1_.CATEGORY_TYPE as CATEGORY11_52_0_,
> gradebook1_.IS_EQUAL_WEIGHT_CATS as IS12_52_0_,
> gradebook1_.IS_SCALED_EXTRA_CREDIT as IS13_52_0_,
> gradebook1_.DO_SHOW_MEAN as DO14_52_0_,
> gradebook1_.DO_SHOW_MEDIAN as DO15_52_0_,
> gradebook1_.DO_SHOW_MODE as DO16_52_0_,
> gradebook1_.DO_SHOW_RANK as DO17_52_0_,
> gradebook1_.DO_SHOW_ITEM_STATS as DO18_52_0_,
> gradebook1_.DO_SHOW_STATISTICS_CHART as DO19_52_0_,
> grademappi2_.ID as ID60_1_,
> grademappi2_.VERSION as VERSION60_1_,
> grademappi2_.GRADEBOOK_ID as GRADEBOOK4_60_1_,
> grademappi2_.GB_GRADING_SCALE_T as GB5_60_1_,
> grademappi2_.OBJECT_TYPE_ID as OBJECT2_60_1_,
> gradebook3_.ID as ID52_2_,
> gradebook3_.VERSION as VERSION52_2_,
> gradebook3_.GRADEBOOK_UID as GRADEBOOK3_52_2_,
> gradebook3_.NAME as NAME52_2_,
> gradebook3_.SELECTED_GRADE_MAPPING_ID as SELECTED5_52_2_,
> gradebook3_.ASSIGNMENTS_DISPLAYED as ASSIGNME6_52_2_,
> gradebook3_.COURSE_GRADE_DISPLAYED as COURSE7_52_2_,
> gradebook3_.ALL_ASSIGNMENTS_ENTERED as ALL8_52_2_,
> gradebook3_.LOCKED as LOCKED52_2_,
> gradebook3_.GRADE_TYPE as GRADE10_52_2_,
> gradebook3_.CATEGORY_TYPE as CATEGORY11_52_2_,
> gradebook3_.IS_EQUAL_WEIGHT_CATS as IS12_52_2_,
> gradebook3_.IS_SCALED_EXTRA_CREDIT as IS13_52_2_,
> gradebook3_.DO_SHOW_MEAN as DO14_52_2_,
> gradebook3_.DO_SHOW_MEDIAN as DO15_52_2_,
> gradebook3_.DO_SHOW_MODE as DO16_52_2_,
> gradebook3_.DO_SHOW_RANK as DO17_52_2_,
> gradebook3_.DO_SHOW_ITEM_STATS as DO18_52_2_,
> gradebook3_.DO_SHOW_STATISTICS_CHART as DO19_52_2_,
> gradingsca4_.ID as ID56_3_,
> gradingsca4_.VERSION as VERSION56_3_,
> gradingsca4_.SCALE_UIDas SCALE4_56_3_,
> gradingsca4_.NAME as NAME56_3_,
> gradingsca4_.UNAVAILABLE asUNAVAILA6_56_3_
> from GB_CATEGORY_T category0_
> inner join GB_GRADEBOOK_Tgradebook1_ on
> category0_.GRADEBOOK_ID=gradebook1_.ID
> left outer join GB_GRADE_MAP_Tgrademappi2_ on
> gradebook1_.SELECTED_GRADE_MAPPING_ID=grademappi2_.ID
> left outer joinGB_GRADEBOOK_T gradebook3_ on
> grademappi2_.GRADEBOOK_ID=gradebook3_.ID
> leftouter join GB_GRADING_SCALE_T gradingsca4_ on
> grademappi2_.GB_GRADING_SCALE_T=gradingsca4_.ID
> where category0_.ID=:1
>
>
> 93ks178rapj5v
>
> select grades0_.GRADING_SCALE_ID as GRADING1_0_,
> grades0_.LETTER_GRADE as LETTER2_0_,
> grades0_.GRADE_IDX as GRADE3_0_
> from GB_GRADING_SCALE_GRADES_T grades0_
> where grades0_.GRADING_SCALE_ID=:1
>
>
> 530g43d181ncq
>
> select grademappi0_.ID as ID60_3_,
> grademappi0_.VERSIONas VERSION60_3_,
> grademappi0_.GRADEBOOK_ID as GRADEBOOK4_60_3_,
> grademappi0_.GB_GRADING_SCALE_T as GB5_60_3_,
> grademappi0_.OBJECT_TYPE_ID as OBJECT2_60_3_,
> gradebook1_.ID as ID52_0_,
> gradebook1_.VERSION as VERSION52_0_,
> gradebook1_.GRADEBOOK_UID as GRADEBOOK3_52_0_,
> gradebook1_.NAME as NAME52_0_,
> gradebook1_.SELECTED_GRADE_MAPPING_ID as SELECTED5_52_0_,
> gradebook1_.ASSIGNMENTS_DISPLAYED asASSIGNME6_52_0_,
> gradebook1_.COURSE_GRADE_DISPLAYED as COURSE7_52_0_,
> gradebook1_.ALL_ASSIGNMENTS_ENTERED as ALL8_52_0_,
> gradebook1_.LOCKED as LOCKED52_0_,
> gradebook1_.GRADE_TYPE as GRADE10_52_0_,
> gradebook1_.CATEGORY_TYPE as CATEGORY11_52_0_,
> gradebook1_.IS_EQUAL_WEIGHT_CATS as IS12_52_0_,
> gradebook1_.IS_SCALED_EXTRA_CREDITas IS13_52_0_,
> gradebook1_.DO_SHOW_MEAN as DO14_52_0_,
> gradebook1_.DO_SHOW_MEDIAN as DO15_52_0_,
> gradebook1_.DO_SHOW_MODE as DO16_52_0_,
> gradebook1_.DO_SHOW_RANK as DO17_52_0_,
> gradebook1_.DO_SHOW_ITEM_STATS as DO18_52_0_,
> gradebook1_.DO_SHOW_STATISTICS_CHART as DO19_52_0_,
> grademappi2_.ID as ID60_1_,
> grademappi2_.VERSION as VERSION60_1_,
> grademappi2_.GRADEBOOK_ID as GRADEBOOK4_60_1_,
> grademappi2_.GB_GRADING_SCALE_T as GB5_60_1_,
> grademappi2_.OBJECT_TYPE_ID as OBJECT2_60_1_,
> gradingsca3_.ID as ID56_2_,
> gradingsca3_.VERSION as VERSION56_2_,
> gradingsca3_.SCALE_UID as SCALE4_56_2_,
> gradingsca3_.NAME as NAME56_2_,
> gradingsca3_.UNAVAILABLE as UNAVAILA6_56_2_
> from GB_GRADE_MAP_T grademappi0_
> inner join GB_GRADEBOOK_Tgradebook1_ on
> grademappi0_.GRADEBOOK_ID=gradebook1_.ID
> left outer joinGB_GRADE_MAP_T grademappi2_ on
> gradebook1_.SELECTED_GRADE_MAPPING_ID=grademappi2_.ID
> left outer join GB_GRADING_SCALE_T gradingsca3_ on
> grademappi2_.GB_GRADING_SCALE_T=gradingsca3_.ID
> where grademappi0_.ID=:1
>
>
> 393bnxvzfuf00
>
> select assignment0_.ID as ID53_,
> assignment0_.VERSION asVERSION53_,
> assignment0_.GRADEBOOK_ID as GRADEBOOK4_53_,
> assignment0_.NAME asNAME53_,
> assignment0_.REMOVED as REMOVED53_,
> assignment0_.SORT_ORDER asSORT7_53_,
> assignment0_.POINTS_POSSIBLE as POINTS8_53_,
> assignment0_.DUE_DATE asDUE9_53_,
> assignment0_.NOT_COUNTED as NOT10_53_,
> assignment0_.EXTERNALLY_MAINTAINED asEXTERNALLY11_53_,
> assignment0_.EXTERNAL_STUDENT_LINK as EXTERNAL12_53_,
> assignment0_.EXTERNAL_INSTRUCTOR_LINK as EXTERNAL13_53_,
> assignment0_.EXTERNAL_ID asEXTERNAL14_53_,
> assignment0_.EXTERNAL_APP_NAME as EXTERNAL15_53_,
> assignment0_.IS_EXTRA_CREDIT as IS16_53_,
> assignment0_.ASSIGNMENT_WEIGHTING as ASSIGNMENT17_53_,
> assignment0_.RELEASED asRELEASED53_,
> assignment0_.CATEGORY_ID as CATEGORY19_53_,
> assignment0_.UNGRADED as UNGRADED53_,
> assignment0_.IS_NULL_ZERO as IS21_53_,
> assignment0_.HIDE_IN_ALL_GRADES_TABLE as HIDE22_53_
> from GB_GRADABLE_OBJECT_Tassignment0_
> where assignment0_.OBJECT_TYPE_ID=:"SYS_B_0" and
> assignment0_.GRADEBOOK_ID=:1 and assignment0_.EXTERNAL_ID=:2
>
>
> cy1qm9k3679u4
>
> select defaultbot0_.GRADING_SCALE_ID as GRADING1_0_,
> defaultbot0_.PERCENT as PERCENT0_,
> defaultbot0_.LETTER_GRADE as LETTER3_0_
> from GB_GRADING_SCALE_PERCENTS_Tdefaultbot0_
> where defaultbot0_.GRADING_SCALE_ID=:1
>
>
> 9at5n139vvmmx
>
> select grademap0_.GRADE_MAP_ID as GRADE1_0_,
> grademap0_.PERCENT as PERCENT0_,
> grademap0_.LETTER_GRADE as LETTER3_0_
> from GB_GRADE_TO_PERCENT_MAPPING_T grademap0_
> where grademap0_.GRADE_MAP_ID=:1
>
>
> 2xgba4qhv8squ
>
> select gradebook0_.ID as ID52_,
> gradebook0_.VERSION asVERSION52_,
> gradebook0_.GRADEBOOK_UID as GRADEBOOK3_52_,
> gradebook0_.NAME asNAME52_,
> gradebook0_.SELECTED_GRADE_MAPPING_ID as SELECTED5_52_,
> gradebook0_.ASSIGNMENTS_DISPLAYED as ASSIGNME6_52_,
> gradebook0_.COURSE_GRADE_DISPLAYED asCOURSE7_52_,
> gradebook0_.ALL_ASSIGNMENTS_ENTERED as ALL8_52_,
> gradebook0_.LOCKED asLOCKED52_,
> gradebook0_.GRADE_TYPE as GRADE10_52_,
> gradebook0_.CATEGORY_TYPE asCATEGORY11_52_,
> gradebook0_.IS_EQUAL_WEIGHT_CATS as IS12_52_,
> gradebook0_.IS_SCALED_EXTRA_CREDIT as IS13_52_,
> gradebook0_.DO_SHOW_MEAN as DO14_52_,
> gradebook0_.DO_SHOW_MEDIAN as DO15_52_,
> gradebook0_.DO_SHOW_MODE as DO16_52_,
> gradebook0_.DO_SHOW_RANK as DO17_52_,
> gradebook0_.DO_SHOW_ITEM_STATS as DO18_52_,
> gradebook0_.DO_SHOW_STATISTICS_CHART as DO19_52_
> from GB_GRADEBOOK_T gradebook0_
> where gradebook0_.GRADEBOOK_UID=:1
>
>
> On Thu, Mar 7, 2013 at 2:15 AM, David Horwitz <david.horwitz at uct.ac.za>
> wrote:
>>
>> Hi David,
>>
>> I have created and umbrella issue for GB related performance issues:
>>
>> https://jira.sakaiproject.org/browse/SAK-23324
>>
>> So far I have linked SAK-23221 and SAK-22664 (A scaling related issue
>> I'm busy working on a patch for)
>>
>> At the SakaiSA conference earlier this week UNISA reported on GB related
>> issues at large scale and I hope to get them involved in some baseline
>> testing.
>>
>> Regards
>>
>> David
>>
>> On Wed, 2013-03-06 at 16:08 -0500, David Haines wrote:
>> > This is an update on some responses at Michigan to the gradebook
>> > performance issues noted in SAK-22978.
>> >
>> >
>> > The changes implemented for
>> > https://jira.sakaiproject.org/browse/SAK-22978 are very good. That
>> > said they explicitly do not address the issue of many people
>> > requesting the same data at once or requesting it multiple times.
>> > E.g. an instructor may send an email to 1,300 students letting them
>> > know that all the grades will be available at 6pm and people may be
>> > reloading the page until they get the grades (or the system goes
>> > down). At Michigan we've run into that problem more than once so we
>> > created a Jira ( https://jira.sakaiproject.org/browse/SAK-23321 ) to
>> > add data caching across users. Caching was discussed in but isn't part
>> > of SAK-22978. Thoughts about this Jira are welcome.
>> >
>> >
>> > Also please note that the SAK-22978 work will not address Mneme where
>> > we have found similar issues.
>> >
>> >
>> > - Dave
>> > _______________________________________________
>> > 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"
>>
>> ________________________________
>> UNIVERSITY OF CAPE TOWN
>>
>> This e-mail is subject to the UCT ICT policies and e-mail disclaimer
>> published on our website at
>> http://www.uct.ac.za/about/policies/emaildisclaimer/ or obtainable from +27
>> 21 650 9111. This e-mail is intended only for the person(s) to whom it is
>> addressed. If the e-mail has reached you in error, please notify the author.
>> If you are not the intended recipient of the e-mail you may not use,
>> disclose, copy, redirect or print the content. If this e-mail is not related
>> to the business of UCT it is sent by the sender in the sender's individual
>> capacity.
>
>
>
> _______________________________________________
> 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"
--
John Bush
602-490-0470
More information about the sakai-dev
mailing list