OOM errors when running queries that have a boolean fulltext search?

Question

I am experiencing intermittent oom errors when running queries that have a boolean fulltext search in them.

I am on AWS Aurora (t2.medium).

Here is an example query, considering the table "sets" which has a fulltext index on the columns (savename, title) and has about 2 million rows in it.

        select          sets.id, sets.title,
                        drafts.draftId as draftId,
                        folderSets.folderId as folderId,
                        concat_ws(' ', savedBy.firstname, savedBy.lastname) as savedBy,
                        match(sets.savename, sets.title) against ("+s*" in boolean mode) as relevance
        from            sets as sets
        join            folderSets as folderSets on folderSets.setId = sets.id
        join            folders as folders on folders.id = folderSets.folderId 
        left join       draftSets as drafts on drafts.originalId = sets.id and drafts.userId = ?
        left join       users as savedBy on savedBy.id = folderSets.userId
        where           (folders.userId = ?)
                        and match(sets.savename, sets.title) against ("+s*" in boolean mode)
        order by        relevance
        limit           0, 25;

This query takes about 5 seconds to run. If I hammer it a few times, the query is likely to fail with an OOM.

If I change the boolean fulltext search phrase from "+s*" to "+sam*" -- so there are more characters before the wildcard -- the same query executies in about .15 seconds, without any issues.

If I remove the boolean search altogehter from the query, the query runs even faster, without any issues.

So it seems like there is something going on with the boolean fulltext portion of the query that is hitting a mysql limit.

Here are the innodb fulltext system variables:

ft_boolean_syntax................... + -><()~*:""&|
ft_max_word_len..................... 84
ft_min_word_len..................... 1
ft_query_expansion_limit............ 20
ft_stopword_file.................... /dev/null
innodb_ft_aux_table................. 
innodb_ft_cache_size................ 8000000
innodb_ft_enable_diag_print......... OFF
innodb_ft_enable_stopword........... OFF
innodb_ft_max_token_size............ 84
innodb_ft_min_token_size............ 1
innodb_ft_num_word_optimize......... 2000
innodb_ft_result_cache_limit........ 2000000000
innodb_ft_server_stopword_table..... 
innodb_ft_sort_pll_degree........... 2
innodb_ft_total_cache_size.......... 640000000
innodb_ft_user_stopword_table.......

Does anyone have any advice on any tuning that can be done to reduce the likelihood of an OOM related to boolean fulltext? I am unclear is this is something that can / should be fixed at the sql query level / the mysql innodb settings level / or if I am in the situation of needing to increase the size of the aws rds instance.

Thanks (in advance) for your help!

Answer 1

It sounds like you got to, perhaps, 500M x 5 processes and ran out of RAM.

Your choices:

• Don't ask for so many words (all words starting with "s").
• Don't allow users to ask for "s*".
• Get more RAM.
• Lower innodb_buffer_pool_size to free up some RAM
• See if AWS Aurora can be configured with more swap space -- for the rare times when you hit such big queries at the same time. It would be better to slow down rather than crash.
• Decrease innodb_ft_result_cache_limit -- Then deal with the fall out perhaps having the query fail.

Meanwhile, folderSets smells like a Many:many mapping table

Answer 2

Reading the manual one can find this description of the innodb_ft_result_cache_limit variable:

The InnoDB full-text search query result cache limit (defined in bytes) per full-text search query or per thread. Intermediate and final InnoDB full-text search query results are handled in memory. Use innodb_ft_result_cache_limit to place a size limit on the full-text search query result cache to avoid excessive memory consumption in case of very large InnoDB full-text search query results (millions or hundreds of millions of rows, for example). Memory is allocated as required when a full-text search query is processed. If the result cache size limit is reached, an error is returned indicating that the query exceeds the maximum allowed memory.

The default value of this variable, which is in effect in your case, means that every time you run your query it will try to allocate up to 2 GB (in the old-fashioned sense of "G") to store "intermediate and final results" of the text search. This is in addition to all other memory pools allocated by MySQL.

order by relevance limit 0, 25 in your query likely means that the entire result set matching the query search criteria must be retrieved and sorted in memory before the 25 rows can be returned.

To avoid the OOM condition you can either reduce innodb_ft_result_cache_limit to a more reasonable, for your use case, value (which can lead to errors if your text search criterion is too broad) or scale your server up to accommodate your demands.