You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Michael Andreasen <mi...@dunlops.com> on 2006/05/15 14:02:24 UTC
Out of Memory
I am running derby in embedded mode using EmbeddedSimpleDataSource and I
am running out of memory.
If I start with an empty, newly created database, and try to do a loop
that just keeps inserted records, I get an out of memory error after it
has inserted about 12,000 rows.
I am running a small application with the -Xmx8m set, when it begins
inserting free memory is about 6m. As it runs this goes down and down
until I get an OutOfMemory Exception.
It is as if every insert is being cached?
Any ideas?
Re: Out of Memory
Posted by Michael Andreasen <mi...@dunlops.com>.
I'll attach file as the format went a little mad when added in line.
Michael Andreasen wrote:
> Here is a demo.
>
> It will run ok on my desktop with -Xmx8m. However, I'm using the
> database for real on a CDC device and 8 meg is about the total memory
> I have for the database and the snall application together, so I'd
> really like derby to operate in 4 or 6 meg tops. Speed is not top
> priority here, so long as it actually operates. However, using -Xmx6m
> or less, I can't even get the test to complete without running out of
> memory.
>
>
>
Re: Out of Memory
Posted by Sunitha Kambhampati <ks...@gmail.com>.
Michael Andreasen wrote:
> Here is a demo.
>
> It will run ok on my desktop with -Xmx8m. However, I'm using the
> database for real on a CDC device and 8 meg is about the total memory
> I have for the database and the snall application together, so I'd
> really like derby to operate in 4 or 6 meg tops. Speed is not top
> priority here, so long as it actually operates. However, using -Xmx6m
> or less, I can't even get the test to complete without running out of
> memory.
The default page cache size of derby is 1000 pages and depending on the
schema the default page size can vary and the max pagesize can be 32k.
You could reduce the pagecache size by setting the following properties
derby.storage.pageSize=4096
derby.storage.pageCacheSize=40
References:
http://db.apache.org/derby/docs/10.1/tuning/rtunproper40688.html
http://db.apache.org/derby/docs/10.1/tuning/rtunproper40688.html
http://db.apache.org/derby/faq.html#derby_faster
HTH,
Sunitha.
Re: Out of Memory
Posted by Michael Andreasen <mi...@dunlops.com>.
Here is a demo.
It will run ok on my desktop with -Xmx8m. However, I'm using the
database for real on a CDC device and 8 meg is about the total memory I
have for the database and the snall application together, so I'd really
like derby to operate in 4 or 6 meg tops. Speed is not top priority
here, so long as it actually operates. However, using -Xmx6m or less, I
can't even get the test to complete without running out of memory.
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.FileWriter;
import java.sql.*;
import java.util.Random;
import java.text.DecimalFormat;
import java.util.StringTokenizer;
import org.apache.derby.jdbc.EmbeddedSimpleDataSource;
public class DbTest
{
static class Product
{
public int prodno;
public String pcode;
public String description;
public float costPrice;
public float listPrice;
public String newDesc;
public String packDesc;
public String unitDesc;
public float units;
public int suppno;
public boolean orderable;
public boolean show;
public String barcode;
public int imsId;
public int medType;
public int noahId;
public boolean hasImage;
public Date lastOrdered;
}
private static final float MEM_K = 1024;
private static final float MEM_M = 1024*MEM_K;
private static DecimalFormat form = new DecimalFormat("0.0");
private static PreparedStatement insStmt;
private static PreparedStatement updStmt;
public static void main(String[] args) throws Exception
{
EmbeddedSimpleDataSource dataSource= new EmbeddedSimpleDataSource();
dataSource.setDatabaseName("/test.db");
dataSource.setCreateDatabase("create");
Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
stmt.execute(
"create table product ( "+
"prodno int, "+
"pcode varchar(10), "+
"description varchar(60), "+
"cost_price float, "+
"list_price float, "+
"new_desc varchar(60), "+
"pack_desc varchar(20), "+
"unit_desc varchar(20), "+
"units float, "+
"supp_no int, "+
"orderable int, "+
"show int, "+
"barcode varchar(40), "+
"ims_cat int, "+
"med_type int, "+
"noah_id int, "+
"has_image int, "+
"last_ordered date) " );
stmt.execute(
"create index product_x1 on product (prodno)");
stmt.execute(
"create index product_x2 on product (pcode)");
stmt.close();
insStmt = conn.prepareStatement(
"insert into product ("+
"prodno,pcode,description,cost_price,list_price,new_desc," +
"pack_desc,unit_desc,units,supp_no,orderable,show,barcode," +
"ims_cat,med_type,noah_id,has_image) " +
"values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
updStmt = conn.prepareStatement(
"update product " +
"set description=?, cost_price=?, list_price=?, new_desc=?, " +
"pack_desc=?, unit_desc=?, units=?, supp_no=?, orderable=?, " +
"show=?, barcode=?, ims_cat=?, med_type=?, noah_id=?,
has_image=? " +
"where prodno=?" );
Random rnd = new Random();
FileWriter f = new FileWriter("/load.file");
for (int i=0; i<100000; i++) {
int prodno = rnd.nextInt(20000); // make a random product
for update or insert
f.write(""+prodno+"\t");
f.write("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\n");
if ((i%100)==0) showMemory("Creating Rows "+i);
}
f.close();
String s;
FileReader r = new FileReader("/load.file");
BufferedReader br = new BufferedReader(r);
int i = 0;
while ((s=br.readLine())!=null) {
StringTokenizer tok = new StringTokenizer(s);
Product p = new Product();
p.prodno = Integer.parseInt(tok.nextToken());
p.pcode = "P"+p.prodno;
p.description = tok.nextToken();
p.costPrice = (float) 1.00;
p.listPrice = (float)2.00;
p.newDesc = "Another Description";
p.packDesc = "Pack Size";
p.unitDesc = "Unit Desc";
p.units = (float)1.0;
p.suppno = 1;
p.orderable = true;
p.show = false;
p.barcode = "1234567890123";
p.imsId = 99;
p.medType = 9;
p.noahId = 999;
p.hasImage = true;
p.lastOrdered = new Date(System.currentTimeMillis());
actionProduct(p);
if ((i%100)==0) showMemory("Loading Row: "+i);
i++;
}
insStmt.close();
updStmt.close();
br.close();
r.close();
System.out.println("Closing database ...");
conn.close();
dataSource.setShutdownDatabase("shutdown");
System.out.println("Database Closed.");
System.exit(0);
}
public static void showMemory(String mess)
{
long free = Runtime.getRuntime().freeMemory();
long total = Runtime.getRuntime().totalMemory();
System.out.println(mess+", " +
"Total Memory: "+niceFormat(total)+", "+
"Free Memory: "+niceFormat(free));
}
public static void actionProduct(Product p) throws SQLException
{
updStmt.setInt(1,p.prodno);
updStmt.setString(1,p.description);
updStmt.setFloat(2,p.costPrice);
updStmt.setFloat(3,p.listPrice);
updStmt.setString(4,p.newDesc);
updStmt.setString(5,p.packDesc);
updStmt.setString(6,p.unitDesc);
updStmt.setFloat(7,p.units);
updStmt.setInt(8,p.suppno);
updStmt.setBoolean(9,p.orderable);
updStmt.setBoolean(10,p.show);
updStmt.setString(11,p.barcode);
updStmt.setInt(12,p.imsId);
updStmt.setInt(13,p.medType);
updStmt.setInt(14,p.noahId);
updStmt.setBoolean(15,p.hasImage);
updStmt.setInt(16,p.prodno);
int upd = updStmt.executeUpdate();
if (upd==0) {
insStmt.setInt(1,p.prodno);
insStmt.setString(2,p.pcode);
insStmt.setString(3,p.description);
insStmt.setFloat(4,p.costPrice);
insStmt.setFloat(5,p.listPrice);
insStmt.setString(6,p.newDesc);
insStmt.setString(7,p.packDesc);
insStmt.setString(8,p.unitDesc);
insStmt.setFloat(9,p.units);
insStmt.setInt(10,p.suppno);
insStmt.setBoolean(11,p.orderable);
insStmt.setBoolean(12,p.show);
insStmt.setString(13,p.barcode);
insStmt.setInt(14,p.imsId);
insStmt.setInt(15,p.medType);
insStmt.setInt(16,p.noahId);
insStmt.setBoolean(17,p.hasImage);
insStmt.executeUpdate();
}
}
public static String niceFormat(long b)
{
if (b<MEM_K) return ""+b;
else if (b<MEM_M) return form.format(b/MEM_K)+" K";
else return form.format(b/MEM_M)+" M";
}
}
Craig L Russell wrote:
> Hi,
>
> Often, memory leaks are caused by not closing statements or result
> sets. If you post the program that leaks, we might be able to help you
> figure out if it's a problem in your application or a problem in Derby.
>
> Regards,
>
> Craig
>
> On May 15, 2006, at 5:02 AM, Michael Andreasen wrote:
>
>> I am running derby in embedded mode using EmbeddedSimpleDataSource
>> and I am running out of memory.
>>
>> If I start with an empty, newly created database, and try to do a
>> loop that just keeps inserted records, I get an out of memory error
>> after it has inserted about 12,000 rows.
>>
>> I am running a small application with the -Xmx8m set, when it begins
>> inserting free memory is about 6m. As it runs this goes down and down
>> until I get an OutOfMemory Exception.
>>
>> It is as if every insert is being cached?
>>
>> Any ideas?
>
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
Re: Out of Memory
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi,
Often, memory leaks are caused by not closing statements or result
sets. If you post the program that leaks, we might be able to help
you figure out if it's a problem in your application or a problem in
Derby.
Regards,
Craig
On May 15, 2006, at 5:02 AM, Michael Andreasen wrote:
> I am running derby in embedded mode using EmbeddedSimpleDataSource
> and I am running out of memory.
>
> If I start with an empty, newly created database, and try to do a
> loop that just keeps inserted records, I get an out of memory error
> after it has inserted about 12,000 rows.
>
> I am running a small application with the -Xmx8m set, when it
> begins inserting free memory is about 6m. As it runs this goes down
> and down until I get an OutOfMemory Exception.
>
> It is as if every insert is being cached?
>
> Any ideas?
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!