Using streaming queries in MySQL to avoid data OOM

1. Introduction

When a program accesses a MySQL database, if the amount of data queried is particularly large, the database driver loads all the loaded data into the memory, which may cause a memory overflow (OOM).

In fact, MySQL database provides streaming query, which allows to load qualified data into memory in batches, which can effectively avoid OOM. This article mainly introduces how to use streaming query and compares it with ordinary query for performance testing.

2. JDBC implements streaming query

Streaming query can be implemented by setting the setFetchSize method of JDBC's PreparedStatement/Statement to Integer.MIN_VALUE or using the method Statement.enableStreamingResults() . When ResultSet.next() method is executed, it will be returned one by one through the database connection, which will not occupy a large amount of client memory.

public int execute(String sql, boolean isStreamQuery) throws SQLException {
 Connection conn = null;
 PreparedStatement stmt = null;
 ResultSet rs = null;
 int count = 0;
 try {
  //Get database connection conn = getConnection();
  if (isStreamQuery) {
   //Set streaming query parameters stmt = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
   stmt.setFetchSize(Integer.MIN_VALUE);
  } else {
   //Normal query stmt = conn.prepareStatement(sql);
  }

  //Execute the query to get the result rs = stmt.executeQuery();
  //Traverse the results while (rs.next ()) {
   System.out.println(rs.getString(1));
   count++;
  }
 } catch (SQLException e) {
  e.printStackTrace();
 finally
  close(stmt, rs, conn);
 }
 return count;
}

"PS": In the above example, the parameter isStreamQuery is used to switch between "streaming query" and "normal query" for the following test comparison.

3. Performance Test

A test table my_test is created for testing. The total amount of data is 27w . The following four test cases are used for testing:

• Large data volume general query (270,000 items)
• Large data volume streaming query (270,000 records)
• Small data volume general query (10 items)
• Small data volume streaming query (10 items)

3.1. Test large data volume general query

@Test
public void testCommonBigData() throws SQLException {
 String sql = "select * from my_test";
 testExecute(sql, false);
}

3.1.1. Query time

27w data volume takes 38 seconds

3.1.2. Memory usage

Uses nearly 1G memory

3.2. Testing large data volume streaming queries

@Test
public void testStreamBigData() throws SQLException {
 String sql = "select * from my_test";
 testExecute(sql, true);
}

3.2.1. Query time

27w data volume takes 37 seconds

3.2.2. Memory usage

Since it is acquired in batches, the memory fluctuates between 30-270m

3.3. Test small data volume ordinary query

@Test
public void testCommonSmallData() throws SQLException {
 String sql = "select * from my_test limit 100000, 10";
 testExecute(sql, false);
}

3.3.1. Query time

10 pieces of data take 1 second

3.4. Test streaming query with small amount of data

@Test
public void testStreamSmallData() throws SQLException {
 String sql = "select * from my_test limit 100000, 10";
 testExecute(sql, true);
}

3.4.1. Query time

10 pieces of data take 1 second

IV. Conclusion

MySQL streaming query has obvious optimization effects on memory usage, but has little impact on query speed. It is mainly used to solve the scenario of high memory usage when querying large amounts of data.

「DEMO address」: https://github.com/zlt2000/mysql-stream-query

This is the end of this article about using streaming queries in MySQL to avoid data OOM. For more relevant MySQL streaming query content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!