There are many JDBC connection parameters, and some settings can assist in the application’s processing logic. This article from the technical community, “Original | Analysis of JDBC and MySQL Temporary Table Space“, explains the useCursorFetch parameter and is worth learning from.
Background
When using the JDBC connection parameter with useCursorFetch=true, the query result set is stored in the MySQL temporary table space, causing the ibtmp1 file size to surge to over 90G, exhausting the server’s disk space. To limit the size of the temporary table space, the following setting was made:
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2
Problem Description
After limiting the temporary table space, when the application continues to access it in the same way as before, the ibtmp1 file reaches 2G, and the program keeps waiting until it times out and disconnects. SHOW PROCESSLIST shows that the program’s connection thread is in a sleep state, with state and info information empty. This is not very friendly for application development, as after the program times out, there is a lack of prompt information to analyze the cause.
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 of Temporary Table Exceeding Maximum Limit
Simulate the following environment:
-
ibtmp1:12M:autoextend:max:30M
-
Delete the k field index from a sbtest table with 5 million rows
Running a group by query that exceeds the temporary table size limit will directly report an error:
select sum(k) from sbtest1 group by k; ERROR 1114 (HY000): The table ‘/tmp/#sql_60f1_0’ is full
2. Check Driver Settings for MySQL
In the previous step, we saw that executing SQL manually returns an error, but JDBC does not return an error, causing the connection to remain in a sleep state. We suspected that the MySQL driver had special settings, so we checked the general_log to see what settings were made. No special settings were found.
3. Test JDBC Connection
The background of the problem involves special configuration for JDBC: useCursorFetch=true, and we are unsure if this is related to the hidden error, so we will test it next.
We observed the following phenomena:
-
When the parameter useCursorFetch=true is added, the same query does not report an error
This parameter is designed to prevent the returned result set from being too large by using segmented reading. That is, after the program issues a SQL command to MySQL, it waits for feedback on the results. Since MySQL returns an error when the result reaches the ibtmp limit during SQL execution, but does not close that thread, it remains in a sleep state. The program does not receive feedback and will keep waiting without reporting an error. If this thread is killed, the program will then report an error.
-
When the parameter useCursorFetch=true is not added, the same query will report an error
Therefore,
1. Under normal circumstances, when the temporary table size reaches the ibtmp limit during SQL execution, an error will be reported;
It is further understood that using useCursorFetch=true is to prevent the query result set from overwhelming the JVM, but using useCursorFetch=true also causes ordinary queries to generate temporary tables, leading to the issue of excessive temporary table space. The solution to the excessive temporary table space is to limit the size of ibtmp1, but useCursorFetch=true causes JDBC not to return errors. Therefore, other methods need to be used to achieve the same effect, and the SQL error must also lead to a corresponding error in the program. In addition to the segmented reading method of useCursorFetch=true, a streaming read method can also be used. For details on streaming read, please refer to the attachment.
-
Error Comparison
-
In the segmented reading method, when SQL reports an error, the program does not report an error
-
In the streaming read method, when SQL reports an error, the program will report an error
-
Memory Usage Comparison
Here we compare three methods: normal reading, segmented reading, and streaming reading. The initial memory usage is around 28M:
-
After normal reading, memory usage exceeds 100M
-
After segmented reading, memory usage is around 60M
-
After streaming reading, memory usage is around 60M
Supplementary Knowledge Point – MySQL Shared Temporary Table Space Knowledge Point
MySQL 5.7 has made improvements to the temporary tablespace, separating it from ibdata (shared tablespace file). It can also be reset in size upon restart, avoiding the previous issue of ibdata being too large and difficult to release.
The parameter is:innodb_temp_data_file_path
1. Performance
When MySQL starts, an ibtmp1 file is created in the datadir with an initial size of 12M, and under default settings, it will expand indefinitely. Generally, if a query leads to a temporary table (such as group by) exceeding the limits of tmp_table_size and max_heap_table_size, an InnoDB disk temporary table will be created (the default temporary table engine for MySQL 5.7 is InnoDB), stored in the shared temporary table space;
If an operation creates a temporary table of size 100M, the temporary table space data file will expand to 100M to meet the needs of the temporary table. When the temporary table is deleted, the released space can be reused for new temporary tables, but the ibtmp1 file maintains its expanded size.
2. Query View
You can query the usage of the 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’
*************************** 1. row *************************** FILE_NAME: /data/mysql5722/data/ibtmp1TABLESPACE_NAME: innodb_temporary ENGINE: InnoDB INITIAL_SIZE: 12582912 TotalSizeBytes: 31457280 DATA_FREE: 27262976 MAXIMUM_SIZE: 314572801 row in set (0.00 sec)
3. Reclamation Method
MySQL can only be reclaimed by restarting.
4. Size Limitation
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 of Temporary Table Space and tmpdir
The shared temporary table space is used to store non-compressed InnoDB temporary tables, related objects, rollback segments, and other data. tmpdir is used to store specified temporary files and temporary tables, and unlike the shared temporary table space, tmpdir stores compressed InnoDB temporary tables.
You can test with the following statements:
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 for the above tests to deepen your understanding of the content.
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); //仅修改此处即可 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 find this article helpful, please do not hesitate to click the “like” and “view” buttons at the end of the article, or directly share it on your social media.
