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.