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

[Pavel Ruzicka]

what about other supported databases ? is there better performance in
fulltext searches on pg/oracle ?

ruza

On 11/15/05 18:55, Pavel Ruzicka wrote:
> 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 641
mailto:pavel.ruzicka at i.cz http://www.i.cz
*******************************************