Basic (RR) routing rule is locking up the database after working for months.
In March I posted our Routing Rule in response to another question here - http://www.itninja.com/question/k1000-round-robin.
A couple of weeks ago we began to experience a system performance issue where page changes (ticket saves, new ticket so on) were taking 40 to 90 seconds to complete. Not a pleasant experience for the ticket agents or the business users. A week of triage on different components led us to the Round Robin ticket rule that has been in place since late last year.
In an effort to replace it we began testing with GillySpy’s RR rule (http://www.itninja.com/question/auto-assign-tickets-in-a-round-robin) which works flawlessly except I needed to narrow down the selection group by Label. I found another post which filled that need here (http://www.itninja.com/question/k1000-round-robin).
So I added two lines to GillySpy’s RR rule (works flawlessly) to capture only the users in the Label. This rule now hangs the system in the same manner as our existing RR rule does. I tested a little further and found this single JOIN (JOIN USER_LABEL_JT ULJ ON ULJ.USER_ID = O.ID) is spiking the CPU to 100% and sits there for the full 40 to 90 seconds and this JOIN is in our original RR rule as well.
Am I looking at database corruption?
Compilation from two Ninja forum posts - http://www.itninja.com/question/auto-assign-tickets-in-a-round-robin - GillySpy
and - http://www.itninja.com/question/k1000-round-robin - dchristian
***Select***
select HD_TICKET.ID from HD_TICKET WHERE HD_TICKET.HD_QUEUE_ID=1 and HD_TICKET.CUSTOM_FIELD_VALUE2='No' and HD_TICKET.HD_STATUS_ID !=2 and (HD_TICKET.OWNER_ID=0 OR HD_TICKET.OWNER_ID IS NULL) /* no owner yet */
***Update***
UPDATE HD_TICKET set HD_TICKET.OWNER_ID = (select BATTERUP.ID from (select O.ID from HD_TICKET T JOIN HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.HD_QUEUE_ID=Q.ID JOIN USER_LABEL_JT UL ON UL.LABEL_ID=QOL.LABEL_ID JOIN USER O ON O.ID=UL.USER_ID JOIN USER_LABEL_JT ULJ ON ULJ.USER_ID = O.ID - - HANGS WHEN THIS JOIN IS PRESENT JOIN LABEL L ON (L.ID = UL.LABEL_ID AND L.NAME = 'ticket owner label') -- add round robin label here LEFT JOIN (select max(CREATED) MAXC, OWNER_ID, HD_QUEUE_ID from HD_TICKET WHERE CREATED > CURDATE() GROUP BY OWNER_ID, HD_QUEUE_ID) T2 ON O.ID=T2.OWNER_ID and Q.ID=T2.HD_QUEUE_ID LEFT JOIN (select USER_ID from USER_LABEL_JT UL2 JOIN LABEL OUTLABEL ON OUTLABEL.ID=UL2.LABEL_ID and OUTLABEL.NAME='U-Out of office') OOO ON O.ID=OOO.USER_ID where OOO.USER_ID IS NULL and T.OWNER_ID=0 and T.ID=<TICKET_IDS> ORDER BY MAXC ASC, RAND() LIMIT 1 ) BATTERUP) WHERE HD_TICKET.ID =<TICKET_IDS>
Answers (2)
I re-read your question and you said things have been working fine until recently. Are there any errors in the logs related to this ticket rule? Have you tried rebooting the K1000 with an extended database check?
There also may be a way to do the join statements a little differently that may help with the way this rule runs, I've been thinking about this since answering earlier but haven't had time to write it out.
John
Disclaimer - I'm not a MySQL guru and since this uses an Update statement which could potentially mess things up, I want that to be absolutely clear.
That aside, I can't really test this since my setup is completely different, but I went through the JOIN statements and found one potential issue with this statement (what you correctly identified as the problem):
JOIN USER_LABEL_JT ULJ ON ULJ.USER_ID = O.ID - - HANGS WHEN THIS JOIN IS PRESENT
The *potential* problem might be that the USER_LABEL_JT table is already being aliased as UL in this JOIN statement:
JOIN USER_LABEL_JT UL ON UL.LABEL_ID=QOL.LABEL_ID
Therefore, I'd try changing the problem line to this as a first step and see if that helps things:
JOIN USER_LABEL_JT UL ON UL.USER_ID = O.ID
The good news is all of the joins do line up properly and the ULJ alias isn't being used anywhere else.
That being said, if it's still not working the next place I'd look is where USER_LABEL_JT is aliased as UL2 (later on in the LEFT JOIN statements).
_______________________________
Here's my scratch notes from comparing this using my setup (up to the problem line), just so you can see my thought process on breaking this down (in case that might be helpful later) - just ignore the values, as these are unique to my KBOX:
JOIN HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID
1, 2, 3
JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.HD_QUEUE_ID=Q.ID
1, 2, 3
JOIN USER_LABEL_JT UL ON UL.LABEL_ID=QOL.LABEL_ID
146
JOIN USER O ON O.ID=UL.USER_ID
1574
QOL
HD_QUEUE_OWNER_LABEL_JT.HD_QUEUE_ID 1, 2, 3
HD_QUEUE_OWNER_LABEL_JT.LABEL_ID 146
Q
HD_QUEUE.ID 1, 2, 3
T
HD_TICKET.HD_QUEUE_ID 1, 2, 3
O
USER.ID 1574
USER.USER_NAME jverbosk
UL
USER_LABEL_JT.USER_ID 1574
USER_LABEL_JT.LABEL_ID 146
ULJ
USER_LABEL_JT.USER_ID 1574
USER_LABEL_JT.LABEL_ID 146
JOIN USER_LABEL_JT ULJ ON ULJ.USER_ID = O.ID - - HANGS WHEN THIS JOIN IS PRESENT
1574
*Problem*
USER_LABEL_JT is already being aliased as UL, maybe try:
JOIN USER_LABEL_JT UL ON UL.USER_ID = O.ID
_______________________________
Hope that helps!
John
