MySQL jdbc QueryTimeout a pit

Encounter a MySQL jdbc QueryTimeout pit, compare nausea, it is a bug, also can not count, ^_^, why say so? Take a look at the following explanations:

Phenomenon:

Using the same connection to execute large batches of SQL results in an oom phenomenon.

Description of the detail phenomenon:

1, SQL is taken from a storage device, does not directly occupy a large amount of memory, each time will only fetch up to 1000 data in the past, will also determine the capacity of not more than how many m.

2, each batch of SQL execution will create a separate statement object, after executing a batch of SQL, the statement will be closed off.

3. There are only inserts in the SQL statement, there are no other statements.

Questions:

What's the problem with the egg? All the Code also review and debug, the parameters are their own ideal state, see the next mysqljdbc in the Statementimpl.close () code will clean up the corresponding result set and data, will not leave what garbage.

Dump Memory:

Dump memory discovers hundreds of thousands of Canceltask objects, which are the inner classes of the Statementimpl, and will eventually be placed in a static timer-type object in Connectionimpl.

Here's a few questions to analyze: What does this object do? When did you create it? When will it be destroyed? Where's The pit?

What is this object for? when did you create it?

This object is used to cancel the execution of the SQL Task object, when the SQL execution, through statement.setquerytimeout (int) (parameter units in seconds), the value of this parameter as long as not 0, It will create a task before JDBC communicates with MySQL, and this task will be put into a timer's task queue (see the blog for a special introduction to the timer and TimerTask article).

When is it going to be destroyed?

1. If the SQL statement responds before the timer is dispatched by the Canceltask, the call to the Canceltask.cancel () method is performed in the JDBC code.

2, if the SQL statement has not been responding, Canceltask will normally be dispatched by the timer when the timeout value of the set setting is reached, if the canceloperation is canceled, it is removed directly from the queue. If the canceltask has not been canceled, the appropriate cancellation command will be sent to MySQL to reclaim the resources. When the timer dispatches this task, the Canceltask internally creates a new thread to process, so the timer will soon assume that the task is done, regardless of the time it takes to cancel the SQL itself, and the timer will remove the task object from the queue. Because this task is not loop-executed.

It seems that the destruction is perfect, so where is the pit?

1, according to business needs, this statement.setquerytimeout (int) This value is set very large.

2. When a large amount of SQL executes simultaneously, each SQL creates a Canceltask object, although it is executed quickly and the Canceltask.cancel () method is called, but The source code of the Canceltask method simply modifies its state to: CANCELLED, and does not remove the object directly from the queue, but is not removed from the queue until it is dispatched by the timer when the value exceeds QueryTimeout.

3, so a large number of SQL running simultaneously, and soon end, the JDBC store a lot of canceltask life cycle If you do not end, this object is related to the timer, then the timer is what level?

4, after the source tracking, although the timer is defined in the connection, but static modified, that is, the global level, in other words: even this connection.close (), will not release these The space occupied by the Canceltask object.

5, through the above dump memory map to see, each Canceltask object will occupy about 7K space, 29W objects will occupy nearly 2G space.

Conclusion: As long as the timeout value is not reached, more than a certain number of SQL is executed (single-threaded or multi-threaded), the memory must jump.

A temporary solution:

Do not set timeout for some large-volume SQL execution portals, or set a very short timeout, depending on the actual scenario.

But this can lead to more problems, so it's going to get stuck in a circle. The ultimate plan a bit of egg pain, because this choice problem a bit of trouble, brother a bit want to change the source code this piece, to the official website submitted a lot of bugs, approved, but did not see them changed. This article just let everybody know that there is such a hole in existence.

The following simple paste a few small pieces of MySQL JDBC source code, interested can see:

Code Snippet 1: Setting Up QueryTimeout

public void setquerytimeout (int seconds) throws SQLException {if (seconds < 0) {throw sqlerror.createsqlexception (Mess Ages.getstring ("statement.21"),//$NON-nls-1$sqlerror.sql_state_illegal_argument); $NON-nls-1$}this.  = seconds * 1000;}

Code Snippet 2: If this timeout is not 0, a new task will be created

if (locallyscopedconn.getenablequerytimeouts () &&this.timeoutinmillis! = 0&& Locallyscopedconn.versionmeetsminimum (5, 0, 0)) {timeouttask = new Canceltask (this); Connectionimpl.getcanceltimer (). Schedule (Timeouttask,this.timeoutinmillis);}

The code snippet 3:sql calls the Cancel.cancel () method when it finishes executing

if (timeouttask! = null) {Timeouttask.cancel ();}

Code Snippet 4:java.util.timer Add task to the key part of the queue review

void Add (TimerTask Task) {        //Grow backing store if necessary        if (size + 1 = = queue.length)    queue = Arrays.cop yof (queue, 2*queue.length);        Queue[++size] = task;        Fixup (size);}

The code snippet 5:timertask is the parent of Canceltask, whose cancel method is primarily for setting the State

public Boolean cancel () {        synchronized (lock) {            Boolean result = (state = = scheduled);            state = CANCELLED;            return result;        }    }

About the timer scheduling part of the source code I did not post, previously described in other articles.

Now we are back to say why the bug is not counted? The explanation for not being a bug may be that the TimerTask cancel () method of Java itself is not removed from the queue, and MySQL JDBC is just a call away, and it might think Java should be able to handle resource problems.

Calculate a bug is the problem in turn, Java is the basic component, it is designed to have its original intention (to get from the queue is more troublesome, because it is done with an array, need to traverse, and is the global lock state, the transformation of the map is expensive, because it is a priority order of the queue scheduling mode, Using arrays is a relatively simple way to implement them. Since the Java design, the user should understand its details, at least cancel () need to purge, although the purge process will traverse all tasks, but as long as the number of tasks can be controlled, at any time in the cleanup, traversal is also possible. So also can be considered to use the problem, if MySQL jdbc feel bad, should write a scheduler, not trouble, if you do not want to write this part of the code, it should be able to be used as a dynamic entry parameters, we programmers can find such problems, we programmer to write a scheduler, MySQL JDBC loads our scheduler through reflection, as long as it complies with the TimerTask Schedule specification, but these paths are completely out of line and hopefully the MySQL JDBC will improve in this respect. Now only temporarily dumbfounded to bypass these problems, forcing the only change in the source code.

MySQL jdbc QueryTimeout a pit