db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Matt Pouttu-Clarke <Matt.Pouttu-Cla...@icrossing.com>
Subject Re: Multi-thread access to read-only DB in embedded mode?
Date Thu, 06 Jan 2011 17:10:14 GMT
Hi Kathey,

Thanks for the quick response.

Yes, I tried separate connections but the thread access is not concurrent.
HereĀ¹s my code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Properties;


public class DerbyQuery implements Runnable
{
    
    private static final int QUERY_COUNT = 1000;
   // private static final Connection conn;
    
    static { 
        try {
            Properties props = System.getProperties();
            props.setProperty(
                 "derby.database.defaultConnectionMode",
                 "readOnlyAccess"
            );
            /*
            PrintWriter pw = new PrintWriter(System.out,true);  // to print
messages
            NetworkServerControl server =
                new NetworkServerControl(InetAddress.
                                         getByName("localhost"),1527);
                server.start(pw);
            */
            
//Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
        } catch(Exception e) {
            throw new IllegalStateException(e);
        }
    }
    
    @Override
    public void run()
    {
        try {
            Connection conn =
DriverManager.getConnection("jdbc:derby:ip-info");
            conn.setAutoCommit(false);
        /*
        Statement stmt = conn.createStatement();
            
        stmt.execute("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
                               "'derby.storage.pageCacheSize'," +
                               "'131072')");
        
       

      stmt.executeUpdate("ALTER TABLE IP_INFO ADD CONSTRAINT IP_INFO_CHK_01
CHECK (IP_FROM <= IP_TO)");
        conn.commit();
         */
//       stmt.executeUpdate("CALL
SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");

//      stmt.executeUpdate("CALL
SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1)");
        
      
//        stmt.execute("LOCK TABLE IP_INFO IN SHARE MODE");
        
        PreparedStatement ps = conn.prepareStatement(
                  "SELECT * \n" +
//                  "FROM IP_INFO  \n" +
                  "FROM IP_INFO  -- DERBY-PROPERTIES index = IP_INFO_I01 \n"
+
                  "WHERE IP_TO >= ? ");
        ps.setFetchSize(1);
        Date startDate = new Date();
        for(int x = 0; x < QUERY_COUNT; x++) {
            long random = (long)(Math.random() * Integer.MAX_VALUE);
            ps.setLong(1, random);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                long min = rs.getLong(1);
                long max = rs.getLong(2);
                /*
                System.out.println(random + " between " + min + " and " +
max);
                if(min <= random && max >= random) {
                    System.out.println("true");
                } else {
                    System.out.println("false");
                }
                */
            }
            rs.close();
            //System.out.println(new Date() + " run " + x);
        }
        System.out.println(QUERY_COUNT + " lookups, start: " + startDate +
", finish: " + new Date());
        ps.close();
/*     
        ResultSet timingResults = stmt.executeQuery(
                     "VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");

        if (timingResults.next()) {

            System.out.println(timingResults.getString(1));

        }
       
        timingResults.close();
*/       
        conn.commit();
        conn.close();
        try {
            
DriverManager.getConnection("jdbc:derby:ip-info;shutdown=true").close();
        } catch(SQLException e) {
            if(!e.getMessage().contains("shutdown")) {
                throw e;
            }
        }
        
        } catch(SQLException e) {
            e.printStackTrace();
        }
        
    }
    
    /**
     * @param args
     * @throws SQLException
     */
    public static void main(String[] args) throws SQLException
    {
        for(int x = 0; x < 2; x++) {
            new Thread(new DerbyQuery()).run();
        }
    }

}

And the results are:
1000 lookups, start: Thu Jan 06 10:07:32 MST 2011, finish: Thu Jan 06
10:07:37 MST 2011
1000 lookups, start: Thu Jan 06 10:07:37 MST 2011, finish: Thu Jan 06
10:07:41 MST 2011

Thanks,
Matt

On 1/6/11 9:29 AM, "Kathey Marsden" <kmarsdenderby@sbcglobal.net> wrote:

>    On 1/6/2011 7:53 AM, Matt Pouttu-Clarke wrote:
>>  Multi-thread access to read-only DB in embedded mode? Hi All,
>>  
>>  I am using Derby as an embedded database within a Hadoop job to lookup IP
>> geographic info.
>>  
>> http://mpouttuclarke.wordpress.com/2010/12/10/java-embedded-db-for-ip2locatio
>> n-in-hadoop/
>>  
>>  The problem is that Hadoop has an option called JVM sharing where more than
>> one thread may be active in the JVM instance.  Since the embedded option only
>> supports one thread at a time, I have had to turn off JVM sharing for my IP
>> lookup job (by setting mapred.job.reuse.jvm.num.tasks to 1).
>>  
>>  
>  Embedded definitely supports more than one thread at a time as long as all of
> those threads are in the same JVM.  Each thread should just have it's own
> separate  Connection.
>  
>  


iCrossing Privileged and Confidential Information
This email message is for the sole use of the intended recipient(s) and may contain confidential
and privileged information of iCrossing. Any unauthorized review, use, disclosure or distribution
is prohibited. If you are not the intended recipient, please contact the sender by reply email
and destroy all copies of the original message.



Mime
View raw message