Shulou(Shulou.com)06/01 Report--

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 you will often encounter related requirements in development. The following article will give you the details of JDBC and MySQL temporary tablespaces and share them for your reference and study. Let's take a look at the detailed introduction.

The application JDBC connection parameter uses useCursorFetch=true, and the query result set is stored in the mysqld temporary table space, which causes the ibtmp1 file size to soar to more than 90 G and depletes 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 the temporary tablespace is restricted, the program waits until the timeout disconnects when the ibtmp1 file reaches 2G while the application is still accessed the way it used to. SHOW PROCESSLIST shows that the connection thread of the program is in sleep state, and the state and info information is empty. This is not very friendly for application development, and there is also a lack of hints to analyze the reason after waiting for a timeout.

Problem analysis process

In order 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 a scenario where the temporary table exceeds the maximum limit

Simulate the following environment:

Ibtmp1:12M:autoextend:max:30M

Delete the k-field index of a 5 million-row sbtest table

When you run a query of group by, when the temporary table size exceeds the limit, you will directly report an error:

Select sum (k) from sbtest1 group by k

ERROR 1114 (HY000): The table'/ tmp/#sql_60f1_0' is full

two。 Check the driver's settings for mysql

As we saw in the previous step, manual execution of sql will return an error, but jdbc will not return an error, causing the connection to remain sleep. It is suspected that the mysql driver has made special settings, and the driver is connected to mysql. Check what settings have been made through general_log. No special settings were found.

3. Test JDBC connection

In the background of the question, there is a special configuration for JDBC: useCursorFetch=true. I don't know if it has anything to do with hidden error, so let's test it:

The following phenomena were found:

When adding the parameter useCursorFetch=true, it is true that the same query will not report an error.

This parameter is read in segments to prevent the return result set from being too large. That is, after the program sends a sql to mysql, it will wait for the feedback that mysql can read the result. Because when mysql executes sql, it reports an error when the return result reaches the ibtmp limit, but does not close the thread. The thread processes the sleep status, and the program does not get feedback, so it waits all the time and does not report an error. If you kill this thread, the program will report an error.

If you do not add the parameter useCursorFetch=true, doing the same query will result in an error.

Conclusion

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

two。 When JDBC sets the temporary table size to reach the ibtmp limit during the execution of useCursorFetch=true,sql, it will not report an error.

Solution

It is further learned that the use of useCursorFetch=true is to prevent the query result set from being too large to burst jvm.

However, the use of useCursorFetch=true will lead to the generation of temporary tables in ordinary queries, resulting in the problem of excessive temporary table space.

The solution to excessive temporary tablespaces is to limit the size of the ibtmp1, while useCursorFetch=true causes the JDBC to return no errors.

Therefore, other methods need to be used to achieve the same effect, and the program should also report the corresponding error after the sql error report. In addition to segment reading such as useCursorFetch=true, stream reading can also be used. For details of the stream reader program, see the attachment section.

Error report comparison

Segment reading mode. After sql reports an error, the program does not report an error.

Stream reading mode. After sql reports an error, the program will report an error.

Memory footprint comparison

This paper compares the three methods of normal read, segment read and stream read, and the initial memory takes up about 28m:

After ordinary reading, it takes up more than 100m of memory.

After the segment is read, the memory occupies about 60m.

After the stream is read, the memory takes up about 60m.

Supplementary knowledge points

MySQL shares temporary tablespace knowledge points

MySQL 5.7has been improved on temporary tablespace to separate temporary tablespace from ibdata (shared tablespace files). And you can restart the reset size to avoid problems like the previous ibdata that is too large to release.

Its parameter is: innodb_temp_data_file_path

1. Performance

When MySQL starts, an ibtmp1 file will be created under datadir with an initial size of 12m and will expand indefinitely by default:

Generally speaking, if the temporary tables caused by queries (such as group by) exceed the tmp_table_size and max_heap_table_size size limits, then create innodb disk temporary tables (innodb is the default temporary table engine for MySQL5.7) and store them in the shared temporary table space.

If an operation creates a temporary table with a size of 100m, the temporary tablespace data file is expanded to 100m to meet the needs of the temporary table. When you delete a temporary table, the free space can be reused for the new temporary table, but the ibtmp1 file remains the extended size.

two。 Query view

You can query the usage of shared temporary table spaces:

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'\ gateway * 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. Recovery mode

Restart MySQL to recycle

4. Limit size

To prevent the temporary data file from becoming too large, you can configure the innodb_temp_data_file_path (restart takes effect) option to specify the maximum file size. When the data file reaches its maximum size, the query will return an error:

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

5. Comparison of temporary tablespaces and tmpdir

Shared temporary tablespaces are used to store data such as uncompressed InnoDB temporary tables (non-compressed InnoDB temporary tables), relational objects (related objects), rollback segments (rollback segment), etc.

Tmpdir is used to hold specified temporary files (temporary files) and temporary tables (temporary tables). Unlike sharing temporary tablespaces, 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))

Attachment

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; iTunes +) {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 to 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 ();}

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.