Hello,
I am adding data into a MySQL table, after around 20.000 iterations I get this error message java.sql.SQLException: Out of memory (Needed 8164 bytes)
I execute with java -jar -Xmx1024m -XX:StackShadowPages=60 Navigation.jar
The error message does not appear at the same point each time.
any help will be highly appreciated.
Do you get this error only when you start from jar file, or using class file as well?
Could you post a stacktrace please? It is something like this:
catch(SQLException e)
{
e.printStackTrace();
// you will need to start your application from a console, not by dblclicking the jar
}
BTW you could more experts in the database javaforum:
http://forum.java.sun.com/forum.jspa?forumID=48
You need to show the code part where you are trying to insert records.
In order to get a more accurate response, you need to provide details.
Initially, I would assume the following:
1. You are not using batch insert where necessary
2. You are creating connection every time you insert record
3. Connections are not properly closed leaving precious resources such as memory on air.
Thank you for your replies.
public synchronized void Update(String expression) throws SQLException
{
/*Statement st = null;
st = conn.createStatement();*/
//int i = st.executeUpdate(expression);
PreparedStatement st = conn.prepareStatement(expression);
int i = st.executeUpdate();
if (i == -1)
{System.out.println("db error : " + expression);}
//st.close();
}
This is the code I am using for inserting, where you see comments is code that I have previously used. I am sure there is free memory since I am using Runtime.getRuntime().maxMemory() , TotalMemory(), FreeMemory() methods. The connection opens once and then I start inserting and the error comes up when I try to add data from the same experiment.
How can I check the tablespace, and memory pool size of the table?
By the way with the e.PrintStackTrace... there seams to be an issue with that and jdk 1.6? whenever I use it nothing appears on the screen.. also with the older jdks when you do not try catch you get an exception on the screen but nothing appears with 1.6.
There is two problems with your code:
1 - Use of synchronized method instead of synchronized block. This will increase the memory allocated to your program.
2 - You create a prepared statement for every sql update ! You should rather create a single preparedstatement instance and call executeUpdate method for every update. See http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html
Hope That Helps
I'm still curious as to how are you committing your transaction.
1. Are you using transaction at all? or getting connection through DriverManager?
2. If "AutoCommit" is set to false, do you know that if you don't commit your subsequent updates/inserts through one single connection, all of those DMLs are stacked up in your connection object which in the end may no longer be manageable?
3. What type of MySQL DB Engine are you using? InnoDB? MyISAM? Berkely?
4. Can you show us your MySQL configuration file? /etc/my.cnf or c:\my.cnf or sometimes called c:\my.ini
If you are using InnoDB, you need to check the size of the data log. A typical sample is as follows:
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:100M:autoextend
innodb_log_group_home_dir = /var/log/innodblogs/
innodb_log_arch_dir = /var/log/innodblogsarchive/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 160M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 40M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
java_2006 thanks for the input I will try that... unfortunately is also difficult to debug since run time until the error appears is 3-4 hours....
This is my configurations files, I don't use transactions and I use DriverManager.getConnection() object to execute queries but as I said I create only one connection object through the entire process. I am using the default AutoCommit value (which means that I have no idea what it is :-P) and the default MySQL DB Engine which I believe it is MyISAM. Do you think changing to InnoDB would make a difference?
# CLIENT SECTION
# -
#
# The following options will be read by MySQL client applications.
# Note that only client applications shipped by MySQL are guaranteed
# to read this section. If you want your own MySQL client program to
# honor these values, you need to specify it as an option during the
# MySQL client library initialization.
#
[client]
port=3306
[mysql]
default-character-set=latin1
# SERVER SECTION
# -
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this
# file.
#
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
#Path to the database root
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"
# The default character set that will be used when a new schema or table is
# created and no character set is defined
default-character-set=latin1
# The default storage engine that will be used when create new tables when
default-storage-engine=MYISAM
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=100
# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size=0
# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
table_cache=256
# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size=46M
# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client's threads are put in the cache if there aren't
# more than thread_cache_size threads from before. This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn't give a notable performance
# improvement if you have a good thread implementation.)
thread_cache_size=8
#*** MyISAM Specific options
# The maximum size of the temporary file MySQL is allowed to use while
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
# If the file-size would be bigger than this, the index will be created
# through the key cache (which is slower).
myisam_max_sort_file_size=100G
# If the temporary file used for fast index creation would be bigger
# than using the key cache by the amount specified here, then prefer the
# key cache method. This is mainly used to force long character keys in
# large tables to use the slower key cache method to create the index.
myisam_max_extra_sort_file_size=100G
# If the temporary file used for fast index creation would be bigger
# than using the key cache by the amount specified here, then prefer the
# key cache method. This is mainly used to force long character keys in
# large tables to use the slower key cache method to create the index.
myisam_sort_buffer_size=91M
# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size=149M
# Size of the buffer used for doing full table scans of MyISAM tables.
# Allocated per thread, if a full scan is needed.
read_buffer_size=64K
read_rnd_buffer_size=256K
# This buffer is allocated when MySQL needs to rebuild the index in
# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE
# into an empty table. It is allocated per thread so be careful with
# large settings.
sort_buffer_size=256K
#*** INNODB Specific options ***
# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
skip-innodb
# Additional memory pool that is used by InnoDB to store metadata
# information. If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS. As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size=2M
# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=1
# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size=1M
# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system. Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size=8M
# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size=10M
# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency=8
Okay, MyISAM doesn't support transaction. I'm speaking based on my experience of using MySQL in the last 5 years. InnoDB supports transaction.
If you encounter SQLException during the insert, you loose all the pending records that is waiting to be inserted.
Now, just as I have said, if you're only using single connection - you will end up with problems inserting or updating records into your database because at some point, you're connection object will expire or something evil will happen.
I would suggest that you use the batch insert where possible and this may require a bit of modification to your code. Batch-insert all related records, i.e., group of records from one business transaction, commit it, close the connection and free the resources - period.
Example process flow (you'll need to implement properly):
Connection conn = getConnection(); // encapsulate DriverManager.getConnection
Statement stmt = null;
try {
stmt = conn.createStatement();
conn.setAutoCommit(false); // applicable if InnoDB
stmt.addBatch("INSERT INTO blahblahblah VALUES('blah','blah')");
stmt.addBatch("INSERT INTO blahblahblah VALUES('blah','blah')");
int[] insertCount = stmt.executeBatch();
System.out.println(insertCount.lenght);
conn.commit(); // applicable if InnoDB
catch (Exception e) {
e.printStackTrace();
}
finally {
try {
stmt.close();
conn.close();
} catch (Exception e) {
// don't care; just closing connection anyway
}
}
You can also implement a for-loop to iterate through a Statement in case you'll want to pass-in an array of DML (such as INSERT, DELETE, UPDATE) to batch-insert them:
...
stmt = conn.createStatement();
for (String sql : sqls) {
stmt.addBatch(sql);
}
...
stmt.executeBatch();
...
HTH.
