[SAK-36814] update SAM_ITEMGRADING_T multiplies as user progresses through long assessment Created: 06-Dec-2017  Updated: 26-Apr-2018  Resolved: 15-Dec-2017

Status: Verified
Project: Sakai
Component/s: Tests & Quizzes (Samigo)
Affects Version/s: 11.4
Fix Version/s: 11.5 [Tentative], 12.0, 19.0

Type: Bug Priority: Critical
Reporter: Stephen Marquard Assignee: Sam Ottenhoff
Resolution: Fixed Votes: 2
Labels: performance
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File SAM-3325-11x.diff     PNG File Screen Shot 2017-12-06 at 5.58.49 PM.png     PNG File hibernate-use-merge.png     PNG File screenshot-1.png     PNG File screenshot-2.png     XML File tf400generic.xml    
Issue Links:
Relate
is related to SAK-39928 Samigo > minimize redundant UPDATE qu... Verified
12 status: Resolved
11 status: Resolved
Previous Issue Keys: SAM-3325
Test Plan:

Functional test:

  • Check that student answers continue to be saved while progressing through a test: create a 10 question test (one question per page), start taking it as a student, answer the first 5 questions then close browser without submitting. Open browser, login again as student, restart test, check that answers were saved.

Performance test:

  • Check that response time does not decrease linearly when progressing through a large test.
  • Enable mysql query logging on db and count the number of UPDATE queries while progressing through a test.

Load test:
It would be something along these lines using selenium or other scripting tool in order to simulate large numbers of students taking a test at the same time (300-400). Google Testing Sheet


 Description   

Stephen wrote to the list today:

I think the total number of questions in the assessment is the issue here. If you graph database activity, you’ll see that the number of update queries increases as students progress through an assessment, because T&Q updates the scoring each time an item is saved and I suspect it updates every item in the assessment.

For larger assessments (several hundred students), we’ve seen 1000+ update queries per second, and possibly your database cannot sustain that throughput.



 Comments   
Comment by Sam Ottenhoff [ 06-Dec-2017 ]

Stephen's assertion on the list today is correct. I couldn't believe it, but in my testing with Yourkit, it's confirmed.

 

Is the problem that syncTimeElapsedWithServer() gets called every save and has the effect of calling saveOrUpdateAssessmentGrading() which then does a complete Hibernate save on the assessment:

 

getHibernateTemplate().saveOrUpdate((AssessmentGradingData) assessment);

Comment by Stephen Marquard [ 07-Dec-2017 ]

Thanks for investigating, Sam Ottenhoff. I've attached a graph showing mysql update queries overall from our production system yesterday (the morning was around 200 students; not sure how many questions involved).

Comment by Laura Gekeler [ 07-Dec-2017 ]

Stephen Marquard It would be good to know both how many questions were involved, what the testing duration in time was, and whether or not these were random draw from question pools, all of which has been shown to influence performance in the past. ~LauraG

Comment by Stephen Marquard [ 07-Dec-2017 ]

Laura Gekeler We're familiar with this specific issue because we once (in 2012) had a 400-question assessment with a largish group of students that brought everything to a halt. There are lots of other variables that can affect T&Q performance, but those other issues are largely about the volume of select queries and this specific issue is about update queries.

As a contribution to testing, I attach a 400-question generic assessment. To do a meaningful load-test, you'd need to use JMeter or Selenium or something similar to simulate 10 or more test-takers so around the end of the assessment, it's generating a db query load of something larger than your database can handle.

However, I wasn't successful in important that assessment on Sakai 12 QA server - not sure why, but it seems to die during import and only imports part of it.

Comment by Stephen Marquard [ 07-Dec-2017 ]

Also a graph from a 2012 single-user test showing the response time increasing linearly as the test progresses (around 500ms in total slower per request at the end than at the start).

Comment by Sam Ottenhoff [ 13-Dec-2017 ]

I experimented with using Hibernate merge instead of saveOrUpdate and posted my results in Yourkit. The PR is currently failing tests but this shows potential.

Comment by Laura Gekeler [ 15-Dec-2017 ]

Sam Ottenhoff Congrats on cleaning up the PR so that it doesn't fail your tests (I'm assuming that because of the Resolved/Fixed status from 3 hrs ago). This is impressive work for the whole community. I'd be in favor of adding it to the 12 release. Do you think that would fly with the Core Team?

Comment by Sam Ottenhoff [ 15-Dec-2017 ]

If it passes QA, I see no reason it wouldn't be merged into 12.x

 

Comment by Brian Jones [ 19-Dec-2017 ]

What about an 11.x merge?

Comment by Sam Ottenhoff [ 19-Dec-2017 ]

> What about an 11.x merge?

 

It's a two line change ..... but it needs QA. This is high-risk, high-reward. It needs more eyes.

Comment by Neal Caidin [ 20-Dec-2017 ]

This sounds higher than a major priority issue. Critical?

 

Comment by Sam Ottenhoff [ 20-Dec-2017 ]

I'm not sure why priority matters here. We just need a couple of people who care about Samigo to test.

Comment by Neal Caidin [ 20-Dec-2017 ]

Maybe you're right Sam Ottenhoff .  I was thinking that our standard process is to prioritize issues, and it seems to me like it has a potential impact on where community resources focus, especially as we get closer to a release candidate. We tend to focus a lot more on critical issues than major issues.

 

Comment by Karen McPhaul [ 20-Dec-2017 ]

I could do the functional test (as described in the test plan) today. I just need to know which servers to test on. Is it these?

I could also help with the performance test by creating a large test and going through it. I would not be able to check update queries on mysql.

 

 

Comment by Neal Caidin [ 20-Dec-2017 ]

Hi Karen McPhaul , thanks!  Please test in master/trunk server. For example here:

https://trunk-mysql.nightly.sakaiproject.org/portal/

The first place issues get fixed is in master (currently aka 13, that's why the fix version is 13.0), then they get verified and after that branch managers merge into other branches (when appropriate).  HTH

 

Comment by Karen McPhaul [ 20-Dec-2017 ]

On master, I completed the functional part of the test plan. Answers were saved.

Recap of what I did: Created a test with questions presented one per page. As a student, I answered half the questions, logged out, closed browser, logged in again, and all answers were saved as they should be. Tried several scenarios (different users, different browsers, prev/next, leaving/returning several times, etc). Answers were saved.

Comment by Neal Caidin [ 20-Dec-2017 ]

Thanks Karen McPhaul . Now we just need to figure out how to get the performance and load testing parts addressed.

Comment by Laura Gekeler [ 20-Dec-2017 ]

Karen McPhaul Just for the record, how many questions did you use?

Comment by Karen McPhaul [ 20-Dec-2017 ]

Hi Laura - I used 10 questions, as suggested in the test plan. I figured this was intended to make sure "save" still worked and hadn't been broken somehow by the change.

Comment by Neal Caidin [ 21-Dec-2017 ]

Hi Stephen Marquard , do you have a way to do performance and load testing on this one? 

 

Comment by Stephen Marquard [ 21-Dec-2017 ]

Sure, I could repeat the JMeter test for performance (for a single test-taker with a large number of questions) and look at mysql queries generated.

I'm on holiday until 8 Jan though so won't look at it before then.

Comment by Sam Ottenhoff [ 03-Jan-2018 ]

This seems to be working fine on nightly. Tested as student and instructor.

Comment by Stephen Marquard [ 08-Jan-2018 ]

I backported this manually to our local 11.x dev system (conflict applying the patch generated from the github PR but just for whitespace I think), and confirm that the multiple update queries are gone. When navigating from one question to the next, only 2 UPDATE queries are generated (from watching mysql query logs), one for the assessment attempt and one for the item response, which is correct.

I ran into some difficulty getting our older JMeter to work with the JSF viewstate stuff so haven't been able to test performance throughout an entire assessment, but I'd be 99% sure that it's fixed as a result of eliminating the unnecessary update queries.

Generated at Sun Sep 15 05:43:31 CDT 2019 using Jira 8.0.3#800011-sha1:073e8b433c2c0e389c609c14a045ffa7abaca10d.