These are chat archives for ManageIQ/manageiq/performance

1st
Jul 2016
Keenan Brock
@kbrock
Jul 01 2016 15:44
I like to read regular expressions over the sql build we need to do for like clauses.
It is very easy to add regular expression support to rails (~5 lines)
Interesting read around performance ==> http://dba.stackexchange.com/questions/10694/pattern-matching-with-like-similar-to-or-regular-expressions-in-postgresql
This does mention different indexes that will work for left anchoring and stuff.
to keep in mind, column like 'abc%' often doesn't work so well. since it was indexed using no locale, and we're searching using a locale.
Jason Frey
@Fryguy
Jul 01 2016 15:46
@kbrock You've mentioned that before...does it make sense to just blanket update all of the indexes on string columns to have locale support?
Keenan Brock
@kbrock
Jul 01 2016 15:47
yea - not sure why I ended up back here again
Jason Frey
@Fryguy
Jul 01 2016 15:47
then that becomes a non-issue (aside from making sure future indexes have the right settings)
Keenan Brock
@kbrock
Jul 01 2016 15:47
I think so.
Jason Frey
@Fryguy
Jul 01 2016 15:47
curious what the concrete performance benefit is
Keenan Brock
@kbrock
Jul 01 2016 15:47
to be honest, I'd rather make the indexes case insensitive and remove all our lower(name)
that would be a big win
curious how to measure the concrete performane benefit
Jason Frey
@Fryguy
Jul 01 2016 15:48
yeah but that changes symantecs...we'd have to evaluate
Keenan Brock
@kbrock
Jul 01 2016 15:48
the business take is "I want to turn on and off case sensitivity per column per query"
but giving a customer a switch like that is just too much.
Jason Frey
@Fryguy
Jul 01 2016 15:48
yeah, I think a customer switch is bad
but certain columns shouldn't be indexed insensitive...like type
Keenan Brock
@kbrock
Jul 01 2016 15:49
While I like manual transmission - that bit of "control" (which is probably slower) is not for 90% of americans
yea
Jason Frey
@Fryguy
Jul 01 2016 15:49
so for case-insensitivity, we need to be more selective...the locale thing seems like an across-the-board change
Keenan Brock
@kbrock
Jul 01 2016 15:49
well, the think in that article I liked is actually the regular expression
instead of sql munging
Jason Frey
@Fryguy
Jul 01 2016 15:50

While I like manual transmission - that bit of "control" (which is probably slower) is not for 90% of americans

It's less about giving control and more about preventing the paradox of choice

Keenan Brock
@kbrock
Jul 01 2016 15:50
Vm.where("name like ?", "#{param}%")
# ==>
Vm.where(:name => /^#{param}.*/)
I prefer #2
Jason Frey
@Fryguy
Jul 01 2016 15:50
https://www.google.com/#q=paradox+of+choice <-- +1 to @chessbyte for clueing me into that many years ago :D
Keenan Brock
@kbrock
Jul 01 2016 15:50
jason - +1 paradox of choice
think that is why costco is so successful - just had conversation last weekend
wait
type should be case sensative?
while I don't object, I wonder if it actually matters
Jason Frey
@Fryguy
Jul 01 2016 15:52
probably not... but why would you ever search on type insensitively?
actually ...that makes me curious about whether a case-sensitive index is more performant than a case-insensitive index
Keenan Brock
@kbrock
Jul 01 2016 15:53
locales do strange stuff
I don't know if the indexes we create work that well
Jason Frey
@Fryguy
Jul 01 2016 15:53
only way to know is to measure
Keenan Brock
@kbrock
Jul 01 2016 15:53
for locales
it converts what we type into another format
for stuff like é
so if that translation layer also changes case... no real difference
just a slightly different hash lookup / state machine
but our indexes don't really use locales
so they can't really be used that efficiently
that link talks about this as well
I've been stumbling acros this more and more
anyway
@Fryguy do you have a strong preference of:
Vm.where(:type => 'X').where("name like ?", "#{param}%")
# ==>
Vm.where(:type => 'X', :name => /^#{param}.*/)
if we do the latter, it keeps us closer to AR. (potentially lets us use that query for ruby only / in memory implementations ;) )
Jason Frey
@Fryguy
Jul 01 2016 15:57
are there performance considerations there? I don't have a preference really but I'lm curious if the latter uses indexes properly or not
Keenan Brock
@kbrock
Jul 01 2016 15:58
that was the article
Oleg Barenboim
@chessbyte
Jul 01 2016 15:58
the article @kbrock linked to says that LIKE is faster than regexp
Keenan Brock
@kbrock
Jul 01 2016 15:58
for certain type of indexes
Jason Frey
@Fryguy
Jul 01 2016 15:58
yeah, that's what I'd expect
Oleg Barenboim
@chessbyte
Jul 01 2016 15:58
and SIMILAR TO is slowest
Keenan Brock
@kbrock
Jul 01 2016 15:58
but we can back out of that regex in particular with a shim
Jason Frey
@Fryguy
Jul 01 2016 15:59
Let's just switch to SOUNDEX :P
Keenan Brock
@kbrock
Jul 01 2016 16:00
that article also suggests ways to speed up ancestry
Jason Frey
@Fryguy
Jul 01 2016 16:00
On that note, we may want to consider converting to closure_tree at some point
Keenan Brock
@kbrock
Jul 01 2016 16:02
there has to be a way to work better with heterogenius trees
Nick LaMuro
@NickLaMuro
Jul 01 2016 16:02
If we are going to blanket update indexes, we will want to be aware of clients with large tables. Not sure what the upgrade process is, but dropping and creating indexes does do a table lock for the duration of the indexing process IIRC
Oleg Barenboim
@chessbyte
Jul 01 2016 16:02
@kbrock what are you actually trying to fix? some specific screen? something in general?
Jason Frey
@Fryguy
Jul 01 2016 16:03
@NickLaMuro This is exactly the reason we don't support backporting migrations to old versions...we can constrain these kinds of updates to major version releases only
Keenan Brock
@kbrock
Jul 01 2016 16:04
@chessbyte thanks - nope. I'm out. was renaming local initializers and found a regular expression one. curious about performance... snowball
Oleg Barenboim
@chessbyte
Jul 01 2016 16:04
gotcha
you seem pretty engaged for being out
Jason Frey
@Fryguy
Jul 01 2016 16:05
even so, I think it's something we should add to the performance pivotal board as an item to research
Keenan Brock
@kbrock
Jul 01 2016 16:05
but I still have pipe dream of implementing sql in memory
lol
Jason Frey
@Fryguy
Jul 01 2016 16:05
both blanket updating indexes for the proper locale support and switching to closure_tree
Keenan Brock
@kbrock
Jul 01 2016 16:05
@chessbyte appreciated your comment. it made me realize I have more pressing matters
@Fryguy let me know when you have some time for PR reviews
Keenan Brock
@kbrock
Jul 01 2016 16:25
ugh - dumb question
      vm.tag_add("monitor_me", :ns  => "/managed#{MiqAlertSet.namespace}")
How do I get the "classification" for the tag I just added to a vm?
Keenan Brock
@kbrock
Jul 01 2016 19:58
^ solved. boy was that painful
@Fryguy I'm blocked on #9447 - any time today?
Keenan Brock
@kbrock
Jul 01 2016 20:05

@jrafanie do you have stronv views pro/con for changing MiqEnterprise to just cache by 10 seconds instead of our funky approach?
https://github.com/ManageIq/manageiq/blob/master/app/models/miq_enterprise.rb#L30-L36

  def self.my_enterprise
    # Cache the enterprise instance, but clear the association
    #   cache to support keeping the associations fresh
    @my_enterprise ||= in_my_region.first
    @my_enterprise.send(:clear_association_cache) unless @my_enterprise.nil?
    @my_enterprise
  end

# vs
  cache_with_timeout(:my_enterprise, 10) { in_my_region.first}

latter makes my life easier since it hits MiqEnterprise.my_enterprise a bunch. will help anything that does rollups

Keenan Brock
@kbrock
Jul 01 2016 21:06
@NickLaMuro thanks for finding this. think I may just have to solve active_support::notifications so others can use them as well w/ rack mini-profiler
Nick LaMuro
@NickLaMuro
Jul 01 2016 21:13
Uh, no problem?
Keenan Brock
@kbrock
Jul 01 2016 21:18
lol
er - thanks for asking me something that I already had low on a todo list. that way I knew it was needed by someone and not just work I was trying to do (for some odd reason)
Nick LaMuro
@NickLaMuro
Jul 01 2016 21:26
ah, gotcha
Keenan Brock
@kbrock
Jul 01 2016 21:55
@NickLaMuro any suggestions MiniProfiler/rack-mini-profiler#254 would be great
if we can customize the sql patching, then we can put in activerecord notifications - or something else, and patch the way we want
that pr could be simplified by not separating sql detection from mongo detection.
maybe I should just have 1 method that detects them?
not thrilled about adding in active record. but they already had in the gemfile, so it is probably fine
while I don't like their monkey patching techniques (vs just have modules that are included) - that is for a different day