[Building Sakai] Heavy Samigo query
Kusnetz, Jeremy
JKusnetz at APUS.EDU
Mon Oct 17 06:18:40 PDT 2011
I've created:
https://jira.sakaiproject.org/browse/SAM-1440
From: sakai-dev-bounces at collab.sakaiproject.org
[mailto:sakai-dev-bounces at collab.sakaiproject.org] On Behalf Of Kusnetz,
Jeremy
Sent: Monday, October 17, 2011 9:13 AM
To: Karen Tsao
Cc: sakai-dev
Subject: Re: [Building Sakai] Heavy Samigo query
That would be absolutely fantastic. We didn't survive last night, too
may of these queries piling up by the time we hit peak loads.
Here are two more queries that were really stinging:
select publisheds0_.ASSESSMENTID as col_0_0_ from SAM_PUBLISHEDSECTION_T
publisheds0_, SAM_PUBLISHEDSECTIONMETADATA_T publisheds1_ where
publisheds0_.SECTIONID=publisheds1_.SECTIONID and
publisheds1_.LABEL='AUTHOR_TYPE' and publisheds1_.ENTRY='2' group by
publisheds0_.ASSESSMENTID
select assessment0_.PUBLISHEDASSESSMENTID as col_0_0_,
count(assessment0_.ASSESSMENTGRADINGID) as col_1_0_ from
SAM_ASSESSMENTGRADING_T assessment0_ group by
assessment0_.PUBLISHEDASSESSMENTID
The 2nd one doesn't even have a where statement.
We absolutely need to limit these queries to query by SITE_ID so we are
only pulling exams for just the one site the user is in.
There are probably other queries that aren't limited by SITE_ID, ideally
all queries would be.
I will open a JIRA ASAP. If at all possible we would be able to get
these changes in by next Saturday so we can hopefully survive next
Sunday.
From: ktsao at hungs.org [mailto:ktsao at hungs.org] On Behalf Of Karen Tsao
Sent: Monday, October 17, 2011 2:28 AM
To: Kusnetz, Jeremy
Cc: sakai-dev
Subject: Re: [Building Sakai] Heavy Samigo query
Hi Jeremy,
I think I found where that query got executed. Can you please log a Jira
for this issue? I will work on the fix and might need your help to
verify it once I am done.
Thanks,
Karen
On Fri, Oct 14, 2011 at 5:14 PM, Kusnetz, Jeremy <JKusnetz at apus.edu>
wrote:
FYI, comparing the code in 2.6.3 to 2.8 it appears that the first query
has been fixed in 2.8 (it adds querying by the SITE_ID), but this one
below still doesn't have any kind of where and will return all rows for
SAM_PUBLISHEDFEEDBACK_T, which in our case is currently over 50K and
will return all this data each time a user enters the tool.
From: Kusnetz, Jeremy
Sent: Wednesday, October 12, 2011 9:15 AM
To: Kusnetz, Jeremy; sakai-dev
Subject: RE: [Building Sakai] Heavy Samigo query
Here is another one:
EXPLAIN
select publishedf0_.ASSESSMENTID as col_0_0_,
publishedf0_.FEEDBACKDELIVERY as col_1_0_,
publishedf0_.FEEDBACKAUTHORING as col_2_0_, publishedf0_.EDITCOMPONENTS
as col_3_0_, publishedf0_.SHOWQUESTIONTEXT as col_4_0_,
publishedf0_.SHOWSTUDENTRESPONSE as col_5_0_,
publishedf0_.SHOWCORRECTRESPONSE as col_6_0_,
publishedf0_.SHOWSTUDENTSCORE as col_7_0_,
publishedf0_.SHOWSTUDENTQUESTIONSCORE as col_8_0_,
publishedf0_.SHOWQUESTIONLEVELFEEDBACK as col_9_0_,
publishedf0_.SHOWSELECTIONLEVELFEEDBACK as col_10_0_,
publishedf0_.SHOWGRADERCOMMENTS as col_11_0_,
publishedf0_.SHOWSTATISTICS as col_12_0_ from SAM_PUBLISHEDFEEDBACK_T
publishedf0_
______ Sub-Part 1 _______
Select Type: SIMPLE
Table: publishedf0_
Partitions:
Type: ALL
Poss. Keys:
Index:
Key Length:
Index Ref:
Row Count: 45745
Special:
Press e to explain, f for full query, o for optimized query
I'm worried that these queries are not even doing any kind of where's so
they are just doing full table scans.
Jeremy Kusnetz | Sr. Systems Engineer
American Public University System
American Military University | American Public University
661 S George Street, Charles Town, WV 25414
T 304-885-5333 | M 703-967-5212 | jkusnetz at apus.edu| www.apus.edu
<http://www.apus.edu>
From: sakai-dev-bounces at collab.sakaiproject.org
[mailto:sakai-dev-bounces at collab.sakaiproject.org] On Behalf Of Kusnetz,
Jeremy
Sent: Wednesday, October 12, 2011 9:13 AM
To: sakai-dev
Subject: [Building Sakai] Heavy Samigo query
One of the queries that we see piling up during high load is the
following (this is from innotop). We heavily utilize Samigo, with
thousands of active classrooms, many with a dozen or more assessments.
This is on CLE 2.6.3
EXPLAIN
select publishede0_.ASSESSMENTID as col_0_0_, publishede0_.SCORINGTYPE
as col_1_0_, publisheda1_.SUBMISSIONSALLOWED as col_2_0_ from
SAM_PUBLISHEDEVALUATION_T publishede0_, SAM_PUBLISHEDACCESSCONTROL_T
publisheda1_ where publishede0_.ASSESSMENTID=publisheda1_.ASSESSMENTID
_____________ Sub-Part 1 ______________ __________________ Sub-Part 1
__________________
Select Type: SIMPLE Select Type: SIMPLE
Table: publishede0_ Table: publisheda1_
Partitions: Partitions:
Type: ALL Type: eq_ref
Poss. Keys: PRIMARY,FK94CB245F9482C945 Poss. Keys:
PRIMARY,FK2EDF39E09482C945
Index: Index: PRIMARY
Key Length: Key Length: 8
Index Ref: Index Ref:
sakai_300.publishede0_.ASSESSMENTID
Row Count: 45874 Row Count: 1
Special: Special:
Press e to explain, f for full query, o for optimized query
Jeremy Kusnetz | Sr. Systems Engineer
American Public University System
American Military University | American Public University
661 S George Street, Charles Town, WV 25414
T 304-885-5333 | M 703-967-5212 | jkusnetz at apus.edu| www.apus.edu
<http://www.apus.edu>
This message is private and confidential. If you have received it in
error, please notify the sender and remove it from your system.
This message is private and confidential. If you have received it in
error, please notify the sender and remove it from your system.
_______________________________________________
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"
This message is private and confidential. If you have received it in
error, please notify the sender and remove it from your system.
This message is private and confidential. If you have received it in error, please notify the sender and remove it from your system.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20111017/5e648831/attachment.html
More information about the sakai-dev
mailing list