0

I'm using MariaDB version 10.2.22 with a InnoDB engine, utf8 charset, it has one column that is indexed FULLTEXT. The table itself is only 4.4 GB with avg row length at 255. When I do a query such as:

    SELECT m.id as id
    FROM 
    table1 m,
    table2 d
    where 
    m.id= d.id AND
    MATCH ([column]) AGAINST ('"CE20-2019"' in boolean mode)

I get a "SQL Error [128] [HY000]:Table handler out of memory". I suspect that the "-2019" is being searched or not included, I'm not sure, and that the found matches exceeds the memory allowed. The [column] is a "LongText" datatype.

I've tried changing the innodb_buffer_pool_size to 4 GB and then 8 GB, but still get this error.

(System has 24 GB of RAM, not sure if my comfortable changing it to 70% of RAM listed on their documentation found here: https://mariadb.com/kb/en/mariadb-memory-allocation/), since I'm on version 10.2.22, this innodb_buffer_pool_size variable is suppose to be dynamic so I don't have to restart the MariaDB service to restart Mysqld.

Any ideas what's causing the Table handler out of memory?

4
  • Does the 2019 "word" occurs frequently in the dataset? (My Answers, below, is predicated on that assumption.)
    – Rick James
    Nov 2 '20 at 17:05
  • Yeah 2019 does appear quite a bit since this OCR files starting from last year and 2019 occurs quite a bit. It also gives me an error when I do a match([column]) against ('"2019"")
    – Yodavish
    Nov 2 '20 at 17:15
  • 70% of a 24GB server is still a reasonable setting for a machine that is running essently only the database.
    – Rick James
    Nov 2 '20 at 17:31
  • Consider writing a bug report about against ('"2019"").
    – Rick James
    Nov 2 '20 at 17:31
0

Plan A:

As you put the data into the table, change - to, say, _ so that such items will be on "word", not two. Do a similar change when constructing the AGAINST clause.

If necessary, make an extra column with that and do any other cleansing of the data -- Then index that column with FULLTEXT, but use the original table for output.

Plan B:

MATCH (col) AGAINST ('+CE20' in boolean mode)
AND col LIKE '%CE20-2019%'

Or, if you need a regexp to handle a letter/digit before:

MATCH (col) AGAINST ('+CE20' in boolean mode)
AND col REGEXP '\\bCE20-2019'

(That's for MySQL 8; use [[:<:]] instead of \\b for other versions. That is "word boundary" and should eliminate most of the 20 extra rows.)

That will run nearly as fast as the original query should have run.

Alas, this requires understanding the incoming data and making programmatic decisions as to how best to construct a fast query.

Note that the use of + when IN BOOLEAN MODE may be the 'right' thing to do for your app.

4
  • Tried Plan B first, originally it worked but when I performed a search on something I know that works and didn't error out before, it produced extra results. For instance, "TC19-23587" originally I would get 4 results, with the new query it produces 24 results. I might have to look at doing option A now.
    – Yodavish
    Nov 2 '20 at 18:45
  • @Yodavish - Check those extra 20 results. Might they have been "XYZTC19-..." or other cases where "%" acts differently than the "word boundary" of Fulltext? If so, we might be able to devise a REGEXP instead of LIKE.
    – Rick James
    Nov 2 '20 at 20:20
  • yeah it's OCR text so I think it's just including "[a-z0-9]TC19" or maybe even "[a-zA-z0-9][space] TC19". How could I include a REGEXP?
    – Yodavish
    Nov 2 '20 at 22:22
  • @Yodavish - If you want "[a-z0-9]TC19", FULLTEXT is not useful.
    – Rick James
    Nov 2 '20 at 23:14

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.