[Deploying Sakai] A major Samigo performance improvement
da1 at vt.edu
Mon Nov 21 05:55:58 PST 2011
Just wanted to give an update on some Samigo performance issues I think a lot of folks have encountered and that we recently were able to track down to two particularly slow-running queries that we were able to speed up significantly using some fixes that happened to already be in the queue for Samigo, just a couple of years ahead of where we are.
We're running Sakai 2.7.1 with Samigo 2.7.1 on Java 1.6 against Oracle 220.127.116.11. As of this morning, the three Samigo database tables sam_publishedevaluation_t, sam_publishedaccesscontrol_t, and sam_publishedfeedback_t each has 21,894 rows.
Prior to any patching, we had noticed some incredibly slow response from the Tests & Quizzes tool, which was causing lots of frustration from our users and potentially was causing some database instability we'd been running into. Response times from sakai.samigo tool URIs containing "/jsf/" (ie, all the stuff that uses Java/Sakai logic, and not the static files) were averaging 1.5 seconds, and the /jsf/index/mainIndex URI, which is the first page users get when they load the Tests & Quizzes tool, was averaging over eight seconds.
We tracked the slowdown in the mainIndex page to two queries, mentioned in this JIRA:
In particular, our logs showed that one execution of the first query mentioned in that JIRA, along with two executions of the query mentioned in the second comment of that JIRA amounted for nearly 90% of the time spent loading the mainIndex page. The queries in question were pulling in every record from the three tables I mentioned above, and as our database has grown, they've become correspondingly slower and slower.
After applying the fixes we put together (one of which is part of 2.8 and one of which is now part of trunk), we saw production response time for all Samigo /jsf/ page loads drop from 1.5 seconds to just over 500ms. And average load times for the mainIndex page went from 8.5 seconds to 725 milliseconds!
Now, it was pretty confusing what patches were what in the various JIRAs linked to SAM-1440, so I posted two patch files that isolate just the fixes for the two queries involved to the JIRA: vt-SAM-1440-2.7.patch has been tested and is in production for us with 2.7.1 and applies cleanly to all 2.7 branches. It also appears to apply relatively cleanly to all 2.6 branches, though I haven't tested building or running on 2.6. And the vt-SAM-1440-2.8.patch (which contains a fix for only the one query not fixed in 2.8) applies cleanly to all 2.8 branches, but again, I haven't had a chance to test it myself.
I'm hoping that our efforts here will help some other folks. The improvement was so dramatic for us and presumably will help everyone running Samigo, but we did find it very confusing to dig through the JIRAs to find exactly what fixes we needed, so hopefully the patches will help someone.
Director, Learning Systems Integration and Support
Virginia Tech Learning Technologies
More information about the production