0

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!

4
  • How many zillion words match "+s*"?
    – Rick James
    Oct 29, 2021 at 3:52
  • "If I hammer it a few times" -- sequentially? or in parallel?
    – Rick James
    Oct 29, 2021 at 3:53
  • in parallel -- sequential queries seem to not be an issue Oct 29, 2021 at 4:18
  • Additional information request, please. Any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; AND very helpful information, includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. Nov 15, 2021 at 1:37

2 Answers 2

0

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

1

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.

5
  • Thanks for the reply. So I am clear... You are saying that when innodb_ft_result_cache_limit is set to 2G, mysql allocates that much memory to the query, even if the query doesn't use that much? Eg if the query uses 25mb, 2G is still allocated? Or if not, and it only actually uses the 25mb, how does decreasing the max allowable help? Oct 28, 2021 at 4:32
  • "Memory is allocated as required when a full-text search query is processed."
    – mustaccio
    Oct 28, 2021 at 12:06
  • Always remember your AWS AURORA t2.medium has a TOTAL of 4G of RAM to support, OS, MySQL and any other application running on this server - such as php or opcache or whatever else you see listed in TOP or HTOP requires some RAM. Consider next bump up in RAM. Oct 28, 2021 at 12:37
  • Alright thanks for the help -- looks like increasing the instance size is likely the easiest solution for now. I'll do some research into how opensearch works alongside a relational db, as it seems like long-term doing a lot of search in mysql may run us into problems, and it may be better to remove search from the db altogether. Oct 29, 2021 at 0:51
  • Removing search will likely not be in your customer's best interest. Nov 15, 2021 at 1:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.