[rt-users] Strange custom field performance issue after upgrading to RT 3.6.4
Jesse Vincent
jesse at bestpractical.com
Tue Oct 23 10:17:44 EDT 2007
On Oct 23, 2007, at 8:59 AM, Matthew Goheen wrote:
> We are in the process of upgrading our RT installation from 3.4.2
> (to 3.6.4).
> As part of the upgrade, we were planning to restructure RT to allow
> additional
> groups within our lab use RT (previously we just used it within our
> own group).
> Part of that plan involved moving from having multiple queues for
> our group to
> using just a single queue and using a custom field to store what
> was previously
> the queue name. I performed that change with no problem --
> however, performance
> on SOME queries that include cust
Have you done a "FULL VACUUM ANALYZE" recently? It looks like the
statistics engine is...confused. That's the best way to reset it.
> om fields is so slow that it is unusable.
>
> Here is the system background:
> RT 3.6.4
> RHEL 5 (with all updates through last week)
> Postgres 8.1.9-1.el5
> Apache 2.2.3.7.el5
> mod_perl 2.0.2-6.3.el5
> SearchBuilder 1.49
>
> The system has only about 7000 tickets and about 100 or so users
> (only about 10
> have rights to RT).
>
> The following query takes something like 165 seconds and returns
> three records:
>
> SELECT DISTINCT main.* FROM Tickets main JOIN ObjectCustomFields
> ObjectCustomFields_1
> ON ( ObjectCustomFields_1.ObjectId = '0' ) OR
> ( ObjectCustomFields_1.ObjectId = main.Queue )
> JOIN CustomFields CustomFields_2 ON ( CustomFields_2.id =
> ObjectCustomFields_1.CustomField )
> JOIN ObjectCustomFieldValues ObjectCustomFieldValues_3 ON
> ( ObjectCustomFieldValues_3.ObjectId = main.id )
> AND ( ObjectCustomFieldValues_3.Disabled = '0' )
> AND ( ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket' )
> AND ( ObjectCustomFieldValues_3.CustomField = CustomFields_2.id )
> WHERE (CustomFields_2.Name = 'Category') AND (main.Status !=
> 'deleted') AND (main.Owner = '368'
> AND ( main.Status = 'new' OR main.Status = 'open' OR main.Status =
> 'stalled' )
> AND main.Queue = '5' AND ( ObjectCustomFieldValues_3.Content =
> 'General' ) )
> AND (main.Type = 'ticket') AND (main.EffectiveId = main.id)
> ORDER BY main.id ASC
>
> Here is the Postgres explain plan output:
>
> QUERY PLAN
> ----------
> Unique (cost=17.16..17.23 rows=1 width=620) (actual
> time=165267.134..165267.161 rows=3 loops=1)
> -> Sort (cost=17.16..17.17 rows=1 width=620) (actual
> time=165267.127..165267.133 rows=3 loops=1)
> Sort Key: main.id, main.effectiveid, main.queue,
> main."type", main.issuestatement, main.resolution, main."owner",
> main.subject, main.initialpriority, main.finalpriority,
> main.priority, main.timeestimated, main.timeworked, main.status,
> main.timeleft, main.told, main.starts, main.started, main.due,
> main.resolved, main.lastupdatedby, main.lastupdated, main.creator,
> main.created, main.disabled
> -> Nested Loop (cost=5.02..17.15 rows=1 width=620)
> (actual time=32924.858..165266.929 rows=3 loops=1)
> Join Filter: ((("outer".objectid = 0) OR
> ("outer".objectid = "inner".queue)) AND ("outer".objectid =
> "inner".id))
> -> Nested Loop (cost=0.00..8.09 rows=1 width=8)
> (actual time=0.124..555.995 rows=53712 loops=1)
> -> Nested Loop (cost=0.00..2.07 rows=1
> width=12) (actual time=0.032..0.180 rows=9 loops=1)
> Join Filter: ("outer".id =
> "inner".customfield)
> -> Seq Scan on customfields
> customfields_2 (cost=0.00..1.02 rows=1 width=4) (actual
> time=0.018..0.036 rows=1 loops=1)
> Filter: ((name)::text =
> 'Category'::text)
> -> Seq Scan on objectcustomfields
> objectcustomfields_1 (cost=0.00..1.02 rows=2 width=8) (actual
> time=0.005..0.063 rows=17 loops=1)
> -> Index Scan using objectcustomfieldvalues2
> on objectcustomfieldvalues objectcustomfieldvalues_3
> (cost=0.00..6.00 rows=1 width=8) (actual time=0.044..35.073
> rows=5968 loops=9)
> Index Cond:
> ((objectcustomfieldvalues_3.customfield = "outer".id) AND
> ((objectcustomfieldvalues_3.objecttype)::text = 'RT::Ticket'::text))
> Filter: ((disabled = 0) AND
> ((content)::text = 'General'::text))
> -> Bitmap Heap Scan on tickets main
> (cost=5.02..9.05 rows=1 width=620) (actual time=3.002..3.052 rows=3
> loops=53712)
> Recheck Cond: (("owner" = 368) AND (queue = 5))
> Filter: (((status)::text <> 'deleted'::text)
> AND (((status)::text = 'new'::text) OR ((status)::text =
> 'open'::text) OR ((status)::text = 'stalled'::text)) AND
> (("type")::text = 'ticket'::text) AND (effectiveid = id))
> -> BitmapAnd (cost=5.02..5.02 rows=1
> width=0) (actual time=2.513..2.513 rows=0 loops=53712)
> -> Bitmap Index Scan on tickets2
> (cost=0.00..2.39 rows=110 width=0) (actual time=0.148..0.148
> rows=910 loops=53712)
> Index Cond: ("owner" = 368)
> -> Bitmap Index Scan on tickets1
> (cost=0.00..2.39 rows=110 width=0) (actual time=2.325..2.325
> rows=19050 loops=53712)
> Index Cond: (queue = 5)
> Total runtime: 165267.372 ms
> (23 rows)
>
> ----------
>
> At this point, I think this could be a Postgres bug. If I change
> the part of the query that
> specifies the custom field (the "ObjectCustomFieldValues_3.Content
> = 'General'") to instead
> EXCLUDE all non-matching fields, the query executes almost
> immediately -- e.g. using something
> like:
> (ObjectCustomFieldValues_3.Content != 'FY07' AND
> ObjectCustomFieldValues_3.Content != 'Upgrades' AND
> [etc.])
>
> Should I simply try upgrading Postgres, or does anyone have any
> ideas about what
> the REAL problem might be?
>
> Thanks,
> Matt Goheen
>
>
>
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> SAVE THOUSANDS OF DOLLARS ON RT SUPPORT:
>
> If you sign up for a new RT support contract before December 31,
> we'll take
> up to 20 percent off the price. This sale won't last long, so get
> in touch today.
> Email us at sales at bestpractical.com or call us at +1 617 812 0745.
>
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: PGP.sig
Type: application/pgp-signature
Size: 186 bytes
Desc: This is a digitally signed message part
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20071023/e21f1d5d/attachment.sig>
More information about the rt-users
mailing list