[rt-users] Slow Query on RT 4.2.4 after upgrade from 4.0
Rabin Yasharzadehe
rabin at isoc.org.il
Sun May 25 06:49:19 EDT 2014
>From RT Wiki: FAQ Page In most case it's a problem with User/Groups rights
that makes too much users appears in the select owner dropdown. If this
dropdown seems to contains more than wanted people, then check everywhere
(Global rights/ Queue rights) in RT rights to see if OwnTicket right is not
granted to Everyone, Unprivileged or a group that shouldn't have this right
and contains many people. You can also run the following SQL query on the
RT database to find each objects that grant OwnTicket:
SELECT <http://search.oracle.com/search/search?group=MySQL&q=SELECT> *
FROM <http://search.oracle.com/search/search?group=MySQL&q=FROM> ACL
where <http://search.oracle.com/search/search?group=MySQL&q=WHERE>
RightName='OwnTicket';
e.g:
1.
mysql> SELECT
<http://search.oracle.com/search/search?group=MySQL&q=SELECT> * FROM
<http://search.oracle.com/search/search?group=MySQL&q=FROM> ACL where
<http://search.oracle.com/search/search?group=MySQL&q=WHERE>
RightName='OwnTicket';
2.
3. +-----+---------------+-------------+-----------+------------+----------+---------+---------------------+---------------+---------------------+
4. | id | PrincipalType | PrincipalId | RightName | ObjectType |
ObjectId | Creator | Created | LastUpdatedBy | LastUpdated
|
5. +-----+---------------+-------------+-----------+------------+----------+---------+---------------------+---------------+---------------------+
6. | 316 | Group | 50 | OwnTicket | RT::Queue |
1 | 0 | NULL
<http://search.oracle.com/search/search?group=MySQL&q=NULL>
| 0 | NULL
<http://search.oracle.com/search/search?group=MySQL&q=NULL>
|
7. ..
8. ..
9.
..
10. | 557 | Group | 50 | OwnTicket | RT::Queue |
27 | 12 | 2012-06-03 13:07:19 | 12 | 2012-06-03
13:07:19 |
11. | 669 | AdminCc | 271319 | OwnTicket | RT::Queue |
28 | 12 | 2012-06-03 14:06:24 | 12 | 2012-06-03
14:06:24 |
12. | 723 | Owner | 271321 | OwnTicket | RT::Queue |
28 | 12 | 2012-06-03 14:06:24 | 12 | 2012-06-03
14:06:24 |
13. | 911 | Group | 349063 | OwnTicket | RT::Queue |
33 | 12 | 2013-02-26 10:57:44 | 12 | 2013-02-26
10:57:44 |
14. +-----+---------------+-------------+-----------+------------+----------+---------+---------------------+---------------+---------------------+
Here OwnTicket is granted to Owner on queue "28". Check this on the UI:
https://rt/Admin/Queues/GroupRights.html?id=28
I also found that adding index to several more
tables<http://blog.rabin.io/23/speed-up-and-improve-ticket-deleting-and-shredding-in-rt>will
speed up shredder as well,
--
Rabin
On Fri, May 23, 2014 at 7:11 PM, Patrick Muldoon <doon.bulk at inoc.net> wrote:
> Recently upgraded from 4.0 to 4.2 and have noticed that we are now getting
> slow queries on pretty much all ticket history queries. I ’ve been
> reading the list and attempting to google but I cannot seem to figure out
> what the cause is.
>
> This RT database started life 11 years ago and has been dragged along from
> upgrade to upgrade and this is the first time we’ve run into any real
> issue. I’ve compared our Schema with a freshly installed version of 4.2
> and don’t see any indexes missing, or anything along those lines.
>
> I think that it has something to do with the way either have groups setup,
> or permissions, since if I make my account an admin/super user all the
> queries are super fast..
>
> We are running on postgresql 9.3.4
>
> here is the query that is causing us issues..
>
> SQL(20.280673s):
>
> SELECT COUNT(DISTINCT main.id)
> FROM Tickets main
> JOIN Groups Groups_1 ON ( LOWER(Groups_1.Domain) = 'rt::ticket-role' )
> AND ( LOWER(Groups_1.Name) = 'requestor' ) AND ( Groups_1.Instance =
> main.id )
> JOIN Groups Groups_3 ON ( LOWER(Groups_3.Domain) = 'rt::ticket-role' )
> AND ( Groups_3.Instance = main.id )
> JOIN CachedGroupMembers CachedGroupMembers_2 ON (
> CachedGroupMembers_2.Disabled = '0' ) AND ( CachedGroupMembers_2.GroupId =
> Groups_1.id )
> LEFT JOIN CachedGroupMembers CachedGroupMembers_4 ON (
> CachedGroupMembers_4.Disabled = '0' ) AND ( CachedGroupMembers_4.MemberId =
> '22' ) AND ( CachedGroupMembers_4.GroupId = Groups_3.id )
> WHERE (
> (
> ( main.Queue = '1' OR main.Queue = '4' OR main.Queue = '5' ) OR
> ( main.Owner = '22' AND main.Queue = '2' ) OR
> ( CachedGroupMembers_4.MemberId IS NOT NULL AND
> LOWER(Groups_3.Name) = 'admincc' AND main.Queue = '2' )
> )
> )
> AND (main.IsMerged IS NULL)
> AND (main.Status != 'deleted')
> AND (main.Type = 'ticket')
> AND (
> ( CachedGroupMembers_2.MemberId = '364' ) AND
> ( LOWER(main.Status) = 'new' OR LOWER(main.Status) = 'open'
> OR LOWER(main.Status) = 'stalled' )
> )
>
>
> And then the next query which returns the data takes about the same time
> to run. so a small ticket takes around 40 seconds to render.
>
> and the query plan
>
> Aggregate (cost=37.50..37.51 rows=1 width=4)
> -> Nested Loop Left Join (cost=1.95..37.50 rows=1 width=4)
> Filter: ((main.queue = 1) OR (main.queue = 4) OR (main.queue = 5)
> OR ((main.owner = 22) AND (main.queue = 2)) OR
> ((cachedgroupmembers_4.memberid IS NOT NULL) AND (lower((groups_3.name)::text)
> = 'admincc'::text) AND (main.queue = 2)))
> -> Nested Loop (cost=1.53..30.61 rows=1 width=22)
> -> Nested Loop (cost=1.11..22.16 rows=1 width=26)
> Join Filter: (groups_1.instance = main.id)
> -> Nested Loop (cost=0.83..18.32 rows=1 width=22)
> -> Index Scan using groups2 on groups groups_1
> (cost=0.42..8.44 rows=1 width=8)
> Index Cond: ((lower((domain)::text) =
> 'rt::ticket-role'::text) AND (lower((name)::text) = 'requestor'::text))
> -> Index Scan using groups2 on groups groups_3
> (cost=0.41..9.87 rows=1 width=14)
> Index Cond: ((lower((domain)::text) =
> 'rt::ticket-role'::text) AND (instance = groups_1.instance))
> -> Index Scan using tickets_pkey on tickets main
> (cost=0.28..3.83 rows=1 width=12)
> Index Cond: (id = groups_3.instance)
> Filter: ((ismerged IS NULL) AND ((status)::text
> <> 'deleted'::text) AND ((type)::text = 'ticket'::text) AND ((queue = 1) OR
> (queue = 4) OR (queue = 5) OR (queue = 2) OR (queue = 2)) AND
> ((lower((status)::text) = 'new'::text) OR (lower((status)::text) =
> 'open'::text) OR (lower((status)::text) = 'stalled'::text)))
> -> Index Only Scan using disgroumem on cachedgroupmembers
> cachedgroupmembers_2 (cost=0.41..8.44 rows=1 width=4)
> Index Cond: ((groupid = groups_1.id) AND (memberid =
> 364) AND (disabled = 0::smallint))
> -> Index Only Scan using disgroumem on cachedgroupmembers
> cachedgroupmembers_4 (cost=0.41..6.85 rows=1 width=8)
> Index Cond: ((groupid = groups_3.id) AND (memberid = 22)
> AND (disabled = 0::smallint))
> (18 rows)
>
>
> So pointers on places to look or things to try would be very helpful. I
> am still trying to wrap my head around why if I am root it doesn’t slow
> down, which leads me to think it has something to do with the way we have
> permissions setup.
>
>
> Thanks,
> -Patrick
>
> --
> Patrick Muldoon
> Network/Software Engineer
> INOC (http://www.inoc.net)
> PGPKEY (http://www.inoc.net/~doon)
> Key ID: 0x2D808DE5
>
> I do not fear computers. I fear the lack of them. - Isaac Asimov
>
> --
> RT Training - Boston, September 9-10
> http://bestpractical.com/training
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20140525/52e43e1a/attachment.htm>
More information about the rt-users
mailing list