Motivation

Many database vendors have some instruments for SQL management and monitoring.

User should be able to understand what is going on with executed queries:

• Queries being executed right now
• Identify slow queries
• Have ability to cancel of execution of a query from any node or client
• Get a gathered IO query statistics

Proposed Changes

• Each of execution query should have unique identifier for whole cluster. It could be an initial node UUID + sequentially growing number of an executing queries on the node. As example: b3c0624a-122c-46ea-9d65-67b56df00001 and 341. In such case management could be done on query level. This query id should be used for all of the query parts executed on other nodes.
• User should be able to cancel any query by query id. 

Industrial  RDBMS vendors (Oracle, Postgres, MySQL) have the following approaches to do it:

• • ORACLE: ALTER SYSTEM CANCEL SQL 'SID, SERIAL, SQL_ID'

• • Postgres: SELECT pg_cancel_backend(<pid of the process>) and SELECT pg_terminate_backend(<pid of the process>)
  • MySQL: KILL QUERY <qry_Id>
    
• We propose MySQL-like syntax: KILL QUERY <node_id> <query_id>
• Also we need to support query cancel for JDBC, ODBC and thin clients

• For all executed queries we should gather statistics. They should be grouped by SQL query and have at least min, max, avg time of execution; min, max, avg of IO operations (when IO stats are enabled); number of executions. 
• Access to the query statistics should be provided through both JMX and system SQL view. For SQL views it could be very convenient to get specific information due to power of SQL, e.g. number of  queries for last 2 hours.
• IO stats could be enabled/disabled for the given session through SQL commands, e.g. SET STATISTICS <ON|OFF>
• Statistics kept on node locally, collected from other nodes only when needed

Risks and Assumptions

Statistics gather could cause performance degradation and excessive memory consumption. We should limit depth of statistics history and allow to enable/disable statistics gathering in runtime.

Discussion Links

// Links to discussions on the devlist, if applicable.

Reference Links

// Links to various reference documents, if applicable.

Tickets