[rt-users] Performance Issues after 3.8.0 upgrade -- PostgreSQL delays

[Jessie Bryan]

On Wed, Jul 16, 2008 at 8:58 PM, Jessie Bryan <jessie.bryan at gmail.com> wrote:
> Update-
>
> Ok, after several hours of logging, what I see most common in the slow
> query (1s) log is:
>
> LOG:  duration: 3151.786 ms  statement: EXECUTE <unnamed>  [PREPARE:
> SELECT  * FROM Groups WHERE LOWER(Domain) = LOWER($1) AND LOWER(Type)
> = LOWER($2)]
>
> Several hundred of these queries in the slow query log data.
>

Any suggestions - or more information I can provide? the slow query
log is pretty lengthy...
It's most common slow query is listed above, as well as many of these below:

<2008-07-17 12:49:03 PDT> LOG:  duration: 3737.479 ms  statement:
EXECUTE <unnamed>  [PREPARE:  SELECT  * FROM GroupMembers WHERE
GroupId = $1 AND MemberId = $2]

<2008-07-17 17:20:43 PDT> LOG:  duration: 8930.945 ms  statement:
SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4 JOIN
Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2  ON (
CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3
 ON ( Groups_3.id = CachedGroupMembers_2.GroupId )  WHERE
(Principals_1.Disabled = '0') AND (ACL_4.PrincipalType =
Groups_3.Type) AND (Principals_1.id != '1') AND
(Principals_1.PrincipalType = 'User') AND (ACL_4.RightName =
'OwnTicket') AND (Groups_3.Domain = 'RT::System-Role') AND
((ACL_4.ObjectType = 'RT::Queue') OR (ACL_4.ObjectType =
'RT::System'))  ORDER BY main.Name ASC

<2008-07-17 13:42:06 PDT> LOG:  duration: 9115.251 ms  statement:
SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN
Groups Groups_1  ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2  ON (
CachedGroupMembers_2.MemberId = Users_3.id ) AND (
CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE (Users_3.id =
'5698') AND (main.Status != 'deleted') AND ( ( CachedGroupMembers_2.id
IS NOT NULL )  AND  ( main.Status = 'open' OR main.Status = 'new' ) )
AND (main.Type = 'ticket') AND (main.EffectiveId = main.id)  ORDER BY
main.Priority DESC  LIMIT 10

<2008-07-17 13:42:12 PDT> LOG:  duration: 9128.360 ms  statement:
SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN
Groups Groups_1  ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND (
Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN
CachedGroupMembers CachedGroupMembers_2  ON (
CachedGroupMembers_2.MemberId = Users_3.id ) AND (
CachedGroupMembers_2.GroupId = Groups_1.id )  WHERE (Users_3.id =
'5698') AND (main.Status != 'deleted') AND ( ( CachedGroupMembers_2.id
IS NOT NULL )  AND  ( main.Status = 'open' OR main.Status = 'new' ) )
AND (main.Type = 'ticket') AND (main.EffectiveId = main.id)  ORDER BY
main.Priority DESC  LIMIT 10