Search unresponsive and causes timeout

[mariagould]

Searches on prd and stg are indicating slow response times, occasionally resulting in a 504 Gateway timeout. Behavior seems to be consistent across prd and stg. Searching anonymous vs. as a logged-in user does not seem to produce different results.

To reproduce, click "Search" in the top navigation of the EZID UI. Try running searches on the following terms. Some of these searches may result in a gateway timeout; others may be successful albeit slow. Others might return results as expected with a reasonably fast turnaround.

• "ucsf"
• "tobacco"
• "test"
• "water"
• "cactus"
• "doi:10.7291/D14T2W"
• "ark:/88122/ylmx0001"
• "ark:/88122"
• "rj reynolds"

[datadavev]

Appears that the db instances are hitting resource barriers. There is an occasional error Exception Value: (188, 'FTS query exceeds result cache limit') which will cause an error response. This occurs when the query handling a full text search exceeds the configured memory, for which the setting is (unchanged from default 2GB) :

MySQL [ezid]> show variables like 'innodb_ft_result_cache_limit';
+------------------------------+------------+
| Variable_name                | Value      |
+------------------------------+------------+
| innodb_ft_result_cache_limit | 2000000000 |
+------------------------------+------------+
1 row in set (0.01 sec)

The setting could be increased, but first it would be necessary to review the queries to see what improvements should be implemented.

[datadavev]

The queries being executed can be determined by watching the performance_schema.events_statements_current table (requires the performance_schema config to be ON). The general text search in EZID results in queries of the form:

SELECT COUNT(*) AS __count FROM ezidapp_searchidentifier 
WHERE (ezidapp_searchidentifier.publicSearchVisible AND 
MATCH (ezidapp_searchidentifier.keywords) AGAINST ('+water' IN BOOLEAN MODE));

the EXPLAIN ANALYZE output:

-> Aggregate: count(0)  (cost=1.14 rows=1) (actual time=44996.712..44996.712 rows=1 loops=1)
    -> Filter: ((0 <> ezidapp_searchidentifier.publicSearchVisible) and (match ezidapp_searchidentifier.keywords against ('+water' in boolean mode)))  (cost=1.05 rows=1) (actual time=39.672..44972.326 rows=112125 loops=1)
        -> Full-text index search on ezidapp_searchidentifier using ezidapp_searchidentifier_keywords (keywords='+water')  (cost=1.05 rows=1) (actual time=39.668..44891.607 rows=117708 loops=1)

[datadavev]

The problem comes down to determining the number of rows that match. This is an expensive operation that is used to show the number of matches and compute the number of pages to display. There is no easy way around this if an accurate count is desired. Solutions can include:

• Get an approximate count only. This may be suitable for the UI - is it really necessary for someone to be able to page through all few hundred pages?
• Deploy alternate technology. A dedicated FTS such as Solr or Elastic Search (Open Search) can readily handle these types of queries.
• Refactor the database by analyzing the text and generating a separate table of keywords and alter the search UI accordingly. This would be more complicated and error prone than using a dedicated full text search solution.

[jsjiang]

Another option is to refactor the "executeSearchCountOnly" function. Instead of "SELECT COUNT(*) AS __count FROM ezidapp_searchidentifier where ", we can just select an indexed field such as ID. Then get the count from the length of the returned record sets.

I tested the keyword search "water" by selecting ID on the Dev server. The results was returned in seconds.

[marisastrong]

This isn't a high priority given the search functionality is mainly used by the internal team.

[marisastrong]

io1/3000 provisioned

• [x] ticket in to change from gp2 vs gp3
• [ ] db query analysis w/ Jing and & Martin
• [x] review librato metrics

[ashleygould]

created issue #412 to track change to EBS volume type from gp2 vs gp3