Re: [MonetDB-users] mserver5 crashed after 10, 000 times sql queries through JDBC interface
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.
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
Dear Martin, In fact, this program is an extracted version of a big system. In this system, we're using apache-commons-dbutils to execute the database operations. I know the commons-dbutils always use preparedstatement to execute sql, and always close the resultset and the preparedstatement after each query, so I write this simple program to verify my guess. I add the close statement of the resultset and the jdbc statement, and rerun the benchmark, the result is same: with jdbc normal statement, the memory usage starts from 17M, to 85M after first 10,000 queries, then to 92M after the second 10000 queries, then 93M, 94M ....about 1M memory increase per 10000 queries. But with the jdbc prepared statement(I have closed the resultset and the preparedstatement and the connection after each query), the memory usage starts from 17M to 800M after first 10000 queries, after 4-5 times benchmark, the mserver5 process will use more then 2G memory and crashed. Finally, Is the latest monetdb5 program stable enough for production use? I love its light speed:) It's really a great database for query intensive program. Thank you. /////////////////////////////////////////////////////////////////////////////////// finally { try { rs.close(); } catch (SQLException e1) { } try { stmt.close(); } catch (SQLException e1) { } try { conn.close(); } catch (Exception e) { } }
On 26-12-2009 10:36:41 +0800, xulizhong wrote:
Dear Martin, In fact, this program is an extracted version of a big system. In this system, we're using apache-commons-dbutils to execute the database operations. I know the commons-dbutils always use preparedstatement to execute sql, and always close the resultset and the preparedstatement after each query, so I write this simple program to verify my guess. I add the close statement of the resultset and the jdbc statement, and rerun the benchmark, the result is same: with jdbc normal statement, the memory usage starts from 17M, to 85M after first 10,000 queries, then to 92M after the second 10000 queries, then 93M, 94M ....about 1M memory increase per 10000 queries. But with the jdbc prepared statement(I have closed the resultset and the preparedstatement and the connection after each query), the memory usage starts from 17M to 800M after first 10000 queries, after 4-5 times benchmark, the mserver5 process will use more then 2G memory and crashed.
The problem with your example program is that you create many prepared statements (on the server side) but only use them once. For that use a normal statement is better, since it doesn't create a server-side executor for the prepared query. The problem you are triggering here is that MonetDB doesn't clean up server-side executors as time goes, since it cannot know that you will never reference them again. I guess we can solve this by implementing a release for the executors which is triggered by a close of the PreparedStatement (either by you or the garbage collector, as it would be before you added the close() calls).
Finally, Is the latest monetdb5 program stable enough for production use? I love its light speed:) It's really a great database for query intensive program.
This is something which you can only judge yourself, as every workload is different, and hence behaves differently.
participants (3)
-
Fabian Groffen -
Martin Kersten -
xulizhong