[rt-users] Searching with RT "hangs"

[Matthew Watson]

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 at bla.com" AND requestor ="john at 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 at ist.uwaterloo.ca> wrote:

> "John DeBerry" <jdeberry at 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 at 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 at 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