Bad query?

Hi,
Im running rt 2.0.9. About once every 2 weeks the system seems to lock up. When I do a show full processlist I see the query that is causing the problem:

SELECT DISTINCT main.* FROM Tickets main, Watchers Watchers_1, Watchers Watchers_3, Watchers Watchers_5 LEFT JOIN Users as Users_6 ON Watchers_5.Owner = Users_6.id LEFT JOIN Users as User
s_2 ON Watchers_1.Owner = Users_2.id LEFT JOIN Users as Users_4 ON Watchers_3.Owner = Users_4.id WHERE ((Watchers_3.Scope = ‘Ticket’)) AND ((Watchers_3.Type = ‘Requestor’)) AND ((Watchers_1.Sc
ope = ‘Ticket’)) AND ((Watchers_1.Type = ‘Requestor’)) AND ((Watchers_5.Type = ‘Requestor’)) AND ((Watchers_1.Email LIKE ‘%bdole%’)OR(Users_2.EmailAddress LIKE ‘%bdole%’)OR(Watchers_3.Email LIKE ‘%
bdole@vmsinfo.com%’)OR(Users_4.EmailAddress LIKE ‘%bdole@vmsinfo.com%’)OR(Watchers_5.Email LIKE ‘%bdole@vmsinfo.com%’)OR(Users_6.EmailAddress LIKE ‘%bdole@vmsinfo.com%’)) AND ((Watchers_5.Scope = ‘
Ticket’)) AND main.id = Watchers_1.Value AND main.id = Watchers_3.Value AND main.id = Watchers_5.Value ORDER BY main.id ASC LIMIT 49, 50;

when I describe the query I see:
| table | type | possible_keys | key | key_len | ref | rows | Extra |
| Watchers_1 | range | Watchers1 | Watchers1 | 17 | NULL | 10219 | where used; Using temporary; Using filesort |
| main | eq_ref | PRIMARY,Tickets4 | PRIMARY | 4 | Watchers_1.Value | 1 | |
| Watchers_3 | ALL | Watchers1 | NULL | NULL | NULL | 10219 | where used; Distinct |
| Watchers_5 | ALL | Watchers1 | NULL | NULL | NULL | 10219 | where used; Distinct |
| Users_6 | eq_ref | PRIMARY | PRIMARY | 4 | Watchers_5.Owner | 1 | Distinct |
| Users_2 | eq_ref | PRIMARY | PRIMARY | 4 | Watchers_1.Owner | 1 | Distinct |
| Users_4 | eq_ref | PRIMARY | PRIMARY | 4 | Watchers_3.Owner | 1 | where used; Distinct |

Can anyone tell me what this is and why it hangs for what seems like forever?

Thanks,
Jon

Hi,
Im running rt 2.0.9. About once every 2 weeks
the system seems to lock up. When I do a show full
processlist I see the query that is causing the
problem:

I’m more interested in what else showed up in the
show full proc listing. Was this the only entry,
or were there others?

I used to have a heck of a problem with MySQL’s
default locking granularity (which is table-level)
with a number of different apps. Readers and writers
blocking each other at that coarse a granularity
will kill you.

Try using InnoDB or BDB tables instead within the
database, if you in fact find this to be your issue.
Otherwise, migrate to another database like Oracle.

Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

There where a few other procs, all small select statements and update
statements, and all in a “locked” state. I don’t want to have to upgrade to
Oracle for a problem that only happens every few weeks. For the most part
the system is very fast. If there any way to isolate this query and remove
the ability for rt to do perform it?

JonOn Thursday 19 September 2002 06:12 pm, Mark E. Dawson, Jr. wrote:

Hi,
Im running rt 2.0.9. About once every 2 weeks
the system seems to lock up. When I do a show full
processlist I see the query that is causing the
problem:

I’m more interested in what else showed up in the
show full proc listing. Was this the only entry,
or were there others?

I used to have a heck of a problem with MySQL’s
default locking granularity (which is table-level)
with a number of different apps. Readers and writers
blocking each other at that coarse a granularity
will kill you.

Try using InnoDB or BDB tables instead within the
database, if you in fact find this to be your issue.
Otherwise, migrate to another database like Oracle.


Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

I used to get this too, it happens when someone does a
search for tickets, limiting on the requestor, thats a slow
query, especially since it doesn’t seem to be indexable
(someone please send me in the index if I’m wrong). And it
gets REALLY bad when you try and limit 2 requestors…

I got around that by making it so you cannot limit more than
one requestor, not great, but better than bringing the db to a
halt.

The reason the db is grinding to a halt is due to mysql’s default
behaviour of table level locking, as was suggested innodb tables
should fix this, as should moving to a db that has more advanced
locking that mysql :slight_smile:

Mat.

-----Original Message-----
From: rt-users-admin@lists.fsck.com
[mailto:rt-users-admin@lists.fsck.com]On Behalf Of Jon Hittner
Sent: Friday, 20 September 2002 8:26 AM
To: Mark E. Dawson, Jr.; rt-users@lists.fsck.com
Subject: Re: [rt-users] Bad query?

There where a few other procs, all small select statements and update
statements, and all in a “locked” state. I don’t want to have to
upgrade to
Oracle for a problem that only happens every few weeks. For the
most part
the system is very fast. If there any way to isolate this query
and remove
the ability for rt to do perform it?

Jon

Hi,
Im running rt 2.0.9. About once every 2 weeks
the system seems to lock up. When I do a show full
processlist I see the query that is causing the
problem:

I’m more interested in what else showed up in the
show full proc listing. Was this the only entry,
or were there others?

I used to have a heck of a problem with MySQL’s
default locking granularity (which is table-level)
with a number of different apps. Readers and writers
blocking each other at that coarse a granularity
will kill you.

Try using InnoDB or BDB tables instead within the
database, if you in fact find this to be your issue.
Otherwise, migrate to another database like Oracle.


Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com


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

Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.389 / Virus Database: 220 - Release Date: 16/09/2002

Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.389 / Virus Database: 220 - Release Date: 16/09/2002

There where a few other procs, all small select
statements and update
statements, and all in a “locked” state. I don’t
want to have to upgrade to
Oracle for a problem that only happens every few
weeks.

You don’t have to upgrade to Oracle. As I stated
before, you can do a dump of the DDL, append the
table type of INNODB to the create statements, and
thus be able to use tables with row-level locking.
This way, you can stay on MySQL.

Or, you could go a half-step up and use BDB tables
within MySQL, which would give you page-level lock-
ing, or something to that effect.

Either way, you have options that will keep you on
MySQL. Personally, I’ve just finished demo-ing and
testing RT with upper management, and everyone just
loves the tool. My plan is to migrate this to pro-
duction, but to convert the tables to InnoDB, since
we’ll have 250+ employees creating, updating, and
searching tickets all at once.

Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

Thanks very much… I will try converting to INNODB tables.

JonOn Thursday 19 September 2002 07:11 pm, Mark E. Dawson, Jr. wrote:

There where a few other procs, all small select
statements and update
statements, and all in a “locked” state. I don’t
want to have to upgrade to
Oracle for a problem that only happens every few
weeks.

You don’t have to upgrade to Oracle. As I stated
before, you can do a dump of the DDL, append the
table type of INNODB to the create statements, and
thus be able to use tables with row-level locking.
This way, you can stay on MySQL.

Or, you could go a half-step up and use BDB tables
within MySQL, which would give you page-level lock-
ing, or something to that effect.

Either way, you have options that will keep you on
MySQL. Personally, I’ve just finished demo-ing and
testing RT with upper management, and everyone just
loves the tool. My plan is to migrate this to pro-
duction, but to convert the tables to InnoDB, since
we’ll have 250+ employees creating, updating, and
searching tickets all at once.


Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com