Bug #104946 EXPLAIN uses too much memory
Submitted: 14 Sep 2021 21:12 Modified: 16 Sep 2021 19:11
Reporter: Charles Thompson Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.7.33 OS:CentOS (RDS)
Assigned to: CPU Architecture:Any

[14 Sep 2021 21:12] Charles Thompson
Description:
I executed the EXPLAIN below using FORCE INDEX and my server started to stall queries and use a lot of memory. My charts showed swap went up to 140GB before it was OOM killed. I tried to kill the EXPLAIN, but it went into 'Killed' state and kept running until the server was killed.

When I tried not using FORCE INDEX, the EXPLAIN took about 30 seconds, but successfully finished. 

I anonymized the query below, but it represents the original one executed. Also, the IN clauses had a combined total of 2.4k values. 

SELECT
  *
FROM
  db.table FORCE INDEX (idx_field1)
WHERE
  field1 IN (
    '34543453-ADS',
    '32422121-ADS',
    '43242332-ADS',
  )
  AND field2 IN (
    '3534545334',
    '5463453211',
  )
  AND field3 = 53433432
  AND field4 IN (
    '2121212',
    '4234322',
  )

How to repeat:
Execute the EXPLAIN
[15 Sep 2021 12:30] MySQL Verification Team
Hi Mr. Thompson,

Thank you for your bug report.

However, we do not think that this is a bug at all.

Based on the data that you have provided us, we have calculated that each of these queries uses circa 150 Mb of RAM. If you run the query in 20 parallel threads, it will use (at that moment) circa 3 Gb of memory, which is not taken out of any of the existing memory pools, like innodb_buffer_pool or similar, but is allocated on the need-to-have basis. This can lead to memory fragmentation. The problem would be further exacerbated if the column that you are searching for is not of the VARCHAR/CHAR type, in which case consumption of memory is even larger.

There is, however, an easy workaround. For such large filtering, you would do much better and faster by using a temporary table and then simply do a join.

Not a bug.
[15 Sep 2021 18:08] Charles Thompson
Hello,

In this case, this query used over 140GB memory. That's normal? My server got OOM killed because it used this much.

Thanks!
[15 Sep 2021 18:20] Charles Thompson
Also, what about it not being able to be killed?
[16 Sep 2021 12:01] MySQL Verification Team
Hi,

Yes, what you are experiencing is normal.

If you are using 64-bit CPU, then you got 8-byte alignments in the objects, so those item are even larger. Regarding killing the server, use shutdown procedure.

You could increase the verbosity of the general log and tail it, so that you can watch what is server doing. It takes lot's of time to free 140 Gb of memory.

We are recommending you, once again, to change this type of query with very large number of items , as we have already explained.
[16 Sep 2021 19:11] Charles Thompson
Thanks for the recommendation - I understand it isn't good to have that many values in the IN () clauses. My point about trying to kill the query is AFTER I killed the query, it kept going up in memory, not down. MySQL didn't terminate it like it should have.

I just find it strange that MySQL allocated over 140GB memory for an EXPLAIN. That's highly abnormal.
[17 Sep 2021 12:32] MySQL Verification Team
Hi,

That only means that you have not reached yet yours 2.4 thousands items. 

MySQL honours KILL signal, but ONLY when it is safe. If you are in the middle of the dynamic memory allocations it is NOT a safe time to kill the thread or query.

As simple as that.