Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was trying to execute a fairly large INSERT...SELECT in MySQL with JDBC, and I got the following exception:

Exception in thread "main" java.sql.SQLException: Out of memory (Needed 1073741824 bytes)
at com.mysql.jdbc.SQLError.createSQLException(

Since I'm not actually returning a ResultSet object, I thought the Java heap space shouldn't be an issue. However, I tried to up it anyway and it did no good. I then tried to execute the statement in MySQL Workbench and I got essentially the same thing:

Error Code 5: Out of memory (Needed 1073741816 bytes)

I should have plenty of RAM to complete these operations (enough to fit the whole table I'm selecting from), but I'm guessing there are various settings I need to tweak to take advantage of all my memory. I'm running an Amazon EC2 High Memory Double Extra Large Instance with a Windows Server 2008 AMI. I've tried fiddling with the my.ini file to use better settings, but for all I know I might have made things worse. Here's a dump of that file:

basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
myisam_repair_threads = 2
bulk_insert_buffer_size = 4000M
max_heap_table_size = 10G

So is this just a matter of changing the above settings to work better for my environment? If so, what settings should I use? I'm the only one who ever uses this instance; I use it for my personal hobby project that involves statistical analysis of large datasets. As such, I'm free to let it consume all available resources for my own queries.

If this is not a matter of changing those settings, what is the problem? Thanks for any help you can offer for how to better configure everything.

share|improve this question
Anyone using 1g query cache has no clue what they are doing. –  user11039 Aug 22 '12 at 18:07
@winmutt You may very well be correct, but your comment does not help anyone without further explanation. Could you please help us by giving reasons for your sentiment? –  Michael McGowan Aug 27 '12 at 20:54
A handy tool to get started is –  KCD Mar 6 '13 at 22:01

4 Answers 4

up vote 4 down vote accepted

Given this is a Windows installation, @DTest still provided the initial proper direction.

Apply the following formula:

Most people use this:

Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + (read_buffer_size + sort_buffer_size) X max_connections

I prefer this:

Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)

These variables are the ones you need to adjust until the formula yields 80% of installed RAM or less.

share|improve this answer

You did not say how much RAM you have? I assume it is at least 32GB.

innodb_buffer_pool_size - 23G

Good for that much RAM.

query_cache_size = 1G

Much too big. It is inefficient when it is big. Recommend no more than 50M.

key-buffer_size = 5G

There might be a hard limit of 4G (still) on Windows, had a hard limit of 4G. Your 5G may have turned into 1G. Anyway, if all your tables are InnoDB, why waste the ram. Set it to 50M.

Since the error message had exactly 1G, it smells like sort_buffer_size. 32M might be reasonable.

share|improve this answer

A quick way to determine how much memory MySQL thinks it could allocate is as follows:



When you run this script, it will tell you what percentage of the installed RAM MySQL thinks it can safely allocate. If the answer given is over 100%, you definitely need to lower your buffer sizes. The main one to focus on are:

key_buffer_size (not really effective past 4G)

@DTest already set the direction for you in his answer, so +1 for his anwser. The perl script will tell you what happens if you don't set it or if you change any value. Here is an example:

A client of mine has

Here is the output from

MySQLTuner 1.2.0 - Major Hayden
Bug reports, feature requests, and downloads at
Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: lwdba
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a-community-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 319M (Tables: 108)
[--] Data in InnoDB tables: 2M (Tables: 5)
[!!] Total fragmented tables: 22

-------- Performance Metrics -------------------------------------------------
[--] Up for: 52d 23h 15m 57s (72M q [15.875 qps], 241K conn, TX: 2B, RX: 1B)
[--] Reads / Writes: 59% / 41%
[--] Total buffers: 34.0M global + 2.7M per thread (1050 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.8G (72% of installed RAM)
[OK] Slow queries: 0% (54/72M)
[OK] Highest usage of available connections: 6% (65/1050)
[OK] Key buffer size / total MyISAM indexes: 8.0M/82.1M
[OK] Key buffer hit rate: 100.0% (4B cached / 1M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 948K sorts)
[OK] Temporary tables created on disk: 3% (11K on disk / 380K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 32K opened)
[OK] Open file limit used: 2% (125/5K)
[OK] Table locks acquired immediately: 99% (30M immediate / 30M locks)
[OK] InnoDB data size / buffer pool: 2.7M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)
table_cache (> 64)

Please notice under performance metrics

[--] Total buffers: 34.0M global + 2.7M per thread (1050 max threads)

that MySQL can allocate up to 72% of installed RAM based on the settings in /etc/my.cnf.

The 34M is based on innodb_buffer_pool_size and key_buffer_size combined

The 2.7M per thread was based on read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size.

Multiples of the 2.7M are based on max_connections.

Therefore, you must tweek these parameters until the Performance metrics report says you have under 100% (preferrably under 80%) of installed RAM.

share|improve this answer
I'm not sure I can use your tool; I'm using Windows. The documentation mentioned that Windows is not supported, but I tried anyway. When I tried running it, it indicated that it could not find mysqladmin in my $PATH, but MySQL's bin directory is indeed in my $PATH. –  Michael McGowan Mar 27 '11 at 22:10
Sorry, I didn't notice the Windows datadir. I'll add a different answer. –  RolandoMySQLDBA Mar 28 '11 at 15:22

I would try lowering your buffer sizes. Making them as large as you have them is going to cause problems. How much memory do you have available to run these values:

bulk_insert_buffer_size = 4000M

Some of the buffer sizes are allocated per-thread, for instance myisam_sort_buffer_size of 10G allocates 10G for each thread.

I would first reduce those values dramatically, and then investigate which values you really need to have this much RAM allocated (if any).

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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