[rt-users] Slow PostgreSQL perf with 3.8.7
Jeff Blaine
jblaine at kickflop.net
Wed Sep 22 12:57:58 EDT 2010
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