All Products
Search
Document Center

Specify a timeout period for an SQL statement

Last Updated: Aug 12, 2020

In DRDS, the SQL statements for DRDS instances and ApsaraDB RDS for MySQL instances are timed out after 900 seconds by default, which can be adjusted. However, for some slow SQL statements, the timeout period may exceed 900 seconds. DRDS provides a custom hint to adjust the timeout periods for these slow SQL statements. You can use this custom hint to adjust the timeout periods of SQL statements as needed.

This topic is applicable to Distributed Relational Database Service (DRDS) 5.3 and later versions.

Limits

  • DRDS hints can be in the formats of /*+TDDL:hint_command*/ and /!+TDDL:hint_command*/.

  • In the MySQL Command-Line Client, if you need to execute an SQL statement that contains a DRDS hint in the format of /*+TDDL:hint_command*/, add the -c parameter to the logon command. Otherwise, the client may delete the DRDS hint, which is used in the form of a MySQL comment, before sending the SQL statement to the server for execution. In this case, the DRDS hint is invalidated. For more information, see MySQL Client Options.

Syntax

The syntax of the DRDS hint for customizing a timeout period for an SQL statement is as follows:

 
  1. /*+TDDL:SOCKET_TIMEOUT(time)*/

The SOCKET_TIMEOUT parameter is measured in milliseconds. With this custom hint, you can adjust the timeout period for the SQL statement based on business requirements.

Examples

  • Set the timeout period of an SQL statement to 40 seconds:
 
  1. /*+TDDL:SOCKET_TIMEOUT(40000)*/SELECT * FROM t_item;

A longer timeout period causes database resources to be occupied for a longer period of time. If excessive SQL statements are executed over a long time within the same period, a large amount of database resources may be consumed. As a result, users cannot use database services properly. In this case, we recommend that you use this custom hint to optimize the SQL statements that take a long time to execute.