Searching with RT "hangs"

RT Users
1

I am having an elusive problem with RT 2.0.15. It “hangs” sometimes when
displaying a search result (i.e., the user’s browser sits and spins), and I
have to restart MySQL to get it going again.

We are using:
RedHat 6.2
Apache 1.3.20
MySQL 3.23.51

It seems to happen intermittently, or we cannot determine the cause of it.
(One speculation is that it sometimes happens when someone searches RT
looking for all tickets from a Requestor. In our case, we have about eight
or so Requestors that have a lot of tickets. I’m not sure if it’s related or
not.)

When RT “hangs,” the %CPU for mysql jumps to about 96% and stays there. And
the thing is, it will happen sometimes with the same Requestor and same
results.

If anyone has any ideas as to what I can try, it would be much appreciated.
:slight_smile:

John DeBerry

2

“John DeBerry” jdeberry@townnews.com writes:

I am having an elusive problem with RT 2.0.15. It “hangs” sometimes when
displaying a search result

We have a similar problem, with a similar setup. I turned on “slow”
logging on mysql and confirmed that it is searches doing it, usually
pretty hairy ones. Somewhere (can’t remember where) I read that this
behaviour is triggered when a query results in more than one join to
the Watcher’s table. MySQL is not stuck, it’s just doing huge amounts
of processing. If you leave it long enough it will proceed, logging
that it searched thousands of records… Example below, note that
before we killed it it ran for 1020 seconds = 17 minutes on an

vendor_id : GenuineIntel
model name : Intel(R) Pentium(R) 4 CPU 1.60GHz
stepping : 4
cpu MHz : 1607.112
cache size : 512 KB

Not sure who’s to blame. Poorly constructed SQL queries from RT, or
MySQL doing something silly?

Still searching for answers myself.

gdm

Time: 030124 15:40:53

User@Host: rt2_user[rt2_user] @ localhost

Query_time: 1020 Lock_time: 0 Rows_sent: 0 Rows_examined: 54377

SELECT DISTINCT main.* FROM Tickets main, Watchers Watchers_1, Watchers Watchers_3 LEFT JOIN Users as Users_2 ON Watchers_1.Owner = Users_2.id LEFT JOIN Users as Users_4 ON Watchers_3.Owner = Users_4.id WHERE ((Watchers_3.Type = ‘Requestor’)) AND ((Watchers_3.Scope = ‘Ticket’)) AND ((main.EffectiveId = main.id)) AND ((Watchers_1.Scope = ‘Ticket’)) AND ((Watchers_1.Type = ‘Requestor’)) AND ((main.Queue = ‘23’)OR(main.Queue = ‘25’)) AND ((Watchers_1.Email LIKE ‘%alfred%’)OR(Users_2.EmailAddress LIKE ‘%alfred%’)OR(Watchers_3.Email LIKE ‘%alfred%’)OR(Users_4.EmailAddress LIKE ‘%alfred%’)) AND main.id = Watchers_1.Value AND main.id = Watchers_3.Value ORDER BY main.id DESC LIMIT 50;

3

I am having an elusive problem with RT 2.0.15. It “hangs” sometimes when
displaying a search result (i.e., the user’s browser sits and spins), and I
have to restart MySQL to get it going again.

Table level locking stopping (eventually) all queries to wait. Try innodb
type tables (may require an SQL server upgrade).

                         Bruce Campbell                            RIPE
               Systems/Network Engineer                             NCC
             www.ripe.net - PGP562C8B1B             Operations/Security
4

I had this problem. I solved is as follows

  1. Altered search pages so staff could not do a search on more than one
    requestor,
    eg
    Where request = “bob@bla.com” AND requestor ="john@bla.com",
    I found often what was happening is users didn’t quite understand that if
    they
    refreshed the page on a search, it would add those items into the search
    again.

  2. Moved all tables to innodb, this allowed from row level locking, so even
    if a big query was being run, other queries could also get processed and
    not get queued waiting for that big one to finish.

Previously to doing this, I was getting lockups atleast once a day,
normally more
and the only practical solution was to restart mysql or manually kill the
offending mysql querys.

Hope this helps.

Mat.–On Saturday, 25 January 2003 6:29 PM -0500 Giles Malet - IST gdmalet@ist.uwaterloo.ca wrote:

“John DeBerry” jdeberry@townnews.com writes:

I am having an elusive problem with RT 2.0.15. It “hangs” sometimes when
displaying a search result

We have a similar problem, with a similar setup. I turned on “slow”
logging on mysql and confirmed that it is searches doing it, usually
pretty hairy ones. Somewhere (can’t remember where) I read that this
behaviour is triggered when a query results in more than one join to
the Watcher’s table. MySQL is not stuck, it’s just doing huge amounts
of processing. If you leave it long enough it will proceed, logging
that it searched thousands of records… Example below, note that
before we killed it it ran for 1020 seconds = 17 minutes on an

vendor_id : GenuineIntel
model name : Intel(R) Pentium(R) 4 CPU 1.60GHz
stepping : 4
cpu MHz : 1607.112
cache size : 512 KB

Not sure who’s to blame. Poorly constructed SQL queries from RT, or
MySQL doing something silly?

Still searching for answers myself.

gdm

Time: 030124 15:40:53

User@Host: rt2_user[rt2_user] @ localhost

Query_time: 1020 Lock_time: 0 Rows_sent: 0 Rows_examined: 54377

SELECT DISTINCT main.* FROM Tickets main, Watchers Watchers_1, Watchers
Watchers_3 LEFT JOIN Users as Users_2 ON Watchers_1.Owner = Users_2.id
LEFT JOIN Users as Users_4 ON Watchers_3.Owner = Users_4.id WHERE
((Watchers_3.Type = ‘Requestor’)) AND ((Watchers_3.Scope = ‘Ticket’)) AND
((main.EffectiveId = main.id)) AND ((Watchers_1.Scope = ‘Ticket’)) AND
((Watchers_1.Type = ‘Requestor’)) AND ((main.Queue = ‘23’)OR(main.Queue =
‘25’)) AND ((Watchers_1.Email LIKE ‘%alfred%’)OR(Users_2.EmailAddress
LIKE ‘%alfred%’)OR(Watchers_3.Email LIKE
‘%alfred%’)OR(Users_4.EmailAddress LIKE ‘%alfred%’)) AND main.id =
Watchers_1.Value AND main.id = Watchers_3.Value ORDER BY main.id DESC
LIMIT 50; _______________________________________________
rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm