How does JDBC read massive amounts of data from PostgreSQL? PostgreSQL Source Code Analysis record

Objective:

Recently do data synchronization, need to get data from PostgreSQL, found that once the data is more, then the speed of reading is very slow, and memory consumption is very much &GC not fall.

Code Sample:

In order to facilitate the explanation, the following writing the case code, from B2c_order to obtain data, this data table about 6G.

Package com.synchro;import java.sql.*,/** * Created by Qiu.li in 2015/10/16. */public class Test {    public static void main (string[] args) {        Connection conn = null;        Try {class.forname ("org.postgresql.Driver"); conn = Drivermanager.getconnection ("jdbc:postgresql://***. Qunar.com:5432/database "," username "," password "); String sql = "SELECT * from Mirror.b2c_order"; PreparedStatement PS = conn.preparestatement (sql); Ps.setmaxrows (+); ResultSet rs = ps.executequery (); int i = 0; while (Rs.next ()) {i++; if (i% = = 0) {System. Out.println (i); }}} catch (ClassNotFoundException e) {e.printstacktrace ();} catch (SQLException e) {e.printstacktrace ();} }}

Phenomenon:

Execute code in idea, find the card is dead and consume a lot of memory

Solution:

Then I decided to start debugging, tracking code:

The first step, I found it was stuck in the execution of the ExecuteQuery method.

The second step, which is stuck in the execution of the Abstractjdbc2statement.executewithflags method

The third step, continue tracking, and view on the network may cause is related to setting the fetchsize parameter, so I set the fetchsize, wonderful is not effective

The fourth step, Sendquery,sendonequery method, found the problem here, fortunately the code is not too much, I have all posted out:

        Boolean useportal = (Flags & 8)! = 0 &&!noresults &&!nometa && fetchsize > 0 &&! describeonly;        Boolean oneShot = (Flags & 1)! = 0 &&! useportal;        int rows;        If(noresults) {            rows = 1;        } else if (!  Useportal) {            rows = maxRows;} else if (maxRows! = 0 && fetchsize > maxRows) {rows = MaxRow S } else {rows = fetchsize;}         

Visible is Useportal is true, then fetchsize will take effect.

Boolean useportal = (Flags & 8)! = 0 &&!noresults &&!nometa && fetchsize > 0 &&! Describeonly;

So let's look at these conditions individually:

• !noresults indicates that this SQL does not need to return any results, which is definitely equal to true, because all select will require a return result
• !nometa indicates that this SQL does not need to return metadata, which is definitely equal to true, because select requires the return of metadata for subsequent resultset.get use
• !fetchsize is more than 0, this does not say, nature is true
• !describeonly, this will only be false when the statement such as DESC table, is true for select.

Well, the only thing that could possibly cause Useportal to be true is that the flags & 8 value is true: (I want to say that this is very chic, TMD, set the flags when it must be flags=flag|8, and later found that the new driver modified this type of writing)

Keep going up and see when it's going to execute. Flags = Flags | 8 This code, because only this code has been executed, will cause the above condition is true

        if (this.fetchsize > 0 &&!this.wantsscrollableresultset () &&!this.connection.getautocommit () &&!this. Wantsholdableresultset ()) {            flags |= 8;        } 

Where: the Wantsholdableresultset () code returns the false directly, so, do not consider this.

Then, Wantsscrollableresultset () returns false, and Connection.getautocommit () returns false to cause Fetchsize to take effect. Wantsscrollableresultset () The code for this method is:

Protected Boolean Wantsscrollableresultset () {        return ResultsetType! = 1003;//old code, see here I really want to die, 1003 is what? Fortunately, the chance to see the new PostgreSQL driver, using resultset.type_forward_only to represent 1003
}

At this point, the problem is finally fixed:

1. If connection does not commit the transaction automatically, then Fetchsize will take effect (non-default)

2, if the statement is type_forward_only, then, Fetchsize will also take effect (default)

Conclusion

If you want fetchsize to take effect, you must ensure that connection is Autocommit = False and that statement is 1003 (forward_only):

Conn.setautocommit (false); final Statement Statement = conn.createstatement (resultset.type_forward_only, Resultset.fetch_forward);

In addition, Conn.createstatement () with no parameters, the default is Type_forward_only. Therefore, in general, if you want fetchsize to take effect, only need to set autocommit to Flase, that is, to manually manage the transaction. The default source code is as follows:

     Public throws SQLException {        returnthis// Interested students can continue to follow-up to see, 1003 is ResultsetType    }

Code:

Then modify the code as follows:

Package com.synchro;import java.sql.* ;/** * Created by Qiu.li in 2015/10/16. */public class  Test {public St        atic void  Main (string[] args) {Connection conn = null ; Try  {class.forname ("Org.postgresql.Driver" ), conn = Drivermanager.getconnection ("jdbc:postgresql://*** . Qunar.com:5432/datasource "," username "," password "); Conn.setautocommit (FALSE); Not all databases are applicable, such as hive is not supported, Orcle does not require String sql = "SELECT * from Mirror.b2c_order" ; PreparedStatement PS =  conn.preparestatement (sql), ps.setfetchsize (1000);//10,000 records per acquisition//ps.setmaxrows (1000); ResultSet rs =  ps.executequery (); int i = 0 ; while  (Rs.next ()) {i++ ; if (i% = = 0 ) {Sy Stem.out.println (i); }}} catch  (ClassNotFoundException e) {e.printstacktrace ();} catch  (SQLException e) {e.printstacktrace (); } }} 

This time again, we found that there was no card at all.

Sentiment: Similar to the problem of slowly tracking code, more important is to have colleagues around the need to discuss each other, forming an atmosphere, because the process is very boring, it is difficult to adhere to it.

Reference documents

Https://jdbc.postgresql.org/documentation/head/query.html

http://m.blog.csdn.net/blog/itjin45/42004447#

How does JDBC read massive amounts of data from PostgreSQL? PostgreSQL Source Code Analysis record