[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