[rt-users] Slow PostgreSQL perf with 3.8.7

Kenneth Marshall ktm at rice.edu
Wed Sep 22 15:25:38 EDT 2010


Hi Jeff,

Just a quick glance at your plan seems to indicate that there is
a fairly wide discrepency between how many results a query returns
and what the planner thinks the query should return. Have you already
bumped the planner statistics target to 100 or 200 and then re-analyzed
the database. I do not think that 8.1 had autovacuum enabled by default
so you should enable it to keep the statistics updated and run a full
database-wide re-analyze to update the stats. Then try your query again.

Regards,
Ken

On Wed, Sep 22, 2010 at 12:57:58PM -0400, Jeff Blaine wrote:
> We have this in place:
>
>     CREATE INDEX ObjectCustomFieldValues3 ON ObjectCustomFieldValues 
> (ObjectId,ObjectType);
>
> And I don't see any other suggested indexes in the wiki.
> Maybe I am missing a certain page?  Should I add these:
>
>     http://wiki.bestpractical.com/view/DatabaseIndexes
>
> Here's the EXPLAIN for one of the slow statements:
>
> rt3=# EXPLAIN ANALYZE 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 = '35339') 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;
>
>      QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Unique  (cost=1033.85..1033.86 rows=1 width=396) (actual 
> time=3720.579..3720.579 rows=1 loops=1)
>    ->  Sort  (cost=1033.85..1033.85 rows=1 width=396) (actual 
> time=3720.373..3720.373 rows=1 loops=1)
>          Sort Key: main.id, main.content, main.contenttype, 
> main.transactionid, main.contentencoding
>          ->  Nested Loop  (cost=17.77..1033.84 rows=1 width=396) (actual 
> time=3569.107..3719.649 rows=1 loops=1)
>                ->  Nested Loop  (cost=14.13..383.55 rows=1 width=4) (actual 
> time=3562.353..3717.041 rows=28 loops=1)
>                      ->  Bitmap Heap Scan on transactions transactions_1  
> (cost=14.13..154.48 rows=38 width=8) (actual time=159.630..1185.953 
> rows=75440 loops=1)
>                            Recheck Cond: ((objecttype)::text = 
> 'RT::Ticket'::text)
>                            ->  Bitmap Index Scan on transactions1 
> (cost=0.00..14.13 rows=38 width=0) (actual time=156.409..156.409 
> rows=133079 loops=1)
>                                  Index Cond: ((objecttype)::text = 
> 'RT::Ticket'::text)
>                      ->  Index Scan using tickets4 on tickets tickets_2  
> (cost=0.00..6.02 rows=1 width=4) (actual time=0.026..0.026 rows=0 
> loops=75440)
>                            Index Cond: (tickets_2.id = "outer".objectid)
>                            Filter: (effectiveid = 35339)
>                ->  Bitmap Heap Scan on attachments main (cost=3.63..648.02 
> rows=181 width=396) (actual time=0.078..0.082 rows=0 loops=28)
>                      Recheck Cond: ("outer".id = main.transactionid)
>                      Filter: (((contenttype)::text = 'text/plain'::text) OR 
> ((contenttype)::text ~~ 'message/%'::text) OR ((contenttype)::text = 
> 'text'::text))
>                      ->  Bitmap Index Scan on attachments2 (cost=0.00..3.63 
> rows=181 width=0) (actual time=0.011..0.011 rows=1 loops=28)
>                            Index Cond: ("outer".id = main.transactionid)
>  Total runtime: 3722.252 ms
> (18 rows)
>
> rt3=#
>
>
> On 9/22/2010 12:24 PM, Kenneth Marshall wrote:
>> 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!
>>>
>>
>



More information about the rt-users mailing list