Re: [rt-users] Slow query on CFs
On Fri, Jun 7, 2013 at 3:31 PM, Vegard Vesterheim < vegard.vesterh...@uninett.no> wrote: > On Fri, 07 Jun 2013 09:32:46 +0200 Christian Loos > wrote: > > > Hi, > > > > the Database indexes are not optimal for the ObjectCustomFieldValues > > table. Sadly I didn't had time to dig deeper into this. > > But maybe you have time and an new index will help you. > > Thanks for the tip. Better indexing might help, but in this case the SQL > query is clearly suboptimal, so a fix in the SQL generation is in order > anyway. In our database, a properly optimised SQL statement returns > results immediately. > In this case there is no better indexing. The SQL itself falls into category of hardly optimizable by DBs. Some DBs do better job by using hash joins, but mysql with its loop strategy can not cope. > > There is already an ticket open for this: > > http://issues.bestpractical.com/Ticket/Display.html?id=16898 > > The symptoms might be the same (slow queries), so the tickets might be > related, but I think this is two separate issues which will require > different solutions. One issue is about suboptimal SQL, the other issue > is about suboptimal indexes. > > - Vegard V - > > > -- > RT Training in Seattle, June 19-20: http://bestpractical.com/training > -- Best regards, Ruslan. -- RT Training in Seattle, June 19-20: http://bestpractical.com/training
Re: [rt-users] Slow query on CFs
On Fri, 07 Jun 2013 09:32:46 +0200 Christian Loos wrote: > Hi, > > the Database indexes are not optimal for the ObjectCustomFieldValues > table. Sadly I didn't had time to dig deeper into this. > But maybe you have time and an new index will help you. Thanks for the tip. Better indexing might help, but in this case the SQL query is clearly suboptimal, so a fix in the SQL generation is in order anyway. In our database, a properly optimised SQL statement returns results immediately. > There is already an ticket open for this: > http://issues.bestpractical.com/Ticket/Display.html?id=16898 The symptoms might be the same (slow queries), so the tickets might be related, but I think this is two separate issues which will require different solutions. One issue is about suboptimal SQL, the other issue is about suboptimal indexes. - Vegard V - -- RT Training in Seattle, June 19-20: http://bestpractical.com/training
Re: [rt-users] Slow query on CFs
Hi, the Database indexes are not optimal for the ObjectCustomFieldValues table. Sadly I didn't had time to dig deeper into this. But maybe you have time and an new index will help you. There is already an ticket open for this: http://issues.bestpractical.com/Ticket/Display.html?id=16898 Chris -- RT Training in Seattle, June 19-20: http://bestpractical.com/training
Re: [rt-users] Slow query on CFs
On Wed, Jun 5, 2013 at 11:08 AM, Vegard Vesterheim < vegard.vesterh...@uninett.no> wrote: > On Tue, 4 Jun 2013 17:30:02 +0400 Ruslan Zakirov > wrote: > > > Hi, > > > > It's not a bug, but missing feature. > > Hm, ok. This problem makes searching for multiple values in CFs unusable > for our purposes. I would like to help in getting it fixed. For now I > have worked around it by searching for one CF value at a time in a > loop. Seems a bit silly since this is something SQL could handle easily. > > Should I register this as a feature request somewhere, or is this > something that is already on the map? > > > It's possible to improve using similar improvements to searches by > > watchers. If you want to help then you can take a look at recent > > changes in lib/RT/Tickets_SQL.pm. > > Yes, I had a quick look at the relevant git commits. I guess the problem > is similar. > If you don't have cycles and/or knoledge to help here then file a bug report on http://issues.bestpractical.com . > > - Vegard V - > -- Best regards, Ruslan. -- RT Training in Seattle, June 19-20: http://bestpractical.com/training
Re: [rt-users] Slow query on CFs
On Tue, 04 Jun 2013 07:47:02 -0700 Thomas Sibley wrote:
> I'm sure you've considered this, but I have to ask: Could you simplify
> most of the CF conditions with CF.{utstyr} ENDSWITH '.uninett.no'?
Thanks for the suggestion, but no, that will not give me the result I am
after.
- Vegard V -
--
RT Training in Seattle, June 19-20: http://bestpractical.com/training
Re: [rt-users] Slow query on CFs
On Tue, 4 Jun 2013 17:30:02 +0400 Ruslan Zakirov wrote: > Hi, > > It's not a bug, but missing feature. Hm, ok. This problem makes searching for multiple values in CFs unusable for our purposes. I would like to help in getting it fixed. For now I have worked around it by searching for one CF value at a time in a loop. Seems a bit silly since this is something SQL could handle easily. Should I register this as a feature request somewhere, or is this something that is already on the map? > It's possible to improve using similar improvements to searches by > watchers. If you want to help then you can take a look at recent > changes in lib/RT/Tickets_SQL.pm. Yes, I had a quick look at the relevant git commits. I guess the problem is similar. - Vegard V - -- RT Training in Seattle, June 19-20: http://bestpractical.com/training
Re: [rt-users] Slow query on CFs
On 06/04/2013 06:03 AM, Vegard Vesterheim wrote:
> A RT (TicketSQL) query like this:
>
> Queue = 'drift' AND ( Status = 'new' or Status = 'open' or Status =
> 'stalled' ) AND ( CF.{utstyr} = 'ufisaweb.uninett.no' OR CF.{utstyr} =
> 'dss2.uninett.no' OR CF.{utstyr} = 'myrhauk.uninett.no' OR CF.{utstyr} =
> 'angel.uninett.no' OR CF.{utstyr} = 'jatoba-esxi2.uninett.no' OR
> CF.{utstyr} = 'bold.uninett.no' OR CF.{utstyr} = 'nidar.uninett.no' OR
> CF.{utstyr} = 'voll.uninett.no' OR CF.{utstyr} = 'brekka.uninett.no' OR
> CF.{utstyr} = 'www.stroemme.no' OR CF.{utstyr} = 'xen.uninett.no' OR
> CF.{utstyr} = 'jatoba-kvm4.uninett.no' OR CF.{utstyr} =
> 'inventory.uninett.no' OR CF.{utstyr} = 'busy.uninett.no' OR CF.{utstyr}
> = 'ufisa.uninett.no' OR CF.{utstyr} = 'wildfire.uninett.no' OR
> CF.{utstyr} = 'newfire.uninett.no' OR CF.{utstyr} = 'fou1.uninett.no' OR
> CF.{utstyr} = 'kanari.uninett.no' OR CF.{utstyr} = 'dok.uninett.no' OR
> CF.{utstyr} = 'ebony-kvm5.uninett.no' OR CF.{utstyr} =
> 'jatoba-kvm12.uninett.no' )
I'm sure you've considered this, but I have to ask: Could you simplify
most of the CF conditions with CF.{utstyr} ENDSWITH '.uninett.no'?
For a technical solution, the approach Ruslan described is the way to
go. It works great for watchers.
--
RT Training in Seattle, June 19-20: http://bestpractical.com/training
Re: [rt-users] Slow query on CFs
Hi,
It's not a bug, but missing feature. It's possible to improve using similar
improvements to searches by watchers. If you want to help then you can take
a look at recent changes in lib/RT/Tickets_SQL.pm.
On Tue, Jun 4, 2013 at 5:03 PM, Vegard Vesterheim <
vegard.vesterh...@uninett.no> wrote:
> A RT (TicketSQL) query like this:
>
> Queue = 'drift' AND ( Status = 'new' or Status = 'open' or Status =
> 'stalled' ) AND ( CF.{utstyr} = 'ufisaweb.uninett.no' OR CF.{utstyr} =
> 'dss2.uninett.no' OR CF.{utstyr} = 'myrhauk.uninett.no' OR CF.{utstyr} =
> 'angel.uninett.no' OR CF.{utstyr} = 'jatoba-esxi2.uninett.no' OR
> CF.{utstyr} = 'bold.uninett.no' OR CF.{utstyr} = 'nidar.uninett.no' OR
> CF.{utstyr} = 'voll.uninett.no' OR CF.{utstyr} = 'brekka.uninett.no' OR
> CF.{utstyr} = 'www.stroemme.no' OR CF.{utstyr} = 'xen.uninett.no' OR
> CF.{utstyr} = 'jatoba-kvm4.uninett.no' OR CF.{utstyr} =
> 'inventory.uninett.no' OR CF.{utstyr} = 'busy.uninett.no' OR CF.{utstyr}
> = 'ufisa.uninett.no' OR CF.{utstyr} = 'wildfire.uninett.no' OR
> CF.{utstyr} = 'newfire.uninett.no' OR CF.{utstyr} = 'fou1.uninett.no' OR
> CF.{utstyr} = 'kanari.uninett.no' OR CF.{utstyr} = 'dok.uninett.no' OR
> CF.{utstyr} = 'ebony-kvm5.uninett.no' OR CF.{utstyr} =
> 'jatoba-kvm12.uninett.no' )
>
> gets translated to the following SQL:
>
> SELECT COUNT(DISTINCT main.id) FROM Tickets main LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_5 ON (
> ObjectCustomFieldValues_5.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_5.Disabled = '0' ) AND (
> ObjectCustomFieldValues_5.ObjectId = main.id ) AND (
> ObjectCustomFieldValues_5.CustomField = '8' ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_3 ON (
> ObjectCustomFieldValues_3.CustomField = '8' ) AND (
> ObjectCustomFieldValues_3.Disabled = '0' ) AND (
> ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_3.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_22 ON (
> ObjectCustomFieldValues_22.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_22.CustomField = '8' ) AND (
> ObjectCustomFieldValues_22.Disabled = '0' ) AND (
> ObjectCustomFieldValues_22.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_19 ON (
> ObjectCustomFieldValues_19.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_19.CustomField = '8' ) AND (
> ObjectCustomFieldValues_19.Disabled = '0' ) AND (
> ObjectCustomFieldValues_19.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_10 ON (
> ObjectCustomFieldValues_10.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_10.CustomField = '8' ) AND (
> ObjectCustomFieldValues_10.Disabled = '0' ) AND (
> ObjectCustomFieldValues_10.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_4 ON (
> ObjectCustomFieldValues_4.Disabled = '0' ) AND (
> ObjectCustomFieldValues_4.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_4.CustomField = '8' ) AND (
> ObjectCustomFieldValues_4.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_8 ON (
> ObjectCustomFieldValues_8.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_8.CustomField = '8' ) AND (
> ObjectCustomFieldValues_8.ObjectId = main.id ) AND (
> ObjectCustomFieldValues_8.Disabled = '0' ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_21 ON (
> ObjectCustomFieldValues_21.CustomField = '8' ) AND (
> ObjectCustomFieldValues_21.Disabled = '0' ) AND (
> ObjectCustomFieldValues_21.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_21.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_12 ON (
> ObjectCustomFieldValues_12.CustomField = '8' ) AND (
> ObjectCustomFieldValues_12.Disabled = '0' ) AND (
> ObjectCustomFieldValues_12.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_12.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_16 ON (
> ObjectCustomFieldValues_16.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_16.Disabled = '0' ) AND (
> ObjectCustomFieldValues_16.CustomField = '8' ) AND (
> ObjectCustomFieldValues_16.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_17 ON (
> ObjectCustomFieldValues_17.Disabled = '0' ) AND (
> ObjectCustomFieldValues_17.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_17.CustomField = '8' ) AND (
> ObjectCustomFieldValues_17.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_20 ON (
> ObjectCustomFieldValues_20.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_20.CustomField = '8' ) AND (
> ObjectCustomFieldValues_20.Disabled = '0' ) AND (
> ObjectCustomFieldValues_20.ObjectId = main.id ) LEFT JOIN
> ObjectCustomFieldValues ObjectCustomFieldValues_13 ON (
> ObjectCustomFieldValues_13.ObjectType = 'RT::Ticket' ) AND (
> ObjectCustomFieldValues_13.Disabled = '0' ) AND (
> ObjectC
[rt-users] Slow query on CFs
A RT (TicketSQL) query like this:
Queue = 'drift' AND ( Status = 'new' or Status = 'open' or Status =
'stalled' ) AND ( CF.{utstyr} = 'ufisaweb.uninett.no' OR CF.{utstyr} =
'dss2.uninett.no' OR CF.{utstyr} = 'myrhauk.uninett.no' OR CF.{utstyr} =
'angel.uninett.no' OR CF.{utstyr} = 'jatoba-esxi2.uninett.no' OR
CF.{utstyr} = 'bold.uninett.no' OR CF.{utstyr} = 'nidar.uninett.no' OR
CF.{utstyr} = 'voll.uninett.no' OR CF.{utstyr} = 'brekka.uninett.no' OR
CF.{utstyr} = 'www.stroemme.no' OR CF.{utstyr} = 'xen.uninett.no' OR
CF.{utstyr} = 'jatoba-kvm4.uninett.no' OR CF.{utstyr} =
'inventory.uninett.no' OR CF.{utstyr} = 'busy.uninett.no' OR CF.{utstyr}
= 'ufisa.uninett.no' OR CF.{utstyr} = 'wildfire.uninett.no' OR
CF.{utstyr} = 'newfire.uninett.no' OR CF.{utstyr} = 'fou1.uninett.no' OR
CF.{utstyr} = 'kanari.uninett.no' OR CF.{utstyr} = 'dok.uninett.no' OR
CF.{utstyr} = 'ebony-kvm5.uninett.no' OR CF.{utstyr} =
'jatoba-kvm12.uninett.no' )
gets translated to the following SQL:
SELECT COUNT(DISTINCT main.id) FROM Tickets main LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_5 ON (
ObjectCustomFieldValues_5.ObjectType = 'RT::Ticket' ) AND (
ObjectCustomFieldValues_5.Disabled = '0' ) AND (
ObjectCustomFieldValues_5.ObjectId = main.id ) AND (
ObjectCustomFieldValues_5.CustomField = '8' ) LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_3 ON (
ObjectCustomFieldValues_3.CustomField = '8' ) AND (
ObjectCustomFieldValues_3.Disabled = '0' ) AND (
ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket' ) AND (
ObjectCustomFieldValues_3.ObjectId = main.id ) LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_22 ON (
ObjectCustomFieldValues_22.ObjectType = 'RT::Ticket' ) AND (
ObjectCustomFieldValues_22.CustomField = '8' ) AND (
ObjectCustomFieldValues_22.Disabled = '0' ) AND (
ObjectCustomFieldValues_22.ObjectId = main.id ) LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_19 ON (
ObjectCustomFieldValues_19.ObjectType = 'RT::Ticket' ) AND (
ObjectCustomFieldValues_19.CustomField = '8' ) AND (
ObjectCustomFieldValues_19.Disabled = '0' ) AND (
ObjectCustomFieldValues_19.ObjectId = main.id ) LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_10 ON (
ObjectCustomFieldValues_10.ObjectType = 'RT::Ticket' ) AND (
ObjectCustomFieldValues_10.CustomField = '8' ) AND (
ObjectCustomFieldValues_10.Disabled = '0' ) AND (
ObjectCustomFieldValues_10.ObjectId = main.id ) LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_4 ON (
ObjectCustomFieldValues_4.Disabled = '0' ) AND (
ObjectCustomFieldValues_4.ObjectType = 'RT::Ticket' ) AND (
ObjectCustomFieldValues_4.CustomField = '8' ) AND (
ObjectCustomFieldValues_4.ObjectId = main.id ) LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_8 ON (
ObjectCustomFieldValues_8.ObjectType = 'RT::Ticket' ) AND (
ObjectCustomFieldValues_8.CustomField = '8' ) AND (
ObjectCustomFieldValues_8.ObjectId = main.id ) AND (
ObjectCustomFieldValues_8.Disabled = '0' ) LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_21 ON (
ObjectCustomFieldValues_21.CustomField = '8' ) AND (
ObjectCustomFieldValues_21.Disabled = '0' ) AND (
ObjectCustomFieldValues_21.ObjectType = 'RT::Ticket' ) AND (
ObjectCustomFieldValues_21.ObjectId = main.id ) LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_12 ON (
ObjectCustomFieldValues_12.CustomField = '8' ) AND (
ObjectCustomFieldValues_12.Disabled = '0' ) AND (
ObjectCustomFieldValues_12.ObjectType = 'RT::Ticket' ) AND (
ObjectCustomFieldValues_12.ObjectId = main.id ) LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_16 ON (
ObjectCustomFieldValues_16.ObjectType = 'RT::Ticket' ) AND (
ObjectCustomFieldValues_16.Disabled = '0' ) AND (
ObjectCustomFieldValues_16.CustomField = '8' ) AND (
ObjectCustomFieldValues_16.ObjectId = main.id ) LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_17 ON (
ObjectCustomFieldValues_17.Disabled = '0' ) AND (
ObjectCustomFieldValues_17.ObjectType = 'RT::Ticket' ) AND (
ObjectCustomFieldValues_17.CustomField = '8' ) AND (
ObjectCustomFieldValues_17.ObjectId = main.id ) LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_20 ON (
ObjectCustomFieldValues_20.ObjectType = 'RT::Ticket' ) AND (
ObjectCustomFieldValues_20.CustomField = '8' ) AND (
ObjectCustomFieldValues_20.Disabled = '0' ) AND (
ObjectCustomFieldValues_20.ObjectId = main.id ) LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_13 ON (
ObjectCustomFieldValues_13.ObjectType = 'RT::Ticket' ) AND (
ObjectCustomFieldValues_13.Disabled = '0' ) AND (
ObjectCustomFieldValues_13.ObjectId = main.id ) AND (
ObjectCustomFieldValues_13.CustomField = '8' ) LEFT 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 = '8' ) LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_9 ON (
ObjectCustomFieldValues_9.CustomF
