Bug 590723

The query takes so long it seems to timeout. Given a reduced search set it doesn't have this problem. Will have a look.

Hint: May be a relative of Bug 584046?

Oops, that was a typo in my comment... the second pastebin link is the speedier version from *my vm*, not db01.

Hi Dan,

Just to give you a little more background about this.  The original query that Schick is talking about was self joins on the key table.  I can't remember the number of self joins that made it go south but it should be easy to find out on your local machine. ;-)


Also - There is a bug in the WebUI key value search where it doesn't alias the joins so only the first join to the key_int table or key_string table works.  This bug does not exist in the xml parser and thats where we saw the performance hit.

<hostRequires>
 <and>
  <key_value key="CPUFLAGS" op="=" value="lm"/>
  <key_value key="HVM" op="=" value="1"/>
  <key_value key="MODULE" op="=" value="aufofs4"/>
 </and>
</hostRequires>

notice those are all string based keys, the performance hit is only when its multiple self joins on the same table.

(In reply to comment #13)
> Hi Dan,
> 
> Just to give you a little more background about this.  The original query that
> Schick is talking about was self joins on the key table.  I can't remember the
> number of self joins that made it go south but it should be easy to find out on
> your local machine. ;-)
> 
> 
> Also - There is a bug in the WebUI key value search where it doesn't alias the
> joins so only the first join to the key_int table or key_string table works. 

Ok, I believe there is a simple fix of adding 'has_alias=True' into the 'KeyColumn' definition.
See https://bugzilla.redhat.com/show_bug.cgi?id=632806

> This bug does not exist in the xml parser and thats where we saw the
> performance hit.
> 
> <hostRequires>
>  <and>
>   <key_value key="CPUFLAGS" op="=" value="lm"/>
>   <key_value key="HVM" op="=" value="1"/>
>   <key_value key="MODULE" op="=" value="aufofs4"/>
>  </and>
> </hostRequires>
> 
> notice those are all string based keys, the performance hit is only when its
> multiple self joins on the same table.

Okay Bill I see what you mean, having more than 6 <key_value/> conditions in <hostRequires/> causes a blowout in the query optimizer when optimizer_search_depth is left at the default setting.

Back to the original query for this bug: rmancy suggested using FORCE INDEX [1] but I couldn't come up with any combination of index hints that would cause MySQL to pick the right query plan.

I can't think of anything else we can do, short of drastically changing our db schema/querying approaches. :-(

[1] http://dev.mysql.com/doc/refman/5.0/en/index-hints.html

*** Bug 648762 has been marked as a duplicate of this bug. ***

Ok so should we then remove this feature seeing as it's only causing people woe?

Hi, we are rely on this search function to find the certain machines in Beaker, so I think better to fix it than remove.

We're going to have to ween this out of the Key/Value table.

I'm still at a loss why this is a problem for the WebUI but not when we pass in an xml that asks for several key values to search on.

<hostRequires>
 <and>
  <key_value key="CPUFLAGS" op="=" value="lm"/>
  <key_value key="HVM" op="=" value="1"/>
  <key_value key="MODULE" op="=" value="aufofs4"/>
 </and>
</hostRequires>

Is it because of the contains?  Currently I don't support the contains search method from the xml.

I was looking into this some more this morning. That <hostRequires/> xml you pasted Bill will result in a monster of a query [1] but it still performs well. That's when I noticed that module searches in the web UI also perform very well *if* you are searching for a module that actually exists (like autofs4). If you search for one for which we have no records (like z90crypt, as per the original bug report) it runs slowly.

Here's a reproducer (this needs the optimizer_search_depth=6 setting as per the comments above):

mysql> SELECT DISTINCT system.id FROM system LEFT OUTER JOIN system_group AS system_group_1 ON system.id = system_group_1.system_id LEFT OUTER JOIN tg_group AS tg_group_1 ON tg_group_1.group_id = system_group_1.group_id LEFT OUTER JOIN user_group AS user_group_1 ON tg_group_1.group_id = user_group_1.group_id LEFT OUTER JOIN tg_user AS tg_user_1 ON tg_user_1.user_id = user_group_1.user_id LEFT OUTER JOIN key_value_int ON system.id = key_value_int.system_id LEFT OUTER JOIN key_ ON key_.id = key_value_int.key_id LEFT OUTER JOIN key_value_string ON system.id = key_value_string.system_id LEFT OUTER JOIN tg_user ON system.user_id = tg_user.user_id  WHERE system.private = 0 AND key_value_string.key_value LIKE '%autofs4%' AND key_value_string.key_id = 14 ORDER BY system.fqdn ASC LIMIT 1;
+------+
| id   |
+------+
| 2220 | 
+------+
1 row in set (0.22 sec)

mysql> SELECT DISTINCT system.id FROM system LEFT OUTER JOIN system_group AS system_group_1 ON system.id = system_group_1.system_id LEFT OUTER JOIN tg_group AS tg_group_1 ON tg_group_1.group_id = system_group_1.group_id LEFT OUTER JOIN user_group AS user_group_1 ON tg_group_1.group_id = user_group_1.group_id LEFT OUTER JOIN tg_user AS tg_user_1 ON tg_user_1.user_id = user_group_1.user_id LEFT OUTER JOIN key_value_int ON system.id = key_value_int.system_id LEFT OUTER JOIN key_ ON key_.id = key_value_int.key_id LEFT OUTER JOIN key_value_string ON system.id = key_value_string.system_id LEFT OUTER JOIN tg_user ON system.user_id = tg_user.user_id  WHERE system.private = 0 AND key_value_string.key_value LIKE '%asdf%' AND key_value_string.key_id = 14 ORDER BY system.fqdn ASC LIMIT 1;
Empty set (53.89 sec)

The only difference between those two is LIKE '%autofs4%' vs LIKE '%asdf%'. It makes no difference whether we do substring search or just use the = operator.



[1] http://pastebin.test.redhat.com/33831

We could work around this by first querying whether there are any module rows matching the given value (this query is fast, irrespective of whether there are any matching rows). If there are no matching rows, we could substitute FALSE for the query clause so that we can avoid issuing the slow query.

It seems suckful that we are essentially doing the query optimizer's job for it by hand, but I can't think of anything else we can do about it in the short term.

We've missed the boat for 0.5.61 I think, but we can do the workaround described in comment 23 for 0.5.62.

*** Bug 797720 has been marked as a duplicate of this bug. ***

copying in comment from refer bz#590723

Dan Callaghan 2012-02-27 17:38:58 EST
Searches on "MODULE" were intentionally disabled in the web UI some time ago,
to prevent users triggering this unresolved bug which kills our MySQL database:

(In reply to comment #26)
> copying in comment from refer bz#590723
> 
> Dan Callaghan 2012-02-27 17:38:58 EST
> Searches on "MODULE" were intentionally disabled in the web UI some time ago,
> to prevent users triggering this unresolved bug which kills our MySQL database:

Hi, is it dangerous if I search on "MODULE" with job xml?
Thanks.

(In reply to comment #27)
> Hi, is it dangerous if I search on "MODULE" with job xml?
> Thanks.

As per comment 22, these "bad" queries have only been observed when searching for a module which does not exist at all in Beaker. So if you stick to MODULE values which are known to exist, it should be fine.

Let's put a manual filter in so then people don't read this bug and think it's a good idea to filter via directly meddling with the url.

*** Bug 908522 has been marked as a duplicate of this bug. ***

At least adding the fast "does this exist" prefilter seems like a decent workaround for 1.0, even if it's an ugly hack (there should definitely be a comment referencing back to this bug report!)

Well, I wrote a patch for the pre-filtering workaround described in comment 23:

http://gerrit.beaker-project.org/1712

but it's not enough. :-( I can still easily trigger queries that send MySQL into a tizzy, for example by adding two NETWORK "is not" filters on the search bar. So it seems my analysis in comment 22 was incorrect.

(My memory back to 2010 is hazy, but it seems to me looking at this again now that the problem has gotten worse... maybe because our queries have grown in complexity since then, or our database has. But even still, the two queries I gave in comment 22 are *both* slow now that I try them again, which I cannot explain.)

The only ways forward I see for this bug are:
* increase the optimizer_search_depth setting in MySQL
* remove key-values in Beaker

Clearing acks as there is no good solution for this.

Increasing optimizer_search_depth isn't an option on this one for the reasons I posted previously.  Limiting the complexity or fixing the queries would be the best option here.

We still don't have a good answer for this, so 1.1 is likely the earliest chance for any associated changes.