Ludicrously slow queries on custom fields

Our RT installation has recently gotten amazingly slow, most obviously
where it’s dealing with custom fields.

Some of the biggest culprits are rt_tidy snippets that we (used to!) run
from cron, although many of the standard queries on the web interface
have become unacceptably slow.

After investigating a set of particularly problematic queries that were
each taking 40 minutes(!) to run we noticed a very peculiar clause in
the query, which basically boils down to a select on

Tickets LEFT JOIN TicketCustomFieldValues
ON TicketCustomFieldValues.CustomField = ‘1’
OR TicketCustomFieldValues.Ticket = Tickets.id

There are other tables involved as well, but this ‘OR’ seems to wipe
out all hope of sensibly using indexes, and results an a 1.4 billion
row temporary table being created.

Unless I’m missing something important about how this should work, this
should surely be an AND, not an OR. And, indeed, replacing the OR with
an AND and running the query by hand gets back the correct results in a
fraction of a second.

Also, some queries on custom fields appear to have recently stopped
working altogether in that they now return every ticket that meets the
other criteria - not restricting by the custom field at all.

This appears to have happened after we upgraded mysql. We didn’t upgrade
any direct RT things with this, but perhaps something else underneath
has changed in an unexpected way, or MySQL has changed its use of
indexes on certain types of queries or something? (Mostly I’m at a loss
as to what could have caused this and just scrambling here)

I can’t find anything obvious in the list history or on the wiki about
this. (We’ve added the suggested index to the table, but that doesn’t seem
to help, and even if it did, it appears it would be just papering over
a deeper problem.) I’m assuming that other people would have encountered
the problem if it was something common, so perhaps this has already been
fixed in a recent version? We’re still running RT 3.2.2, and although
we are planning on upgrading, it’s not something we were expecting to
do just yet. We have about 45,000 tickets over about 15 queues, which I
wouldn’t have thought was a particularly huge installation.

Anyone any ideas, or pointers to anything I’ve missed?

Thanks,

Tony

After investigating a set of particularly problematic queries that were
each taking 40 minutes(!) to run we noticed a very peculiar clause in
the query, which basically boils down to a select on

Tickets LEFT JOIN TicketCustomFieldValues
ON TicketCustomFieldValues.CustomField = ‘1’
OR TicketCustomFieldValues.Ticket = Tickets.id

Unless I’m missing something important about how this should work, this
should surely be an AND, not an OR. And, indeed, replacing the OR with
an AND and running the query by hand gets back the correct results in a
fraction of a second.

That sure doesn’t look right, but 3.2.2 was a long time ago. 3.4
entirely reimplemented the custom fields searching, and 3.6 further
improved queries on customfields.

This appears to have happened after we upgraded mysql.

From what to what? We’ve seen pathalogically bad query optimization on
5.0. But still, that query above just doesn’t look right.

-j

Tickets LEFT JOIN TicketCustomFieldValues
ON TicketCustomFieldValues.CustomField = ‘1’
OR TicketCustomFieldValues.Ticket = Tickets.id
That sure doesn’t look right, but 3.2.2 was a long time ago. 3.4
entirely reimplemented the custom fields searching, and 3.6 further
improved queries on customfields.

Yeah - upgrading has been on the todo list for quite a while, but we’re
not sure how easy it’ll be so it keeps getting put off.

This appears to have happened after we upgraded mysql.
From what to what? We’ve seen pathalogically bad query optimization on
5.0. But still, that query above just doesn’t look right.

Not sure what we were at, but we’re now on 5.0.24

In further investigation we found a line in the DBIx::SearchBuilder
Changes file from earlier this year that says:

  • Allow ORs on left joins

It’s conceivable that that was upgraded with mysql - is it possible that
this change might have made the old 3.2.2 query work differently?

Tony

Tickets LEFT JOIN TicketCustomFieldValues
ON TicketCustomFieldValues.CustomField = ‘1’
OR TicketCustomFieldValues.Ticket = Tickets.id
That sure doesn’t look right, but 3.2.2 was a long time ago. 3.4
entirely reimplemented the custom fields searching, and 3.6 further
improved queries on customfields.

Yeah - upgrading has been on the todo list for quite a while, but we’re
not sure how easy it’ll be so it keeps getting put off.

This appears to have happened after we upgraded mysql.
From what to what? We’ve seen pathalogically bad query optimization on
5.0. But still, that query above just doesn’t look right.

Not sure what we were at, but we’re now on 5.0.24

nod

In further investigation we found a line in the DBIx::SearchBuilder
Changes file from earlier this year that says:

  • Allow ORs on left joins

It’s conceivable that that was upgraded with mysql - is it possible that
this change might have made the old 3.2.2 query work differently?

It’s possible, but I couldn’t say for sure. Try backing down and see if
it’s better?

In further investigation we found a line in the DBIx::SearchBuilder
Changes file from earlier this year that says:

  • Allow ORs on left joins
    It’s conceivable that that was upgraded with mysql - is it possible that
    this change might have made the old 3.2.2 query work differently?
    It’s possible, but I couldn’t say for sure. Try backing down and see if
    it’s better?

Reverting SearchBuilder fixed our problem.

Looks like 3.2.2 constructed queries on custom fields badly, but as SB
couldn’t cope with them, then it wasn’t really a problem! :slight_smile:

Looks like we just need to be careful not to upgrade SearchBuilder until
we can upgrade RT as well…

Thanks,

Tony

In further investigation we found a line in the DBIx::SearchBuilder
Changes file from earlier this year that says:

  • Allow ORs on left joins

It’s conceivable that that was upgraded with mysql - is it possible that
this change might have made the old 3.2.2 query work differently?

My surmise would be that if they weren’t allowed before, then it was
silently dropped, and never got to the database engine to generate the
aforementioned 1.4 gigarow temp table… Once they were allowed, it
wasn’t dropped anymore, and Bob’s your male relative.

Cheers,
– jra
Jay R. Ashworth jra@baylink.com
Designer Baylink RFC 2100
Ashworth & Associates The Things I Think '87 e24
St Petersburg FL USA http://baylink.pitas.com +1 727 647 1274

"That's women for you; you divorce them, and 10 years later,
  they stop having sex with you."  -- Jennifer Crusie; _Fast_Women_