[Building Sakai] More on: Performance issue with group member items in gradebook
David Haines
dlhaines at umich.edu
Fri Mar 8 13:15:04 PST 2013
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.
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130308/cbe70094/attachment.html
More information about the sakai-dev
mailing list