[Building Sakai] Followup on production issue with Lesson Builder
Kusnetz, Jeremy
JKusnetz at APUS.EDU
Tue Jan 14 11:24:19 PST 2014
#1 is cluster aware since it's logged in the database. For us we have a lot of nodes in our cluster so this was important for us. Plus we thought that spawning off a new thread was not need and could be difficult to track if the thread had errors.
#3, yes both patches do the same thing in regards to the email. We actually borrowed your code for this one. :)
#2 I think is the bigger one. I haven't looked at what trunk is doing, so my only experience is with 2.9.x. There is a site.upd event and then the individual tools that were imported trigger their own event. But there is still a lot lacking. Was the site.upd event generated from an import there are other things that also triggers a site.upd event. We actually created a new event with this too, site.import so there is no ambiguity. But even if we could figure out that the site.upd was triggered from an import, we still don't know what the source site/s were. We don't know when the import started and finished. I guess we can infer who did the import by looking at the session user tied to that event. For the tools, we have to rely on the tool's own events, and some may be lacking. Plus there is no way to tell if that tool's even was generated from a site import or something else, similar to the site.upd event. Plus if we are merging, the tools can be coming from multiple sites, there is no way to tell what the source site was for that event.
All of this is solved via the new tables. Here are the tables and their schema and what they track. Below is a description of what they do.
mysql> show create table SAKAI_SITE_IMPORT_STATE\G
*************************** 1. row ***************************
Table: SAKAI_SITE_IMPORT_STATE
Create Table: CREATE TABLE `SAKAI_SITE_IMPORT_STATE` (
`SITE_IMPORT_STATE_ID` bigint(20) NOT NULL AUTO_INCREMENT,
`SITE_ID` varchar(99) NOT NULL,
`STARTEDDATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`SITE_IMPORT_STATES_LIST_ID` bigint(20) NOT NULL,
`FINISHEDDATETIME` timestamp NULL DEFAULT NULL,
`USER_ID` varchar(99) NOT NULL,
PRIMARY KEY (`SITE_IMPORT_STATE_ID`),
KEY `test_idx` (`SITE_IMPORT_STATES_LIST_ID`),
CONSTRAINT `site_import_states_ID_FK` FOREIGN KEY (`SITE_IMPORT_STATES_LIST_ID`) REFERENCES `SAKAI_SITE_IMPORT_STATES_LIST` (`SITE_IMPORT_STATES_LIST_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
So this table tracks what site is being imported, when it started, and when it ended, who kicked off the import, and what the current state of the import is.
mysql> show create table SAKAI_SITE_IMPORT_STATES_LIST\G
*************************** 1. row ***************************
Table: SAKAI_SITE_IMPORT_STATES_LIST
Create Table: CREATE TABLE `SAKAI_SITE_IMPORT_STATES_LIST` (
`SITE_IMPORT_STATES_LIST_ID` bigint(20) NOT NULL AUTO_INCREMENT,
`IMPORT_STATE` varchar(45) NOT NULL,
PRIMARY KEY (`SITE_IMPORT_STATES_LIST_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
This table just handles the definition of the import state. Currently we just have 3 states, Finished, Running, Error. So with this we can see if there were problems, or if a import is still running. New states can be added as needed.
mysql> show create table SAKAI_SITE_IMPORT_TOOLS\G
*************************** 1. row ***************************
Table: SAKAI_SITE_IMPORT_TOOLS
Create Table: CREATE TABLE `SAKAI_SITE_IMPORT_TOOLS` (
`SITE_IMPORT_TOOLS_ID` bigint(20) NOT NULL AUTO_INCREMENT,
`SITE_IMPORT_STATE_ID` bigint(20) NOT NULL,
`TOOL_ID` varchar(99) NOT NULL,
`REGISTRATION` varchar(99) NOT NULL,
`DateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`SITE_IMPORT_TOOLS_ID`),
KEY `sakai_import_tools_site_tool_ID_FK_idx` (`TOOL_ID`),
KEY `site_import_tools_import_state_ID_FK_idx` (`SITE_IMPORT_STATE_ID`),
CONSTRAINT `site_import_tools_import_state_ID_FK` FOREIGN KEY (`SITE_IMPORT_STATE_ID`) REFERENCES `SAKAI_SITE_IMPORT_STATE` (`SITE_IMPORT_STATE_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show create table SAKAI_SITE_IMPORT_STATES_LIST\G
*************************** 1. row ***************************
Table: SAKAI_SITE_IMPORT_STATES_LIST
Create Table: CREATE TABLE `SAKAI_SITE_IMPORT_STATES_LIST` (
`SITE_IMPORT_STATES_LIST_ID` bigint(20) NOT NULL AUTO_INCREMENT,
`IMPORT_STATE` varchar(45) NOT NULL,
PRIMARY KEY (`SITE_IMPORT_STATES_LIST_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
These two tables show what tools came from what source sites and when they were imported. Again for us, knowing the sources of the tools is very important and as far as I know there is no way to figure this out now.
All of these are questions that I as a Sakai administrator have not been able to answer until now. Again, if trunk's event logging has more events or there is another way to pull out this information I'd like to know how. But we created this patch and tables to solve that issue.
I've started to work on reports based off of this data, here is an example:
Site test import 2014 modelsite (97f3444f-f243-4f88-876a-29b627322890) Import start 2014-01-11 05:21:47 by admin:
COLL100 Model Site modelsite (a0c7b27f-55b5-febb-a707-5d1888a9b310) sakai.announcements: 2014-01-11 05:21:47
COLL100 Model Site modelsite (a0c7b27f-55b5-febb-a707-5d1888a9b310) sakai.assignment2: 2014-01-11 05:22:10
COLL100 Model Site modelsite (a0c7b27f-55b5-febb-a707-5d1888a9b310) sakai.forums: 2014-01-11 05:22:13
COLL100 Model Site modelsite (a0c7b27f-55b5-febb-a707-5d1888a9b310) sakai.gradebook.gwt.rpc: 2014-01-11 05:22:18
COLL100 Model Site modelsite (a0c7b27f-55b5-febb-a707-5d1888a9b310) sakai.iframe.site: 2014-01-11 05:23:14
COLL100 Model Site modelsite (a0c7b27f-55b5-febb-a707-5d1888a9b310) sakai.melete: 2014-01-11 05:22:31
COLL100 Model Site modelsite (a0c7b27f-55b5-febb-a707-5d1888a9b310) sakai.samigo: 2014-01-11 05:23:14
COLL100 Model Site modelsite (a0c7b27f-55b5-febb-a707-5d1888a9b310) sakai.schedule: 2014-01-11 05:22:10
COLL100 Model Site modelsite (a0c7b27f-55b5-febb-a707-5d1888a9b310) sakai.sitestats: 2014-01-11 05:22:31
COLL100 Model Site modelsite (a0c7b27f-55b5-febb-a707-5d1888a9b310) sakai.syllabus: 2014-01-11 05:22:32
Finsished 2014-01-11 05:23:19
From: Bryan Holladay [mailto:holladay at longsight.com]
Sent: Tuesday, January 14, 2014 12:36 PM
To: Kusnetz, Jeremy
Cc: May, Megan Marie; sakai-dev (sakai-dev at collab.sakaiproject.org)
Subject: Re: [Building Sakai] Followup on production issue with Lesson Builder
Jeremy,
I don't see the necessity of SAK-25541. The jira mentions 3 functions:
1) Prevent multiple imports at one time
2) Creates new tables that tracks the site import
3) Emails the user who started the import when the import is complete.
The jira https://jira.sakaiproject.org/browse/SAK-23897 already does all 3 of these things.
For #1: it checks to make sure there are no other threads running before running a new site import. Granted, by not using a DB, it won't be cluster aware so if 2 different instructors try to import on two different nodes at the same time, then you could have multiple running at the same time. It was a decision I made since I didn't believe it merited a new DB table for this use case.
For #2: The events are tracked in the event database and anyone with a little experience in mysql can create a report based on these events.
For #3: The site import already emails the user when the import is complete.
Am I missing something else this patch does?
Thanks,
Bryan
On Tue, Jan 14, 2014 at 12:11 PM, Kusnetz, Jeremy <JKusnetz at apus.edu<mailto:JKusnetz at apus.edu>> wrote:
I just filed a JIRA that I think would help with the multiple import issue.
SAK-25541
It adds a transaction history in the database of what the source site and tools were imported, and also keeps track of current running imports and won't allow a 2nd import if there is already one running.
From: sakai-dev-bounces at collab.sakaiproject.org<mailto:sakai-dev-bounces at collab.sakaiproject.org> [mailto:sakai-dev-bounces at collab.sakaiproject.org<mailto:sakai-dev-bounces at collab.sakaiproject.org>] On Behalf Of May, Megan Marie
Sent: Tuesday, January 14, 2014 11:47 AM
To: sakai-dev (sakai-dev at collab.sakaiproject.org<mailto:sakai-dev at collab.sakaiproject.org>)
Subject: [Building Sakai] Followup on production issue with Lesson Builder
All,
IU is testing SAK-23897 for implementation in the short term. I think that the community should consider porting this back to the 2.9.x code line.
Additionally, I think changes need to be made to Lesson Builder and/or the import process. That it's really easy to create 'orphaned' (archived) records in Lesson builder. These are items that show up under the 'Index of Pages' but are not available when a faculty member first loads the Lessons tool.
There are a few ways to go about creating these types of items :
* Remove/Add Lesson tool via Site Setup
* Import from Site using replace functionality
* Import timing out: each time many records are still created but it's not apparent to user. Timeout happens when there are too many.
* User decides not to link to a page any longer. They click to delete but that doesn't really happen
If you think about a faculty getting impatient waiting for the import & running it multiple times . . . . . the problem grows exponentially. Below are some suggestions we brainstormed to address the root of the problem:
* Import from Site (Replace option) - Should delete the records rather than move them into orphaned/archive state
* When a user deletes an item via the UI, it should indicate it's just being removed from this page and it's available from the archive
* Import from site should only copy/merge over 'active' pages
Thoughts?
Megan
From: Bryan Holladay [mailto:holladay at longsight.com]
Sent: Monday, January 13, 2014 1:15 PM
To: May, Megan Marie
Cc: Neal Caidin; Kirschner, Beth; sakai-dev (sakai-dev at collab.sakaiproject.org<mailto:sakai-dev at collab.sakaiproject.org>)
Subject: Re: [Building Sakai] Production issue with Lesson Builder
>When did Longsight introduce the code
I introduced our solution to alleviate the Lesson Builder import problem in the beginning of the Fall semester last year. The jira was created on Sept 4th 2013. We probably ran into the bug a few days prior to that. We notified our clients and updated the ones who were willing to update, so the majority of our 2.9 instances have this patch. We have several people starting their first week this week, but I don't have a number for that.
Once again, this patch doesn't address LB specifically and is more about a larger problem with site import running in the user's thread. However, it was the LB import that we saw causing the biggest issue and was the catalyst for this, which is why the jira only briefly mentions LB. I would love to hear what Chuck H. has to add about the LB import code itself.
-Bryan
On Mon, Jan 13, 2014 at 12:25 PM, May, Megan Marie <mmmay at iu.edu<mailto:mmmay at iu.edu>> wrote:
Our testing didn't show that LSNBLDR-304<https://jira.sakaiproject.org/browse/LSNBLDR-304> was the solution.
And thanks Bryan. We also tracked down the faculty member to ask them to stop. Thank goodness they were a good sport about it! When did Longsight introduce the code (and have any schools been through the first week or so?
Megan
From: sakai-dev-bounces at collab.sakaiproject.org<mailto:sakai-dev-bounces at collab.sakaiproject.org> [mailto:sakai-dev-bounces at collab.sakaiproject.org<mailto:sakai-dev-bounces at collab.sakaiproject.org>] On Behalf Of Neal Caidin
Sent: Monday, January 13, 2014 10:51 AM
To: Kirschner, Beth
Cc: sakai-dev (sakai-dev at collab.sakaiproject.org<mailto:sakai-dev at collab.sakaiproject.org>)
Subject: Re: [Building Sakai] Production issue with Lesson Builder
Is it related to this one
https://jira.sakaiproject.org/browse/LSNBLDR-304 ?
-- Neal
[cid:image001.jpg at 01CF112F.FF4857E0]
Kirschner, Beth<mailto:bkirschn at umich.edu>
January 13, 2014 at 10:45 AM
Is this written up in JIRA anywhere?
- Beth
_______________________________________________
sakai-dev mailing list
sakai-dev at collab.sakaiproject.org<mailto: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<mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a subject of "unsubscribe"
[cid:image002.jpg at 01CF112F.FF4857E0]
Bryan Holladay<mailto:holladay at longsight.com>
January 13, 2014 at 10:39 AM
>Does that help with the excessive number of items?
Yes and no. It prevents the excessive number of orphaned pages by the instructor re-importing and deleting the half imported material. I've seen cases where it takes 10+ minutes for the import to complete, which is why instructor's keep trying the import and causing millions of pages to be created. We actually had to track the instructor down and tell them to stop importing until we get the fix in since every time we restarted the instructor went back in to re-import. However, this patch doesn't address the issue inside of LB's import code. So I'm sure there is room for more improvement, but it seemed like it was the combination of multiple imports + non-performant import code. The good/bad thing about this issue is that it only comes up in the first week or so of the semester. So it won't be an issue again until next semester and any fix you add you won't really be certain until then as well. I'm not going to jinx us by saying everything is going fine so far with that patch with this start of this semester but you can infer that if you wish ;)
Thanks,
Bryan
_______________________________________________
sakai-dev mailing list
sakai-dev at collab.sakaiproject.org<mailto: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<mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a subject of "unsubscribe"
[cid:image002.jpg at 01CF112F.FF4857E0]
May, Megan Marie<mailto:mmmay at iu.edu>
January 13, 2014 at 9:16 AM
Thanks Bryan! We started looking at that patch last night.
Does that help with the excessive number of items? Even with 1 import we've seen a high number of duplicate entries (over 1000).
Megan
From: Bryan Holladay [mailto:holladay at longsight.com]
Sent: Monday, January 13, 2014 8:38 AM
To: May, Megan Marie
Cc: sakai-dev (sakai-dev at collab.sakaiproject.org<mailto:sakai-dev at collab.sakaiproject.org>)
Subject: Re: [Building Sakai] Production issue with Lesson Builder
My first assumption would be that it is from importing previous course LB's into the new courses. We've seen a lot of issues with performance, excessive number of items and server stress with this process. We created/provided this jira to help alleviate some of the issue:
SAK-23897 Site Info -> Import from site needs to run in a seperate thread and email user when finished
We were seeing instructors importing several times b/c it would either time out or they'd give up on the process and try again (or click several times). If I wanted to bring down a Sakai server, that is how I would do it (site import with LB)
-Bryan
On Sun, Jan 12, 2014 at 1:22 PM, May, Megan Marie <mmmay at iu.edu<mailto:mmmay at iu.edu>> wrote:
On Friday the following SQL began running for excessive periods of time
update lesson_builder_items set pageId=:1 , sequence=:2 , type=:3 , sakaiId=:4 , name=:5 , html=:6 , description=:7 , height=:8 , width=:9 , alt=:10 , nextPage=:11 , format=:12 , required=:13 , alternate=:14 , prerequisite=:15 , subrequirement=:16 , requirementText=:17 , sameWindow=:18 , groups=:19 , anonymous=:20 , showComments=:21 , forcedCommentsAnonymous=:22 , gradebookId=:23 , gradebookPoints=:24 , gradebookTitle=:25 , altGradebook=:26 , altPoints=:27 , altGradebookTitle=:28 where id=:29
As a stop gap we've bounced the app servers these are occurring on. We've noted HUGE increases in the number of rows in LESSON_BUILDER_ITEMS & the increases pertain to rows in lesson_builder_items show a SAKAIID of "/dummy" (Import issues?) The behavior seems like a loop - and we've looked at LSNBLDR-304 although the cause of it is ambiguous so we're hesitant to move forward with it.
Has anyone encountered this?
Megan
Megan May
Manager, Learning Management Systems / Kuali Student Development
UITS Enterprise Student Systems
Enterprise Software Division
Indiana University
Office: 317-274-4528<tel:317-274-4528>
_______________________________________________
sakai-dev mailing list
sakai-dev at collab.sakaiproject.org<mailto: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<mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a subject of "unsubscribe"
_______________________________________________
sakai-dev mailing list
sakai-dev at collab.sakaiproject.org<mailto: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<mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a subject of "unsubscribe"
[cid:image002.jpg at 01CF112F.FF4857E0]
Bryan Holladay<mailto:holladay at longsight.com>
January 13, 2014 at 8:37 AM
My first assumption would be that it is from importing previous course LB's into the new courses. We've seen a lot of issues with performance, excessive number of items and server stress with this process. We created/provided this jira to help alleviate some of the issue:
SAK-23897 Site Info -> Import from site needs to run in a seperate thread and email user when finished
We were seeing instructors importing several times b/c it would either time out or they'd give up on the process and try again (or click several times). If I wanted to bring down a Sakai server, that is how I would do it (site import with LB)
-Bryan
_______________________________________________
sakai-dev mailing list
sakai-dev at collab.sakaiproject.org<mailto: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<mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a subject of "unsubscribe"
[cid:image002.jpg at 01CF112F.FF4857E0]
May, Megan Marie<mailto:mmmay at iu.edu>
January 12, 2014 at 1:22 PM
On Friday the following SQL began running for excessive periods of time
update lesson_builder_items set pageId=:1 , sequence=:2 , type=:3 , sakaiId=:4 , name=:5 , html=:6 , description=:7 , height=:8 , width=:9 , alt=:10 , nextPage=:11 , format=:12 , required=:13 , alternate=:14 , prerequisite=:15 , subrequirement=:16 , requirementText=:17 , sameWindow=:18 , groups=:19 , anonymous=:20 , showComments=:21 , forcedCommentsAnonymous=:22 , gradebookId=:23 , gradebookPoints=:24 , gradebookTitle=:25 , altGradebook=:26 , altPoints=:27 , altGradebookTitle=:28 where id=:29
As a stop gap we've bounced the app servers these are occurring on. We've noted HUGE increases in the number of rows in LESSON_BUILDER_ITEMS & the increases pertain to rows in lesson_builder_items show a SAKAIID of "/dummy" (Import issues?) The behavior seems like a loop - and we've looked at LSNBLDR-304 although the cause of it is ambiguous so we're hesitant to move forward with it.
Has anyone encountered this?
Megan
Megan May
Manager, Learning Management Systems / Kuali Student Development
UITS Enterprise Student Systems
Enterprise Software Division
Indiana University
Office: 317-274-4528<tel:317-274-4528>
_______________________________________________
sakai-dev mailing list
sakai-dev at collab.sakaiproject.org<mailto: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<mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a subject of "unsubscribe"
--
Neal Caidin
Sakai Community Coordinator
Apereo Foundation
neal.caidin at apereo.org<mailto:neal.caidin at apereo.org>
Skype me! (but let me know in advance for the first interaction) - nealkdin
_______________________________________________
sakai-dev mailing list
sakai-dev at collab.sakaiproject.org<mailto: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<mailto: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<mailto: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<mailto: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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20140114/031d1c01/attachment.html
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 1196 bytes
Desc: image001.jpg
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20140114/031d1c01/attachment.jpg
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.jpg
Type: image/jpeg
Size: 770 bytes
Desc: image002.jpg
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20140114/031d1c01/attachment-0001.jpg
More information about the sakai-dev
mailing list