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

[Ruslan Zakirov]

On 12/5/05, Pavel Ruzicka <pavel.ruzicka at i.cz> wrote:
> 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
>
> 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
> *******************************************
> _______________________________________________
> 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
>


--
Best regards, Ruslan.