Why does MySQL say I'm out of memory?

Question

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(SQLError.java:1073)

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:

[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
character-set-server=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=1024M
table_cache=256
tmp_table_size=25G
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_repair_threads = 2
myisam_sort_buffer_size=10G
key_buffer_size=5000M
bulk_insert_buffer_size = 4000M
read_buffer_size=8000M
read_rnd_buffer_size=8000M
sort_buffer_size=1G
innodb_additional_mem_pool_size=26M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=13M
innodb_buffer_pool_size=23G
innodb_log_file_size=622M
innodb_thread_concurrency=18
innodb_file_per_table=TRUE
join_buffer_size=4G
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.

Answer 1

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.

sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
max_connections

Answer 2

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:

query_cache_size=1024M
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=10G
key_buffer_size=5000M
bulk_insert_buffer_size = 4000M
read_buffer_size=8000M
read_rnd_buffer_size=8000M
sort_buffer_size=1G
innodb_buffer_pool_size=23G

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).

Answer 3

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

wget mysqltuner.pl

perl mysqltuner.pl

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:

sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
max_connections
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
read_buffer_size=128K
read_rnd_buffer_size=256K
sort_buffer_size=2M
join_buffer_size=128K
max_connections=1050

Here is the output from mysqltuner.pl:

MySQLTuner 1.2.0 - Major Hayden
Bug reports, feature requests, and downloads at http://mysqltuner.com/
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.

Answer 4

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.