[rt-users] RT + MySQL Query optimization for improved slow ticket fetching
ronald higgins
ronald.higgins at gmail.com
Sun Jul 25 11:17:05 EDT 2010
Hi fellow RT Users, hope you're having a good day and might be able to
offer a much needed assist.
I'm troubleshooting some tickets in RT taking nearly a minute to
display to the users.
I'm currently running a 3.8.7 Version of RT on a VMWare Virtual
Machine assigned 4CPU's & 10GB RAM.
Centos 5.4 (64bit) and MySQL 5.1.48 the Apps in question.
RT stores a lot of faxes received from users so the DB (Attachments)
is hovering just above the 200GB Mark.
Count of entries in the RT DB are:
Tickets: 1546751
Attachments: 7540684
I've partitioned the Tickets & Attachment tables by Range based on ID
in partition in chunks of 100 000.
However, i think there is something that is not being optimized.
When opening a ticket it takes up to a minute to open the file, DISK
I/O looks fine:
Here is an example from the MySQL Slow log ( this one took 44 seconds);
########################################################
# Time: 100725 16:39:14
# User at Host: rt_user[rt_user] @ [172.18.16.253]
# Query_time: 44.051787 Lock_time: 0.000764 Rows_sent: 10
Rows_examined: 591398
SET timestamp=1280068754;
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 =
'1241833') AND (main.Status != 'deleted') AND ( (
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =
'ticket') AND (main.EffectiveId = main.id) ORDER BY main.Priority
DESC LIMIT 10;
##########################################################
And here is the result of the explain:
mysql> explain 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 = '1241833') AND (main.Status != 'deleted') AND ( (
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =
'ticket') AND (main.EffectiveId = main.id) ORDER BY main.Priority
DESC LIMIT 10\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Users_3
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: CachedGroupMembers_2
type: ref
possible_keys: PRIMARY,DisGrouMem,CachedGroupMembers3
key: CachedGroupMembers3
key_len: 5
ref: const
rows: 214802
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: Groups_1
type: eq_ref
possible_keys: PRIMARY,Groups1,Groups2
key: PRIMARY
key_len: 4
ref: vmed_rt.CachedGroupMembers_2.GroupId
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: main
type: eq_ref
possible_keys: PRIMARY,Tickets6
key: PRIMARY
key_len: 4
ref: vmed_rt.Groups_1.Instance
rows: 1
Extra: Using where
4 rows in set (0.00 sec)
ERROR:
No query specified
mysql>
######################################################3
And below is an explain partitions to see which partitions it's hitting:
mysql> explain partitions 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 = '1241833') AND (main.Status != 'deleted') AND ( (
CachedGroupMembers_2.id IS NOT NULL ) AND ( main.Status = 'new' OR
main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type =
'ticket') AND (main.EffectiveId = main.id) ORDER BY main.Priority
DESC LIMIT 10\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Users_3
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: CachedGroupMembers_2
partitions: NULL
type: ref
possible_keys: PRIMARY,DisGrouMem,CachedGroupMembers3
key: CachedGroupMembers3
key_len: 5
ref: const
rows: 214802
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: Groups_1
partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44,p45,p46,p47,p48,p49,p50,p51,p52,p53,p54,p55,p56,p57,p58,p59,p60,p61,p62,p63,p64,p65,p66,p67,p68,p69,p70,p71,p72,p73,p74,p75,p76,p77,p78,p79,p80,p81,p82,p83,p84,p85,p86,p87,p88,p89,p90,p91,p92,p93,p94,p95,p96,p97,p98,p99,p100,p101,p102,p103,p104,p105,p106,p107,p108,p109,p110,p111,p112,p113,p114,p115,p116,p117,p118,p119,p120,p121,p122,p123,p124,p125,p126,p127,p128,p129,p130,p131,p132,p133,p134,p135,p136,p137,p138,p139,p140,p141,p142,p143,p144,p145,p146,p147,p148,p149,p150,p151,p152,p153,p154,p155,p156,p157,p158,p159,p160,p161,p162,p163,p164,p165,p166,p167,p168,p169,p170,p171,p172,p173,p174,p175,p176,p177,p178,p179,p180,p181,p182,p183,p184,p185,p186,p187,p188,p189,p190,p191,p192,p193,p194,p195,p196,p197,p198,p199,p200,p201,p202,p203,p204,p205,p206,p207,p208,p209,p210,p211
type: eq_ref
possible_keys: PRIMARY,Groups1,Groups2
key: PRIMARY
key_len: 4
ref: vmed_rt.CachedGroupMembers_2.GroupId
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: main
partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44,p45,p46,p47,p48,p49,p50,p51,p52,p53,p54,p55,p56,p57,p58,p59,p60,p61,p62,p63,p64,p65,p66,p67,p68,p69,p70,p71,p72,p73,p74,p75,p76,p77,p78,p79,p80,p81,p82,p83,p84,p85,p86,p87,p88,p89,p90,p91,p92,p93,p94,p95,p96,p97,p98,p99,p100,p101,p102,p103,p104,p105,p106,p107,p108,p109,p110,p111,p112,p113,p114,p115,p116,p117,p118,p119,p120,p121,p122,p123,p124,p125,p126,p127,p128,p129,p130,p131,p132,p133,p134,p135,p136,p137,p138,p139,p140,p141,p142,p143,p144,p145,p146,p147,p148,p149,p150,p151,p152,p153,p154,p155,p156,p157,p158,p159,p160,p161,p162,p163,p164,p165,p166,p167,p168,p169,p170,p171,p172,p173,p174,p175,p176,p177,p178,p179,p180,p181,p182,p183,p184,p185,p186,p187,p188,p189,p190,p191,p192,p193,p194,p195,p196,p197,p198,p199,p200,p201,p202,p203,p204,p205,p206,p207,p208,p209,p210,p211
type: eq_ref
possible_keys: PRIMARY,Tickets6
key: PRIMARY
key_len: 4
ref: vmed_rt.Groups_1.Instance
rows: 1
Extra: Using where
4 rows in set (0.01 sec)
ERROR:
No query specified
mysql>
##################################################################
If anyone can suggest some further MySQL optimizations that will allow
tickets to load a bit quicker it would really be appreciated.
Regards
Ronald
More information about the rt-users
mailing list