Quote from MariaDB Documents

Description: Limit in bytes of the InnoDB FULLTEXT index query result cache per fulltext query. The latter stages of the full-text search are handled in memory, and limiting this prevents excess memory usage. If the limit is exceeded, the query returns an error.

Default: 2000000000

I want to know if for example both queries run FTS query in parallel, MySQL Allocate about 1907MB to per query (if query wants) ?

Because we have to many FTS query and all are complex query which lead to growing use of RAM.

Update 1

This is one our query:

select id,title,content,time,c_count,agency_name
    from news join agency use(id)
    where time >= '2016-10-01 00:00:00'
      and time <  '2016-11-01 00:00:00'
      and match("content") against('+("Hello" "Hi" "Hey")+("World" "All" "guys")'
            in boolean mode)
    order by c_count desc
  • 180GB of RAM, so that is about 1% of RAM? What is Max_used_connections; better yet, how many queries are likely to run in parallel? – Rick James Dec 7 '16 at 20:43
  • Do you use OR in any of the complex queries? (That usually prevents use of indexes.) Can you show us one of the queries, together with SHOW CREATE TABLE? – Rick James Dec 7 '16 at 20:44
  • Yes Rick we use OR but in Fulltext for example: match(content) against('"Hello" "Hi" "Good Morning"'). Max used connection is 32. max_tmp_table set to 512M. It's not obvious how many queries run in parallel. Sometimes four quries, sometimes eight etc. This is one our query: Look at Update 1 – HamoonDBA Dec 8 '16 at 5:10
  • Wouldn't that match content="All attendees said 'Hello'."? – Rick James Dec 8 '16 at 6:35
  • Suggest that you write a bug at bugs.mysql.com -- it seems unconscionable for a default value to involve 2GB of RAM, even if there is only one instance per server. Even worse since it is "per FTS query or per thread". (That seems ambiguous.) – Rick James Dec 8 '16 at 6:50

I wrote a bug report to complain about the default value of that setting.

  • Users: Click "Affects me" on the bug. – Rick James Sep 29 '17 at 20:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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