RT-IR IP lookup slow

Hello!

We have RT with ca 50 000 tickets in it, which, I think, has been
running since 3.2 and upgraded lately to 3.8.5. After that I added RT-
IR to it and everything seems to be fine except the Lookup IP query.
First of all it takes ages. MySQL slow log reports query_time in order
of 500 to 800 seconds and rows examined 3 to 4 millions.

And after I wait through it I get irrelevant data - Incident Reports
and Incidents, which do not have this IP nowhere in them.

Before I upgraded the RT and installed RTIR to it, I copied the whole
database for testing (that means all the same content in it except
newer tickets) and played around with RTIR a bit. I did not notice the
slowness in the test database and IIRC the virgin production RTIR was
fast also. But search results were irrelevant in test too (though at
the time I hoped it was my testing that messed things up). And lookup
in the production DB seems to grow slower with every Incident Report
added.

Can somebody take a look at this query (taken from slow log) if this
seems correct and why does RT join ObjectCustomFieldValues to itself 4
times and perform WHERE lookup on different fields in all of them.

Below are the query and it’s explain from phpMyAdmin.

Best,
Laas

SELECT DISTINCT main.* FROM Tickets main JOIN ObjectCustomFieldValues
ObjectCustomFieldValues_3 ON ( ObjectCustomFieldValues_3.CustomField
= ‘14’ ) AND ( ObjectCustomFieldValues_3.Disabled = ‘0’ ) AND
( ObjectCustomFieldValues_3.ObjectType = ‘RT::Ticket’ ) AND (
ObjectCustomFieldValues_3.ObjectId = main.id ) JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_4 ON
( ObjectCustomFieldValues_4.Disabled = ‘0’ ) AND
( ObjectCustomFieldValues_4.ObjectType = ‘RT::Ticket’ ) AND
( ObjectCustomFieldValues_4.CustomField = ‘14’ )
AND ( ObjectCustomFieldValues_4.ObjectId = main.id ) JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1 ON
( ObjectCustomFieldValues_1.CustomField = ‘14’ ) AND
( ObjectCustomFieldValues_1.ObjectType = ‘RT::Ticket’ ) AND
( ObjectCustomFieldValues_1.Disabled =
‘0’ ) AND ( ObjectCustomFieldValues_1.ObjectId = main.id ) JOIN
Transactions Transactions_5 ON ( Transactions_5.ObjectId = main.id
) JOIN ObjectCustomFieldValues ObjectCustomFieldValues_2 ON
( ObjectCustomFieldValues_2.Disabled = ‘0’ ) AND
( ObjectCustomFieldValues_2.ObjectId = main.id ) AND
( ObjectCustomFieldValues_2.ObjectType = ‘RT::Ticket’ ) AND
( ObjectCustomFieldValues_2.CustomField =
‘14’ ) WHERE (main.Status != ‘deleted’) AND
(Transactions_5.ObjectType = ‘RT::Ticket’) AND
( ( ( ( ( ( ObjectCustomFieldValues_1.Content <= '193.040.010.218’
OR ( ( ObjectCustomFieldValues_1.Content = ‘’ OR
ObjectCustomFieldValues_1.Content IS NULL ) AND
ObjectCustomFieldValues_1.LargeContent <=
‘193.040.010.218’ ) ) ) ) AND
( ( ( ObjectCustomFieldValues_2.LargeContent >=
‘193.040.010.218’ ) ) ) AND
( ( ( ObjectCustomFieldValues_3.Content >= ‘000.000.000.000’ OR
( ( ObjectCustomFieldValues_3.Content
= ‘’ OR ObjectCustomFieldValues_3.Content IS NULL ) AND
ObjectCustomFieldValues_3.LargeContent >=
‘000.000.000.000’ ) ) ) ) AND
( ( ( ObjectCustomFieldValues_4.LargeContent <=
‘255.255.255.255’ ) ) ) ) ) AND ( Transactions_5.Created >
‘2009-08-08 10:56:21’ ) ) AND (main.Type = ‘ticket’) AND
(main.EffectiveId = main.id)

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ObjectCustomFieldValues_1 ref
TicketCustomFieldValues1,TicketCustomFieldValues2,…
ObjectCustomFieldValues3 263 const,const,const 3832 Using where; Using
temporary
1 SIMPLE ObjectCustomFieldValues_3 ref
TicketCustomFieldValues1,TicketCustomFieldValues2,…
TicketCustomFieldValues1 8
const,rt3.ObjectCustomFieldValues_1.ObjectId 1 Using where
1 SIMPLE ObjectCustomFieldValues_4 ref
TicketCustomFieldValues1,TicketCustomFieldValues2,…
TicketCustomFieldValues1 8
const,rt3.ObjectCustomFieldValues_1.ObjectId 1 Using where
1 SIMPLE ObjectCustomFieldValues_2 ref
TicketCustomFieldValues1,TicketCustomFieldValues2,…
TicketCustomFieldValues1 8
const,rt3.ObjectCustomFieldValues_1.ObjectId 1 Using where
1 SIMPLE main eq_ref PRIMARY,Tickets3,Tickets4,Tickets5,Tickets6
PRIMARY 4 rt3.ObjectCustomFieldValues_2.ObjectId 1 Using where
1 SIMPLE Transactions_5 ref Transactions1 Transactions1 70
const,rt3.ObjectCustomFieldValues_1.ObjectId 1 Using where; Distinct

Hello!

We have RT with ca 50 000 tickets in it, which, I think, has been
running since 3.2 and upgraded lately to 3.8.5. After that I added RT-
IR to it and everything seems to be fine except the Lookup IP query.
First of all it takes ages. MySQL slow log reports query_time in order
of 500 to 800 seconds and rows examined 3 to 4 millions.

What sorts of tuning have you done to mysql to date?

Laas,
This SQL is too heavy and incorrect, RT 3.8.6 has a fix.On Fri, Oct 23, 2009 at 1:00 PM, Laas Toom laas.toom@eenet.ee wrote:

Hello!

We have RT with ca 50 000 tickets in it, which, I think, has been running
since 3.2 and upgraded lately to 3.8.5. After that I added RT-IR to it and
everything seems to be fine except the Lookup IP query. First of all it
takes ages. MySQL slow log reports query_time in order of 500 to 800 seconds
and rows examined 3 to 4 millions.

And after I wait through it I get irrelevant data - Incident Reports and
Incidents, which do not have this IP nowhere in them.

Before I upgraded the RT and installed RTIR to it, I copied the whole
database for testing (that means all the same content in it except newer
tickets) and played around with RTIR a bit. I did not notice the slowness in
the test database and IIRC the virgin production RTIR was fast also. But
search results were irrelevant in test too (though at the time I hoped it
was my testing that messed things up). And lookup in the production DB seems
to grow slower with every Incident Report added.

Can somebody take a look at this query (taken from slow log) if this seems
correct and why does RT join ObjectCustomFieldValues to itself 4 times and
perform WHERE lookup on different fields in all of them.

Below are the query and it’s explain from phpMyAdmin.

Best,
Laas

SELECT DISTINCT main.* FROM Tickets main JOIN ObjectCustomFieldValues
ObjectCustomFieldValues_3 ON ( ObjectCustomFieldValues_3.CustomField = ‘14’
) AND ( ObjectCustomFieldValues_3.Disabled = ‘0’ ) AND (
ObjectCustomFieldValues_3.ObjectType = ‘RT::Ticket’ ) AND (
ObjectCustomFieldValues_3.ObjectId = main.id ) JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_4 ON (
ObjectCustomFieldValues_4.Disabled = ‘0’ ) AND (
ObjectCustomFieldValues_4.ObjectType = ‘RT::Ticket’ ) AND (
ObjectCustomFieldValues_4.CustomField = ‘14’ )
AND ( ObjectCustomFieldValues_4.ObjectId = main.id ) JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1 ON (
ObjectCustomFieldValues_1.CustomField = ‘14’ ) AND (
ObjectCustomFieldValues_1.ObjectType = ‘RT::Ticket’ ) AND (
ObjectCustomFieldValues_1.Disabled =
‘0’ ) AND ( ObjectCustomFieldValues_1.ObjectId = main.id ) JOIN
Transactions Transactions_5 ON ( Transactions_5.ObjectId = main.id
) JOIN ObjectCustomFieldValues ObjectCustomFieldValues_2 ON (
ObjectCustomFieldValues_2.Disabled = ‘0’ ) AND (
ObjectCustomFieldValues_2.ObjectId = main.id ) AND (
ObjectCustomFieldValues_2.ObjectType = ‘RT::Ticket’ ) AND (
ObjectCustomFieldValues_2.CustomField =
‘14’ ) WHERE (main.Status != ‘deleted’) AND (Transactions_5.ObjectType =
‘RT::Ticket’) AND ( ( ( ( ( ( ObjectCustomFieldValues_1.Content <=
‘193.040.010.218’ OR ( ( ObjectCustomFieldValues_1.Content = ‘’ OR
ObjectCustomFieldValues_1.Content IS NULL ) AND
ObjectCustomFieldValues_1.LargeContent <= ‘193.040.010.218’ ) ) ) ) AND
( ( ( ObjectCustomFieldValues_2.LargeContent >= ‘193.040.010.218’ ) ) )
AND ( ( ( ObjectCustomFieldValues_3.Content >= ‘000.000.000.000’ OR (
( ObjectCustomFieldValues_3.Content
= ‘’ OR ObjectCustomFieldValues_3.Content IS NULL ) AND
ObjectCustomFieldValues_3.LargeContent >= ‘000.000.000.000’ ) ) ) ) AND
( ( ( ObjectCustomFieldValues_4.LargeContent <= ‘255.255.255.255’ ) ) )
) ) AND ( Transactions_5.Created > ‘2009-08-08 10:56:21’ ) ) AND
(main.Type = ‘ticket’) AND (main.EffectiveId = main.id)

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLE
ObjectCustomFieldValues_1ref
TicketCustomFieldValues1,TicketCustomFieldValues2,…
ObjectCustomFieldValues3263const,const,const3832Using where; Using
temporary1SIMPLEObjectCustomFieldValues_3ref
TicketCustomFieldValues1,TicketCustomFieldValues2,…
TicketCustomFieldValues18const,rt3.ObjectCustomFieldValues_1.ObjectId1Using
where1SIMPLEObjectCustomFieldValues_4ref
TicketCustomFieldValues1,TicketCustomFieldValues2,…
TicketCustomFieldValues18const,rt3.ObjectCustomFieldValues_1.ObjectId1Using
where1SIMPLEObjectCustomFieldValues_2ref
TicketCustomFieldValues1,TicketCustomFieldValues2,…
TicketCustomFieldValues18const,rt3.ObjectCustomFieldValues_1.ObjectId1Using
where1SIMPLEmaineq_refPRIMARY,Tickets3,Tickets4,Tickets5,Tickets6PRIMARY4
rt3.ObjectCustomFieldValues_2.ObjectId1Using where1SIMPLETransactions_5ref
Transactions1Transactions170const,rt3.ObjectCustomFieldValues_1.ObjectId1Using
where; Distinct


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

Hi,On 23.10.2009, at 20:36, Ruslan Zakirov wrote:

Laas,

This SQL is too heavy and incorrect, RT 3.8.6 has a fix.

Upgrading to 3.8.6 seems to have fixed this issue - lookup IP is now
fast again.

Thank you.

Best,
Laas