Here is the discussion thread I have with Glenn Golden, architect behind Mneme code:

mneme problem
9 messages
Zhen Qian <zqian@umich.edu> Wed, Jun 19, 2013 at 10:34 AM
To: "dev@etudes.org" <dev@etudes.org>
Hi, etudes team:

I want to report a recent discovered Test Center performance problem UMICH-814:

We have a CTools site used for Spanish Placement Testing for freshmen every year. on 5/31/13, one app server became unresponsive because of heavy Test Center usage. The symptoms include high app cpu, high db cpu, etc.

We have traced down the following problem query within awr report:

CPU CPU per Elapsed
Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id
---------- ------------ ---------- ------ ---------- ------ ------ -------------
109.6 16 6.85 22.9 109.1 100.5 .0 fpcp7ndz82hs7
Module: JDBC Thin Client
SELECT A.GUEST, A.EVAL_ATRIB_DATE, A.EVAL_ATRIB_USER, A.EVAL_ATTACHMENTS, A.EVAL
_COMMENT, A.EVAL_EVALUATED, A.EVAL_SCORE, A.ID, A.PART_ID, A.QUESTION_ID, A.QUES
TION_TYPE, A.REASON, A.REVIEW, A.SUBMISSION_ID, A.SUBMITTED_DATE, A.AUTO_SCORE,
A.CONTEST FROM MNEME_ANSWER A JOIN MNEME_SUBMISSION S ON A.SUBMISSION_ID=S.ID WH...

Notice that the query was not executed many times, but one query was costly to execute.

The Spanish Placement Test site was created in 2011, and has been reused since every year. No data purge was done for the past records.So the current row count for the following query is 502424, which no wonder takes long time to execute.

=======================

The suggested fix here is to at least limit the return result to those user still active within the site. I have attached the patch here. Would you please review it? We plan to roll this out before Fall starts.

Thanks,

• Zhen

UMICH-814.diff
2K
Glenn R. Golden <ggolden@etudes.org> Wed, Jun 19, 2013 at 12:10 PM
To: Zhen Qian <zqian@umich.edu>
Zhen, I'm looking at this. Tell me about the site - how do you manage it? Do you just add new students to it each term? Are there a huge number of students in there already? Do you do anything to remove the prior students?

• Glenn

Glenn R. Golden
Chief Architect, Etudes, Inc.
ggolden@etudes.org

[Quoted text hidden]
[Quoted text hidden]
<UMICH-814.diff>

Zhen Qian <zqian@umich.edu> Wed, Jun 19, 2013 at 12:12 PM
To: "Glenn R. Golden" <ggolden@etudes.org>
Nice to hear from you, Glenn.

MPathway people remove old students every year and add more new students.

Right now, the site has 1400+ users.

• Zhen
  [Quoted text hidden]
  Glenn R. Golden <ggolden@etudes.org> Wed, Jun 19, 2013 at 12:14 PM
  To: Zhen Qian <zqian@umich.edu>
  Ah, but the answers that the students had made to that same assessment remain, don't they. That's why this query is getting such a massive record count.

• Glenn

Glenn R. Golden
Chief Architect, Etudes, Inc.
ggolden@etudes.org

[Quoted text hidden]
Zhen Qian <zqian@umich.edu> Wed, Jun 19, 2013 at 12:15 PM
To: "Glenn R. Golden" <ggolden@etudes.org>
That's right.

• Zhen
  [Quoted text hidden]
  Zhen Qian <zqian@umich.edu> Thu, Jun 20, 2013 at 2:18 PM
  To: "Glenn R. Golden" <ggolden@etudes.org>
  Hi, Glenn:

Any suggestions for the patch?

Thanks,

• Zhen
  [Quoted text hidden]
  Glenn R. Golden <ggolden@etudes.org> Thu, Jun 20, 2013 at 3:57 PM
  To: Zhen Qian <zqian@umich.edu>
  My thoughts …

It is probably better from many perspectives to create new sites periodically, rather than keep reusing the same one, This patch changes things by joining against the realm tables to weed out users not in the site anymore … every time we read any sort of submission! The method where you put the patch is used from like 8 different methods in the same file, with different where clauses - make sure that your change doesn't interfere with any of these where clauses.

I would not do this in Etudes - I'd rather not keep hitting the realm table like this (that can get pretty large!), and we don't have this sort of use of sites. Instead of a single site that we rotate users through, our users just create new sites for each new class, and import over their content.

For your case, you might have a new site created each time there's a new batch of students to test. That keeps things nicely organized, and probably benefits in other data access cases than this one.

Another approach might be to enhance your process that removes students from the site, to remove their submissions as well. We NEVER remove submissions in Etudes, other than when the entire site is mothballed (and then, we only keep the authored content, not student work). So if you do take this approach, be careful that you don't end up deleting active student records, or student records that you would otherwise want access to. And if you do consider this, there might be other student stuff to delete as well as the Mneme submissions.

• Glenn

Glenn R. Golden
Chief Architect, Etudes, Inc.
ggolden@etudes.org

[Quoted text hidden]
Zhen Qian <zqian@umich.edu> Thu, Jun 20, 2013 at 4:06 PM
To: "Glenn R. Golden" <ggolden@etudes.org>
Thanks, Glenn.

If you see the configuration of Test Center for this site, you will understand why they want to create it once and use it forever: it has 8 parts and altogether 99 questions, of course, some of the questions are randomly picked from the question pool.

My question is: does Mneme support Site Duplciation, so that when they duplicate the site, they get the same Test Center copied over?

We are thinking of another approach meanwhile, which is to archive the mneme answer table for those gone-students, without code changes.

• Zhen
  [Quoted text hidden]
  Glenn R. Golden <ggolden@etudes.org> Thu, Jun 20, 2013 at 4:25 PM
  To: Zhen Qian <zqian@umich.edu>
  Sure! Mneme participates in import-from-site in the worksite setup tool. Without that, our users would be very unhappy!

Archiving the answers (and submission records) for the removed students is a good idea, too. That's what I was getting at.

• Glenn

Glenn R. Golden
Chief Architect, Etudes, Inc.
ggolden@etudes.org

We've decided not to implement this due to concerns of undesired side-effects with this patch.

The alternative approach will be to purge all test results prior to 2013, and in future years, duplicate the Spanish Test Site (which will duplicate the test center questions, but not the results).

The patch modifies the sql code to retrieve only assessment submissions made by current site users.