10

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.

Improve this question
3
  • Anyone using 1g query cache has no clue what they are doing. Commented Aug 22, 2012 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? Commented Aug 27, 2012 at 20:54
  • A handy tool to get started is tools.percona.com/wizard Commented Mar 6, 2013 at 22:01

4 Answers 4

Reset to default
9

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
Improve this answer
4

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

Improve this answer
4

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.

Improve this answer
2
  • 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. Commented Mar 27, 2011 at 22:10
  • Sorry, I didn't notice the Windows datadir. I'll add a different answer. Commented Mar 28, 2011 at 15:22
1

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.