In-depth analysis of JDBC and MySQL temporary tablespace

background

Temporary tablespaces are used to manage database sorting operations and to store temporary objects such as temporary tables and intermediate sorting results. I believe that you will often encounter related requirements in development. The following article will give you detailed information about JDBC and MySQL temporary tablespaces, and share them for your reference and study. Let's take a look at the detailed introduction.

The JDBC connection parameter useCursorFetch=true is used, and the query result set is stored in the mysqld temporary tablespace, causing the ibtmp1 file size to surge to more than 90G, exhausting the server disk space. To limit the size of the temporary tablespace, set:

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2G

Problem Description

After limiting the temporary table space, when the application still accesses it in the previous way, after the ibtmp1 file reaches 2G, the program waits until it times out and disconnects. SHOW PROCESSLIST shows that the program's connection thread is in sleep state, and the state and info information are empty. This is not very friendly for application development. There is no prompt information when analyzing the cause after the program waits for timeout.

Problem Analysis Process

To analyze the problem, we conducted the following tests

Test environment:

mysql:5.7.16

java:1.8u162

jdbc driver: 5.1.36

OS: Red Hat 6.4

1. Manually simulate the scenario where the temporary table exceeds the maximum limit

Simulate the following environment:

ibtmp1:12M:autoextend:max:30M

Delete the k field index of the sbtest table with 5 million rows

When running a group by query, if the size of the generated temporary table exceeds the limit, an error will be reported directly:

select sum(k) from sbtest1 group by k;
ERROR 1114 (HY000): The table '/tmp/#sql_60f1_0' is full

2. Check the driver's settings for mysql

In the previous step, we saw that manual execution of sql returns an error, but jdbc does not return an error, causing the connection to sleep all the time. It is suspected that the mysql driver has made special settings. The driver connects to mysql and checks what settings have been made through general_log. No special settings were found.

3. Test JDBC connection

In the background of the problem, there is a special configuration for JDBC: useCursorFetch=true. I don’t know if it is related to hiding the error. Next, test it:

The following phenomena were found:

When adding the parameter useCursorFetch=true, the same query will not report an error

This parameter is used to prevent the returned result set from being too large and to use segmented reading. That is, after the program sends a SQL statement to MySQL, it will wait for feedback that MySQL can read the result. Because MySQL reports an error after the returned result reaches the ibtmp upper limit when executing the SQL statement, but does not close the thread. The thread processes the sleep state, and the program cannot get feedback and will keep waiting without reporting an error. If you kill this thread, the program will report an error.

Without the parameter useCursorFetch=true, the same query will result in an error

in conclusion

1. Under normal circumstances, an error will be reported when the temporary table size reaches the ibtmp upper limit during SQL execution;

2. When JDBC sets useCursorFetch=true , no error will be reported when the temporary table size reaches the ibtmp upper limit during SQL execution.

Solution

I further learned that using useCursorFetch=true is to prevent the query result set from being too large to burst the JVM;

However, using useCursorFetch=true will cause ordinary queries to generate temporary tables, causing the temporary table space to be too large;

The solution to the problem of too large temporary tablespace is to limit the size of ibtmp1. However, useCursorFetch=true causes JDBC to not return an error.

Therefore, other methods need to be used to achieve the same effect, and the program must also report an error accordingly after the SQL report an error. In addition to the segment reading method of useCursorFetch=true, you can also use the stream reading method. The stream reading procedure is detailed in the attachment.

Error comparison

Segment reading mode, after SQL reports an error, the program does not report an error

· In stream reading mode, after SQL reports an error, the program will report an error

Memory usage comparison

Here we compare the three methods of normal reading, segment reading, and stream reading. The initial memory usage is about 28M:

After normal reading, the memory occupies more than 100M

After the segment is read, the memory occupies about 60M

After the stream is read, the memory occupies about 60M

Additional knowledge points

MySQL shared temporary tablespace knowledge points

MySQL 5.7 has made improvements to temporary tablespace and has separated temporary tablespace from ibdata (shared tablespace files). And you can restart and reset the size to avoid the problem of ibdata being too large to release as before.
Its parameters are: innodb_temp_data_file_path

1. Performance

When MySQL starts, an ibtmp1 file is created under datadir with an initial size of 12M. It will expand infinitely under the default value:

Generally speaking, if the temporary table caused by the query (such as group by) exceeds the size limit of tmp_table_size and max_heap_table_size, an innodb disk temporary table is created (the default temporary table engine of MySQL5.7 is innodb) and stored in the shared temporary tablespace;

If an operation creates a temporary table of size 100M, the temporary tablespace data file will be expanded to 100M to meet the needs of the temporary table. When a temporary table is dropped, the freed space can be reused for a new temporary table, but the ibtmp1 file remains extended.

2. Query View

You can query the usage of shared temporary tablespace:

SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE AS TotalSizeBytes, DATA_FREE,MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME = 'innodb_temporary'\G
*************************** 1. row ***************************
    FILE_NAME: /data/mysql5722/data/ibtmp1
TABLESPACE_NAME: innodb_temporary
      ENGINE: InnoDB
    INITIAL_SIZE: 12582912
   TotalSizeBytes: 31457280
    DATA_FREE: 27262976
  MAXIMUM_SIZE: 31457280
1 row in set (0.00 sec)

3. Recycling methods

Restart MySQL to recover

4. Limit size

To prevent temporary data files from growing too large, you can configure the innodb_temp_data_file_path (requires restart to take effect) option to specify a maximum file size. When the data file reaches the maximum size, queries will return an error:

innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:2G

5. Temporary tablespace vs. tmpdir

The shared temporary tablespace is used to store data such as non-compressed InnoDB temporary tables, related objects, and rollback segments.

tmpdir is used to store specified temporary files and temporary tables. Unlike the shared temporary tablespace, tmpdir stores compressed InnoDB temporary tables.

This can be tested with the following statement:

CREATE TEMPORARY TABLE compress_table (id int, name char(255)) ROW_FORMAT=COMPRESSED;
CREATE TEMPORARY TABLE uncompress_table (id int, name char(255));

appendix

SimpleExample.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.atomic.AtomicLong;
public class SimpleExample {
 public static void main(String[] args) throws Exception {
  Class.forName("com.mysql.jdbc.Driver");
  Properties props = new Properties();
  props.setProperty("user", "root");
  props.setProperty("password", "root");
  SimpleExample engine = new SimpleExample();
// engine.execute(props,"jdbc:mysql://10.186.24.31:3336/hucq?useSSL=false");
  engine.execute(props,"jdbc:mysql://10.186.24.31:3336/hucq?useSSL=false&useCursorFetch=true");
 }
 final AtomicLong tmAl = new AtomicLong();
 final String tableName="test";
 public void execute(Properties props,String url) {
  CountDownLatch cdl = new CountDownLatch(1);
  long start = System.currentTimeMillis();
  for (int i = 0; i < 1; i++) {
   TestThread insertThread = new TestThread(props,cdl, url);
   Thread t = new Thread(insertThread);
   t.start();
   System.out.println("Test start");
  }
  try {
   cdl.await();
   long end = System.currentTimeMillis();
   System.out.println("Test end,total cost:" + (end-start) + "ms");
  } catch (Exception e) {
  }
 }
 
 class TestThread implements Runnable {
  Properties props;
  private CountDownLatch countDownLatch;
  String url;
  public TestThread(Properties props, CountDownLatch cdl, String url) {
   this.props = props;
   this.countDownLatch = cdl;
   this.url = url;
  }
  public void run() {
   Connection connection = null;
   PreparedStatement ps = null;
   Statement st = null;
   long start = System.currentTimeMillis();
   try {
    connection = DriverManager.getConnection(url,props);
    connection.setAutoCommit(false);
    st = connection.createStatement();
     
    //st.setFetchSize(500);
    st.setFetchSize(Integer.MIN_VALUE); //Just modify here ResultSet rstmp;
     
    st.executeQuery("select sum(k) from sbtest1 group by k");
    rstmp = st.getResultSet();
    while(rstmp.next()){
      
    }
   } catch (Exception e) {
    System.out.println(System.currentTimeMillis() - start);
    System.out.println(new java.util.Date().toString());
    e.printStackTrace();
   finally
    if (ps != null)
     try {
      ps.close();
     } catch (SQLException e1) {
      e1.printStackTrace();
     }
    if (connection != null)
     try {
      connection.close();
     } catch (SQLException e1) {
      e1.printStackTrace();
     }
    this.countDownLatch.countDown();
   }
  }
 }
}

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.