Typeahead on [srch/notes] paralyzes app

[icarito]

@steviepubliclab reported this afternoon that the website was slow, and referenced Skylight. Analysis shows the relevant is srch/notes. @milaaraujo let me know if I can help debug.

[milaaraujo]

Code:

• https://github.com/publiclab/plots2/blob/master/app/services/execute_search.rb#L15-L16
• https://github.com/publiclab/plots2/blob/master/app/services/search_service.rb#L48-L51

  def search_notes(input, limit = 25, order = :natural, type = :boolean)
  Node.search(query: input, order: order, type: type, limit: limit)
      .where("`node`.`type` = 'note'")
  end

• https://github.com/publiclab/plots2/blob/master/app/models/node.rb#L27-L68

  def self.search(query:, order: :default, type: :natural, limit: 25)
  order_param = if order == :default
                  { changed: :desc }
                elsif order == :likes
                  { cached_likes: :desc }
                elsif order == :views
                  { views: :desc }
                end
  
  if ActiveRecord::Base.connection.adapter_name == 'Mysql2'
    if order == :natural
      query = connection.quote(query.to_s)
      if type == :boolean
        # Query is done as a boolean full-text search. More info here: https://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html
        nids = Revision.select("node_revisions.nid, node_revisions.body, node_revisions.title, MATCH(node_revisions.body, node_revisions.title) AGAINST(#{query} IN BOOLEAN MODE) AS score")
          .where("MATCH(node_revisions.body, node_revisions.title) AGAINST(#{query} IN BOOLEAN MODE)")
          .limit(limit)
          .distinct
          .collect(&:nid)
      else
        nids = Revision.select("node_revisions.nid, node_revisions.body, node_revisions.title, MATCH(node_revisions.body, node_revisions.title) AGAINST(#{query} IN NATURAL LANGUAGE MODE) AS score")
          .where("MATCH(node_revisions.body, node_revisions.title) AGAINST(#{query} IN NATURAL LANGUAGE MODE)")
          .limit(limit)
          .distinct
          .collect(&:nid)
      end
      where(nid: nids, status: 1)
    else
      nids = Revision.where('MATCH(node_revisions.body, node_revisions.title) AGAINST(?)', query).collect(&:nid)
      tnids = Tag.find_nodes_by_type(query, type = %w(note page)).collect(&:nid) # include results by tag
      where(nid: nids + tnids, status: 1)
        .order(order_param)
        .limit(limit)
        .distinct
    end
  else
    nodes = Node.limit(limit)
      .where('title LIKE ?', '%' + query + '%')
      .where(status: 1)
      .order(order_param)
  end
  end

[milaaraujo]

Do you know where the endpoint is used on the site?

[jywarren]

Thanks I'll try to look more into this soon... Saw another potential instance of this today reported by @skillfullycurled

https://oss.skylight.io/app/applications/GZDPChmcfm1Q/1548196200/57m/endpoints

[jywarren]

Could it have been a direct search at /search/notes?

[jywarren]

Oh sorry @skilfullycurled, misspelled

[skilfullycurled]

@jywarren That's on me. It's what happens when it's 2008, get your first domain, and then make everything consistent without realizing the implications for your future. I've been meaning to change it.

[icarito]

Indeed same issue I think. I agree with @milaaraujo we should look into reducing our database of inactive users or only query those. @skilfullycurled - certainly not all things work all the time, thanks for the positive input!

[skilfullycurled]

not all things work all the time

Actually, now that I think on it, that'd probably make for a good 500 error page.

I'm going to go submit a feature request.

Brb.

[jywarren]

Found the endpoint: it's on https://publiclab.org/post

I think this has potential for optimization in both JavaScript and in the back-end, and has a lot in common with: https://github.com/publiclab/plots2/issues/4670

[icarito]

Today we had another dramatic slowdown for about an hour at 6:30PM - 7:30PM UTC. .

[icarito]

I have checked the logs and found that the flurry of searches stems from typeahead search indeed!

[icarito]

[379a8f91-10e2-455a-b7f0-8df2f702f782] Started GET "/api/srch/notes?query=Soil%20Contm" for REDACTED at 2019-02-11 18:14:09 +0000
[2901cf71-76e8-478b-b9f1-3adf97080251] Started GET "/api/srch/notes?query=Soil%20Cont" for REDACTED at 2019-02-11 18:14:13 +0000
[fea79ecd-0375-454f-9ec0-141a29100b1d] Started GET "/api/srch/notes?query=Soil%20Conta" for REDACTED at 2019-02-11 18:14:13 +0000
[743a9606-c694-4f47-867f-0ac58ea6b8eb] Started GET "/api/srch/notes?query=Soil%20Contam" for REDACTED at 2019-02-11 18:14:14 +0000
[564a2877-ae95-4992-8c75-5a60e59d389d] Started GET "/api/srch/notes?query=Soil%20Contami" for REDACTED at 2019-02-11 18:14:17 +0000
[9ae08d81-5486-4d62-9003-32035186d299] Started GET "/api/srch/notes?query=Soil%20Contamin" for REDACTED at 2019-02-11 18:14:17 +0000
[a9dca184-9a03-4db6-82a8-599afe62ac5a] Started GET "/api/srch/notes?query=Soil%20Contamina" for REDACTED at 2019-02-11 18:14:21 +0000

[icarito]

As a workaround to prevent user experience degradation we might want to throttle the API endpoint?

I also like the idea of issuing typeahead requests every few seconds if the entry input has changed, rather than onChange.

[icarito]

This is happening now on any keypress, even arrow keys!

I've made https://github.com/publiclab/plots2/pull/4841 hopefully it will help!

[icarito]

The more one types the longer it takes for the API to respond I guess it's slowing down due to the many requests. It'd be cool to cache responses in Javascript. Also I noticed even empty string and one or two chars are being queried (I thought minLength: 3 was meant to avoid this?).

[jywarren]

Thanks, @icarito i'm traveling now so didn't see this. Merged https://github.com/publiclab/plots2/pull/4841 and thanks -- remaining tasks for optimizing are listed at:

• https://github.com/publiclab/plots2/issues/3472
• https://github.com/publiclab/plots2/issues/4670#issuecomment-460770933

Closing this now to focus on those!