Motivation

All of database vendors have some instruments for SQL management and monitoring.

User should have ability to understand what is going on with an executed queries. Which queries executed right now. Which of them slow/heavy. Have ability to cancel of execution of a query. Get a gathered IO query statistics and so on.

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 possible 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>
    
  Propose to use MySQL like syntax KILL QUERY <node_id> <query_id>
• Also need to support JDBC Statement.cancel() to cancel current query.

• For all executed queries should be gathering statistics. Statistics should be grouped by SQL query and keep at least  min, max, avg time of execution and min, max, avg, IO operations statistics (In case gathering IO statistics is ON), count of executions. Access to the statistics should be provided through system SQL view, then user can use power of SQL to select required information, for example get TOP 5 of IO heavy queries.
• Taking metrics for current session through thin client could be achieved by use additional think client syntax.  For JDBC client propose syntax is SET STATISTICS <ON|OFF>
• Security monitoring. Each of query should be identified for who started query: subject id, application name/ module/label.

Common approach: all statistics keep only locally and collecting from other nodes when it required.

Risks and Assumptions

Gathering any metrics take resource of server. Any metrics should be collecting for sliding window with predefined size.

Discussion Links

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

Reference Links

// Links to various reference documents, if applicable.

Tickets

// Links or report with relevant JIRA tickets.