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>
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1

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

Answered 06/25/2012 by: jverbosk
Red Belt

Please log in to comment

Answers

0

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

Answered 06/21/2012 by: jverbosk
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity