The Ultimate Guide to High Availability with MariaDB

Aborting Statements that Exceed a Certain Time to Execute

You are viewing an old version of this article. View the current version here.
MariaDB starting with 10.1.1

max_statement_time and the associated functionality was introduced in MariaDB 10.1.1

Contents

  1. Overview
  2. User max_statement_time
  3. Per-query max_statement_time
  4. Limitation
  5. Differences Between the MariaDB and MySQL Implementations
  6. See Also

Overview

MariaDB 10.1.1 introduced the max_statement_time system variable. When set to a non-zero value, any queries taking longer than this time in seconds will be aborted. The default is zero, and no limits are then applied. The aborted query has no effect on any larger transaction or connection contexts.

The value can be set globally or per session, as well as per user or per query (see below). Slave's are not affected by this variable.

An associated status variable, max_statement_time_exceeded, stores the number of queries that have exceeded the execution time specified by max_statement_time, and a MAX_STATEMENT_TIME_EXCEEDED column was added to the CLIENT_STATISTICS and USER STATISTICS Information Schema tables.

The feature was based upon a patch by Davi Arnaut.

User max_statement_time

max_statement_time can be stored per user with the GRANT ... MAX_STATEMENT_TIME syntax.

MariaDB starting with 10.1.2

Per-query max_statement_time

By using max_statement_time in conjunction with SET STATEMENT, it is possible to limit the execution time of individual queries. For example:

SET STATEMENT max_statement_time=100 FOR 
  SELECT field1 FROM table_name ORDER BY field1;

Limitation

Differences Between the MariaDB and MySQL Implementations

MySQL 5.7.4 introduced similar functionality, but the MariaDB implementation differs in a number of ways.

  • The MySQL version of max_statement_time is defined in millseconds, not seconds
  • MySQL's implementation can only kill SELECTs, while MariaDB's can kill any queries (excluding stored procedures).
  • MariaDB only introduced the max_statement_time_exceeded status variable, while MySQL also introduced a number of other variables which were not seen as necessary in MariaDB.
  • The SELECT MAX_STATEMENT_TIME = N ... syntax is not valid in MariaDB.

See Also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.