[rt-users] Slow PostgreSQL perf with 3.8.7

[Kenneth Marshall]

Hi Jeff,

Wow, is that version of PostgreSQL old. Certainly there have
been many, many performance improvements since v8.1.x. While
upgrading would help, you really need to run an EXPLAIN ANALYZE
for your slow queries to see if there is a problem area. Also,
you should check to wiki to see if you have all of the recommended
PostgreSQL indexes.

Some things to check.
Ken

On Wed, Sep 22, 2010 at 12:12:59PM -0400, Jeff Blaine wrote:
> 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
> #
>
> RT Training in Washington DC, USA on Oct 25 & 26 2010
> Last one this year -- Learn how to get the most out of RT!
>