MySQL how to release memory used up by SELECT

memorymyisamMySQLndbcluster

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.

INSERT IGNORE INTO sid(sid) 
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:

max_allowed_packet=1024M

# Memory Table

max_heap_table_size=8192M

Everything else is default.

MySQL version 5.5.25, using MyISAM

Inserts into ndbcluster 7.2.7

NDB Config

SharedGlobalMemory=256M
DataMemory=2048M
IndexMemory=512M

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

Best Answer

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;
SELECT SLEEP(30);
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.

Related Solutions

MySQL – How Much Memory Will a MEMORY Table Take Up?

The exact memory requirement of a row is calculated from the following formula:

SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) × 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2)
+ ALIGN(length_of_row+1, sizeof(char*))

[src]

ALIGN() represents a round-up factor to cause the row length to be an exact multiple of the char pointer size. sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines.

So, on a 64-bit machine, replace sizeof(char*) with 8.

You can get an estimate of the length_of_row from the Information Schema:

SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM information_schema.tables WHERE  table_schema='foo' AND table_name='bar';

Then you add up all your BTREE keys and then HASH keys. Note that it might be worth it space-wise to convert any keys to HASH, as they require less memory.

I was going to mention the limitation of maximum MEMORY size dependent on max_heap_table_size, but gbn beat me to it.

Mysql – Setting up MySQL Cluster on Two Systems

Your management configuration seems fine to me. It sounds like ndb_mgmd didn't quite die or there is a problem with the contents in the /var/lib/mysql-cluster directory on the management server.

You might also want to make sure that ndb_mgmd isn't starting automatically, like through some startup script in /etc/init.d/.

To try and resolve the issue, I'd start with these steps first.

Since you are just getting started and don't have any real data yet.

Kill any left over ndb_mgmd processes with: killall ndb_mgmd or by rebooting if killall doesn't help.
Completely delete everything within /var/lib/mysql-cluster: rm -Rf /var/lib/mysql-cluster/*
Start the cluster process again: ndb_mgmd -f /etc/config.ini --initial
Verify that ndb_mgmd is listening with netstat

$> netstat -tlpn

Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:1186 0.0.0.0:* LISTEN 3251/ndb_mgmd

Connect to management node

$> ndb_mgm
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration


[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.9.26  (mysql-5.1.61 ndb-7.1.22)

Update

Here is a Quick Start guide from Oracle with all nodes, management, mysql and data running on one server.

http://downloads.mysql.com/tutorials/cluster/GetMySQLClusterRunning-LINUX.pdf

Best Answer

Related Solutions

MySQL – How Much Memory Will a MEMORY Table Take Up?

Mysql – Setting up MySQL Cluster on Two Systems

Related Question