Bug 590723

Summary: Searching for supported module results in Internal server error
Product: [Community] Beaker Reporter: Petr Šplíchal <psplicha>
Component: inventoryAssignee: beaker-dev-list
Status: NEW --- QA Contact:
Severity: medium Docs Contact:
Priority: high    
Version: 0.5CC: abourne, bpeck, dcallagh, ebaak, hjia, kzhang, mcsontos, ncoghlan, ohudlick, rjoost, tools-bugs
Target Milestone: ---   
Target Release: ---   
Hardware: All   
OS: Linux   
Whiteboard: Misc
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Comment 1 Raymond Mancy 2010-08-11 21:21:08 EDT
The query takes so long it seems to timeout. Given a reduced search set it doesn't have this problem. Will have a look.
Comment 2 Marian Csontos 2010-08-12 01:44:18 EDT
Hint: May be a relative of Bug 584046?
Comment 5 Dan Callaghan 2010-09-05 21:35:36 EDT
Oops, that was a typo in my comment... the second pastebin link is the speedier version from *my vm*, not db01.
Comment 13 Bill Peck 2010-09-10 09:25:04 EDT
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.
Comment 14 Raymond Mancy 2010-09-11 00:06:46 EDT
(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.
Comment 15 Dan Callaghan 2010-09-12 20:46:50 EDT
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.
Comment 16 Dan Callaghan 2010-09-12 21:08:06 EDT
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
Comment 17 Dan Callaghan 2010-11-01 22:56:51 EDT
*** Bug 648762 has been marked as a duplicate of this bug. ***
Comment 18 Raymond Mancy 2010-11-01 23:03:25 EDT
Ok so should we then remove this feature seeing as it's only causing people woe?
Comment 19 Hushan Jia 2010-11-02 02:12:14 EDT
Hi, we are rely on this search function to find the certain machines in Beaker, so I think better to fix it than remove.
Comment 20 Raymond Mancy 2010-11-02 02:55:23 EDT
We're going to have to ween this out of the Key/Value table.
Comment 21 Bill Peck 2010-11-04 15:48:51 EDT
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.
Comment 22 Dan Callaghan 2010-11-04 19:34:10 EDT
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
Comment 23 Dan Callaghan 2010-11-04 19:38:53 EDT
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.
Comment 24 Dan Callaghan 2010-11-07 19:10:28 EST
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.
Comment 25 Dan Callaghan 2012-02-27 17:38:58 EST
*** Bug 797720 has been marked as a duplicate of this bug. ***
Comment 26 Kevin Baker 2012-03-02 16:05:53 EST
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:
Comment 27 Qian Hong 2012-03-04 21:33:21 EST
(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.
Comment 28 Dan Callaghan 2012-03-04 21:43:30 EST
(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.
Comment 29 Raymond Mancy 2012-03-07 00:52:30 EST
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.
Comment 30 Dan Callaghan 2013-02-06 19:42:01 EST
*** Bug 908522 has been marked as a duplicate of this bug. ***
Comment 31 Nick Coghlan 2013-02-06 22:07:51 EST
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!)
Comment 32 Dan Callaghan 2013-02-08 01:47:29 EST
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
Comment 33 Dan Callaghan 2013-02-08 01:49:57 EST
Clearing acks as there is no good solution for this.
Comment 34 Matthew Schick 2013-02-08 08:59:07 EST
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.
Comment 37 Nick Coghlan 2013-04-22 00:48:58 EDT
We still don't have a good answer for this, so 1.1 is likely the earliest chance for any associated changes.