Thank you. Not being an JDBC expert myself, i notice in preparemode you do not free the prepared query. You never close the result sets/connections either. This means the server most likely keeps them all, which could explain your memory. It should not crash though. xulizhong wrote:
Could you provide the query and schema? CREATE TABLE STOCKINFO ( STOCK_ID INTEGER NOT NULL, STOCK_CODE VARCHAR(30), EX_CODE VARCHAR(30), SH_CODE VARCHAR(30), LONGNAME_CN VARCHAR(50), SHORTNAME_CN VARCHAR(30), EX_ID INTEGER NOT NULL, CSRC_CAT INTEGER DEFAULT NULL, SW_CAT INTEGER DEFAULT NULL, SH_CAT INTEGER DEFAULT NULL, HAS_DETAIL BOOLEAN DEFAULT NULL, BG_CODE VARCHAR(20), IPO_TIME DATE DEFAULT NULL, BOARD_CAT INTEGER DEFAULT NULL, IS_ST BOOLEAN DEFAULT NULL, HAS_OPTION BOOLEAN DEFAULT NULL, CURRENCY INTEGER DEFAULT NULL, CARRY INTEGER DEFAULT NULL, DECIMALS INTEGER DEFAULT NULL, MIN_PRICE_CHANGE DOUBLE DEFAULT NULL, STOCK_CAT INTEGER DEFAULT NULL, SURGED_LIMIT DOUBLE DEFAULT NULL, DECLINE_LIMIT DOUBLE DEFAULT NULL, HIGH_52WK INTEGER DEFAULT NULL, LOW_52WK INTEGER DEFAULT NULL );
query: SELECT STOCK_ID FROM STOCKINFO WHERE LOW_52WK<85 and HIGH_52WK>110 and EX_ID=15 AND IPO_TIME>'2000-1-1' AND STOCK_CODE LIKE '1%';
I write a simple java program using 4 threads to execute this query with a connection pool of 4 max active connections. The mserver5 process' memory increase steadily upto 2.5G, then the process crashes with segmentation fault:(
I have noticed something special, when I run the query using : ResultSet result = conn.prepareStatement(sql).executeQuery(); the monetdb5 server will consume large amount of memory and crash definitely. However, when I query using: ResultSet result = conn.createStatement().executeQuery(sql); the server's memory usage is very stable.
This may indicate there's something wrong with the prepared statement of the sql module of MonetDB5.
Below is my test program: ////////////////////////////////////////////////////////// import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Date; import java.util.concurrent.ExecutorService; import java.util.concurrent.Executors; import java.util.concurrent.TimeUnit;
import org.apache.commons.dbcp.BasicDataSource;
public class MonetBenchmark {
/** * @param args */ public static void main(String[] args) { ExecutorService executor = Executors.newFixedThreadPool(4);
final BasicDataSource dataSource = new BasicDataSource(); dataSource.setMaxActive(4); dataSource.setInitialSize(4); dataSource.setDriverClassName("nl.cwi.monetdb.jdbc.MonetDriver"); dataSource.setUrl("jdbc:monetdb://localhost/demo"); dataSource.setUsername("monetdb"); dataSource.setPassword("monetdb");
final String sql = "SELECT STOCK_ID FROM STOCKINFO WHERE LOW_52WK<85 and HIGH_52WK>110 and EX_ID=15 AND IPO_TIME>'2000-1-1' AND STOCK_CODE LIKE '1%';";
System.out.println("running ... " + new Date());; long start = System.currentTimeMillis(); int loop = 10000; for (int x = 0; x < loop; x++) { executor.submit(new Runnable() { public void run() { Connection conn = null; try { conn = dataSource.getConnection(); ResultSet rs = conn.createStatement().executeQuery(sql); //using prepared statement will cause the server crashed with segmentation fault and very big memory usage. // ResultSet rs = conn.prepareStatement(sql).executeQuery();
while(rs.next()) { //... } } catch (SQLException e) { e.printStackTrace(); } finally { try { conn.close(); } catch (Exception e) { } } } });
}
try { executor.shutdown(); executor.awaitTermination(5, TimeUnit.MINUTES); } catch (InterruptedException e) { e.printStackTrace(); }
long time = System.currentTimeMillis() - start; System.out.println(time + " ms for " + loop + " times query! " + (time / loop) + " ms/q"); } }
And my last question, is this newest version of MonetDB suitable for production use if I always use normal jdbc statement to execute my query?
Thanks alot.
------------------------------------------------------------------------------ This SF.Net email is sponsored by the Verizon Developer Community Take advantage of Verizon's best-in-class app development support A streamlined, 14 day to market process makes app distribution fast and easy Join now and get one step closer to millions of Verizon customers http://p.sf.net/sfu/verizon-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users