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 # u...@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 Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com