db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Andreasen <mich...@dunlops.com>
Subject Re: Out of Memory
Date Mon, 15 May 2006 18:18:16 GMT
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!
>


Mime
View raw message