In order to optimize the Oceanbase query timeout setting way, special research mysql about timeout processing, the record is as follows.
Copy Code code as follows:
Mysql> Show variables like '%time% ';
+----------------------------+-------------------+
| variable_name | Value |
+----------------------------+-------------------+
| Connect_timeout | 10 |
| Datetime_format | %y-%m-%d%h:%i:%s |
| Delayed_insert_timeout | 300 |
| Flush_time | 1800 |
| Innodb_lock_wait_timeout | 50 |
| Innodb_old_blocks_time | 0 |
| Innodb_rollback_on_timeout | Off |
| Interactive_timeout | 28800 |
| Lc_time_names | en_US |
| Lock_wait_timeout | 31536000 |
| Long_query_time | 10.000000 |
| Net_read_timeout | 30 |
| Net_write_timeout | 60 |
| Slave_net_timeout | 3600 |
| Slow_launch_time | 2 |
| System_time_zone | |
| Time_format | %h:%i:%s |
| Time_zone | SYSTEM |
| timed_mutexes | Off |
| Timestamp | 1366027807 |
| Wait_timeout | 28800 |
+----------------------------+-------------------+
Rows in Set, 1 Warning (0.00 sec)
Some of these parameters are explained in particular:
Connect_timeout:
The number of seconds that's mysqld server waits for a connect packet before bad Respondingwith. The default value is seconds as of a MySQL 5.1.23 and 5 seconds before that. Increasing the Connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MYSQ L server at ' XXX ', System Error:errno.
Explanation: When getting the link, wait for the handshake timeout, only valid at login, login success This parameter is no matter. The main purpose is to prevent network bad when the application of reconnection causes the number of connections to rise too fast, the general default can be.
Interactive_timeout:
The number of seconds the server waits for activity on a interactive connection before closing it. An interactive client are defined as a client that uses the Client_interactive option to Mysql_real_connect (). Alsowait_timeout.
Explanation: How long a thread in a continuous sleep state is closed. Each time the thread is used, it is awakened to the Acrivity state, the interactive state after the execution of query, and the timer starts again. Wait_timeout differs in that the threads that act only on TCP/IP and socket links are of the same meaning.
MySQL can configure the connection timeout, this time if done too long, even to 10min, so it is very likely that this situation, 3,000 links are occupied and sleep, the new link does not come in, resulting in the normal service. So this configuration as much as possible to configure a logical value, 60s or 120s and so on.
Words:
After a command is struck below the command line, the time interval before the next command arrives is Interactive_time, and if the interval exceeds interactive_timeout, the connection is automatically disconnected and the next command fails. However, the general MySQL client has an automatic connection mechanism, the next command will be implemented after the connection.
Copy Code code as follows:
mysql> set interactive_timeout = 1;
Query OK, 0 rows Affected (0.00 sec)
Mysql> Show sessions variables like '%timeout% ';
+----------------------------+----------+
| variable_name | Value |
+----------------------------+----------+
| Connect_timeout | 10 |
| Interactive_timeout | 1 |
| Wait_timeout | 28800 |
+----------------------------+----------+
Rows in Set (0.00 sec)
Copy Code code as follows:
mysql> set wait_timeout = 1;
Query OK, 0 rows Affected (0.00 sec)
"Get a cup of tea and wait."
Mysql> Show sessions variables like '%timeout% ';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect ...
Connection Id:7
Current database: * * * NONE * * *
+----------------------------+----------+
| variable_name | Value |
+----------------------------+----------+
| Connect_timeout | 10 |
| Interactive_timeout | 28800 |
| Wait_timeout | 28800 |
+----------------------------+----------+
Rows in Set (0.01 sec)
Wait_timeout:
The number of seconds the server waits for activities on a noninteractive connection (there is no active command on the connection, it may be the client drinking coffee.) ) before closing it. Before MySQL 5.1.41, this timeout applies a to TCP/IP connections, not to connections made through Unix socket files, n Amed pipes, or shared memory.
On thread startup, the session Wait_timeout value are initialized from the global wait_timeout value or from the global int Eractive_timeout value, depending on the type of client
Here, incidentally, explain what a non-interactive connection
> Non-interactive Commands
Just do a quick look up on a table without logging to the client, running the query then logging back out again.
Can instead just type one line using the '-e ' flag.
Copy Code code as follows:
C:\mysql\bin\mysql-u admin-p mydatabase-e ' SELECT * from employee '
Net_read_timeout/net_write_timeout
The number of seconds to wait for more data from a connection before aborting the read. Before MySQL 5.1.41, this timeout applies a to TCP/IP connections, not to connections made through Unix socket files, n Amed pipes, or shared memory. When the ' server is ' reading from the client, net_read_timeout are the timeout value controlling when to abort. When the ' server is ' writing to the client, Net_write_timeout is the timeout value controlling when to abort. Also slave_net_timeout.
On Linux, the NO_ALARM builds flag affects timeout behavior as indicated in the description of the Net_retry_count System V Ariable.
Explanation: This parameter is valid only for TCP/IP links, which is the timeout that the database waits for the receiving client to send the network packet and send the network packet to the client, which is a valid parameter of the line Cheng in the activity state.
JDBC setquerytimeout Function:
In order to avoid the phenomenon that the query appears dead loop, or time is too long, cause thread to block, after obtaining statement instance, Stmt.setquerytimeout (10); Avoid thread blocking because the query caused the program to appear.
But yesterday the Discovery program appeared, "ORA-01013: User requests to cancel the current operation" exception. Manually executing an Error SQL statement found that the statement took more than 20 seconds. Because of SetQueryTimeout (10), the query statement has not been executed before the exception is thrown. When using SetQueryTimeout (10), be sure to set the time longer, such as 60 seconds or more. As long as it does not cause the thread to block for a long time. Too short to throw, "ORA-01013: User requests to cancel current operation" exception
The principle of JDBC implementation setquerytimeout:
Copy Code code as follows:
Class Ifxcancelqueryimpl extends TimerTask
Implements Ifmxcancelquery
{
Ifxstatement stmt;
Timer t = null;
public void Startcancel (ifxstatement paramifxstatement, int paramint)
Throws Exception
{
this.stmt = paramifxstatement;
THIS.T = new Timer (true);
This.t.schedule (This, paramint * 1000);
}
public void Run ()
{
Try
{
This.stmt.cancel ();
This.t.cancel ();
}
catch (SQLException localsqlexception)
{
This.t.cancel ();
throw new Error (Localsqlexception.geterrorcode () + ":" + localsqlexception.getmessage ());
}
}
}
As you can see, query timeout is done through a client solution, and the server side doesn't need to know. Monitor the execution time through a timer thread and schedule the run () function if the execution time times out.
Reference:
http://wangwei.cao.blog.163.com/blog/static/10236252620111119115540534/
http://sls8204.blog.163.com/blog/static/62979632200741683453114/
Oceanbase can support query timeout through the server side, you can set query timeout and do not interrupt the current session. This is a bit more advanced than MySQL.
