[Building Sakai] Heavy Samigo query

[Earle Nietzel]

Jeremy try running analyze on that table when this happens.

Analyze will throw out the current cached query plans which I found MySQL
sometimes makes bad query plan decisions.

Earle

On Wed, Oct 12, 2011 at 11:39 AM, Kusnetz, Jeremy <JKusnetz at apus.edu> wrote:

> I’m not 100%, when we are doing load testing, it’s these queries that are
> piling up on top of each other to the point where MySQL performance is
> highly degraded. ****
>
> ** **
>
> *From:* ktsao at hungs.org [mailto:ktsao at hungs.org] *On Behalf Of *Karen Tsao
> *Sent:* Wednesday, October 12, 2011 11:35 AM
> *To:* Kusnetz, Jeremy
> *Cc:* sakai-dev
> *Subject:* Re: [Building Sakai] Heavy Samigo query****
>
> ** **
>
> Hi Jeremy,
>
> You are running Samigo 2.6.3? I don't have a 2.6.3 instance to explore.
> It'll be easier for me if you can let me know in which steps these queries
> get executed.
>
> Thanks,
> Karen
>
> ****
>
> On Wed, Oct 12, 2011 at 6:15 AM, Kusnetz, Jeremy <JKusnetz at apus.edu>
> wrote:****
>
> 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****
>
>  ****
>
> *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****
>
>  ****
>
> 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.
>
> _______________________________________________
> 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/20111012/c3f08bfa/attachment.html