Crazy sql during "reply" action

Hi all,

I’m running RT 3.0.0 with Postgres 7.3.2. It’s running at an average speed,
but when I click on the Reply link to eply to a ticket, the database jumps
up to 100% CPU usage for about 15 minutes, before it finally returns and I
am presented wi th the Update ticket page for sending correspondance.

A bit of investigation showed that the database is choking on this SQL
query:

SELECT DISTINCT main.*
FROM Users main, Principals Principals_1, Groups Groups_2,
Principals Principals_3, Principals Principals_4, ACL ACL_5,
CachedGroupMembers CachedGroupMembers_6,
CachedGroupMembers CachedGroupMembers_7
WHERE ((ACL_5.RightName = ‘SuperUser’)OR(ACL_5.RightName = ‘OwnTicket’)) AND
((CachedGroupMembers_7.GroupId = ‘4’)) AND
((Principals_1.Disabled = ‘0’)) AND
((Principals_1.PrincipalType = ‘User’)) AND
((Principals_3.PrincipalType = ‘User’)) AND
(ACL_5.ObjectType = ‘RT::System’ OR
(ACL_5.ObjectType = ‘RT::Queue’ AND ACL_5.ObjectId = ‘4’)) AND
((ACL_5.PrincipalId = Principals_4.Id AND
Principals_4.id = Groups_2.Id AND
ACL_5.PrincipalType = ‘Group’ AND
(Groups_2.Domain = ‘SystemInternal’ OR
Groups_2.Domain = ‘UserDefined’ OR
Groups_2.Domain = ‘ACLEquivalence’)) OR
(((Groups_2.Domain = ‘RT::Queue-Role’ AND
Groups_2.Instance = ‘4’) OR
(Groups_2.Domain = ‘RT::Ticket-Role’ AND
Groups_2.Instance = ‘376’)) AND
Groups_2.Type = ACL_5.PrincipalType AND
Groups_2.Id = Principals_4.id AND
Principals_4.PrincipalType = ‘Group’)) AND
main.id = Principals_1.id AND
Principals_1.id = CachedGroupMembers_7.MemberId AND
main.id = Principals_3.id AND
CachedGroupMembers_6.MemberId = Principals_3.Id AND
CachedGroupMembers_6.GroupId = Principals_4.Id
ORDER BY main.Name ASC

Now, my first question is: What is this query trying to do? I ran it
manually, it looks like it’s trying to fill out the Owner dropdown box, i.e.
finding all the users who could possibly own this ticket.

My second question is: did you write this query by hand? :slight_smile: It’s quite
insane.

Finally, what can I do to speed this up? 15 minutes is incredibly
undesirable to wait to reply to a ticket, we may as well not bother using
RT.

I’ve already checked postgres’s configuration for tuning, and checked the
indexes relating to this query and they seem okay. I’m not sure that this
is a problem with the database, though, only this query.

jaq@spacepants.org http://spacepants.org/jaq.gpg

You may want to follow the ongoing discussion of this issue on rt-devel.
It’s a known problem and it’s being worked on.

-jOn Fri, Apr 11, 2003 at 04:31:33PM +1000, Jamie Wilkinson wrote:

Hi all,

I’m running RT 3.0.0 with Postgres 7.3.2. It’s running at an average speed,
but when I click on the Reply link to eply to a ticket, the database jumps
up to 100% CPU usage for about 15 minutes, before it finally returns and I
am presented wi th the Update ticket page for sending correspondance.

A bit of investigation showed that the database is choking on this SQL
query:

SELECT DISTINCT main.*
FROM Users main, Principals Principals_1, Groups Groups_2,
Principals Principals_3, Principals Principals_4, ACL ACL_5,
CachedGroupMembers CachedGroupMembers_6,
CachedGroupMembers CachedGroupMembers_7
WHERE ((ACL_5.RightName = ‘SuperUser’)OR(ACL_5.RightName = ‘OwnTicket’)) AND
((CachedGroupMembers_7.GroupId = ‘4’)) AND
((Principals_1.Disabled = ‘0’)) AND
((Principals_1.PrincipalType = ‘User’)) AND
((Principals_3.PrincipalType = ‘User’)) AND
(ACL_5.ObjectType = ‘RT::System’ OR
(ACL_5.ObjectType = ‘RT::Queue’ AND ACL_5.ObjectId = ‘4’)) AND
((ACL_5.PrincipalId = Principals_4.Id AND
Principals_4.id = Groups_2.Id AND
ACL_5.PrincipalType = ‘Group’ AND
(Groups_2.Domain = ‘SystemInternal’ OR
Groups_2.Domain = ‘UserDefined’ OR
Groups_2.Domain = ‘ACLEquivalence’)) OR
(((Groups_2.Domain = ‘RT::Queue-Role’ AND
Groups_2.Instance = ‘4’) OR
(Groups_2.Domain = ‘RT::Ticket-Role’ AND
Groups_2.Instance = ‘376’)) AND
Groups_2.Type = ACL_5.PrincipalType AND
Groups_2.Id = Principals_4.id AND
Principals_4.PrincipalType = ‘Group’)) AND
main.id = Principals_1.id AND
Principals_1.id = CachedGroupMembers_7.MemberId AND
main.id = Principals_3.id AND
CachedGroupMembers_6.MemberId = Principals_3.Id AND
CachedGroupMembers_6.GroupId = Principals_4.Id
ORDER BY main.Name ASC

Now, my first question is: What is this query trying to do? I ran it
manually, it looks like it’s trying to fill out the Owner dropdown box, i.e.
finding all the users who could possibly own this ticket.

My second question is: did you write this query by hand? :slight_smile: It’s quite
insane.

Finally, what can I do to speed this up? 15 minutes is incredibly
undesirable to wait to reply to a ticket, we may as well not bother using
RT.

I’ve already checked postgres’s configuration for tuning, and checked the
indexes relating to this query and they seem okay. I’m not sure that this
is a problem with the database, though, only this query.


jaq@spacepants.org http://spacepants.org/jaq.gpg


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

http://www.bestpractical.com/rt – Trouble Ticketing. Free.

This one time, at band camp, Jesse Vincent wrote:

You may want to follow the ongoing discussion of this issue on rt-devel.
It’s a known problem and it’s being worked on.

Cool, thanks.

jaq@spacepants.org http://spacepants.org/jaq.gpg

15 minutes - check if you are running out of disk space while this query is
running. When postgres has used up its sort ram it will writ
pg_sorttempXXXX files to the database directory and do sorts on disk. If
you have a bad query, this will go on until you run out of disk space, at
which time the postgres backend you were talking to will die and clean up
all its temp sort files (so you dont’ see the evidence). You have to do a
disk usage while it’s running and see if that’s the cause.

Hi all,

I’m running RT 3.0.0 with Postgres 7.3.2. It’s running at an average speed,
but when I click on the Reply link to eply to a ticket, the database jumps
up to 100% CPU usage for about 15 minutes, before it finally returns and I
am presented wi th the Update ticket page for sending correspondance.

A bit of investigation showed that the database is choking on this SQL
query:

SELECT DISTINCT main.*
FROM Users main, Principals Principals_1, Groups Groups_2,
Principals Principals_3, Principals Principals_4, ACL ACL_5,
CachedGroupMembers CachedGroupMembers_6,
CachedGroupMembers CachedGroupMembers_7
WHERE ((ACL_5.RightName = ‘SuperUser’)OR(ACL_5.RightName = ‘OwnTicket’)) AND
((CachedGroupMembers_7.GroupId = ‘4’)) AND
((Principals_1.Disabled = ‘0’)) AND
((Principals_1.PrincipalType = ‘User’)) AND
((Principals_3.PrincipalType = ‘User’)) AND
(ACL_5.ObjectType = ‘RT::System’ OR
(ACL_5.ObjectType = ‘RT::Queue’ AND ACL_5.ObjectId = ‘4’)) AND
((ACL_5.PrincipalId = Principals_4.Id AND
Principals_4.id = Groups_2.Id AND
ACL_5.PrincipalType = ‘Group’ AND
(Groups_2.Domain = ‘SystemInternal’ OR
Groups_2.Domain = ‘UserDefined’ OR
Groups_2.Domain = ‘ACLEquivalence’)) OR
(((Groups_2.Domain = ‘RT::Queue-Role’ AND
Groups_2.Instance = ‘4’) OR
(Groups_2.Domain = ‘RT::Ticket-Role’ AND
Groups_2.Instance = ‘376’)) AND
Groups_2.Type = ACL_5.PrincipalType AND
Groups_2.Id = Principals_4.id AND
Principals_4.PrincipalType = ‘Group’)) AND
main.id = Principals_1.id AND
Principals_1.id = CachedGroupMembers_7.MemberId AND
main.id = Principals_3.id AND
CachedGroupMembers_6.MemberId = Principals_3.Id AND
CachedGroupMembers_6.GroupId = Principals_4.Id
ORDER BY main.Name ASC

Now, my first question is: What is this query trying to do? I ran it
manually, it looks like it’s trying to fill out the Owner dropdown box, i.e.
finding all the users who could possibly own this ticket.

My second question is: did you write this query by hand? :slight_smile: It’s quite
insane.

Finally, what can I do to speed this up? 15 minutes is incredibly
undesirable to wait to reply to a ticket, we may as well not bother using
RT.

I’ve already checked postgres’s configuration for tuning, and checked the
indexes relating to this query and they seem okay. I’m not sure that this
is a problem with the database, though, only this query.


jaq@spacepants.org http://spacepants.org/jaq.gpg


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

Nicolae P. Costescu, Ph.D. / Senior Developer
Stronghold Technologies
46040 Center Oak Plaza, Suite 160 / Sterling, Va 20166
Tel: 571-434-1472 / Fax: 571-434-1478

This one time, at band camp, Nicolae P. Costescu wrote:

15 minutes - check if you are running out of disk space while this query is
running. When postgres has used up its sort ram it will writ
pg_sorttempXXXX files to the database directory and do sorts on disk. If
you have a bad query, this will go on until you run out of disk space, at
which time the postgres backend you were talking to will die and clean up
all its temp sort files (so you dont’ see the evidence). You have to do a
disk usage while it’s running and see if that’s the cause.

Nah, the machine has more disk than we really know what to do with, and it’s
certainly not running out of RAM. As Jesse said, the thread on rt-devel
contained some of the answers to my questions – it’s a problem with the way
the queries are being created.

Short of hacking on it (DBIx::SearchBuilder, I think) myself, I’m going to
hope that the guys who are already working on it come up with a good
solution soon :slight_smile:

jaq@spacepants.org http://spacepants.org/jaq.gpg

This one time, at band camp, Jesse Vincent wrote:

You may want to follow the ongoing discussion of this issue on rt-devel.
It’s a known problem and it’s being worked on.

I’ve just installed DBIx::SearchBuilder 0.81_02. I can’t see any noticeable
improvement in speed.

I ran vacuum full just before attempting the query. It took 15 almost
exactly 15 minutes from the moment I clicked “Reply” until the page had
finished loading. Postgres took about 13:53 in cpu time to complete the
query.

jaq@spacepants.org http://spacepants.org/jaq.gpg

Can you tell me exactly what query it was stuck on? 13 minutes on a
query really sounds like your system must be thrashing to hell and back.
Is the machine swapping heavily?On Mon, Apr 14, 2003 at 02:59:34PM +1000, Jamie Wilkinson wrote:

This one time, at band camp, Jesse Vincent wrote:

You may want to follow the ongoing discussion of this issue on rt-devel.
It’s a known problem and it’s being worked on.

I’ve just installed DBIx::SearchBuilder 0.81_02. I can’t see any noticeable
improvement in speed.

I ran vacuum full just before attempting the query. It took 15 almost
exactly 15 minutes from the moment I clicked “Reply” until the page had
finished loading. Postgres took about 13:53 in cpu time to complete the
query.


jaq@spacepants.org http://spacepants.org/jaq.gpg


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

http://www.bestpractical.com/rt – Trouble Ticketing. Free.

This one time, at band camp, Jesse Vincent wrote:

Can you tell me exactly what query it was stuck on? 13 minutes on a
query really sounds like your system must be thrashing to hell and back.
Is the machine swapping heavily?

As I said ealier this thread (and I’ve just rechecked now to make sure, by
turning statement logging on in postgres) the query is this:

SELECT DISTINCT main.* FROM Users main, Principals Principals_1, Groups Groups_2, Principals Principals_3, Principals Principals_4, ACL ACL_5, CachedGroupMembers CachedGroupMembers_6, CachedGroupMembers CachedGroupMembers_7 WHERE ((ACL_5.RightName = ‘SuperUser’)OR(ACL_5.RightName = ‘OwnTicket’)) AND ((CachedGroupMembers_7.GroupId = ‘4’)) AND ((Principals_1.Disabled = ‘0’)) AND ((Principals_1.PrincipalType = ‘User’)) AND ((Principals_3.PrincipalType = ‘User’)) AND (ACL_5.ObjectType = ‘RT::System’ OR (ACL_5.ObjectType = ‘RT::Queue’ AND ACL_5.ObjectId = ‘4’) ) AND ( (ACL_5.PrincipalId = Principals_4.Id AND Principals_4.id = Groups_2.Id AND ACL_5.PrincipalType = ‘Group’ AND (Groups_2.Domain = ‘SystemInternal’ OR Groups_2.Domain = ‘UserDefined’ OR Groups_2.Domain = ‘ACLEquivalence’)) OR ( ( (Groups_2.Domain = ‘RT::Queue-Role’ AND Groups_2.Instance = ‘4’) OR ( Groups_2.Domain = ‘RT::Ticket-Role’ AND Groups_2.Instance = ‘376’) ) AND Groups_2.Type = ACL_5.PrincipalType AND Groups_2.Id = Principals_4.id AND Principals_4.PrincipalType = ‘Group’) ) AND main.id = Principals_1.id AND Principals_1.id = CachedGroupMembers_7.MemberId AND main.id = Principals_3.id AND CachedGroupMembers_6.MemberId = Principals_3.Id AND CachedGroupMembers_6.GroupId = Principals_4.Id ORDER BY main.Name ASC

Which I believe to be the query that’s filling out the Owner: dropdown list on
the Update ticket page.

The machine isn’t swapping excessively, there is almost no load on it at all
until this query hits the database.

I’ve added the output of EXPLAIN on that queryi below, which is
unintelligible to me but our postgres guru thinks is odd in that the nesting
has that V pattern.

Given that I can’t see any speedup in the query, is there any way for me to
check that RT is using the right version of DBIx::SearchBuilder?

Unique (cost=176.88…176.97 rows=1 width=1433)
-> Sort (cost=176.88…176.89 rows=1 width=1433)
Sort Key: main.name, main.id, main.“password”, main.comments, main.signature, main.emailaddress, main.freeformcontactinfo, main.organization, main.realname, main.nickname, main.lang, main.emailencoding, main.webencoding, main.externalcontactinfoid, main.contactinfosystem, main.externalauthid, main.authsystem,
main.gecos, main.homephone, main.workphone, main.mobilephone, main.pagerphone, main.address1, main.address2, main.city, main.state, main.zip, main.country, main.timezone, main.pgpkey, main.creator, main.created, main.lastupdatedby, main.lastupdated
-> Nested Loop (cost=32.57…176.87 rows=1 width=1433)
Join Filter: (((“outer”.id = “inner”.id) OR (“inner”.“domain” = ‘RT::Ticket-Role’::character varying) OR (“inner”.“domain” = ‘RT::Queue-Role’::character varying)) AND ((“outer”.id = “inner”.id) OR (“inner”.instance = ‘376’::character varying) OR (“inner”.“domain” = ‘RT::Queue-Role’::character varying)) AND ((“outer”.id = “inner”.id) OR (“inner”.“domain” = ‘RT::Ticket-Role’::character varying) OR (“inner”.instance = ‘4’::character varying)) AND ((“outer”.id = “inner”.id) OR (“inner”.instance = ‘376’::character varying) OR (“inner”.instance = ‘4’::character varying)) AND ((“outer”.id = “inner”.id) OR (“inner”.id = “outer”.id)) AND ((“inner”.“domain” = ‘SystemInternal’::character varying) OR (“inner”.“domain” = ‘UserDefined’::character varying) OR (“inner”.“domain” = ‘ACLEquivalence’::character varying) OR (“inner”.id = “outer”.id)) AND ((“outer”.id = “inner”.id) OR (“outer”.principaltype = ‘Group’::character varying)) AND ((“inner”.“domain” = ‘SystemInternal’::character varying) OR (“inner”.“domain” = ‘UserDefined’::character varying) OR (“inner”.“domain” = ‘ACLEquivalence’::character varying) OR (“outer”.principaltype = ‘Group’::character varying)) AND ((“outer”.principalid = “outer”.id) OR (“inner”.“domain” = ‘RT::Ticket-Role’::character varying) OR (“inner”.“domain” = ‘RT::Queue-Role’::character varying)) AND ((“outer”.principalid = “outer”.id) OR (“inner”.instance = ‘376’::character varying) OR (“inner”.“domain” = ‘RT::Queue-Role’::character varying)) AND ((“outer”.principalid = “outer”.id) OR (“inner”.“domain” = ‘RT::Ticket-Role’::character varying) OR (“inner”.instance = ‘4’::character varying)) AND ((“outer”.principalid = “outer”.id) OR (“inner”.instance = ‘376’::character varying) OR (“inner”.instance = ‘4’::character varying)) AND ((“outer”.principalid = “outer”.id) OR (“inner”.“type” = “outer”.principaltype)) AND ((“outer”.id = “inner”.id) OR (“inner”.“type” = “outer”.principaltype)) AND ((“outer”.principalid = “outer”.id) OR (“inner”.id = “outer”.id)) AND ((“outer”.principaltype = ‘Group’::character varying) OR (“inner”.id = “outer”.id)) AND ((“outer”.principaltype = ‘Group’::character varying) OR (“inner”.“domain” = ‘RT::Ticket-Role’::character varying) OR (“inner”.“domain” =
‘RT::Queue-Role’::character varying)) AND ((“outer”.principaltype = ‘Group’::character varying) OR (“inner”.instance = ‘376’::character varying) OR (“inner”.“domain” = ‘RT::Queue-Role’::character varying)) AND ((“outer”.principaltype = ‘Group’::character varying) OR (“inner”.“domain” = ‘RT::Ticket-Role’::character varying) OR (“inner”.instance = ‘4’::character varying)) AND ((“outer”.principaltype = ‘Group’::character varying) OR (“inner”.instance = ‘376’::character varying)
OR (“inner”.instance = ‘4’::character varying)) AND ((“outer”.principaltype = ‘Group’::character varying) OR (“inner”.“type” = “outer”.principaltype)) AND ((“inner”.“domain” = ‘SystemInternal’::character varying) OR (“inner”.“domain” = ‘UserDefined’::character varying) OR (“inner”.“domain” = ‘ACLEquivalence’::character varying) OR (“inner”.“type” = “outer”.principaltype)))
-> Nested Loop (cost=32.57…64.27 rows=1 width=1396)
Join Filter: (((“inner”.principalid = “outer”.id) OR (“outer”.principaltype = ‘Group’::character varying)) AND ((“inner”.principaltype = ‘Group’::character varying) OR (“outer”.principaltype = ‘Group’::character varying)))
-> Nested Loop (cost=32.57…62.05 rows=1 width=1383)
Join Filter: (“outer”.id = “inner”.memberid)
-> Nested Loop (cost=32.57…56.11 rows=1 width=1379)
-> Nested Loop (cost=32.57…50.25 rows=1 width=1367)
-> Nested Loop (cost=32.57…40.08 rows=1 width=1359)
Join Filter: (“outer”.id = “inner”.id)
-> Hash Join (cost=32.57…34.21 rows=1 width=1355)
Hash Cond: (“outer”.id = “inner”.id)
-> Seq Scan on users main (cost=0.00…1.36 rows=36 width=1351)
-> Hash (cost=32.48…32.48 rows=37 width=4)
-> Seq Scan on principals principals_3 (cost=0.00…32.48 rows=37 width=4)
Filter: (principaltype = ‘User’::character varying)
-> Index Scan using principals_pkey on principals principals_1 (cost=0.00…5.86 rows=1 width=4)
Index Cond: (“outer”.id = principals_1.id)
Filter: ((disabled = 0::smallint) AND (principaltype = ‘User’::character varying))
-> Index Scan using cachedgroupmembers2 on cachedgroupmembers cachedgroupmembers_6 (cost=0.00…10.14 rows=2 width=8)
Index Cond: (cachedgroupmembers_6.memberid = “outer”.id)
-> Index Scan using principals_pkey on principals principals_4 (cost=0.00…5.85 rows=1 width=12)
Index Cond: (“outer”.groupid = principals_4.id)
-> Index Scan using disgroumem on cachedgroupmembers cachedgroupmembers_7 (cost=0.00…5.93 rows=1 width=4)
Index Cond: ((cachedgroupmembers_7.groupid = 4) AND (“outer”.id = cachedgroupmembers_7.memberid))
-> Seq Scan on acl acl_5 (cost=0.00…2.12 rows=5 width=13)
Filter: (((rightname = ‘SuperUser’::character varying) OR (rightname = ‘OwnTicket’::character varying)) AND ((objecttype = ‘RT::Queue’::character varying) OR (objecttype = ‘RT::System’::character varying)) AND ((objectid = 4) OR (objecttype = ‘RT::System’::character varying)))
-> Seq Scan on groups groups_2 (cost=0.00…112.06 rows=3 width=37)
Filter: (((“domain” = ‘SystemInternal’::character varying) OR (“domain” = ‘UserDefined’::character varying) OR (“domain” = ‘ACLEquivalence’::character varying) OR (“domain” = ‘RT::Ticket-Role’::character varying) OR (“domain” = ‘RT::Queue-Role’::character varying)) AND ((“domain” = ‘SystemInternal’::character varying) OR (“domain” = ‘UserDefined’::character varying) OR (“domain” = ‘ACLEquivalence’::character varying) OR (instance = ‘376’::character varying) OR (“domain” = ‘RT::Queue-Role’::character varying)) AND ((“domain” = ‘SystemInternal’::character varying) OR (“domain” = ‘UserDefined’::character varying) OR (“domain” = ‘ACLEquivalence’::character varying) OR (“domain” = ‘RT::Ticket-Role’::character varying) OR (instance = ‘4’::character varying)) AND ((“domain” = ‘SystemInternal’::character varying) OR (“domain” = ‘UserDefined’::character varying) OR (“domain” = ‘ACLEquivalence’::character varying) OR (instance = ‘376’::character varying) OR (instance = ‘4’::character varying)))
(32 rows)

jaq@spacepants.org http://spacepants.org/jaq.gpg

This one time, at band camp, Jesse Vincent wrote:

Can you tell me exactly what query it was stuck on? 13 minutes on a
query really sounds like your system must be thrashing to hell and back.
Is the machine swapping heavily?

As I said ealier this thread (and I’ve just rechecked now to make sure, by
turning statement logging on in postgres) the query is this:

SELECT DISTINCT main.* FROM Users main, Principals Principals_1, Groups Groups_2, Principals Principals_3, Principals Principals_4, ACL ACL_5, CachedGroupMembers CachedGroupMembers_6, CachedGroupMembers CachedGroupMembers_7 WHERE ((ACL_5.RightName = ‘SuperUser’)OR(ACL_5.RightName = ‘OwnTicket’)) AND ((CachedGroupMembers_7.GroupId = ‘4’)) AND ((Principals_1.Disabled = ‘0’)) AND ((Principals_1.PrincipalType = ‘User’)) AND ((Principals_3.PrincipalType = ‘User’)) AND (ACL_5.ObjectType = ‘RT::System’ OR (ACL_5.ObjectType = ‘RT::Queue’ AND ACL_5.ObjectId = ‘4’) ) AND ( (ACL_5.PrincipalId = Principals_4.Id AND Principals_4.id = Groups_2.Id AND ACL_5.PrincipalType = ‘Group’ AND (Groups_2.Domain = ‘SystemInternal’ OR Groups_2.Domain = ‘UserDefined’ OR Groups_2.Domain = ‘ACLEquivalence’)) OR ( ( (Groups_2.Domain = ‘RT::Queue-Role’ AND Groups_2.Instance = ‘4’) OR ( Groups_2.Domain = ‘RT::Ticket-Role’ AND Groups_2.Instance = ‘376’) ) AND Groups_2.Type = ACL_5.PrincipalType AND Groups_2.Id = Principals_4.id AND Principals_4.PrincipalType = ‘Group’) ) AND main.id = Principals_1.id AND Principals_1.id = CachedGroupMembers_7.MemberId AND main.id = Principals_3.id AND CachedGroupMembers_6.MemberId = Principals_3.Id AND CachedGroupMembers_6.GroupId = Principals_4.Id ORDER BY main.Name ASC

That query is very much not what is generated by 0.81_02 on my box. I
think you have an older SB installed somewhere in your @INC before
0.81_02

Given that I can’t see any speedup in the query, is there any way for me to
check that RT is using the right version of DBIx::SearchBuilder?

Without knowing the particulars of your install, I can’t tell you where
to look, but the “big hammer” is

find / -name SearchBuilder.pm


jaq@spacepants.org http://spacepants.org/jaq.gpg


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

http://www.bestpractical.com/rt – Trouble Ticketing. Free.

At 01:08 AM 14/04/2003 -0400, Jesse Vincent wrote:

I ran vacuum full just before attempting the query. It took 15 almost
exactly 15 minutes from the moment I clicked “Reply” until the page had
finished loading. Postgres took about 13:53 in cpu time to complete the
query.

You might like to try the following in postgresql.conf:

 log_statement = true
 log_timestamp = true

If you can’t find log_timestamp or log_statement, then your version of Pg
may be too old.

Once edited, send a HUP to the postmaster, and the log file should start
filling up.

Retry the long ‘refresh’, and see what it is doing. If there is one bad
query, you will see it reflected in the timestamps.

Philip Warner | ___
Albatross Consulting Pty. Ltd. |----/ -
(A.B.N. 75 008 659 498) | /(@)
Tel: (+61) 0500 83 82 81 | _________
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / |
| –
___–
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

This one time, at band camp, Philip Warner wrote:

At 01:08 AM 14/04/2003 -0400, Jesse Vincent wrote:

I ran vacuum full just before attempting the query. It took 15 almost
exactly 15 minutes from the moment I clicked “Reply” until the page had
finished loading. Postgres took about 13:53 in cpu time to complete the
query.

You might like to try the following in postgresql.conf:

Yeah, I might, so I had. That’s how I’d extracted the queries today and
last Friday. Thanks though.

jaq@spacepants.org http://spacepants.org/jaq.gpg

This one time, at band camp, Jesse Vincent wrote:

That query is very much not what is generated by 0.81_02 on my box. I
think you have an older SB installed somewhere in your @INC before
0.81_02

The big hammer shows me:

/home/jaq/rt-3-0-0/lib/RT/SearchBuilder.pm
/opt/rt3/lib/RT/SearchBuilder.pm
/usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder.pm

the last of which contains the line

$VERSION = “0.81_02”;

pause as he goes to edit RT/SearchBuilder.pm to log the version

Aha. It turns out I hadn’t reloaded the webserver and hence mod_perl hadn’t
reloaded the module. Now the query takes about 6 seconds, which is much more
tolerable. Thanks for that.

jaq@spacepants.org http://spacepants.org/jaq.gpg

This one time, at band camp, Jesse Vincent wrote:

That query is very much not what is generated by 0.81_02 on my box. I
think you have an older SB installed somewhere in your @INC before
0.81_02

The big hammer shows me:

/home/jaq/rt-3-0-0/lib/RT/SearchBuilder.pm
/opt/rt3/lib/RT/SearchBuilder.pm
/usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder.pm

the last of which contains the line

$VERSION = “0.81_02”;

pause as he goes to edit RT/SearchBuilder.pm to log the version

Aha. It turns out I hadn’t reloaded the webserver and hence mod_perl hadn’t
reloaded the module. Now the query takes about 6 seconds, which is much more
tolerable. Thanks for that.

Ah. Thank God. There are a few things I can do to get that down a good
deal further, but not before I head to Europe for a month on Tuesday.
I’m really glad to hear that we’ve beaten postgres into at least partial
submission.

-j


jaq@spacepants.org http://spacepants.org/jaq.gpg


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

http://www.bestpractical.com/rt – Trouble Ticketing. Free.

This one time, at band camp, Jesse Vincent wrote:

Ah. Thank God. There are a few things I can do to get that down a good
deal further, but not before I head to Europe for a month on Tuesday.
I’m really glad to hear that we’ve beaten postgres into at least partial
submission.

Cool, keep up the good work.

jaq@spacepants.org http://spacepants.org/jaq.gpg

“JW” == Jamie Wilkinson jaq@spacepants.org writes:

JW> I ran vacuum full just before attempting the query. It took 15 almost
JW> exactly 15 minutes from the moment I clicked “Reply” until the page had
JW> finished loading. Postgres took about 13:53 in cpu time to complete the
JW> query.

There has to be something wrong with your postgres installation. It
just is not that slow of a DB system, unless you’re starved for disk
I/O and/or memory.

Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/