Ticket Query causes mysql to spin endlessly consuming all CPU

RT Users
1

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

2

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:

3

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/… ?

RuzaOn 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

The rt-users Archives

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

The rt-users Archives

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@i.cz http://www.i.cz

4

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

ruzaOn 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

The rt-users Archives

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

The rt-users Archives

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@i.cz http://www.i.cz

5

what about other supported databases ? is there better performance in
fulltext searches on pg/oracle ?
I sent answer on the similar question several hours ago to rt-devel(or
rt-users).
AFAIK each DB has own SQL syntax to use FULLTEXT index on the column
and this is not implemented in the DBIx::SB.
I recall some message about Oracle, there were really good explanation
about how to speed up this on Oracle, search for it archives (most
probably rt-devel).

ruza

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

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

The rt-users Archives

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

The rt-users Archives

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@i.cz http://www.i.cz

The rt-users Archives

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

Best regards, Ruslan.