I executed an INSERT IGNORE combined with SELECT from large table. The SELECT took up 16GB of memory and went into SWAP before I terminated it with CTRL+C.

SELECT sid FROM data WHERE sid != "";

The data contains 64 million rows.

Data type of sid is VARCHAR(13). There were 28.2M records that matched the SELECT (I ran it afterwards). That results in around 350MB of data that needs to be SELECTed and INSERTed.

Is it possible to release memory allocated by MySQL? The query was terminated and there is no longer a need for that memory. Why is it still lingering?

Here are some config details:


# Memory Table


Everything else is default.

MySQL version 5.5.25, using MyISAM

Inserts into ndbcluster 7.2.7

NDB Config


My box runs 1/3 SQL nodes, management node. 2 other servers run data nodes.

  • Are you using InnoDB ??? What version of MySQL are you running ??? Sep 6, 2012 at 20:55
  • MySQL version 5.5.25, using MyISAM for select, inserts into ndbcluster 7.2.7
    – nick
    Sep 6, 2012 at 21:23
  • @FrazerClement, any opinions on this one ??? Sep 6, 2012 at 21:56
  • @MatKeep, any opinions on this one ??? Sep 6, 2012 at 21:57

2 Answers 2


You could run RESET QUERY CACHE to eliminate collected result sets in the query cache. You could also be more aggressive and drop the query cache and recreate it a different size. For example, to set the query_cache_size to 512M, do the following

SET GLOBAL query_cache_size = 0;
SET GLOBAL query_cache_size = 1024 * 1024 * 512;

Unfortunately, you can't release the MyISAM Key Buffer and InnoDB Buffer Pool in the same manner. You would have to resize them in /etc/my.cnf (or my.ini for Windows) and restart mysql.

Reading your comments, I can confidently say that MyISAM does not cache data pages. The query cache is the only thing I can think of that grabs and holds result sets. If you are loading in bulk, there should be a lot of query cache invalidations going on.

I am not Cluster Certified, but I will take shot at saying this: Since you are using MySQL Cluster, you may want to try shutting down any mysqld processes or having the cluster disconnect data nodes. Also, be aware of the transmission speed that data hops around the Cluster.

  • i had 16gb worth of memory used, my query cache is set to 16MB
    – nick
    Sep 6, 2012 at 21:37
  • @DTest it was either that or Fortunately, you can't, LOL !!! Sep 6, 2012 at 21:37
  • After changing the destination table to MyISAM the queries execute extremely fast and take up virtually no memory. This means just one thing.. The problem is caused by the cluster. I am still yet to find out why, but it appears that filling up a MyISAM and changing it to ndbcluster is a better way of doing this.
    – nick
    Sep 6, 2012 at 22:19

Let's review...

  • 16GB of RAM.
  • max_heap_table_size = 8G -- that says you are letting each process create a MEMORY table that is half the size of RAM! Not safe. Likely to lead to swapping.
  • Query Cache is irrelevant because the SELECT's resultset is bigger than you have allocated (16MB) for the QC. Don't make it bigger, it won't help.
  • MyISAM's key_buffer_size can be changed dynamically. But it is irrelevant. Your SELECT will do either a table scan or an index scan (if data has an index starting with sid). The key_buffer is not used for a table scan. For an index scan, it need be big enough for only one block. Bigger will not help. Too big will crowd RAM.
  • You have not said what allocations are made for the NDB table sid. This is likely to be in RAM. Furthermore, you have not said how many nodes that sid is spread across.
  • You have not said whether MyISAM and NDB are running on the same box.
  • i updated my question to include some info about the cluster.
    – nick
    Sep 7, 2012 at 3:36
  • max_heap_table_size = 8G is very likely to be the cause of swapping. Change to 100M.
    – Rick James
    Dec 1, 2017 at 23:25

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.