[rt-users] Slow PostgreSQL perf with 3.8.7

[Jeff Blaine]

Please bear with my ignorance on this topic.  I realize
this isn't a PostgreSQL tuning forum, but since it's RT-related
I thought I'd ask here.

RT 3.8.7 (and RTFM) with PostgreSQL 8.1.18 via RHELv5 running
as a VM.

We're experiencing significant delays today with various
PostgreSQL statements taking longer than 1000ms to complete.

We have 10,000 tickets, 1300 of which are 'open'

Can anyone throw out any ideas, things to check, etc?
Is this just

Viewing a single ticket results in the following, showing
only those longer than 1000ms:

LOG:  duration: 1496.023 ms
LOG:  duration: 1496.023 ms  statement: EXECUTE <unnamed>  [PREPARE: 
SELECT DISTINCT main.Id AS id, main.Filename AS filename, main.Headers 
AS headers, main.Subject AS subject, main.Parent AS parent, 
main.ContentEncoding AS contentencoding, main.ContentType AS 
contenttype, main.TransactionId AS transactionid, main.Created AS 
created FROM Attachments main JOIN Transactions Transactions_1  ON ( 
Transactions_1.id = main.TransactionId ) JOIN Tickets Tickets_2  ON ( 
Tickets_2.id = Transactions_1.ObjectId )  WHERE (Tickets_2.EffectiveId = 
'35803') AND (Transactions_1.ObjectType = 'RT::Ticket')  ORDER BY 
main.id ASC ]
LOG:  duration: 2207.968 ms
LOG:  duration: 2207.968 ms  statement: EXECUTE <unnamed>  [PREPARE: 
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' OR ACL_4.RightName = 'SuperUser') AND 
(Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = '1') AND 
((ACL_4.ObjectType = 'RT::Queue' AND ACL_4.ObjectId = 1) OR 
(ACL_4.ObjectType = 'RT::System'))  ORDER BY main.Name ASC ]
LOG:  duration: 3363.967 ms
LOG:  duration: 3363.967 ms  statement: EXECUTE <unnamed>  [PREPARE: 
SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_2 JOIN 
Principals Principals_1  ON ( Principals_1.id = main.id ) JOIN 
CachedGroupMembers CachedGroupMembers_3  ON ( 
CachedGroupMembers_3.MemberId = Principals_1.id )  WHERE 
(Principals_1.Disabled = '0') AND (ACL_2.PrincipalId = 
CachedGroupMembers_3.GroupId) AND (Principals_1.id != '1') AND 
(ACL_2.PrincipalType = 'Group') AND (Principals_1.PrincipalType = 
'User') AND (ACL_2.RightName = 'OwnTicket' OR ACL_2.RightName = 
'SuperUser') AND ((ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId   = 
1) OR (ACL_2.ObjectType = 'RT::System'))  ORDER BY main.Name ASC ]
LOG:  duration: 1482.014 ms
LOG:  duration: 1482.014 ms  statement: EXECUTE <unnamed>  [PREPARE: 
SELECT DISTINCT main.id AS id, main.Content AS content, main.ContentType 
AS contenttype, main.TransactionId AS transactionid, 
main.ContentEncoding AS contentencoding FROM Attachments main JOIN 
Transactions Transactions_1  ON ( Transactions_1.id = main.TransactionId 
) JOIN Tickets Tickets_2  ON ( Tickets_2.id = Transactions_1.ObjectId ) 
  WHERE (Tickets_2.EffectiveId = '35803') AND (Transactions_1.ObjectType 
= 'RT::Ticket') AND (main.ContentType = 'text/plain' OR main.ContentType 
LIKE 'message/%' OR main.ContentType = 'text')  ORDER BY main.id ASC ]
LOG:  duration: 1329.789 ms
LOG:  duration: 1329.789 ms  statement: EXECUTE <unnamed>  [PREPARE: 
SELECT DISTINCT main.* FROM Transactions main JOIN Tickets Tickets_1  ON 
( Tickets_1.id = main.ObjectId )  WHERE (main.ObjectType = 'RT::Ticket') 
AND (Tickets_1.EffectiveId = '35803')  ORDER BY main.Created ASC, 
main.id ASC ]


# /sbin/sysctl kernel.shmmax kernel.shmall kernel.shmmni kernel.sem
kernel.shmmax = 4294967296
kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.sem = 250        32000   100     128
# cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 15
model name      : Intel(R) Xeon(R) CPU           X5365  @ 3.00GHz
stepping        : 1
cpu MHz         : 2992.499
cache size      : 4096 KB
fpu             : yes
fpu_exception   : yes
cpuid level     : 10
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge 
mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss syscall nx lm 
constant_tsc up pni ssse3 cx16 lahf_lm
bogomips        : 5984.99
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

# cat /proc/meminfo
MemTotal:      2059588 kB
MemFree:        664456 kB
Buffers:         21620 kB
Cached:         612024 kB
SwapCached:      25724 kB
Active:        1164032 kB
Inactive:       116264 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:      2059588 kB
LowFree:        664456 kB
SwapTotal:     2064376 kB
SwapFree:      1997364 kB
Dirty:             400 kB
Writeback:           0 kB
AnonPages:      644712 kB
Mapped:          75512 kB
Slab:            78748 kB
PageTables:      16492 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:   3094168 kB
Committed_AS:  1383072 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    263920 kB
VmallocChunk: 34359473927 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB
#