Usage scenarios of JDBC connection parameter useCursorFetch

Therefore,

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

2. When JDBC sets useCursorFetch=true, no error will be reported after 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 and overwhelming the jvm. However, using useCursorFetch=true will also cause ordinary queries to generate temporary tables, causing the problem of excessive temporary table space. The solution for the temporary table space that is too large 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 after the SQL error is reported, the program must also report an error accordingly. In addition to the segment reading method of useCursorFetch=true, you can also use the stream reading method. See the attachment section for details on the stream reading program.

• Error report comparison

  • Segment reading method, after sql reports an error, the program does not report an error

  • Stream reading method, after sql reports an error, the program will report an error

• Memory usage comparison

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

• After normal reading, the memory takes up more than 100M

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

• After stream reading, the memory occupied is about 60M

Supplementary knowledge points – MySQL shared temporary table space knowledge points

MySQL 5.7 has made improvements in temporary tablespace, and has implemented the separation of temporary tablespace from ibdata (shared tablespace file). And you can restart and reset the size to avoid the previous problem that ibdata was too large and difficult to release.

Its parameters are: innodb_temp_data_file_path

1. Performance

When MySQL starts, an ibtmp1 file will be created under the datadir. The initial size is 12M, and it will expand infinitely by default. Generally speaking, if the temporary table (such as group by) caused by the query exceeds the size limit of tmp_table_size and max_heap_table_size, an innodb disk temporary table will be created (the default temporary table engine of MySQL5.7 is innodb) and stored in the shared temporary table space;

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

2. Query view

You can query the usage of shared temporary table space.

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 becoming too large, you can configure the innodb_temp_data_file_path (requires restart to take effect) option to specify the maximum file size. When the data file reaches the maximum size, the query will return an error.

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

5. Comparison between temporary table space and tmpdir

Shared temporary table space is used to store data such as non-compressed InnoDB temporary tables, related objects, rollback segments, etc. tmpdir is used to store specified temporary files and temporary tables. Unlike shared temporary table spaces, tmpdir stores compressed InnoDB temporary tables.

It can be tested by 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));

You can use this java program to conduct the above test and deepen your understanding of the above content perceptually.

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 & amp;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();
            }
        }
    }
}

If you think this article is helpful, please feel free to click “Like” and “Reading” at the end of the article, or forward it directly to pyq,

Recently updated articles:

“Scenarios of Index Creation Error in MySQL”

“MySQL character set conversion operation scenario”

“Financial Knowledge – Secondary Market”

“Introduction to the poweroff command”

“Detailed Scenario Explanation of MySQL 8.0 New Password Policy”

Recent hot articles:

“Recommend a classic paper on Oracle RAC Cache Fusion”

“The shock that the open source code of the “Red Alert” game brings to us”

Article classification and indexing:

“Classification and Indexing of 1,300 Public Account Articles”

The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. Java skill treeUsing JDBC to operate databasesJDBC Overview 132229 people are learning the system