[rt-users] Ticket Query causes mysql to spin endlessly consuming all CPU

[Pavel Ruzicka]

i have this problem also with rt-3.2.2 @ mysql-3.23.58. fastcgi timeouts 
before it gets response from mysql backend. ugly way for client howto 
gets from this 500error situation can be delete cookie from browser, but 
dont do this!, mysql still tries to solve this SELECT! i increased 
FastCgiServer's -idle-timeout value to satisfy simpler queries

timeout for mysql is afaik defined in Apache/Session/Lock/MySQL.pm
my $sth = $self->{dbh}->prepare_cached(q{SELECT GET_LOCK(?, 3600)}, {}, 1);
i had no time for playing with this value so i dont know at this time if 
  it can help.

next i was pondering about FULLTEXT indexes, its not possible to use 
them with InnoDB, but (good news everyone :)), its in the InnoDB's 
roadmap. http://www.innodb.com/todo.php :

"Updated August 30, 2005. In progress: Add FULLTEXT indexes on InnoDB 
tables. A sponsor for this project has been found, and a developer has 
been hired. Appears probably in 2006."

Any other ideas/workarounds/solutions/... ?

Ruza


On 11/15/05 18:29, Roy El-Hames wrote:
> I 'll be looking forward to the solution  ..I have a similar issue with 
> content + subject only searches .. with RT-3.4.4
> search builder 1.33 mysql 4.1.15
> 
> Roy
> 
> 
> Aaron Nichols wrote:
> 
>> Hello again,
>>     We seem to have stumbled into a situation that has dire
>> concequences for mysql. If we run the following query in RT, mysql
>> will consume 100% of the CPU and RT will end up returning a "500
>> Internal Server Error" due to a timeout waiting for a response from
>> the mysql server. This seems to only impact the current session, if
>> the browser is closed and opened again RT works ok but the mysql
>> process is left consuming CPU time.
>>
>> Query in RT:
>> Subject LIKE 'test' OR Content LIKE 'test'
>>
>> Mysql Query According to mytop:
>> SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions
>> Transactions_1, Attachments Attachments_2 WHERE
>> ((Transactions_1.ObjectType = 'RT::Ticket')) AND ((main.EffectiveId =
>> main.id)) AND ((main.Status != 'deleted')) AND ((main.Type =
>> 'ticket')) AND ((main.Subject LIKE '%test%')OR (
>> (Attachments_2.Content LIKE '%test%')AND(Attachments_2.TransactionId =
>> Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) )
>>
>> The system is running on FreeBSD 6.0-RELEASE, RT 3.4.4, mysql 4.1.15
>> w/ linuxthreads, apache2.0.55, fastcgi 2.4.2 - all built from FreeBSD
>> ports collection.
>> Hardware is a dual Xeon 2.8Ghz, 2Gig ram running my-large.cnf from
>> mysql port w/ minor modifications.
>>
>> When RT eventually errors out after 120 seconds it gives the following
>> error (which makes total sense):
>> [Tue Nov 15 09:09:59 2005] [error] [client 10.1.46.207] FastCGI: comm
>> with server "/usr/local/rt3/bin/mason_handler.fcgi" aborted: idle
>> timeout (120 sec), referer:
>> http://rt-stage.corp.netopia.com/Search/Build.html
>> [Tue Nov 15 09:09:59 2005] [error] [client 10.1.46.207] FastCGI:
>> incomplete headers (0 bytes) received from server
>> "/usr/local/rt3/bin/mason_handler.fcgi", referer:
>> http://rt-stage.corp.netopia.com/Search/Build.html
>>
>> I believe this query is redundant and I've instructed folks not to do
>> this, but it seems like mysql shouldn't endlessly spin on the query
>> the way it does. I'm not sure if this is an RT problem in the way the
>> query is performed or a mysql problem in the way it responds (or
>> both). Any suggestions on how to prevent this in the future?
>>
>> Thanks,
>> Aaron
>>  
>>
>> ------------------------------------------------------------------------
>>
>> _______________________________________________
>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>>
>> Be sure to check out the RT Wiki at http://wiki.bestpractical.com
>>
>> Download a free sample chapter of RT Essentials from O'Reilly Media at 
>> http://rtbook.bestpractical.com
>>
>> WE'RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
>> San Francisco - Find out more at 
>> http://bestpractical.com/services/training.html
>>
> 
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Be sure to check out the RT Wiki at http://wiki.bestpractical.com
> 
> Download a free sample chapter of RT Essentials from O'Reilly Media at 
> http://rtbook.bestpractical.com
> 
> WE'RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
> San Francisco - Find out more at 
> http://bestpractical.com/services/training.html

-- 
Pavel Ruzicka, ICZ

*** ICZ a.s. ******************************
Hvezdova 1689/2a, 140 00 Prague 4, CZ
tel: +420 24 41 00 111   _
Fax: +420 24 41 00 222  @_}-,^--`--
GSM: +420 724 429 767
mailto:pavel.ruzicka at i.cz http://www.i.cz
*******************************************