Extremely slow indexing query
Problem reported by Colin M - 9/3/2014 at 4:03 PM
Submitted
SmarterTrack Enterprise 10.1.5283
 
In my "Errors" log I have a LOT of the following:
 
=====================
[2014-09-03 6:51:58 PM]
Agent: N/A

Indexing error: Lost connection to MySQL server during query


=====================
[2014-09-03 6:53:20 PM]
Agent: N/A

Indexing error: Lost connection to MySQL server during query


=====================
[2014-09-03 6:54:43 PM]
Agent: N/A

Indexing error: Lost connection to MySQL server during query
So almost every minute as you can see. I enabled MySQL Slow Query log and also almost every minute I have:
 
# User@Host: smartertrack[smartertrack] @ localhost [127.0.0.1]
# Query_time: 47.618165  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 53002586
SET timestamp=1409785050;
insert into st_SearchTokenStats
                (SearchTokenID, SearchFieldID, DateCalculatedUTC, WeightFactor)
                ( 
                    select R.SearchTokenID, R.SearchFieldID, '2014:09:03 22:56:42', case  when count(R.TicketID) > 163315 then 0.001  when count(R.TicketID) > 122486 then 0.01  when count(R.TicketID) > 81657 then 0.04  when count(R.TicketID) > 61243 then 0.1  when count(R.TicketID) > 40828 then 0.3  when count(R.TicketID) > 20414 then 0.4  when count(R.TicketID) > 16331 then 0.5  when count(R.TicketID) > 12248 then 0.7  when count(R.TicketID) > 8165 then 1.0  when count(R.TicketID) > 4082 then 1.5  when count(R.TicketID) > 2041 then 2.0  when count(R.TicketID) > 1020 then 4.0  when count(R.TicketID) > 408 then 6.0  when count(R.TicketID) > 81 then 10.0  else 15.0  end from st_SearchTokensInTickets R 
                    left join st_SearchTokenStats S on R.SearchTokenID = S.SearchTokenID and R.SearchFieldID = S.SearchFieldID
                    where R.SearchTokenID > 0 and (S.SearchTokenID is null or S.SearchFieldID is null) 
                    group by R.SearchTokenID, R.SearchFieldID
                );
The problem is obviously the 53,002,586 rows examined! No amount of hardware upgrades is going to alleviate this issue and it will only get worse.. I have a 47 second query occurring every 60 seconds, and it is getting longer over time.. So before long I'm going to have a really major issue...
 
Please optimize this query somehow to use an index, and soon!

3 Replies

Reply to Thread
0
Colin M Replied
NOTE: I opened a support ticket about this on September 27 2013 almost a year ago and it was closed with the last communication being "Our developers will be looking for the causes of these repeat errors. I'll let you know once I hear back from them."
0
Steve Reid Replied
I am sure it must have been closed in error... Have you replied to it asking for an update?
0
Colin M Replied
Yes, I have re-opened the issue. Was hoping it would get some attention here as well though as this seems to be the biggest choking point for an otherwise very scalable system. To be clear, I am worried that when this query starts taking over 60 seconds then there will be two of these running at once, then three, then four until the database is brought to it's knees..

Reply to Thread