Apache Ignite Developers - Legacy Mail Archive

proposed design for thin client SQL management and monitoring (view running queries and kill it)

Classic

List

Threaded

20 messages Options

Юрий

proposed design for thin client SQL management and monitoring (view running queries and kill it)

Igniters,

Below is a proposed design for thin client SQL management and monitoring to
cancel a queries.

1) Ignite expose system SQL view with name *running_queries*
proposed columns: *node_id, query_id, sql, schema_name, connection_id,
duration*.

node_id - initial node of request
query_id - unique id of query on node
sql - text of query
schema name - name of sql schema
connection_id - id of client connection from ClientListenerConnectionContext
class
duration - duration in millisecond from start of query

Ignite will gather info about running queries from each of nodes and
collect it during user query. We already have most of the information
at GridRunningQueryInfo
on each of nodes.

Instead of duration we can use start_time, but I think duration will be
simple to use due to it not depend on a timezone.

2) Propose to use following syntax to kill a running query:

*KILL QUERY node_Id query_id*

Both parameters node_id and query_id can be get through running_queries
system view.

When a node receive such request it can be run locally in case node have
given node_id or send message to node with given id. Because node have
information about local running queries then can cancel it - it already
implemented in GridReduceQueryExecutor.cancelQueries(qryId) method.

Comments are welcome.
--
Живи с улыбкой! :D

Юрий

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

Igniters,

Some comments for my original email's.

The proposal related to part of IEP-29
<https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring>
.

What purpose are we pursuing of the proposal?
We want to be able check which queries running right now through thin
clients. Get some information related to the queries and be able to cancel
a query if it required for some reasons.
So, we need interface to get a running queries. For the goal we propose
running_queries system view. The view contains unique query identifier
which need to pass to kill query command to cancel the query.

What do you think about fields of the running queries view? May be some
useful fields we could easy add to the view.

Also let's discuss syntax of cancellation of query. I propose to use MySQL
like syntax as easy to understand and shorter then Oracle and Postgres
syntax ( detailed information in IEP-29
<https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring>
).

пн, 12 нояб. 2018 г. в 19:28, Юрий <[hidden email]>:

> Igniters,
>
> Below is a proposed design for thin client SQL management and monitoring
> to cancel a queries.
>
> 1) Ignite expose system SQL view with name *running_queries*
> proposed columns: *node_id, query_id, sql, schema_name, connection_id,
> duration*.
>
> node_id - initial node of request
> query_id - unique id of query on node
> sql - text of query
> schema name - name of sql schema
> connection_id - id of client connection from ClientListenerConnectionContext
> class
> duration - duration in millisecond from start of query
>
>
> Ignite will gather info about running queries from each of nodes and
> collect it during user query. We already have most of the information at GridRunningQueryInfo
> on each of nodes.
>
> Instead of duration we can use start_time, but I think duration will be
> simple to use due to it not depend on a timezone.
>
>
> 2) Propose to use following syntax to kill a running query:
>
> *KILL QUERY node_Id query_id*
>
>
> Both parameters node_id and query_id can be get through running_queries
> system view.
>
> When a node receive such request it can be run locally in case node have
> given node_id or send message to node with given id. Because node have
> information about local running queries then can cancel it - it already
> implemented in GridReduceQueryExecutor.cancelQueries(qryId) method.
>
> Comments are welcome.
> --
> Живи с улыбкой! :D
>

--
Живи с улыбкой! :D

Igor Sapego-2

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

Yuriy,

Would not it be more convenient fro user to write a request in a free
form, like
KILL QUERY WHERE ...

For example,
KILL QUERY WHERE duration > 15000

Or is it going to be too hard to implement?

Also, is this syntax going to work only from thin clients, or if it just
designed for them, but will also be usable from basic SQL API?

Best Regards,
Igor

On Tue, Nov 13, 2018 at 12:15 PM Юрий <[hidden email]> wrote:

> Igniters,
>
> Some comments for my original email's.
>
> The proposal related to part of IEP-29
> <
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> >
> .
>
> What purpose are we pursuing of the proposal?
> We want to be able check which queries running right now through thin
> clients. Get some information related to the queries and be able to cancel
> a query if it required for some reasons.
> So, we need interface to get a running queries. For the goal we propose
> running_queries system view. The view contains unique query identifier
> which need to pass to kill query command to cancel the query.
>
> What do you think about fields of the running queries view? May be some
> useful fields we could easy add to the view.
>
> Also let's discuss syntax of cancellation of query. I propose to use MySQL
> like syntax as easy to understand and shorter then Oracle and Postgres
> syntax ( detailed information in IEP-29
> <
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> >
> ).
>
>
>
> пн, 12 нояб. 2018 г. в 19:28, Юрий <[hidden email]>:
>
> > Igniters,
> >
> > Below is a proposed design for thin client SQL management and monitoring
> > to cancel a queries.
> >
> > 1) Ignite expose system SQL view with name *running_queries*
> > proposed columns: *node_id, query_id, sql, schema_name, connection_id,
> > duration*.
> >
> > node_id - initial node of request
> > query_id - unique id of query on node
> > sql - text of query
> > schema name - name of sql schema
> > connection_id - id of client connection from
> ClientListenerConnectionContext
> > class
> > duration - duration in millisecond from start of query
> >
> >
> > Ignite will gather info about running queries from each of nodes and
> > collect it during user query. We already have most of the information at
> GridRunningQueryInfo
> > on each of nodes.
> >
> > Instead of duration we can use start_time, but I think duration will be
> > simple to use due to it not depend on a timezone.
> >
> >
> > 2) Propose to use following syntax to kill a running query:
> >
> > *KILL QUERY node_Id query_id*
> >
> >
> > Both parameters node_id and query_id can be get through running_queries
> > system view.
> >
> > When a node receive such request it can be run locally in case node have
> > given node_id or send message to node with given id. Because node have
> > information about local running queries then can cancel it - it already
> > implemented in GridReduceQueryExecutor.cancelQueries(qryId) method.
> >
> > Comments are welcome.
> > --
> > Живи с улыбкой! :D
> >
>
>
> --
> Живи с улыбкой! :D
>

dmagda

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

In reply to this post by Юрий

Yury,

Please consider the following:

- If we record the duration instead of startTime, then the former has to
be updated frequently - sounds like a performance red flag. Should we store
startTime and endTime instead? This way a query record will be updated
twice - when the query is started and terminated.
- In the IEP you've mentioned I/O related fields that should help to
grasp why a query runs that slow. Should they be stored in this view?
- "KILL QUERY query_id" is more than enough. Let's not add "node_id"
unless it's absolutely required. Our queries are distributed and executed
across several nodes that's why the node_id parameter is redundant.
- This API needs to be supported across all our interfaces. We can start
with JDBC/ODBC and thin clients and then support for the native SQL APIs
(Java, Net, C++)
- Please share examples of SELECTs in the IEP that would show how to
find long running queries, queries that cause a lot of I/O troubles.

--
Denis

On Tue, Nov 13, 2018 at 1:15 AM Юрий <[hidden email]> wrote:

Igor Sapego-2

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

Denis,

If I understand correctly, this is going to be a view, not real storage, so
no
real duration is going to be stored anywhere - it is going to be calculated
dynamically during the SQL execution (please fix me if I wrong here).

Best Regards,
Igor

On Tue, Nov 13, 2018 at 6:52 PM Denis Magda <[hidden email]> wrote:

> Yury,
>
> Please consider the following:
>
> - If we record the duration instead of startTime, then the former has to
> be updated frequently - sounds like a performance red flag. Should we
> store
> startTime and endTime instead? This way a query record will be updated
> twice - when the query is started and terminated.
> - In the IEP you've mentioned I/O related fields that should help to
> grasp why a query runs that slow. Should they be stored in this view?
> - "KILL QUERY query_id" is more than enough. Let's not add "node_id"
> unless it's absolutely required. Our queries are distributed and
> executed
> across several nodes that's why the node_id parameter is redundant.
> - This API needs to be supported across all our interfaces. We can start
> with JDBC/ODBC and thin clients and then support for the native SQL APIs
> (Java, Net, C++)
> - Please share examples of SELECTs in the IEP that would show how to
> find long running queries, queries that cause a lot of I/O troubles.
>
> --
> Denis
>
> On Tue, Nov 13, 2018 at 1:15 AM Юрий <[hidden email]> wrote:
>
> > Igniters,
> >
> > Some comments for my original email's.
> >
> > The proposal related to part of IEP-29
> > <
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > >
> > .
> >
> > What purpose are we pursuing of the proposal?
> > We want to be able check which queries running right now through thin
> > clients. Get some information related to the queries and be able to
> cancel
> > a query if it required for some reasons.
> > So, we need interface to get a running queries. For the goal we propose
> > running_queries system view. The view contains unique query identifier
> > which need to pass to kill query command to cancel the query.
> >
> > What do you think about fields of the running queries view? May be some
> > useful fields we could easy add to the view.
> >
> > Also let's discuss syntax of cancellation of query. I propose to use
> MySQL
> > like syntax as easy to understand and shorter then Oracle and Postgres
> > syntax ( detailed information in IEP-29
> > <
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > >
> > ).
> >
> >
> >
> > пн, 12 нояб. 2018 г. в 19:28, Юрий <[hidden email]>:
> >
> > > Igniters,
> > >
> > > Below is a proposed design for thin client SQL management and
> monitoring
> > > to cancel a queries.
> > >
> > > 1) Ignite expose system SQL view with name *running_queries*
> > > proposed columns: *node_id, query_id, sql, schema_name, connection_id,
> > > duration*.
> > >
> > > node_id - initial node of request
> > > query_id - unique id of query on node
> > > sql - text of query
> > > schema name - name of sql schema
> > > connection_id - id of client connection from
> > ClientListenerConnectionContext
> > > class
> > > duration - duration in millisecond from start of query
> > >
> > >
> > > Ignite will gather info about running queries from each of nodes and
> > > collect it during user query. We already have most of the information
> at
> > GridRunningQueryInfo
> > > on each of nodes.
> > >
> > > Instead of duration we can use start_time, but I think duration will be
> > > simple to use due to it not depend on a timezone.
> > >
> > >
> > > 2) Propose to use following syntax to kill a running query:
> > >
> > > *KILL QUERY node_Id query_id*
> > >
> > >
> > > Both parameters node_id and query_id can be get through running_queries
> > > system view.
> > >
> > > When a node receive such request it can be run locally in case node
> have
> > > given node_id or send message to node with given id. Because node have
> > > information about local running queries then can cancel it - it already
> > > implemented in GridReduceQueryExecutor.cancelQueries(qryId) method.
> > >
> > > Comments are welcome.
> > > --
> > > Живи с улыбкой! :D
> > >
> >
> >
> > --
> > Живи с улыбкой! :D
> >
>

Юрий

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

In reply to this post by dmagda

Denis,

Under the hood 'time' will be as startTime, but for system view I planned
use duration which will be simple calculated as now - startTime. So, there
is't a performance issue.
As I understand you mean that the view should contains both running and
finished queries. If be honest for the view I was going to use just queries
running right now. For finished queries I thought about another view with
another set of fields which should include I/O related ones. Is it works?

For "KILL QUERY node_id query_id" node_id required as part of unique key
of query and help understand Ignite which node start the distributed query.
Use both parameters will allow cheap generate unique key across all nodes.
Node which started a query can cancel it on all nodes participate nodes.
So, to stop any queries initially we need just send the cancel request to
node who started the query. This mechanism is already in Ignite.

Native SQL APIs will automatically support the futures after implementing
for thin clients. So we are good here.

вт, 13 нояб. 2018 г. в 18:52, Denis Magda <[hidden email]>:

--
Живи с улыбкой! :D

Юрий

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

In reply to this post by Igor Sapego-2

Igor,

I think we shouldn't mix management and select syntax. Potentially it can
be dangerous. e.g. your example you don't know set of queries which will be
cancelled. Also I have not seen such approach in other databases.

Yes, the syntax should work from SQL API also.

вт, 13 нояб. 2018 г. в 14:20, Igor Sapego <[hidden email]>:

> Yuriy,
>
> Would not it be more convenient fro user to write a request in a free
> form, like
> KILL QUERY WHERE ...
>
> For example,
> KILL QUERY WHERE duration > 15000
>
> Or is it going to be too hard to implement?
>
> Also, is this syntax going to work only from thin clients, or if it just
> designed for them, but will also be usable from basic SQL API?
>
> Best Regards,
> Igor
>
>
> On Tue, Nov 13, 2018 at 12:15 PM Юрий <[hidden email]> wrote:
>
> > Igniters,
> >
> > Some comments for my original email's.
> >
> > The proposal related to part of IEP-29
> > <
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > >
> > .
> >
> > What purpose are we pursuing of the proposal?
> > We want to be able check which queries running right now through thin
> > clients. Get some information related to the queries and be able to
> cancel
> > a query if it required for some reasons.
> > So, we need interface to get a running queries. For the goal we propose
> > running_queries system view. The view contains unique query identifier
> > which need to pass to kill query command to cancel the query.
> >
> > What do you think about fields of the running queries view? May be some
> > useful fields we could easy add to the view.
> >
> > Also let's discuss syntax of cancellation of query. I propose to use
> MySQL
> > like syntax as easy to understand and shorter then Oracle and Postgres
> > syntax ( detailed information in IEP-29
> > <
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > >
> > ).
> >
> >
> >
> > пн, 12 нояб. 2018 г. в 19:28, Юрий <[hidden email]>:
> >
> > > Igniters,
> > >
> > > Below is a proposed design for thin client SQL management and
> monitoring
> > > to cancel a queries.
> > >
> > > 1) Ignite expose system SQL view with name *running_queries*
> > > proposed columns: *node_id, query_id, sql, schema_name, connection_id,
> > > duration*.
> > >
> > > node_id - initial node of request
> > > query_id - unique id of query on node
> > > sql - text of query
> > > schema name - name of sql schema
> > > connection_id - id of client connection from
> > ClientListenerConnectionContext
> > > class
> > > duration - duration in millisecond from start of query
> > >
> > >
> > > Ignite will gather info about running queries from each of nodes and
> > > collect it during user query. We already have most of the information
> at
> > GridRunningQueryInfo
> > > on each of nodes.
> > >
> > > Instead of duration we can use start_time, but I think duration will be
> > > simple to use due to it not depend on a timezone.
> > >
> > >
> > > 2) Propose to use following syntax to kill a running query:
> > >
> > > *KILL QUERY node_Id query_id*
> > >
> > >
> > > Both parameters node_id and query_id can be get through running_queries
> > > system view.
> > >
> > > When a node receive such request it can be run locally in case node
> have
> > > given node_id or send message to node with given id. Because node have
> > > information about local running queries then can cancel it - it already
> > > implemented in GridReduceQueryExecutor.cancelQueries(qryId) method.
> > >
> > > Comments are welcome.
> > > --
> > > Живи с улыбкой! :D
> > >
> >
> >
> > --
> > Живи с улыбкой! :D
> >
>

--
Живи с улыбкой! :D

dmagda

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

In reply to this post by Юрий

Yury,

As I understand you mean that the view should contains both running and
> finished queries. If be honest for the view I was going to use just queries
> running right now. For finished queries I thought about another view with
> another set of fields which should include I/O related ones. Is it works?

Got you, so if only running queries are there then your initial proposal
makes total sense. Not sure we need a view of the finished queries. It will
be possible to analyze them through the updated DetailedMetrics approach,
won't it?

For "KILL QUERY node_id query_id" node_id required as part of unique key
> of query and help understand Ignite which node start the distributed query.
> Use both parameters will allow cheap generate unique key across all nodes.
> Node which started a query can cancel it on all nodes participate nodes.
> So, to stop any queries initially we need just send the cancel request to
> node who started the query. This mechanism is already in Ignite.

Can we locate node_id behind the scenes if the user supplies query_id only?
A query record in the view already contains query_id and node_id and it
sounds like an extra work for the user to fill in all the details for us.
Embed node_id into query_id if you'd like to avoid extra network hops for
query_id to node_id mapping.

--
Denis

On Wed, Nov 14, 2018 at 1:04 AM Юрий <[hidden email]> wrote:

> Denis,
>
> Under the hood 'time' will be as startTime, but for system view I planned
> use duration which will be simple calculated as now - startTime. So, there
> is't a performance issue.
> As I understand you mean that the view should contains both running and
> finished queries. If be honest for the view I was going to use just queries
> running right now. For finished queries I thought about another view with
> another set of fields which should include I/O related ones. Is it works?
>
> For "KILL QUERY node_id query_id" node_id required as part of unique key
> of query and help understand Ignite which node start the distributed query.
> Use both parameters will allow cheap generate unique key across all nodes.
> Node which started a query can cancel it on all nodes participate nodes.
> So, to stop any queries initially we need just send the cancel request to
> node who started the query. This mechanism is already in Ignite.
>
> Native SQL APIs will automatically support the futures after implementing
> for thin clients. So we are good here.
>
>
>
> вт, 13 нояб. 2018 г. в 18:52, Denis Magda <[hidden email]>:
>
> > Yury,
> >
> > Please consider the following:
> >
> > - If we record the duration instead of startTime, then the former has
> to
> > be updated frequently - sounds like a performance red flag. Should we
> > store
> > startTime and endTime instead? This way a query record will be updated
> > twice - when the query is started and terminated.
> > - In the IEP you've mentioned I/O related fields that should help to
> > grasp why a query runs that slow. Should they be stored in this view?
> > - "KILL QUERY query_id" is more than enough. Let's not add "node_id"
> > unless it's absolutely required. Our queries are distributed and
> > executed
> > across several nodes that's why the node_id parameter is redundant.
> > - This API needs to be supported across all our interfaces. We can
> start
> > with JDBC/ODBC and thin clients and then support for the native SQL
> APIs
> > (Java, Net, C++)
> > - Please share examples of SELECTs in the IEP that would show how to
> > find long running queries, queries that cause a lot of I/O troubles.
> >
> > --
> > Denis
> >
> > On Tue, Nov 13, 2018 at 1:15 AM Юрий <[hidden email]>
> wrote:
> >
> > > Igniters,
> > >
> > > Some comments for my original email's.
> > >
> > > The proposal related to part of IEP-29
> > > <
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > >
> > > .
> > >
> > > What purpose are we pursuing of the proposal?
> > > We want to be able check which queries running right now through thin
> > > clients. Get some information related to the queries and be able to
> > cancel
> > > a query if it required for some reasons.
> > > So, we need interface to get a running queries. For the goal we propose
> > > running_queries system view. The view contains unique query identifier
> > > which need to pass to kill query command to cancel the query.
> > >
> > > What do you think about fields of the running queries view? May be some
> > > useful fields we could easy add to the view.
> > >
> > > Also let's discuss syntax of cancellation of query. I propose to use
> > MySQL
> > > like syntax as easy to understand and shorter then Oracle and Postgres
> > > syntax ( detailed information in IEP-29
> > > <
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > >
> > > ).
> > >
> > >
> > >
> > > пн, 12 нояб. 2018 г. в 19:28, Юрий <[hidden email]>:
> > >
> > > > Igniters,
> > > >
> > > > Below is a proposed design for thin client SQL management and
> > monitoring
> > > > to cancel a queries.
> > > >
> > > > 1) Ignite expose system SQL view with name *running_queries*
> > > > proposed columns: *node_id, query_id, sql, schema_name,
> connection_id,
> > > > duration*.
> > > >
> > > > node_id - initial node of request
> > > > query_id - unique id of query on node
> > > > sql - text of query
> > > > schema name - name of sql schema
> > > > connection_id - id of client connection from
> > > ClientListenerConnectionContext
> > > > class
> > > > duration - duration in millisecond from start of query
> > > >
> > > >
> > > > Ignite will gather info about running queries from each of nodes and
> > > > collect it during user query. We already have most of the information
> > at
> > > GridRunningQueryInfo
> > > > on each of nodes.
> > > >
> > > > Instead of duration we can use start_time, but I think duration will
> be
> > > > simple to use due to it not depend on a timezone.
> > > >
> > > >
> > > > 2) Propose to use following syntax to kill a running query:
> > > >
> > > > *KILL QUERY node_Id query_id*
> > > >
> > > >
> > > > Both parameters node_id and query_id can be get through
> running_queries
> > > > system view.
> > > >
> > > > When a node receive such request it can be run locally in case node
> > have
> > > > given node_id or send message to node with given id. Because node
> have
> > > > information about local running queries then can cancel it - it
> already
> > > > implemented in GridReduceQueryExecutor.cancelQueries(qryId) method.
> > > >
> > > > Comments are welcome.
> > > > --
> > > > Живи с улыбкой! :D
> > > >
> > >
> > >
> > > --
> > > Живи с улыбкой! :D
> > >
> >
>
>
> --
> Живи с улыбкой! :D
>

Vladimir Ozerov

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

Denis,

The idea is that QueryDetailMetrics will be exposed through separate
"historical" SQL view in addition to current API. So we are on the same
page here.

As far as query ID I do not see any easy way to operate on a single integer
value (even 64bit). This is distributed system - we do not want to have
coordination between nodes to get query ID. And coordination is the only
possible way to get sexy "long". Instead, I would propose to form ID from
node order and query counter within node. This will be (int, long) pair.
For use convenience we may convert it to a single string, e.g.
"[node_order].[query_counter]". Then the syntax would be:

KILL QUERY '25.1234'; // Kill query 1234 on node 25
KILL QUERY '25.*; // Kill all queries on the node 25

Makes sense?

Vladimir.

On Wed, Nov 14, 2018 at 1:25 PM Denis Magda <[hidden email]> wrote:

> Yury,
>
> As I understand you mean that the view should contains both running and
> > finished queries. If be honest for the view I was going to use just
> queries
> > running right now. For finished queries I thought about another view with
> > another set of fields which should include I/O related ones. Is it works?
>
>
> Got you, so if only running queries are there then your initial proposal
> makes total sense. Not sure we need a view of the finished queries. It will
> be possible to analyze them through the updated DetailedMetrics approach,
> won't it?
>
> For "KILL QUERY node_id query_id" node_id required as part of unique key
> > of query and help understand Ignite which node start the distributed
> query.
> > Use both parameters will allow cheap generate unique key across all
> nodes.
> > Node which started a query can cancel it on all nodes participate nodes.
> > So, to stop any queries initially we need just send the cancel request to
> > node who started the query. This mechanism is already in Ignite.
>
>
> Can we locate node_id behind the scenes if the user supplies query_id only?
> A query record in the view already contains query_id and node_id and it
> sounds like an extra work for the user to fill in all the details for us.
> Embed node_id into query_id if you'd like to avoid extra network hops for
> query_id to node_id mapping.
>
> --
> Denis
>
> On Wed, Nov 14, 2018 at 1:04 AM Юрий <[hidden email]> wrote:
>
> > Denis,
> >
> > Under the hood 'time' will be as startTime, but for system view I planned
> > use duration which will be simple calculated as now - startTime. So,
> there
> > is't a performance issue.
> > As I understand you mean that the view should contains both running and
> > finished queries. If be honest for the view I was going to use just
> queries
> > running right now. For finished queries I thought about another view with
> > another set of fields which should include I/O related ones. Is it works?
> >
> > For "KILL QUERY node_id query_id" node_id required as part of unique key
> > of query and help understand Ignite which node start the distributed
> query.
> > Use both parameters will allow cheap generate unique key across all
> nodes.
> > Node which started a query can cancel it on all nodes participate nodes.
> > So, to stop any queries initially we need just send the cancel request to
> > node who started the query. This mechanism is already in Ignite.
> >
> > Native SQL APIs will automatically support the futures after implementing
> > for thin clients. So we are good here.
> >
> >
> >
> > вт, 13 нояб. 2018 г. в 18:52, Denis Magda <[hidden email]>:
> >
> > > Yury,
> > >
> > > Please consider the following:
> > >
> > > - If we record the duration instead of startTime, then the former
> has
> > to
> > > be updated frequently - sounds like a performance red flag. Should
> we
> > > store
> > > startTime and endTime instead? This way a query record will be
> updated
> > > twice - when the query is started and terminated.
> > > - In the IEP you've mentioned I/O related fields that should help to
> > > grasp why a query runs that slow. Should they be stored in this
> view?
> > > - "KILL QUERY query_id" is more than enough. Let's not add "node_id"
> > > unless it's absolutely required. Our queries are distributed and
> > > executed
> > > across several nodes that's why the node_id parameter is redundant.
> > > - This API needs to be supported across all our interfaces. We can
> > start
> > > with JDBC/ODBC and thin clients and then support for the native SQL
> > APIs
> > > (Java, Net, C++)
> > > - Please share examples of SELECTs in the IEP that would show how to
> > > find long running queries, queries that cause a lot of I/O troubles.
> > >
> > > --
> > > Denis
> > >
> > > On Tue, Nov 13, 2018 at 1:15 AM Юрий <[hidden email]>
> > wrote:
> > >
> > > > Igniters,
> > > >
> > > > Some comments for my original email's.
> > > >
> > > > The proposal related to part of IEP-29
> > > > <
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > >
> > > > .
> > > >
> > > > What purpose are we pursuing of the proposal?
> > > > We want to be able check which queries running right now through thin
> > > > clients. Get some information related to the queries and be able to
> > > cancel
> > > > a query if it required for some reasons.
> > > > So, we need interface to get a running queries. For the goal we
> propose
> > > > running_queries system view. The view contains unique query
> identifier
> > > > which need to pass to kill query command to cancel the query.
> > > >
> > > > What do you think about fields of the running queries view? May be
> some
> > > > useful fields we could easy add to the view.
> > > >
> > > > Also let's discuss syntax of cancellation of query. I propose to use
> > > MySQL
> > > > like syntax as easy to understand and shorter then Oracle and
> Postgres
> > > > syntax ( detailed information in IEP-29
> > > > <
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > >
> > > > ).
> > > >
> > > >
> > > >
> > > > пн, 12 нояб. 2018 г. в 19:28, Юрий <[hidden email]>:
> > > >
> > > > > Igniters,
> > > > >
> > > > > Below is a proposed design for thin client SQL management and
> > > monitoring
> > > > > to cancel a queries.
> > > > >
> > > > > 1) Ignite expose system SQL view with name *running_queries*
> > > > > proposed columns: *node_id, query_id, sql, schema_name,
> > connection_id,
> > > > > duration*.
> > > > >
> > > > > node_id - initial node of request
> > > > > query_id - unique id of query on node
> > > > > sql - text of query
> > > > > schema name - name of sql schema
> > > > > connection_id - id of client connection from
> > > > ClientListenerConnectionContext
> > > > > class
> > > > > duration - duration in millisecond from start of query
> > > > >
> > > > >
> > > > > Ignite will gather info about running queries from each of nodes
> and
> > > > > collect it during user query. We already have most of the
> information
> > > at
> > > > GridRunningQueryInfo
> > > > > on each of nodes.
> > > > >
> > > > > Instead of duration we can use start_time, but I think duration
> will
> > be
> > > > > simple to use due to it not depend on a timezone.
> > > > >
> > > > >
> > > > > 2) Propose to use following syntax to kill a running query:
> > > > >
> > > > > *KILL QUERY node_Id query_id*
> > > > >
> > > > >
> > > > > Both parameters node_id and query_id can be get through
> > running_queries
> > > > > system view.
> > > > >
> > > > > When a node receive such request it can be run locally in case node
> > > have
> > > > > given node_id or send message to node with given id. Because node
> > have
> > > > > information about local running queries then can cancel it - it
> > already
> > > > > implemented in GridReduceQueryExecutor.cancelQueries(qryId) method.
> > > > >
> > > > > Comments are welcome.
> > > > > --
> > > > > Живи с улыбкой! :D
> > > > >
> > > >
> > > >
> > > > --
> > > > Живи с улыбкой! :D
> > > >
> > >
> >
> >
> > --
> > Живи с улыбкой! :D
> >
>

dmagda

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

Vladimir,

Thanks, make perfect sense to me.

On Thu, Nov 15, 2018 at 12:18 AM Vladimir Ozerov <[hidden email]>
wrote:

> Denis,
>
> The idea is that QueryDetailMetrics will be exposed through separate
> "historical" SQL view in addition to current API. So we are on the same
> page here.
>
> As far as query ID I do not see any easy way to operate on a single integer
> value (even 64bit). This is distributed system - we do not want to have
> coordination between nodes to get query ID. And coordination is the only
> possible way to get sexy "long". Instead, I would propose to form ID from
> node order and query counter within node. This will be (int, long) pair.
> For use convenience we may convert it to a single string, e.g.
> "[node_order].[query_counter]". Then the syntax would be:
>
> KILL QUERY '25.1234'; // Kill query 1234 on node 25
> KILL QUERY '25.*; // Kill all queries on the node 25
>
> Makes sense?
>
> Vladimir.
>
> On Wed, Nov 14, 2018 at 1:25 PM Denis Magda <[hidden email]> wrote:
>
> > Yury,
> >
> > As I understand you mean that the view should contains both running and
> > > finished queries. If be honest for the view I was going to use just
> > queries
> > > running right now. For finished queries I thought about another view
> with
> > > another set of fields which should include I/O related ones. Is it
> works?
> >
> >
> > Got you, so if only running queries are there then your initial proposal
> > makes total sense. Not sure we need a view of the finished queries. It
> will
> > be possible to analyze them through the updated DetailedMetrics approach,
> > won't it?
> >
> > For "KILL QUERY node_id query_id" node_id required as part of unique key
> > > of query and help understand Ignite which node start the distributed
> > query.
> > > Use both parameters will allow cheap generate unique key across all
> > nodes.
> > > Node which started a query can cancel it on all nodes participate
> nodes.
> > > So, to stop any queries initially we need just send the cancel request
> to
> > > node who started the query. This mechanism is already in Ignite.
> >
> >
> > Can we locate node_id behind the scenes if the user supplies query_id
> only?
> > A query record in the view already contains query_id and node_id and it
> > sounds like an extra work for the user to fill in all the details for us.
> > Embed node_id into query_id if you'd like to avoid extra network hops for
> > query_id to node_id mapping.
> >
> > --
> > Denis
> >
> > On Wed, Nov 14, 2018 at 1:04 AM Юрий <[hidden email]>
> wrote:
> >
> > > Denis,
> > >
> > > Under the hood 'time' will be as startTime, but for system view I
> planned
> > > use duration which will be simple calculated as now - startTime. So,
> > there
> > > is't a performance issue.
> > > As I understand you mean that the view should contains both running and
> > > finished queries. If be honest for the view I was going to use just
> > queries
> > > running right now. For finished queries I thought about another view
> with
> > > another set of fields which should include I/O related ones. Is it
> works?
> > >
> > > For "KILL QUERY node_id query_id" node_id required as part of unique
> key
> > > of query and help understand Ignite which node start the distributed
> > query.
> > > Use both parameters will allow cheap generate unique key across all
> > nodes.
> > > Node which started a query can cancel it on all nodes participate
> nodes.
> > > So, to stop any queries initially we need just send the cancel request
> to
> > > node who started the query. This mechanism is already in Ignite.
> > >
> > > Native SQL APIs will automatically support the futures after
> implementing
> > > for thin clients. So we are good here.
> > >
> > >
> > >
> > > вт, 13 нояб. 2018 г. в 18:52, Denis Magda <[hidden email]>:
> > >
> > > > Yury,
> > > >
> > > > Please consider the following:
> > > >
> > > > - If we record the duration instead of startTime, then the former
> > has
> > > to
> > > > be updated frequently - sounds like a performance red flag. Should
> > we
> > > > store
> > > > startTime and endTime instead? This way a query record will be
> > updated
> > > > twice - when the query is started and terminated.
> > > > - In the IEP you've mentioned I/O related fields that should help
> to
> > > > grasp why a query runs that slow. Should they be stored in this
> > view?
> > > > - "KILL QUERY query_id" is more than enough. Let's not add
> "node_id"
> > > > unless it's absolutely required. Our queries are distributed and
> > > > executed
> > > > across several nodes that's why the node_id parameter is
> redundant.
> > > > - This API needs to be supported across all our interfaces. We can
> > > start
> > > > with JDBC/ODBC and thin clients and then support for the native
> SQL
> > > APIs
> > > > (Java, Net, C++)
> > > > - Please share examples of SELECTs in the IEP that would show how
> to
> > > > find long running queries, queries that cause a lot of I/O
> troubles.
> > > >
> > > > --
> > > > Denis
> > > >
> > > > On Tue, Nov 13, 2018 at 1:15 AM Юрий <[hidden email]>
> > > wrote:
> > > >
> > > > > Igniters,
> > > > >
> > > > > Some comments for my original email's.
> > > > >
> > > > > The proposal related to part of IEP-29
> > > > > <
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > >
> > > > > .
> > > > >
> > > > > What purpose are we pursuing of the proposal?
> > > > > We want to be able check which queries running right now through
> thin
> > > > > clients. Get some information related to the queries and be able to
> > > > cancel
> > > > > a query if it required for some reasons.
> > > > > So, we need interface to get a running queries. For the goal we
> > propose
> > > > > running_queries system view. The view contains unique query
> > identifier
> > > > > which need to pass to kill query command to cancel the query.
> > > > >
> > > > > What do you think about fields of the running queries view? May be
> > some
> > > > > useful fields we could easy add to the view.
> > > > >
> > > > > Also let's discuss syntax of cancellation of query. I propose to
> use
> > > > MySQL
> > > > > like syntax as easy to understand and shorter then Oracle and
> > Postgres
> > > > > syntax ( detailed information in IEP-29
> > > > > <
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > >
> > > > > ).
> > > > >
> > > > >
> > > > >
> > > > > пн, 12 нояб. 2018 г. в 19:28, Юрий <[hidden email]>:
> > > > >
> > > > > > Igniters,
> > > > > >
> > > > > > Below is a proposed design for thin client SQL management and
> > > > monitoring
> > > > > > to cancel a queries.
> > > > > >
> > > > > > 1) Ignite expose system SQL view with name *running_queries*
> > > > > > proposed columns: *node_id, query_id, sql, schema_name,
> > > connection_id,
> > > > > > duration*.
> > > > > >
> > > > > > node_id - initial node of request
> > > > > > query_id - unique id of query on node
> > > > > > sql - text of query
> > > > > > schema name - name of sql schema
> > > > > > connection_id - id of client connection from
> > > > > ClientListenerConnectionContext
> > > > > > class
> > > > > > duration - duration in millisecond from start of query
> > > > > >
> > > > > >
> > > > > > Ignite will gather info about running queries from each of nodes
> > and
> > > > > > collect it during user query. We already have most of the
> > information
> > > > at
> > > > > GridRunningQueryInfo
> > > > > > on each of nodes.
> > > > > >
> > > > > > Instead of duration we can use start_time, but I think duration
> > will
> > > be
> > > > > > simple to use due to it not depend on a timezone.
> > > > > >
> > > > > >
> > > > > > 2) Propose to use following syntax to kill a running query:
> > > > > >
> > > > > > *KILL QUERY node_Id query_id*
> > > > > >
> > > > > >
> > > > > > Both parameters node_id and query_id can be get through
> > > running_queries
> > > > > > system view.
> > > > > >
> > > > > > When a node receive such request it can be run locally in case
> node
> > > > have
> > > > > > given node_id or send message to node with given id. Because node
> > > have
> > > > > > information about local running queries then can cancel it - it
> > > already
> > > > > > implemented in GridReduceQueryExecutor.cancelQueries(qryId)
> method.
> > > > > >
> > > > > > Comments are welcome.
> > > > > > --
> > > > > > Живи с улыбкой! :D
> > > > > >
> > > > >
> > > > >
> > > > > --
> > > > > Живи с улыбкой! :D
> > > > >
> > > >
> > >
> > >
> > > --
> > > Живи с улыбкой! :D
> > >
> >
>

Юрий

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

I fully agree with last sentences and can start to implement this part.

Guys, thanks for your productive participate at discussion.

пт, 16 нояб. 2018 г. в 2:53, Denis Magda <[hidden email]>:

> Vladimir,
>
> Thanks, make perfect sense to me.
>
>
> On Thu, Nov 15, 2018 at 12:18 AM Vladimir Ozerov <[hidden email]>
> wrote:
>
> > Denis,
> >
> > The idea is that QueryDetailMetrics will be exposed through separate
> > "historical" SQL view in addition to current API. So we are on the same
> > page here.
> >
> > As far as query ID I do not see any easy way to operate on a single
> integer
> > value (even 64bit). This is distributed system - we do not want to have
> > coordination between nodes to get query ID. And coordination is the only
> > possible way to get sexy "long". Instead, I would propose to form ID from
> > node order and query counter within node. This will be (int, long) pair.
> > For use convenience we may convert it to a single string, e.g.
> > "[node_order].[query_counter]". Then the syntax would be:
> >
> > KILL QUERY '25.1234'; // Kill query 1234 on node 25
> > KILL QUERY '25.*; // Kill all queries on the node 25
> >
> > Makes sense?
> >
> > Vladimir.
> >
> > On Wed, Nov 14, 2018 at 1:25 PM Denis Magda <[hidden email]> wrote:
> >
> > > Yury,
> > >
> > > As I understand you mean that the view should contains both running and
> > > > finished queries. If be honest for the view I was going to use just
> > > queries
> > > > running right now. For finished queries I thought about another view
> > with
> > > > another set of fields which should include I/O related ones. Is it
> > works?
> > >
> > >
> > > Got you, so if only running queries are there then your initial
> proposal
> > > makes total sense. Not sure we need a view of the finished queries. It
> > will
> > > be possible to analyze them through the updated DetailedMetrics
> approach,
> > > won't it?
> > >
> > > For "KILL QUERY node_id query_id" node_id required as part of unique
> key
> > > > of query and help understand Ignite which node start the distributed
> > > query.
> > > > Use both parameters will allow cheap generate unique key across all
> > > nodes.
> > > > Node which started a query can cancel it on all nodes participate
> > nodes.
> > > > So, to stop any queries initially we need just send the cancel
> request
> > to
> > > > node who started the query. This mechanism is already in Ignite.
> > >
> > >
> > > Can we locate node_id behind the scenes if the user supplies query_id
> > only?
> > > A query record in the view already contains query_id and node_id and it
> > > sounds like an extra work for the user to fill in all the details for
> us.
> > > Embed node_id into query_id if you'd like to avoid extra network hops
> for
> > > query_id to node_id mapping.
> > >
> > > --
> > > Denis
> > >
> > > On Wed, Nov 14, 2018 at 1:04 AM Юрий <[hidden email]>
> > wrote:
> > >
> > > > Denis,
> > > >
> > > > Under the hood 'time' will be as startTime, but for system view I
> > planned
> > > > use duration which will be simple calculated as now - startTime. So,
> > > there
> > > > is't a performance issue.
> > > > As I understand you mean that the view should contains both running
> and
> > > > finished queries. If be honest for the view I was going to use just
> > > queries
> > > > running right now. For finished queries I thought about another view
> > with
> > > > another set of fields which should include I/O related ones. Is it
> > works?
> > > >
> > > > For "KILL QUERY node_id query_id" node_id required as part of unique
> > key
> > > > of query and help understand Ignite which node start the distributed
> > > query.
> > > > Use both parameters will allow cheap generate unique key across all
> > > nodes.
> > > > Node which started a query can cancel it on all nodes participate
> > nodes.
> > > > So, to stop any queries initially we need just send the cancel
> request
> > to
> > > > node who started the query. This mechanism is already in Ignite.
> > > >
> > > > Native SQL APIs will automatically support the futures after
> > implementing
> > > > for thin clients. So we are good here.
> > > >
> > > >
> > > >
> > > > вт, 13 нояб. 2018 г. в 18:52, Denis Magda <[hidden email]>:
> > > >
> > > > > Yury,
> > > > >
> > > > > Please consider the following:
> > > > >
> > > > > - If we record the duration instead of startTime, then the
> former
> > > has
> > > > to
> > > > > be updated frequently - sounds like a performance red flag.
> Should
> > > we
> > > > > store
> > > > > startTime and endTime instead? This way a query record will be
> > > updated
> > > > > twice - when the query is started and terminated.
> > > > > - In the IEP you've mentioned I/O related fields that should
> help
> > to
> > > > > grasp why a query runs that slow. Should they be stored in this
> > > view?
> > > > > - "KILL QUERY query_id" is more than enough. Let's not add
> > "node_id"
> > > > > unless it's absolutely required. Our queries are distributed and
> > > > > executed
> > > > > across several nodes that's why the node_id parameter is
> > redundant.
> > > > > - This API needs to be supported across all our interfaces. We
> can
> > > > start
> > > > > with JDBC/ODBC and thin clients and then support for the native
> > SQL
> > > > APIs
> > > > > (Java, Net, C++)
> > > > > - Please share examples of SELECTs in the IEP that would show
> how
> > to
> > > > > find long running queries, queries that cause a lot of I/O
> > troubles.
> > > > >
> > > > > --
> > > > > Denis
> > > > >
> > > > > On Tue, Nov 13, 2018 at 1:15 AM Юрий <[hidden email]>
> > > > wrote:
> > > > >
> > > > > > Igniters,
> > > > > >
> > > > > > Some comments for my original email's.
> > > > > >
> > > > > > The proposal related to part of IEP-29
> > > > > > <
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > >
> > > > > > .
> > > > > >
> > > > > > What purpose are we pursuing of the proposal?
> > > > > > We want to be able check which queries running right now through
> > thin
> > > > > > clients. Get some information related to the queries and be able
> to
> > > > > cancel
> > > > > > a query if it required for some reasons.
> > > > > > So, we need interface to get a running queries. For the goal we
> > > propose
> > > > > > running_queries system view. The view contains unique query
> > > identifier
> > > > > > which need to pass to kill query command to cancel the query.
> > > > > >
> > > > > > What do you think about fields of the running queries view? May
> be
> > > some
> > > > > > useful fields we could easy add to the view.
> > > > > >
> > > > > > Also let's discuss syntax of cancellation of query. I propose to
> > use
> > > > > MySQL
> > > > > > like syntax as easy to understand and shorter then Oracle and
> > > Postgres
> > > > > > syntax ( detailed information in IEP-29
> > > > > > <
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > >
> > > > > > ).
> > > > > >
> > > > > >
> > > > > >
> > > > > > пн, 12 нояб. 2018 г. в 19:28, Юрий <[hidden email]
> >:
> > > > > >
> > > > > > > Igniters,
> > > > > > >
> > > > > > > Below is a proposed design for thin client SQL management and
> > > > > monitoring
> > > > > > > to cancel a queries.
> > > > > > >
> > > > > > > 1) Ignite expose system SQL view with name *running_queries*
> > > > > > > proposed columns: *node_id, query_id, sql, schema_name,
> > > > connection_id,
> > > > > > > duration*.
> > > > > > >
> > > > > > > node_id - initial node of request
> > > > > > > query_id - unique id of query on node
> > > > > > > sql - text of query
> > > > > > > schema name - name of sql schema
> > > > > > > connection_id - id of client connection from
> > > > > > ClientListenerConnectionContext
> > > > > > > class
> > > > > > > duration - duration in millisecond from start of query
> > > > > > >
> > > > > > >
> > > > > > > Ignite will gather info about running queries from each of
> nodes
> > > and
> > > > > > > collect it during user query. We already have most of the
> > > information
> > > > > at
> > > > > > GridRunningQueryInfo
> > > > > > > on each of nodes.
> > > > > > >
> > > > > > > Instead of duration we can use start_time, but I think duration
> > > will
> > > > be
> > > > > > > simple to use due to it not depend on a timezone.
> > > > > > >
> > > > > > >
> > > > > > > 2) Propose to use following syntax to kill a running query:
> > > > > > >
> > > > > > > *KILL QUERY node_Id query_id*
> > > > > > >
> > > > > > >
> > > > > > > Both parameters node_id and query_id can be get through
> > > > running_queries
> > > > > > > system view.
> > > > > > >
> > > > > > > When a node receive such request it can be run locally in case
> > node
> > > > > have
> > > > > > > given node_id or send message to node with given id. Because
> node
> > > > have
> > > > > > > information about local running queries then can cancel it - it
> > > > already
> > > > > > > implemented in GridReduceQueryExecutor.cancelQueries(qryId)
> > method.
> > > > > > >
> > > > > > > Comments are welcome.
> > > > > > > --
> > > > > > > Живи с улыбкой! :D
> > > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Живи с улыбкой! :D
> > > > > >
> > > > >
> > > >
> > > >
> > > > --
> > > > Живи с улыбкой! :D
> > > >
> > >
> >
>

--
Живи с улыбкой! :D

Denis Mekhanikov

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

Guys,

Syntax like *KILL QUERY '25.1234'* look a bit cryptic to me.
I'm going to look up in documentation, which parameter goes first in this
query every time I use it.
I like the syntax, that Igor suggested more.
Will it be better if we make *nodeId* and *queryId *named properties?

Something like this:
KILL QUERY WHERE nodeId=25 and queryId=1234

Denis

пт, 16 нояб. 2018 г. в 14:12, Юрий <[hidden email]>:

> I fully agree with last sentences and can start to implement this part.
>
> Guys, thanks for your productive participate at discussion.
>
> пт, 16 нояб. 2018 г. в 2:53, Denis Magda <[hidden email]>:
>
> > Vladimir,
> >
> > Thanks, make perfect sense to me.
> >
> >
> > On Thu, Nov 15, 2018 at 12:18 AM Vladimir Ozerov <[hidden email]>
> > wrote:
> >
> > > Denis,
> > >
> > > The idea is that QueryDetailMetrics will be exposed through separate
> > > "historical" SQL view in addition to current API. So we are on the same
> > > page here.
> > >
> > > As far as query ID I do not see any easy way to operate on a single
> > integer
> > > value (even 64bit). This is distributed system - we do not want to have
> > > coordination between nodes to get query ID. And coordination is the
> only
> > > possible way to get sexy "long". Instead, I would propose to form ID
> from
> > > node order and query counter within node. This will be (int, long)
> pair.
> > > For use convenience we may convert it to a single string, e.g.
> > > "[node_order].[query_counter]". Then the syntax would be:
> > >
> > > KILL QUERY '25.1234'; // Kill query 1234 on node 25
> > > KILL QUERY '25.*; // Kill all queries on the node 25
> > >
> > > Makes sense?
> > >
> > > Vladimir.
> > >
> > > On Wed, Nov 14, 2018 at 1:25 PM Denis Magda <[hidden email]> wrote:
> > >
> > > > Yury,
> > > >
> > > > As I understand you mean that the view should contains both running
> and
> > > > > finished queries. If be honest for the view I was going to use just
> > > > queries
> > > > > running right now. For finished queries I thought about another
> view
> > > with
> > > > > another set of fields which should include I/O related ones. Is it
> > > works?
> > > >
> > > >
> > > > Got you, so if only running queries are there then your initial
> > proposal
> > > > makes total sense. Not sure we need a view of the finished queries.
> It
> > > will
> > > > be possible to analyze them through the updated DetailedMetrics
> > approach,
> > > > won't it?
> > > >
> > > > For "KILL QUERY node_id query_id" node_id required as part of unique
> > key
> > > > > of query and help understand Ignite which node start the
> distributed
> > > > query.
> > > > > Use both parameters will allow cheap generate unique key across all
> > > > nodes.
> > > > > Node which started a query can cancel it on all nodes participate
> > > nodes.
> > > > > So, to stop any queries initially we need just send the cancel
> > request
> > > to
> > > > > node who started the query. This mechanism is already in Ignite.
> > > >
> > > >
> > > > Can we locate node_id behind the scenes if the user supplies query_id
> > > only?
> > > > A query record in the view already contains query_id and node_id and
> it
> > > > sounds like an extra work for the user to fill in all the details for
> > us.
> > > > Embed node_id into query_id if you'd like to avoid extra network hops
> > for
> > > > query_id to node_id mapping.
> > > >
> > > > --
> > > > Denis
> > > >
> > > > On Wed, Nov 14, 2018 at 1:04 AM Юрий <[hidden email]>
> > > wrote:
> > > >
> > > > > Denis,
> > > > >
> > > > > Under the hood 'time' will be as startTime, but for system view I
> > > planned
> > > > > use duration which will be simple calculated as now - startTime.
> So,
> > > > there
> > > > > is't a performance issue.
> > > > > As I understand you mean that the view should contains both running
> > and
> > > > > finished queries. If be honest for the view I was going to use just
> > > > queries
> > > > > running right now. For finished queries I thought about another
> view
> > > with
> > > > > another set of fields which should include I/O related ones. Is it
> > > works?
> > > > >
> > > > > For "KILL QUERY node_id query_id" node_id required as part of
> unique
> > > key
> > > > > of query and help understand Ignite which node start the
> distributed
> > > > query.
> > > > > Use both parameters will allow cheap generate unique key across all
> > > > nodes.
> > > > > Node which started a query can cancel it on all nodes participate
> > > nodes.
> > > > > So, to stop any queries initially we need just send the cancel
> > request
> > > to
> > > > > node who started the query. This mechanism is already in Ignite.
> > > > >
> > > > > Native SQL APIs will automatically support the futures after
> > > implementing
> > > > > for thin clients. So we are good here.
> > > > >
> > > > >
> > > > >
> > > > > вт, 13 нояб. 2018 г. в 18:52, Denis Magda <[hidden email]>:
> > > > >
> > > > > > Yury,
> > > > > >
> > > > > > Please consider the following:
> > > > > >
> > > > > > - If we record the duration instead of startTime, then the
> > former
> > > > has
> > > > > to
> > > > > > be updated frequently - sounds like a performance red flag.
> > Should
> > > > we
> > > > > > store
> > > > > > startTime and endTime instead? This way a query record will be
> > > > updated
> > > > > > twice - when the query is started and terminated.
> > > > > > - In the IEP you've mentioned I/O related fields that should
> > help
> > > to
> > > > > > grasp why a query runs that slow. Should they be stored in
> this
> > > > view?
> > > > > > - "KILL QUERY query_id" is more than enough. Let's not add
> > > "node_id"
> > > > > > unless it's absolutely required. Our queries are distributed
> and
> > > > > > executed
> > > > > > across several nodes that's why the node_id parameter is
> > > redundant.
> > > > > > - This API needs to be supported across all our interfaces. We
> > can
> > > > > start
> > > > > > with JDBC/ODBC and thin clients and then support for the
> native
> > > SQL
> > > > > APIs
> > > > > > (Java, Net, C++)
> > > > > > - Please share examples of SELECTs in the IEP that would show
> > how
> > > to
> > > > > > find long running queries, queries that cause a lot of I/O
> > > troubles.
> > > > > >
> > > > > > --
> > > > > > Denis
> > > > > >
> > > > > > On Tue, Nov 13, 2018 at 1:15 AM Юрий <
> [hidden email]>
> > > > > wrote:
> > > > > >
> > > > > > > Igniters,
> > > > > > >
> > > > > > > Some comments for my original email's.
> > > > > > >
> > > > > > > The proposal related to part of IEP-29
> > > > > > > <
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > >
> > > > > > > .
> > > > > > >
> > > > > > > What purpose are we pursuing of the proposal?
> > > > > > > We want to be able check which queries running right now
> through
> > > thin
> > > > > > > clients. Get some information related to the queries and be
> able
> > to
> > > > > > cancel
> > > > > > > a query if it required for some reasons.
> > > > > > > So, we need interface to get a running queries. For the goal we
> > > > propose
> > > > > > > running_queries system view. The view contains unique query
> > > > identifier
> > > > > > > which need to pass to kill query command to cancel the query.
> > > > > > >
> > > > > > > What do you think about fields of the running queries view? May
> > be
> > > > some
> > > > > > > useful fields we could easy add to the view.
> > > > > > >
> > > > > > > Also let's discuss syntax of cancellation of query. I propose
> to
> > > use
> > > > > > MySQL
> > > > > > > like syntax as easy to understand and shorter then Oracle and
> > > > Postgres
> > > > > > > syntax ( detailed information in IEP-29
> > > > > > > <
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > >
> > > > > > > ).
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > пн, 12 нояб. 2018 г. в 19:28, Юрий <
> [hidden email]
> > >:
> > > > > > >
> > > > > > > > Igniters,
> > > > > > > >
> > > > > > > > Below is a proposed design for thin client SQL management and
> > > > > > monitoring
> > > > > > > > to cancel a queries.
> > > > > > > >
> > > > > > > > 1) Ignite expose system SQL view with name *running_queries*
> > > > > > > > proposed columns: *node_id, query_id, sql, schema_name,
> > > > > connection_id,
> > > > > > > > duration*.
> > > > > > > >
> > > > > > > > node_id - initial node of request
> > > > > > > > query_id - unique id of query on node
> > > > > > > > sql - text of query
> > > > > > > > schema name - name of sql schema
> > > > > > > > connection_id - id of client connection from
> > > > > > > ClientListenerConnectionContext
> > > > > > > > class
> > > > > > > > duration - duration in millisecond from start of query
> > > > > > > >
> > > > > > > >
> > > > > > > > Ignite will gather info about running queries from each of
> > nodes
> > > > and
> > > > > > > > collect it during user query. We already have most of the
> > > > information
> > > > > > at
> > > > > > > GridRunningQueryInfo
> > > > > > > > on each of nodes.
> > > > > > > >
> > > > > > > > Instead of duration we can use start_time, but I think
> duration
> > > > will
> > > > > be
> > > > > > > > simple to use due to it not depend on a timezone.
> > > > > > > >
> > > > > > > >
> > > > > > > > 2) Propose to use following syntax to kill a running query:
> > > > > > > >
> > > > > > > > *KILL QUERY node_Id query_id*
> > > > > > > >
> > > > > > > >
> > > > > > > > Both parameters node_id and query_id can be get through
> > > > > running_queries
> > > > > > > > system view.
> > > > > > > >
> > > > > > > > When a node receive such request it can be run locally in
> case
> > > node
> > > > > > have
> > > > > > > > given node_id or send message to node with given id. Because
> > node
> > > > > have
> > > > > > > > information about local running queries then can cancel it -
> it
> > > > > already
> > > > > > > > implemented in GridReduceQueryExecutor.cancelQueries(qryId)
> > > method.
> > > > > > > >
> > > > > > > > Comments are welcome.
> > > > > > > > --
> > > > > > > > Живи с улыбкой! :D
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Живи с улыбкой! :D
> > > > > > >
> > > > > >
> > > > >
> > > > >
> > > > > --
> > > > > Живи с улыбкой! :D
> > > > >
> > > >
> > >
> >
>
>
> --
> Живи с улыбкой! :D
>

Юрий

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

Hi Denis,

It's not a problem, the full query id could be get from additional column
from *running_queries* view. So you may not known real meaning of each of
part of the string to use it.
Is it works?

пн, 19 нояб. 2018 г. в 19:02, Denis Mekhanikov <[hidden email]>:

> Guys,
>
> Syntax like *KILL QUERY '25.1234'* look a bit cryptic to me.
> I'm going to look up in documentation, which parameter goes first in this
> query every time I use it.
> I like the syntax, that Igor suggested more.
> Will it be better if we make *nodeId* and *queryId *named properties?
>
> Something like this:
> KILL QUERY WHERE nodeId=25 and queryId=1234
>
> Denis
>
> пт, 16 нояб. 2018 г. в 14:12, Юрий <[hidden email]>:
>
> > I fully agree with last sentences and can start to implement this part.
> >
> > Guys, thanks for your productive participate at discussion.
> >
> > пт, 16 нояб. 2018 г. в 2:53, Denis Magda <[hidden email]>:
> >
> > > Vladimir,
> > >
> > > Thanks, make perfect sense to me.
> > >
> > >
> > > On Thu, Nov 15, 2018 at 12:18 AM Vladimir Ozerov <[hidden email]
> >
> > > wrote:
> > >
> > > > Denis,
> > > >
> > > > The idea is that QueryDetailMetrics will be exposed through separate
> > > > "historical" SQL view in addition to current API. So we are on the
> same
> > > > page here.
> > > >
> > > > As far as query ID I do not see any easy way to operate on a single
> > > integer
> > > > value (even 64bit). This is distributed system - we do not want to
> have
> > > > coordination between nodes to get query ID. And coordination is the
> > only
> > > > possible way to get sexy "long". Instead, I would propose to form ID
> > from
> > > > node order and query counter within node. This will be (int, long)
> > pair.
> > > > For use convenience we may convert it to a single string, e.g.
> > > > "[node_order].[query_counter]". Then the syntax would be:
> > > >
> > > > KILL QUERY '25.1234'; // Kill query 1234 on node 25
> > > > KILL QUERY '25.*; // Kill all queries on the node 25
> > > >
> > > > Makes sense?
> > > >
> > > > Vladimir.
> > > >
> > > > On Wed, Nov 14, 2018 at 1:25 PM Denis Magda <[hidden email]>
> wrote:
> > > >
> > > > > Yury,
> > > > >
> > > > > As I understand you mean that the view should contains both running
> > and
> > > > > > finished queries. If be honest for the view I was going to use
> just
> > > > > queries
> > > > > > running right now. For finished queries I thought about another
> > view
> > > > with
> > > > > > another set of fields which should include I/O related ones. Is
> it
> > > > works?
> > > > >
> > > > >
> > > > > Got you, so if only running queries are there then your initial
> > > proposal
> > > > > makes total sense. Not sure we need a view of the finished queries.
> > It
> > > > will
> > > > > be possible to analyze them through the updated DetailedMetrics
> > > approach,
> > > > > won't it?
> > > > >
> > > > > For "KILL QUERY node_id query_id" node_id required as part of
> unique
> > > key
> > > > > > of query and help understand Ignite which node start the
> > distributed
> > > > > query.
> > > > > > Use both parameters will allow cheap generate unique key across
> all
> > > > > nodes.
> > > > > > Node which started a query can cancel it on all nodes participate
> > > > nodes.
> > > > > > So, to stop any queries initially we need just send the cancel
> > > request
> > > > to
> > > > > > node who started the query. This mechanism is already in Ignite.
> > > > >
> > > > >
> > > > > Can we locate node_id behind the scenes if the user supplies
> query_id
> > > > only?
> > > > > A query record in the view already contains query_id and node_id
> and
> > it
> > > > > sounds like an extra work for the user to fill in all the details
> for
> > > us.
> > > > > Embed node_id into query_id if you'd like to avoid extra network
> hops
> > > for
> > > > > query_id to node_id mapping.
> > > > >
> > > > > --
> > > > > Denis
> > > > >
> > > > > On Wed, Nov 14, 2018 at 1:04 AM Юрий <[hidden email]>
> > > > wrote:
> > > > >
> > > > > > Denis,
> > > > > >
> > > > > > Under the hood 'time' will be as startTime, but for system view I
> > > > planned
> > > > > > use duration which will be simple calculated as now - startTime.
> > So,
> > > > > there
> > > > > > is't a performance issue.
> > > > > > As I understand you mean that the view should contains both
> running
> > > and
> > > > > > finished queries. If be honest for the view I was going to use
> just
> > > > > queries
> > > > > > running right now. For finished queries I thought about another
> > view
> > > > with
> > > > > > another set of fields which should include I/O related ones. Is
> it
> > > > works?
> > > > > >
> > > > > > For "KILL QUERY node_id query_id" node_id required as part of
> > unique
> > > > key
> > > > > > of query and help understand Ignite which node start the
> > distributed
> > > > > query.
> > > > > > Use both parameters will allow cheap generate unique key across
> all
> > > > > nodes.
> > > > > > Node which started a query can cancel it on all nodes participate
> > > > nodes.
> > > > > > So, to stop any queries initially we need just send the cancel
> > > request
> > > > to
> > > > > > node who started the query. This mechanism is already in Ignite.
> > > > > >
> > > > > > Native SQL APIs will automatically support the futures after
> > > > implementing
> > > > > > for thin clients. So we are good here.
> > > > > >
> > > > > >
> > > > > >
> > > > > > вт, 13 нояб. 2018 г. в 18:52, Denis Magda <[hidden email]>:
> > > > > >
> > > > > > > Yury,
> > > > > > >
> > > > > > > Please consider the following:
> > > > > > >
> > > > > > > - If we record the duration instead of startTime, then the
> > > former
> > > > > has
> > > > > > to
> > > > > > > be updated frequently - sounds like a performance red flag.
> > > Should
> > > > > we
> > > > > > > store
> > > > > > > startTime and endTime instead? This way a query record will
> be
> > > > > updated
> > > > > > > twice - when the query is started and terminated.
> > > > > > > - In the IEP you've mentioned I/O related fields that should
> > > help
> > > > to
> > > > > > > grasp why a query runs that slow. Should they be stored in
> > this
> > > > > view?
> > > > > > > - "KILL QUERY query_id" is more than enough. Let's not add
> > > > "node_id"
> > > > > > > unless it's absolutely required. Our queries are distributed
> > and
> > > > > > > executed
> > > > > > > across several nodes that's why the node_id parameter is
> > > > redundant.
> > > > > > > - This API needs to be supported across all our interfaces.
> We
> > > can
> > > > > > start
> > > > > > > with JDBC/ODBC and thin clients and then support for the
> > native
> > > > SQL
> > > > > > APIs
> > > > > > > (Java, Net, C++)
> > > > > > > - Please share examples of SELECTs in the IEP that would
> show
> > > how
> > > > to
> > > > > > > find long running queries, queries that cause a lot of I/O
> > > > troubles.
> > > > > > >
> > > > > > > --
> > > > > > > Denis
> > > > > > >
> > > > > > > On Tue, Nov 13, 2018 at 1:15 AM Юрий <
> > [hidden email]>
> > > > > > wrote:
> > > > > > >
> > > > > > > > Igniters,
> > > > > > > >
> > > > > > > > Some comments for my original email's.
> > > > > > > >
> > > > > > > > The proposal related to part of IEP-29
> > > > > > > > <
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > >
> > > > > > > > .
> > > > > > > >
> > > > > > > > What purpose are we pursuing of the proposal?
> > > > > > > > We want to be able check which queries running right now
> > through
> > > > thin
> > > > > > > > clients. Get some information related to the queries and be
> > able
> > > to
> > > > > > > cancel
> > > > > > > > a query if it required for some reasons.
> > > > > > > > So, we need interface to get a running queries. For the goal
> we
> > > > > propose
> > > > > > > > running_queries system view. The view contains unique query
> > > > > identifier
> > > > > > > > which need to pass to kill query command to cancel the query.
> > > > > > > >
> > > > > > > > What do you think about fields of the running queries view?
> May
> > > be
> > > > > some
> > > > > > > > useful fields we could easy add to the view.
> > > > > > > >
> > > > > > > > Also let's discuss syntax of cancellation of query. I propose
> > to
> > > > use
> > > > > > > MySQL
> > > > > > > > like syntax as easy to understand and shorter then Oracle and
> > > > > Postgres
> > > > > > > > syntax ( detailed information in IEP-29
> > > > > > > > <
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > >
> > > > > > > > ).
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > пн, 12 нояб. 2018 г. в 19:28, Юрий <
> > [hidden email]
> > > >:
> > > > > > > >
> > > > > > > > > Igniters,
> > > > > > > > >
> > > > > > > > > Below is a proposed design for thin client SQL management
> and
> > > > > > > monitoring
> > > > > > > > > to cancel a queries.
> > > > > > > > >
> > > > > > > > > 1) Ignite expose system SQL view with name
> *running_queries*
> > > > > > > > > proposed columns: *node_id, query_id, sql, schema_name,
> > > > > > connection_id,
> > > > > > > > > duration*.
> > > > > > > > >
> > > > > > > > > node_id - initial node of request
> > > > > > > > > query_id - unique id of query on node
> > > > > > > > > sql - text of query
> > > > > > > > > schema name - name of sql schema
> > > > > > > > > connection_id - id of client connection from
> > > > > > > > ClientListenerConnectionContext
> > > > > > > > > class
> > > > > > > > > duration - duration in millisecond from start of query
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Ignite will gather info about running queries from each of
> > > nodes
> > > > > and
> > > > > > > > > collect it during user query. We already have most of the
> > > > > information
> > > > > > > at
> > > > > > > > GridRunningQueryInfo
> > > > > > > > > on each of nodes.
> > > > > > > > >
> > > > > > > > > Instead of duration we can use start_time, but I think
> > duration
> > > > > will
> > > > > > be
> > > > > > > > > simple to use due to it not depend on a timezone.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > 2) Propose to use following syntax to kill a running query:
> > > > > > > > >
> > > > > > > > > *KILL QUERY node_Id query_id*
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Both parameters node_id and query_id can be get through
> > > > > > running_queries
> > > > > > > > > system view.
> > > > > > > > >
> > > > > > > > > When a node receive such request it can be run locally in
> > case
> > > > node
> > > > > > > have
> > > > > > > > > given node_id or send message to node with given id.
> Because
> > > node
> > > > > > have
> > > > > > > > > information about local running queries then can cancel it
> -
> > it
> > > > > > already
> > > > > > > > > implemented in GridReduceQueryExecutor.cancelQueries(qryId)
> > > > method.
> > > > > > > > >
> > > > > > > > > Comments are welcome.
> > > > > > > > > --
> > > > > > > > > Живи с улыбкой! :D
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > > Живи с улыбкой! :D
> > > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Живи с улыбкой! :D
> > > > > >
> > > > >
> > > >
> > >
> >
> >
> > --
> > Живи с улыбкой! :D
> >
>

--
Живи с улыбкой! :D

Vladimir Ozerov

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

In reply to this post by Denis Mekhanikov

Denis,

I partially agree with you. But there are several problem with syntax
proposed by you:
1) This is harder to implement technically - more parsing logic to
implement. Ok, this is our internal problem, users do not care about it
2) User will have to consult to docs in any case
3) "nodeId" is not really node ID. For Ignite users node ID is UUID. In our
case this is node order, and we intentionally avoided any naming here.
Query is just identified by a string, no more than that
4) Proposed syntax is more verbose and open ways for misuse. E.g. what is
"KILL QUERY WHERE queryId=1234"?

I am not 100% satisfied with both variants, but the first one looks simpler
to me. Remember, that user will not guess query ID. Instead, he will get
the list of running queries with some other syntax. What we need to
understand for now is how this syntax will look like. I think that we
should implement getting list of running queries, and only then start
working on cancellation.

Vladimir.

On Mon, Nov 19, 2018 at 7:02 PM Denis Mekhanikov <[hidden email]>
wrote:

dmagda

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

Vladimir,

Please see inline

On Mon, Nov 19, 2018 at 8:23 AM Vladimir Ozerov <[hidden email]>
wrote:

> Denis,
>
> I partially agree with you. But there are several problem with syntax
> proposed by you:
> 1) This is harder to implement technically - more parsing logic to
> implement. Ok, this is our internal problem, users do not care about it
> 2) User will have to consult to docs in any case
>

Two of these are not a big deal. We just need to invest more time for
development and during the design phase so that people need to consult the
docs rarely.

> 3) "nodeId" is not really node ID. For Ignite users node ID is UUID. In our
> case this is node order, and we intentionally avoided any naming here.
>

Let's use a more loose name such as "node".

> Query is just identified by a string, no more than that
> 4) Proposed syntax is more verbose and open ways for misuse. E.g. what is
> "KILL QUERY WHERE queryId=1234"?
>
> I am not 100% satisfied with both variants, but the first one looks simpler
> to me. Remember, that user will not guess query ID. Instead, he will get
> the list of running queries with some other syntax. What we need to
> understand for now is how this syntax will look like. I think that we
> should implement getting list of running queries, and only then start
> working on cancellation.
>

That's a good point. Syntax of both running and killing queires commands
should be tightly coupled. We're going to name a column if running queries
IDs somehow anyway and that name might be resued in the WHERE clause of
KILL.

Should we discuss the syntax in a separate thread?

--
Denis

>
> Vladimir.
>
>
> On Mon, Nov 19, 2018 at 7:02 PM Denis Mekhanikov <[hidden email]>
> wrote:
>
> > Guys,
> >
> > Syntax like *KILL QUERY '25.1234'* look a bit cryptic to me.
> > I'm going to look up in documentation, which parameter goes first in this
> > query every time I use it.
> > I like the syntax, that Igor suggested more.
> > Will it be better if we make *nodeId* and *queryId *named properties?
> >
> > Something like this:
> > KILL QUERY WHERE nodeId=25 and queryId=1234
> >
> > Denis
> >
> > пт, 16 нояб. 2018 г. в 14:12, Юрий <[hidden email]>:
> >
> > > I fully agree with last sentences and can start to implement this part.
> > >
> > > Guys, thanks for your productive participate at discussion.
> > >
> > > пт, 16 нояб. 2018 г. в 2:53, Denis Magda <[hidden email]>:
> > >
> > > > Vladimir,
> > > >
> > > > Thanks, make perfect sense to me.
> > > >
> > > >
> > > > On Thu, Nov 15, 2018 at 12:18 AM Vladimir Ozerov <
> [hidden email]
> > >
> > > > wrote:
> > > >
> > > > > Denis,
> > > > >
> > > > > The idea is that QueryDetailMetrics will be exposed through
> separate
> > > > > "historical" SQL view in addition to current API. So we are on the
> > same
> > > > > page here.
> > > > >
> > > > > As far as query ID I do not see any easy way to operate on a single
> > > > integer
> > > > > value (even 64bit). This is distributed system - we do not want to
> > have
> > > > > coordination between nodes to get query ID. And coordination is the
> > > only
> > > > > possible way to get sexy "long". Instead, I would propose to form
> ID
> > > from
> > > > > node order and query counter within node. This will be (int, long)
> > > pair.
> > > > > For use convenience we may convert it to a single string, e.g.
> > > > > "[node_order].[query_counter]". Then the syntax would be:
> > > > >
> > > > > KILL QUERY '25.1234'; // Kill query 1234 on node 25
> > > > > KILL QUERY '25.*; // Kill all queries on the node 25
> > > > >
> > > > > Makes sense?
> > > > >
> > > > > Vladimir.
> > > > >
> > > > > On Wed, Nov 14, 2018 at 1:25 PM Denis Magda <[hidden email]>
> > wrote:
> > > > >
> > > > > > Yury,
> > > > > >
> > > > > > As I understand you mean that the view should contains both
> running
> > > and
> > > > > > > finished queries. If be honest for the view I was going to use
> > just
> > > > > > queries
> > > > > > > running right now. For finished queries I thought about another
> > > view
> > > > > with
> > > > > > > another set of fields which should include I/O related ones. Is
> > it
> > > > > works?
> > > > > >
> > > > > >
> > > > > > Got you, so if only running queries are there then your initial
> > > > proposal
> > > > > > makes total sense. Not sure we need a view of the finished
> queries.
> > > It
> > > > > will
> > > > > > be possible to analyze them through the updated DetailedMetrics
> > > > approach,
> > > > > > won't it?
> > > > > >
> > > > > > For "KILL QUERY node_id query_id" node_id required as part of
> > unique
> > > > key
> > > > > > > of query and help understand Ignite which node start the
> > > distributed
> > > > > > query.
> > > > > > > Use both parameters will allow cheap generate unique key across
> > all
> > > > > > nodes.
> > > > > > > Node which started a query can cancel it on all nodes
> participate
> > > > > nodes.
> > > > > > > So, to stop any queries initially we need just send the cancel
> > > > request
> > > > > to
> > > > > > > node who started the query. This mechanism is already in
> Ignite.
> > > > > >
> > > > > >
> > > > > > Can we locate node_id behind the scenes if the user supplies
> > query_id
> > > > > only?
> > > > > > A query record in the view already contains query_id and node_id
> > and
> > > it
> > > > > > sounds like an extra work for the user to fill in all the details
> > for
> > > > us.
> > > > > > Embed node_id into query_id if you'd like to avoid extra network
> > hops
> > > > for
> > > > > > query_id to node_id mapping.
> > > > > >
> > > > > > --
> > > > > > Denis
> > > > > >
> > > > > > On Wed, Nov 14, 2018 at 1:04 AM Юрий <
> [hidden email]>
> > > > > wrote:
> > > > > >
> > > > > > > Denis,
> > > > > > >
> > > > > > > Under the hood 'time' will be as startTime, but for system
> view I
> > > > > planned
> > > > > > > use duration which will be simple calculated as now -
> startTime.
> > > So,
> > > > > > there
> > > > > > > is't a performance issue.
> > > > > > > As I understand you mean that the view should contains both
> > running
> > > > and
> > > > > > > finished queries. If be honest for the view I was going to use
> > just
> > > > > > queries
> > > > > > > running right now. For finished queries I thought about another
> > > view
> > > > > with
> > > > > > > another set of fields which should include I/O related ones. Is
> > it
> > > > > works?
> > > > > > >
> > > > > > > For "KILL QUERY node_id query_id" node_id required as part of
> > > unique
> > > > > key
> > > > > > > of query and help understand Ignite which node start the
> > > distributed
> > > > > > query.
> > > > > > > Use both parameters will allow cheap generate unique key across
> > all
> > > > > > nodes.
> > > > > > > Node which started a query can cancel it on all nodes
> participate
> > > > > nodes.
> > > > > > > So, to stop any queries initially we need just send the cancel
> > > > request
> > > > > to
> > > > > > > node who started the query. This mechanism is already in
> Ignite.
> > > > > > >
> > > > > > > Native SQL APIs will automatically support the futures after
> > > > > implementing
> > > > > > > for thin clients. So we are good here.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > вт, 13 нояб. 2018 г. в 18:52, Denis Magda <[hidden email]>:
> > > > > > >
> > > > > > > > Yury,
> > > > > > > >
> > > > > > > > Please consider the following:
> > > > > > > >
> > > > > > > > - If we record the duration instead of startTime, then the
> > > > former
> > > > > > has
> > > > > > > to
> > > > > > > > be updated frequently - sounds like a performance red
> flag.
> > > > Should
> > > > > > we
> > > > > > > > store
> > > > > > > > startTime and endTime instead? This way a query record
> will
> > be
> > > > > > updated
> > > > > > > > twice - when the query is started and terminated.
> > > > > > > > - In the IEP you've mentioned I/O related fields that
> should
> > > > help
> > > > > to
> > > > > > > > grasp why a query runs that slow. Should they be stored in
> > > this
> > > > > > view?
> > > > > > > > - "KILL QUERY query_id" is more than enough. Let's not add
> > > > > "node_id"
> > > > > > > > unless it's absolutely required. Our queries are
> distributed
> > > and
> > > > > > > > executed
> > > > > > > > across several nodes that's why the node_id parameter is
> > > > > redundant.
> > > > > > > > - This API needs to be supported across all our
> interfaces.
> > We
> > > > can
> > > > > > > start
> > > > > > > > with JDBC/ODBC and thin clients and then support for the
> > > native
> > > > > SQL
> > > > > > > APIs
> > > > > > > > (Java, Net, C++)
> > > > > > > > - Please share examples of SELECTs in the IEP that would
> > show
> > > > how
> > > > > to
> > > > > > > > find long running queries, queries that cause a lot of I/O
> > > > > troubles.
> > > > > > > >
> > > > > > > > --
> > > > > > > > Denis
> > > > > > > >
> > > > > > > > On Tue, Nov 13, 2018 at 1:15 AM Юрий <
> > > [hidden email]>
> > > > > > > wrote:
> > > > > > > >
> > > > > > > > > Igniters,
> > > > > > > > >
> > > > > > > > > Some comments for my original email's.
> > > > > > > > >
> > > > > > > > > The proposal related to part of IEP-29
> > > > > > > > > <
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > >
> > > > > > > > > .
> > > > > > > > >
> > > > > > > > > What purpose are we pursuing of the proposal?
> > > > > > > > > We want to be able check which queries running right now
> > > through
> > > > > thin
> > > > > > > > > clients. Get some information related to the queries and be
> > > able
> > > > to
> > > > > > > > cancel
> > > > > > > > > a query if it required for some reasons.
> > > > > > > > > So, we need interface to get a running queries. For the
> goal
> > we
> > > > > > propose
> > > > > > > > > running_queries system view. The view contains unique query
> > > > > > identifier
> > > > > > > > > which need to pass to kill query command to cancel the
> query.
> > > > > > > > >
> > > > > > > > > What do you think about fields of the running queries view?
> > May
> > > > be
> > > > > > some
> > > > > > > > > useful fields we could easy add to the view.
> > > > > > > > >
> > > > > > > > > Also let's discuss syntax of cancellation of query. I
> propose
> > > to
> > > > > use
> > > > > > > > MySQL
> > > > > > > > > like syntax as easy to understand and shorter then Oracle
> and
> > > > > > Postgres
> > > > > > > > > syntax ( detailed information in IEP-29
> > > > > > > > > <
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > >
> > > > > > > > > ).
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > пн, 12 нояб. 2018 г. в 19:28, Юрий <
> > > [hidden email]
> > > > >:
> > > > > > > > >
> > > > > > > > > > Igniters,
> > > > > > > > > >
> > > > > > > > > > Below is a proposed design for thin client SQL management
> > and
> > > > > > > > monitoring
> > > > > > > > > > to cancel a queries.
> > > > > > > > > >
> > > > > > > > > > 1) Ignite expose system SQL view with name
> > *running_queries*
> > > > > > > > > > proposed columns: *node_id, query_id, sql, schema_name,
> > > > > > > connection_id,
> > > > > > > > > > duration*.
> > > > > > > > > >
> > > > > > > > > > node_id - initial node of request
> > > > > > > > > > query_id - unique id of query on node
> > > > > > > > > > sql - text of query
> > > > > > > > > > schema name - name of sql schema
> > > > > > > > > > connection_id - id of client connection from
> > > > > > > > > ClientListenerConnectionContext
> > > > > > > > > > class
> > > > > > > > > > duration - duration in millisecond from start of query
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > Ignite will gather info about running queries from each
> of
> > > > nodes
> > > > > > and
> > > > > > > > > > collect it during user query. We already have most of the
> > > > > > information
> > > > > > > > at
> > > > > > > > > GridRunningQueryInfo
> > > > > > > > > > on each of nodes.
> > > > > > > > > >
> > > > > > > > > > Instead of duration we can use start_time, but I think
> > > duration
> > > > > > will
> > > > > > > be
> > > > > > > > > > simple to use due to it not depend on a timezone.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > 2) Propose to use following syntax to kill a running
> query:
> > > > > > > > > >
> > > > > > > > > > *KILL QUERY node_Id query_id*
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > Both parameters node_id and query_id can be get through
> > > > > > > running_queries
> > > > > > > > > > system view.
> > > > > > > > > >
> > > > > > > > > > When a node receive such request it can be run locally in
> > > case
> > > > > node
> > > > > > > > have
> > > > > > > > > > given node_id or send message to node with given id.
> > Because
> > > > node
> > > > > > > have
> > > > > > > > > > information about local running queries then can cancel
> it
> > -
> > > it
> > > > > > > already
> > > > > > > > > > implemented in
> GridReduceQueryExecutor.cancelQueries(qryId)
> > > > > method.
> > > > > > > > > >
> > > > > > > > > > Comments are welcome.
> > > > > > > > > > --
> > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Живи с улыбкой! :D
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Живи с улыбкой! :D
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> > >
> > > --
> > > Живи с улыбкой! :D
> > >
> >
>

Vladimir Ozerov

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

Denis

I would wait for running queries view first.

ср, 28 нояб. 2018 г. в 1:57, Denis Magda <[hidden email]>:

> Vladimir,
>
> Please see inline
>
> On Mon, Nov 19, 2018 at 8:23 AM Vladimir Ozerov <[hidden email]>
> wrote:
>
> > Denis,
> >
> > I partially agree with you. But there are several problem with syntax
> > proposed by you:
> > 1) This is harder to implement technically - more parsing logic to
> > implement. Ok, this is our internal problem, users do not care about it
> > 2) User will have to consult to docs in any case
> >
>
> Two of these are not a big deal. We just need to invest more time for
> development and during the design phase so that people need to consult the
> docs rarely.
>
>
> > 3) "nodeId" is not really node ID. For Ignite users node ID is UUID. In
> our
> > case this is node order, and we intentionally avoided any naming here.
> >
>
> Let's use a more loose name such as "node".
>
>
> > Query is just identified by a string, no more than that
> > 4) Proposed syntax is more verbose and open ways for misuse. E.g. what is
> > "KILL QUERY WHERE queryId=1234"?
> >
> > I am not 100% satisfied with both variants, but the first one looks
> simpler
> > to me. Remember, that user will not guess query ID. Instead, he will get
> > the list of running queries with some other syntax. What we need to
> > understand for now is how this syntax will look like. I think that we
> > should implement getting list of running queries, and only then start
> > working on cancellation.
> >
>
> That's a good point. Syntax of both running and killing queires commands
> should be tightly coupled. We're going to name a column if running queries
> IDs somehow anyway and that name might be resued in the WHERE clause of
> KILL.
>
> Should we discuss the syntax in a separate thread?
>
> --
> Denis
>
> >
> > Vladimir.
> >
> >
> > On Mon, Nov 19, 2018 at 7:02 PM Denis Mekhanikov <[hidden email]>
> > wrote:
> >
> > > Guys,
> > >
> > > Syntax like *KILL QUERY '25.1234'* look a bit cryptic to me.
> > > I'm going to look up in documentation, which parameter goes first in
> this
> > > query every time I use it.
> > > I like the syntax, that Igor suggested more.
> > > Will it be better if we make *nodeId* and *queryId *named properties?
> > >
> > > Something like this:
> > > KILL QUERY WHERE nodeId=25 and queryId=1234
> > >
> > > Denis
> > >
> > > пт, 16 нояб. 2018 г. в 14:12, Юрий <[hidden email]>:
> > >
> > > > I fully agree with last sentences and can start to implement this
> part.
> > > >
> > > > Guys, thanks for your productive participate at discussion.
> > > >
> > > > пт, 16 нояб. 2018 г. в 2:53, Denis Magda <[hidden email]>:
> > > >
> > > > > Vladimir,
> > > > >
> > > > > Thanks, make perfect sense to me.
> > > > >
> > > > >
> > > > > On Thu, Nov 15, 2018 at 12:18 AM Vladimir Ozerov <
> > [hidden email]
> > > >
> > > > > wrote:
> > > > >
> > > > > > Denis,
> > > > > >
> > > > > > The idea is that QueryDetailMetrics will be exposed through
> > separate
> > > > > > "historical" SQL view in addition to current API. So we are on
> the
> > > same
> > > > > > page here.
> > > > > >
> > > > > > As far as query ID I do not see any easy way to operate on a
> single
> > > > > integer
> > > > > > value (even 64bit). This is distributed system - we do not want
> to
> > > have
> > > > > > coordination between nodes to get query ID. And coordination is
> the
> > > > only
> > > > > > possible way to get sexy "long". Instead, I would propose to form
> > ID
> > > > from
> > > > > > node order and query counter within node. This will be (int,
> long)
> > > > pair.
> > > > > > For use convenience we may convert it to a single string, e.g.
> > > > > > "[node_order].[query_counter]". Then the syntax would be:
> > > > > >
> > > > > > KILL QUERY '25.1234'; // Kill query 1234 on node 25
> > > > > > KILL QUERY '25.*; // Kill all queries on the node 25
> > > > > >
> > > > > > Makes sense?
> > > > > >
> > > > > > Vladimir.
> > > > > >
> > > > > > On Wed, Nov 14, 2018 at 1:25 PM Denis Magda <[hidden email]>
> > > wrote:
> > > > > >
> > > > > > > Yury,
> > > > > > >
> > > > > > > As I understand you mean that the view should contains both
> > running
> > > > and
> > > > > > > > finished queries. If be honest for the view I was going to
> use
> > > just
> > > > > > > queries
> > > > > > > > running right now. For finished queries I thought about
> another
> > > > view
> > > > > > with
> > > > > > > > another set of fields which should include I/O related ones.
> Is
> > > it
> > > > > > works?
> > > > > > >
> > > > > > >
> > > > > > > Got you, so if only running queries are there then your initial
> > > > > proposal
> > > > > > > makes total sense. Not sure we need a view of the finished
> > queries.
> > > > It
> > > > > > will
> > > > > > > be possible to analyze them through the updated DetailedMetrics
> > > > > approach,
> > > > > > > won't it?
> > > > > > >
> > > > > > > For "KILL QUERY node_id query_id" node_id required as part of
> > > unique
> > > > > key
> > > > > > > > of query and help understand Ignite which node start the
> > > > distributed
> > > > > > > query.
> > > > > > > > Use both parameters will allow cheap generate unique key
> across
> > > all
> > > > > > > nodes.
> > > > > > > > Node which started a query can cancel it on all nodes
> > participate
> > > > > > nodes.
> > > > > > > > So, to stop any queries initially we need just send the
> cancel
> > > > > request
> > > > > > to
> > > > > > > > node who started the query. This mechanism is already in
> > Ignite.
> > > > > > >
> > > > > > >
> > > > > > > Can we locate node_id behind the scenes if the user supplies
> > > query_id
> > > > > > only?
> > > > > > > A query record in the view already contains query_id and
> node_id
> > > and
> > > > it
> > > > > > > sounds like an extra work for the user to fill in all the
> details
> > > for
> > > > > us.
> > > > > > > Embed node_id into query_id if you'd like to avoid extra
> network
> > > hops
> > > > > for
> > > > > > > query_id to node_id mapping.
> > > > > > >
> > > > > > > --
> > > > > > > Denis
> > > > > > >
> > > > > > > On Wed, Nov 14, 2018 at 1:04 AM Юрий <
> > [hidden email]>
> > > > > > wrote:
> > > > > > >
> > > > > > > > Denis,
> > > > > > > >
> > > > > > > > Under the hood 'time' will be as startTime, but for system
> > view I
> > > > > > planned
> > > > > > > > use duration which will be simple calculated as now -
> > startTime.
> > > > So,
> > > > > > > there
> > > > > > > > is't a performance issue.
> > > > > > > > As I understand you mean that the view should contains both
> > > running
> > > > > and
> > > > > > > > finished queries. If be honest for the view I was going to
> use
> > > just
> > > > > > > queries
> > > > > > > > running right now. For finished queries I thought about
> another
> > > > view
> > > > > > with
> > > > > > > > another set of fields which should include I/O related ones.
> Is
> > > it
> > > > > > works?
> > > > > > > >
> > > > > > > > For "KILL QUERY node_id query_id" node_id required as part
> of
> > > > unique
> > > > > > key
> > > > > > > > of query and help understand Ignite which node start the
> > > > distributed
> > > > > > > query.
> > > > > > > > Use both parameters will allow cheap generate unique key
> across
> > > all
> > > > > > > nodes.
> > > > > > > > Node which started a query can cancel it on all nodes
> > participate
> > > > > > nodes.
> > > > > > > > So, to stop any queries initially we need just send the
> cancel
> > > > > request
> > > > > > to
> > > > > > > > node who started the query. This mechanism is already in
> > Ignite.
> > > > > > > >
> > > > > > > > Native SQL APIs will automatically support the futures after
> > > > > > implementing
> > > > > > > > for thin clients. So we are good here.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > вт, 13 нояб. 2018 г. в 18:52, Denis Magda <[hidden email]
> >:
> > > > > > > >
> > > > > > > > > Yury,
> > > > > > > > >
> > > > > > > > > Please consider the following:
> > > > > > > > >
> > > > > > > > > - If we record the duration instead of startTime, then
> the
> > > > > former
> > > > > > > has
> > > > > > > > to
> > > > > > > > > be updated frequently - sounds like a performance red
> > flag.
> > > > > Should
> > > > > > > we
> > > > > > > > > store
> > > > > > > > > startTime and endTime instead? This way a query record
> > will
> > > be
> > > > > > > updated
> > > > > > > > > twice - when the query is started and terminated.
> > > > > > > > > - In the IEP you've mentioned I/O related fields that
> > should
> > > > > help
> > > > > > to
> > > > > > > > > grasp why a query runs that slow. Should they be stored
> in
> > > > this
> > > > > > > view?
> > > > > > > > > - "KILL QUERY query_id" is more than enough. Let's not
> add
> > > > > > "node_id"
> > > > > > > > > unless it's absolutely required. Our queries are
> > distributed
> > > > and
> > > > > > > > > executed
> > > > > > > > > across several nodes that's why the node_id parameter is
> > > > > > redundant.
> > > > > > > > > - This API needs to be supported across all our
> > interfaces.
> > > We
> > > > > can
> > > > > > > > start
> > > > > > > > > with JDBC/ODBC and thin clients and then support for the
> > > > native
> > > > > > SQL
> > > > > > > > APIs
> > > > > > > > > (Java, Net, C++)
> > > > > > > > > - Please share examples of SELECTs in the IEP that would
> > > show
> > > > > how
> > > > > > to
> > > > > > > > > find long running queries, queries that cause a lot of
> I/O
> > > > > > troubles.
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Denis
> > > > > > > > >
> > > > > > > > > On Tue, Nov 13, 2018 at 1:15 AM Юрий <
> > > > [hidden email]>
> > > > > > > > wrote:
> > > > > > > > >
> > > > > > > > > > Igniters,
> > > > > > > > > >
> > > > > > > > > > Some comments for my original email's.
> > > > > > > > > >
> > > > > > > > > > The proposal related to part of IEP-29
> > > > > > > > > > <
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > > >
> > > > > > > > > > .
> > > > > > > > > >
> > > > > > > > > > What purpose are we pursuing of the proposal?
> > > > > > > > > > We want to be able check which queries running right now
> > > > through
> > > > > > thin
> > > > > > > > > > clients. Get some information related to the queries and
> be
> > > > able
> > > > > to
> > > > > > > > > cancel
> > > > > > > > > > a query if it required for some reasons.
> > > > > > > > > > So, we need interface to get a running queries. For the
> > goal
> > > we
> > > > > > > propose
> > > > > > > > > > running_queries system view. The view contains unique
> query
> > > > > > > identifier
> > > > > > > > > > which need to pass to kill query command to cancel the
> > query.
> > > > > > > > > >
> > > > > > > > > > What do you think about fields of the running queries
> view?
> > > May
> > > > > be
> > > > > > > some
> > > > > > > > > > useful fields we could easy add to the view.
> > > > > > > > > >
> > > > > > > > > > Also let's discuss syntax of cancellation of query. I
> > propose
> > > > to
> > > > > > use
> > > > > > > > > MySQL
> > > > > > > > > > like syntax as easy to understand and shorter then Oracle
> > and
> > > > > > > Postgres
> > > > > > > > > > syntax ( detailed information in IEP-29
> > > > > > > > > > <
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > > >
> > > > > > > > > > ).
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > пн, 12 нояб. 2018 г. в 19:28, Юрий <
> > > > [hidden email]
> > > > > >:
> > > > > > > > > >
> > > > > > > > > > > Igniters,
> > > > > > > > > > >
> > > > > > > > > > > Below is a proposed design for thin client SQL
> management
> > > and
> > > > > > > > > monitoring
> > > > > > > > > > > to cancel a queries.
> > > > > > > > > > >
> > > > > > > > > > > 1) Ignite expose system SQL view with name
> > > *running_queries*
> > > > > > > > > > > proposed columns: *node_id, query_id, sql, schema_name,
> > > > > > > > connection_id,
> > > > > > > > > > > duration*.
> > > > > > > > > > >
> > > > > > > > > > > node_id - initial node of request
> > > > > > > > > > > query_id - unique id of query on node
> > > > > > > > > > > sql - text of query
> > > > > > > > > > > schema name - name of sql schema
> > > > > > > > > > > connection_id - id of client connection from
> > > > > > > > > > ClientListenerConnectionContext
> > > > > > > > > > > class
> > > > > > > > > > > duration - duration in millisecond from start of query
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > Ignite will gather info about running queries from each
> > of
> > > > > nodes
> > > > > > > and
> > > > > > > > > > > collect it during user query. We already have most of
> the
> > > > > > > information
> > > > > > > > > at
> > > > > > > > > > GridRunningQueryInfo
> > > > > > > > > > > on each of nodes.
> > > > > > > > > > >
> > > > > > > > > > > Instead of duration we can use start_time, but I think
> > > > duration
> > > > > > > will
> > > > > > > > be
> > > > > > > > > > > simple to use due to it not depend on a timezone.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > 2) Propose to use following syntax to kill a running
> > query:
> > > > > > > > > > >
> > > > > > > > > > > *KILL QUERY node_Id query_id*
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > Both parameters node_id and query_id can be get through
> > > > > > > > running_queries
> > > > > > > > > > > system view.
> > > > > > > > > > >
> > > > > > > > > > > When a node receive such request it can be run locally
> in
> > > > case
> > > > > > node
> > > > > > > > > have
> > > > > > > > > > > given node_id or send message to node with given id.
> > > Because
> > > > > node
> > > > > > > > have
> > > > > > > > > > > information about local running queries then can cancel
> > it
> > > -
> > > > it
> > > > > > > > already
> > > > > > > > > > > implemented in
> > GridReduceQueryExecutor.cancelQueries(qryId)
> > > > > > method.
> > > > > > > > > > >
> > > > > > > > > > > Comments are welcome.
> > > > > > > > > > > --
> > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > > Живи с улыбкой! :D
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > > >
> > > > --
> > > > Живи с улыбкой! :D
> > > >
> > >
> >
>

Alexey Plekhanov

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

Yuriy,

If you have plans to implement running queries view in the nearest future,
I already have implemented draft for local node queries some time ago [1].
Maybe it will help to implement a view for whole cluster queries.

[1]:
https://github.com/alex-plekhanov/ignite/commit/6231668646a2b0f848373eb4e9dc38d127603e43

ср, 28 нояб. 2018 г. в 17:34, Vladimir Ozerov <[hidden email]>:

> Denis
>
> I would wait for running queries view first.
>
> ср, 28 нояб. 2018 г. в 1:57, Denis Magda <[hidden email]>:
>
> > Vladimir,
> >
> > Please see inline
> >
> > On Mon, Nov 19, 2018 at 8:23 AM Vladimir Ozerov <[hidden email]>
> > wrote:
> >
> > > Denis,
> > >
> > > I partially agree with you. But there are several problem with syntax
> > > proposed by you:
> > > 1) This is harder to implement technically - more parsing logic to
> > > implement. Ok, this is our internal problem, users do not care about it
> > > 2) User will have to consult to docs in any case
> > >
> >
> > Two of these are not a big deal. We just need to invest more time for
> > development and during the design phase so that people need to consult
> the
> > docs rarely.
> >
> >
> > > 3) "nodeId" is not really node ID. For Ignite users node ID is UUID. In
> > our
> > > case this is node order, and we intentionally avoided any naming here.
> > >
> >
> > Let's use a more loose name such as "node".
> >
> >
> > > Query is just identified by a string, no more than that
> > > 4) Proposed syntax is more verbose and open ways for misuse. E.g. what
> is
> > > "KILL QUERY WHERE queryId=1234"?
> > >
> > > I am not 100% satisfied with both variants, but the first one looks
> > simpler
> > > to me. Remember, that user will not guess query ID. Instead, he will
> get
> > > the list of running queries with some other syntax. What we need to
> > > understand for now is how this syntax will look like. I think that we
> > > should implement getting list of running queries, and only then start
> > > working on cancellation.
> > >
> >
> > That's a good point. Syntax of both running and killing queires commands
> > should be tightly coupled. We're going to name a column if running
> queries
> > IDs somehow anyway and that name might be resued in the WHERE clause of
> > KILL.
> >
> > Should we discuss the syntax in a separate thread?
> >
> > --
> > Denis
> >
> > >
> > > Vladimir.
> > >
> > >
> > > On Mon, Nov 19, 2018 at 7:02 PM Denis Mekhanikov <
> [hidden email]>
> > > wrote:
> > >
> > > > Guys,
> > > >
> > > > Syntax like *KILL QUERY '25.1234'* look a bit cryptic to me.
> > > > I'm going to look up in documentation, which parameter goes first in
> > this
> > > > query every time I use it.
> > > > I like the syntax, that Igor suggested more.
> > > > Will it be better if we make *nodeId* and *queryId *named properties?
> > > >
> > > > Something like this:
> > > > KILL QUERY WHERE nodeId=25 and queryId=1234
> > > >
> > > > Denis
> > > >
> > > > пт, 16 нояб. 2018 г. в 14:12, Юрий <[hidden email]>:
> > > >
> > > > > I fully agree with last sentences and can start to implement this
> > part.
> > > > >
> > > > > Guys, thanks for your productive participate at discussion.
> > > > >
> > > > > пт, 16 нояб. 2018 г. в 2:53, Denis Magda <[hidden email]>:
> > > > >
> > > > > > Vladimir,
> > > > > >
> > > > > > Thanks, make perfect sense to me.
> > > > > >
> > > > > >
> > > > > > On Thu, Nov 15, 2018 at 12:18 AM Vladimir Ozerov <
> > > [hidden email]
> > > > >
> > > > > > wrote:
> > > > > >
> > > > > > > Denis,
> > > > > > >
> > > > > > > The idea is that QueryDetailMetrics will be exposed through
> > > separate
> > > > > > > "historical" SQL view in addition to current API. So we are on
> > the
> > > > same
> > > > > > > page here.
> > > > > > >
> > > > > > > As far as query ID I do not see any easy way to operate on a
> > single
> > > > > > integer
> > > > > > > value (even 64bit). This is distributed system - we do not want
> > to
> > > > have
> > > > > > > coordination between nodes to get query ID. And coordination is
> > the
> > > > > only
> > > > > > > possible way to get sexy "long". Instead, I would propose to
> form
> > > ID
> > > > > from
> > > > > > > node order and query counter within node. This will be (int,
> > long)
> > > > > pair.
> > > > > > > For use convenience we may convert it to a single string, e.g.
> > > > > > > "[node_order].[query_counter]". Then the syntax would be:
> > > > > > >
> > > > > > > KILL QUERY '25.1234'; // Kill query 1234 on node 25
> > > > > > > KILL QUERY '25.*; // Kill all queries on the node 25
> > > > > > >
> > > > > > > Makes sense?
> > > > > > >
> > > > > > > Vladimir.
> > > > > > >
> > > > > > > On Wed, Nov 14, 2018 at 1:25 PM Denis Magda <[hidden email]
> >
> > > > wrote:
> > > > > > >
> > > > > > > > Yury,
> > > > > > > >
> > > > > > > > As I understand you mean that the view should contains both
> > > running
> > > > > and
> > > > > > > > > finished queries. If be honest for the view I was going to
> > use
> > > > just
> > > > > > > > queries
> > > > > > > > > running right now. For finished queries I thought about
> > another
> > > > > view
> > > > > > > with
> > > > > > > > > another set of fields which should include I/O related
> ones.
> > Is
> > > > it
> > > > > > > works?
> > > > > > > >
> > > > > > > >
> > > > > > > > Got you, so if only running queries are there then your
> initial
> > > > > > proposal
> > > > > > > > makes total sense. Not sure we need a view of the finished
> > > queries.
> > > > > It
> > > > > > > will
> > > > > > > > be possible to analyze them through the updated
> DetailedMetrics
> > > > > > approach,
> > > > > > > > won't it?
> > > > > > > >
> > > > > > > > For "KILL QUERY node_id query_id" node_id required as part
> of
> > > > unique
> > > > > > key
> > > > > > > > > of query and help understand Ignite which node start the
> > > > > distributed
> > > > > > > > query.
> > > > > > > > > Use both parameters will allow cheap generate unique key
> > across
> > > > all
> > > > > > > > nodes.
> > > > > > > > > Node which started a query can cancel it on all nodes
> > > participate
> > > > > > > nodes.
> > > > > > > > > So, to stop any queries initially we need just send the
> > cancel
> > > > > > request
> > > > > > > to
> > > > > > > > > node who started the query. This mechanism is already in
> > > Ignite.
> > > > > > > >
> > > > > > > >
> > > > > > > > Can we locate node_id behind the scenes if the user supplies
> > > > query_id
> > > > > > > only?
> > > > > > > > A query record in the view already contains query_id and
> > node_id
> > > > and
> > > > > it
> > > > > > > > sounds like an extra work for the user to fill in all the
> > details
> > > > for
> > > > > > us.
> > > > > > > > Embed node_id into query_id if you'd like to avoid extra
> > network
> > > > hops
> > > > > > for
> > > > > > > > query_id to node_id mapping.
> > > > > > > >
> > > > > > > > --
> > > > > > > > Denis
> > > > > > > >
> > > > > > > > On Wed, Nov 14, 2018 at 1:04 AM Юрий <
> > > [hidden email]>
> > > > > > > wrote:
> > > > > > > >
> > > > > > > > > Denis,
> > > > > > > > >
> > > > > > > > > Under the hood 'time' will be as startTime, but for system
> > > view I
> > > > > > > planned
> > > > > > > > > use duration which will be simple calculated as now -
> > > startTime.
> > > > > So,
> > > > > > > > there
> > > > > > > > > is't a performance issue.
> > > > > > > > > As I understand you mean that the view should contains both
> > > > running
> > > > > > and
> > > > > > > > > finished queries. If be honest for the view I was going to
> > use
> > > > just
> > > > > > > > queries
> > > > > > > > > running right now. For finished queries I thought about
> > another
> > > > > view
> > > > > > > with
> > > > > > > > > another set of fields which should include I/O related
> ones.
> > Is
> > > > it
> > > > > > > works?
> > > > > > > > >
> > > > > > > > > For "KILL QUERY node_id query_id" node_id required as part
> > of
> > > > > unique
> > > > > > > key
> > > > > > > > > of query and help understand Ignite which node start the
> > > > > distributed
> > > > > > > > query.
> > > > > > > > > Use both parameters will allow cheap generate unique key
> > across
> > > > all
> > > > > > > > nodes.
> > > > > > > > > Node which started a query can cancel it on all nodes
> > > participate
> > > > > > > nodes.
> > > > > > > > > So, to stop any queries initially we need just send the
> > cancel
> > > > > > request
> > > > > > > to
> > > > > > > > > node who started the query. This mechanism is already in
> > > Ignite.
> > > > > > > > >
> > > > > > > > > Native SQL APIs will automatically support the futures
> after
> > > > > > > implementing
> > > > > > > > > for thin clients. So we are good here.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > вт, 13 нояб. 2018 г. в 18:52, Denis Magda <
> [hidden email]
> > >:
> > > > > > > > >
> > > > > > > > > > Yury,
> > > > > > > > > >
> > > > > > > > > > Please consider the following:
> > > > > > > > > >
> > > > > > > > > > - If we record the duration instead of startTime, then
> > the
> > > > > > former
> > > > > > > > has
> > > > > > > > > to
> > > > > > > > > > be updated frequently - sounds like a performance red
> > > flag.
> > > > > > Should
> > > > > > > > we
> > > > > > > > > > store
> > > > > > > > > > startTime and endTime instead? This way a query record
> > > will
> > > > be
> > > > > > > > updated
> > > > > > > > > > twice - when the query is started and terminated.
> > > > > > > > > > - In the IEP you've mentioned I/O related fields that
> > > should
> > > > > > help
> > > > > > > to
> > > > > > > > > > grasp why a query runs that slow. Should they be
> stored
> > in
> > > > > this
> > > > > > > > view?
> > > > > > > > > > - "KILL QUERY query_id" is more than enough. Let's not
> > add
> > > > > > > "node_id"
> > > > > > > > > > unless it's absolutely required. Our queries are
> > > distributed
> > > > > and
> > > > > > > > > > executed
> > > > > > > > > > across several nodes that's why the node_id parameter
> is
> > > > > > > redundant.
> > > > > > > > > > - This API needs to be supported across all our
> > > interfaces.
> > > > We
> > > > > > can
> > > > > > > > > start
> > > > > > > > > > with JDBC/ODBC and thin clients and then support for
> the
> > > > > native
> > > > > > > SQL
> > > > > > > > > APIs
> > > > > > > > > > (Java, Net, C++)
> > > > > > > > > > - Please share examples of SELECTs in the IEP that
> would
> > > > show
> > > > > > how
> > > > > > > to
> > > > > > > > > > find long running queries, queries that cause a lot of
> > I/O
> > > > > > > troubles.
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > > Denis
> > > > > > > > > >
> > > > > > > > > > On Tue, Nov 13, 2018 at 1:15 AM Юрий <
> > > > > [hidden email]>
> > > > > > > > > wrote:
> > > > > > > > > >
> > > > > > > > > > > Igniters,
> > > > > > > > > > >
> > > > > > > > > > > Some comments for my original email's.
> > > > > > > > > > >
> > > > > > > > > > > The proposal related to part of IEP-29
> > > > > > > > > > > <
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > > > >
> > > > > > > > > > > .
> > > > > > > > > > >
> > > > > > > > > > > What purpose are we pursuing of the proposal?
> > > > > > > > > > > We want to be able check which queries running right
> now
> > > > > through
> > > > > > > thin
> > > > > > > > > > > clients. Get some information related to the queries
> and
> > be
> > > > > able
> > > > > > to
> > > > > > > > > > cancel
> > > > > > > > > > > a query if it required for some reasons.
> > > > > > > > > > > So, we need interface to get a running queries. For the
> > > goal
> > > > we
> > > > > > > > propose
> > > > > > > > > > > running_queries system view. The view contains unique
> > query
> > > > > > > > identifier
> > > > > > > > > > > which need to pass to kill query command to cancel the
> > > query.
> > > > > > > > > > >
> > > > > > > > > > > What do you think about fields of the running queries
> > view?
> > > > May
> > > > > > be
> > > > > > > > some
> > > > > > > > > > > useful fields we could easy add to the view.
> > > > > > > > > > >
> > > > > > > > > > > Also let's discuss syntax of cancellation of query. I
> > > propose
> > > > > to
> > > > > > > use
> > > > > > > > > > MySQL
> > > > > > > > > > > like syntax as easy to understand and shorter then
> Oracle
> > > and
> > > > > > > > Postgres
> > > > > > > > > > > syntax ( detailed information in IEP-29
> > > > > > > > > > > <
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > > > >
> > > > > > > > > > > ).
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > пн, 12 нояб. 2018 г. в 19:28, Юрий <
> > > > > [hidden email]
> > > > > > >:
> > > > > > > > > > >
> > > > > > > > > > > > Igniters,
> > > > > > > > > > > >
> > > > > > > > > > > > Below is a proposed design for thin client SQL
> > management
> > > > and
> > > > > > > > > > monitoring
> > > > > > > > > > > > to cancel a queries.
> > > > > > > > > > > >
> > > > > > > > > > > > 1) Ignite expose system SQL view with name
> > > > *running_queries*
> > > > > > > > > > > > proposed columns: *node_id, query_id, sql,
> schema_name,
> > > > > > > > > connection_id,
> > > > > > > > > > > > duration*.
> > > > > > > > > > > >
> > > > > > > > > > > > node_id - initial node of request
> > > > > > > > > > > > query_id - unique id of query on node
> > > > > > > > > > > > sql - text of query
> > > > > > > > > > > > schema name - name of sql schema
> > > > > > > > > > > > connection_id - id of client connection from
> > > > > > > > > > > ClientListenerConnectionContext
> > > > > > > > > > > > class
> > > > > > > > > > > > duration - duration in millisecond from start of
> query
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > Ignite will gather info about running queries from
> each
> > > of
> > > > > > nodes
> > > > > > > > and
> > > > > > > > > > > > collect it during user query. We already have most of
> > the
> > > > > > > > information
> > > > > > > > > > at
> > > > > > > > > > > GridRunningQueryInfo
> > > > > > > > > > > > on each of nodes.
> > > > > > > > > > > >
> > > > > > > > > > > > Instead of duration we can use start_time, but I
> think
> > > > > duration
> > > > > > > > will
> > > > > > > > > be
> > > > > > > > > > > > simple to use due to it not depend on a timezone.
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > 2) Propose to use following syntax to kill a running
> > > query:
> > > > > > > > > > > >
> > > > > > > > > > > > *KILL QUERY node_Id query_id*
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > Both parameters node_id and query_id can be get
> through
> > > > > > > > > running_queries
> > > > > > > > > > > > system view.
> > > > > > > > > > > >
> > > > > > > > > > > > When a node receive such request it can be run
> locally
> > in
> > > > > case
> > > > > > > node
> > > > > > > > > > have
> > > > > > > > > > > > given node_id or send message to node with given id.
> > > > Because
> > > > > > node
> > > > > > > > > have
> > > > > > > > > > > > information about local running queries then can
> cancel
> > > it
> > > > -
> > > > > it
> > > > > > > > > already
> > > > > > > > > > > > implemented in
> > > GridReduceQueryExecutor.cancelQueries(qryId)
> > > > > > > method.
> > > > > > > > > > > >
> > > > > > > > > > > > Comments are welcome.
> > > > > > > > > > > > --
> > > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > --
> > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Живи с улыбкой! :D
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > > >
> > > > > --
> > > > > Живи с улыбкой! :D
> > > > >
> > > >
> > >
> >
>

Юрий

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

Hi Alex,

I've just started implement of the view. Thanks for the your efforts!

ср, 28 нояб. 2018 г. в 19:00, Alex Plehanov <[hidden email]>:

> Yuriy,
>
> If you have plans to implement running queries view in the nearest future,
> I already have implemented draft for local node queries some time ago [1].
> Maybe it will help to implement a view for whole cluster queries.
>
> [1]:
>
> https://github.com/alex-plekhanov/ignite/commit/6231668646a2b0f848373eb4e9dc38d127603e43
>
>
> ср, 28 нояб. 2018 г. в 17:34, Vladimir Ozerov <[hidden email]>:
>
> > Denis
> >
> > I would wait for running queries view first.
> >
> > ср, 28 нояб. 2018 г. в 1:57, Denis Magda <[hidden email]>:
> >
> > > Vladimir,
> > >
> > > Please see inline
> > >
> > > On Mon, Nov 19, 2018 at 8:23 AM Vladimir Ozerov <[hidden email]>
> > > wrote:
> > >
> > > > Denis,
> > > >
> > > > I partially agree with you. But there are several problem with syntax
> > > > proposed by you:
> > > > 1) This is harder to implement technically - more parsing logic to
> > > > implement. Ok, this is our internal problem, users do not care about
> it
> > > > 2) User will have to consult to docs in any case
> > > >
> > >
> > > Two of these are not a big deal. We just need to invest more time for
> > > development and during the design phase so that people need to consult
> > the
> > > docs rarely.
> > >
> > >
> > > > 3) "nodeId" is not really node ID. For Ignite users node ID is UUID.
> In
> > > our
> > > > case this is node order, and we intentionally avoided any naming
> here.
> > > >
> > >
> > > Let's use a more loose name such as "node".
> > >
> > >
> > > > Query is just identified by a string, no more than that
> > > > 4) Proposed syntax is more verbose and open ways for misuse. E.g.
> what
> > is
> > > > "KILL QUERY WHERE queryId=1234"?
> > > >
> > > > I am not 100% satisfied with both variants, but the first one looks
> > > simpler
> > > > to me. Remember, that user will not guess query ID. Instead, he will
> > get
> > > > the list of running queries with some other syntax. What we need to
> > > > understand for now is how this syntax will look like. I think that we
> > > > should implement getting list of running queries, and only then start
> > > > working on cancellation.
> > > >
> > >
> > > That's a good point. Syntax of both running and killing queires
> commands
> > > should be tightly coupled. We're going to name a column if running
> > queries
> > > IDs somehow anyway and that name might be resued in the WHERE clause of
> > > KILL.
> > >
> > > Should we discuss the syntax in a separate thread?
> > >
> > > --
> > > Denis
> > >
> > > >
> > > > Vladimir.
> > > >
> > > >
> > > > On Mon, Nov 19, 2018 at 7:02 PM Denis Mekhanikov <
> > [hidden email]>
> > > > wrote:
> > > >
> > > > > Guys,
> > > > >
> > > > > Syntax like *KILL QUERY '25.1234'* look a bit cryptic to me.
> > > > > I'm going to look up in documentation, which parameter goes first
> in
> > > this
> > > > > query every time I use it.
> > > > > I like the syntax, that Igor suggested more.
> > > > > Will it be better if we make *nodeId* and *queryId *named
> properties?
> > > > >
> > > > > Something like this:
> > > > > KILL QUERY WHERE nodeId=25 and queryId=1234
> > > > >
> > > > > Denis
> > > > >
> > > > > пт, 16 нояб. 2018 г. в 14:12, Юрий <[hidden email]>:
> > > > >
> > > > > > I fully agree with last sentences and can start to implement this
> > > part.
> > > > > >
> > > > > > Guys, thanks for your productive participate at discussion.
> > > > > >
> > > > > > пт, 16 нояб. 2018 г. в 2:53, Denis Magda <[hidden email]>:
> > > > > >
> > > > > > > Vladimir,
> > > > > > >
> > > > > > > Thanks, make perfect sense to me.
> > > > > > >
> > > > > > >
> > > > > > > On Thu, Nov 15, 2018 at 12:18 AM Vladimir Ozerov <
> > > > [hidden email]
> > > > > >
> > > > > > > wrote:
> > > > > > >
> > > > > > > > Denis,
> > > > > > > >
> > > > > > > > The idea is that QueryDetailMetrics will be exposed through
> > > > separate
> > > > > > > > "historical" SQL view in addition to current API. So we are
> on
> > > the
> > > > > same
> > > > > > > > page here.
> > > > > > > >
> > > > > > > > As far as query ID I do not see any easy way to operate on a
> > > single
> > > > > > > integer
> > > > > > > > value (even 64bit). This is distributed system - we do not
> want
> > > to
> > > > > have
> > > > > > > > coordination between nodes to get query ID. And coordination
> is
> > > the
> > > > > > only
> > > > > > > > possible way to get sexy "long". Instead, I would propose to
> > form
> > > > ID
> > > > > > from
> > > > > > > > node order and query counter within node. This will be (int,
> > > long)
> > > > > > pair.
> > > > > > > > For use convenience we may convert it to a single string,
> e.g.
> > > > > > > > "[node_order].[query_counter]". Then the syntax would be:
> > > > > > > >
> > > > > > > > KILL QUERY '25.1234'; // Kill query 1234 on node 25
> > > > > > > > KILL QUERY '25.*; // Kill all queries on the node 25
> > > > > > > >
> > > > > > > > Makes sense?
> > > > > > > >
> > > > > > > > Vladimir.
> > > > > > > >
> > > > > > > > On Wed, Nov 14, 2018 at 1:25 PM Denis Magda <
> [hidden email]
> > >
> > > > > wrote:
> > > > > > > >
> > > > > > > > > Yury,
> > > > > > > > >
> > > > > > > > > As I understand you mean that the view should contains both
> > > > running
> > > > > > and
> > > > > > > > > > finished queries. If be honest for the view I was going
> to
> > > use
> > > > > just
> > > > > > > > > queries
> > > > > > > > > > running right now. For finished queries I thought about
> > > another
> > > > > > view
> > > > > > > > with
> > > > > > > > > > another set of fields which should include I/O related
> > ones.
> > > Is
> > > > > it
> > > > > > > > works?
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Got you, so if only running queries are there then your
> > initial
> > > > > > > proposal
> > > > > > > > > makes total sense. Not sure we need a view of the finished
> > > > queries.
> > > > > > It
> > > > > > > > will
> > > > > > > > > be possible to analyze them through the updated
> > DetailedMetrics
> > > > > > > approach,
> > > > > > > > > won't it?
> > > > > > > > >
> > > > > > > > > For "KILL QUERY node_id query_id" node_id required as part
> > of
> > > > > unique
> > > > > > > key
> > > > > > > > > > of query and help understand Ignite which node start the
> > > > > > distributed
> > > > > > > > > query.
> > > > > > > > > > Use both parameters will allow cheap generate unique key
> > > across
> > > > > all
> > > > > > > > > nodes.
> > > > > > > > > > Node which started a query can cancel it on all nodes
> > > > participate
> > > > > > > > nodes.
> > > > > > > > > > So, to stop any queries initially we need just send the
> > > cancel
> > > > > > > request
> > > > > > > > to
> > > > > > > > > > node who started the query. This mechanism is already in
> > > > Ignite.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Can we locate node_id behind the scenes if the user
> supplies
> > > > > query_id
> > > > > > > > only?
> > > > > > > > > A query record in the view already contains query_id and
> > > node_id
> > > > > and
> > > > > > it
> > > > > > > > > sounds like an extra work for the user to fill in all the
> > > details
> > > > > for
> > > > > > > us.
> > > > > > > > > Embed node_id into query_id if you'd like to avoid extra
> > > network
> > > > > hops
> > > > > > > for
> > > > > > > > > query_id to node_id mapping.
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Denis
> > > > > > > > >
> > > > > > > > > On Wed, Nov 14, 2018 at 1:04 AM Юрий <
> > > > [hidden email]>
> > > > > > > > wrote:
> > > > > > > > >
> > > > > > > > > > Denis,
> > > > > > > > > >
> > > > > > > > > > Under the hood 'time' will be as startTime, but for
> system
> > > > view I
> > > > > > > > planned
> > > > > > > > > > use duration which will be simple calculated as now -
> > > > startTime.
> > > > > > So,
> > > > > > > > > there
> > > > > > > > > > is't a performance issue.
> > > > > > > > > > As I understand you mean that the view should contains
> both
> > > > > running
> > > > > > > and
> > > > > > > > > > finished queries. If be honest for the view I was going
> to
> > > use
> > > > > just
> > > > > > > > > queries
> > > > > > > > > > running right now. For finished queries I thought about
> > > another
> > > > > > view
> > > > > > > > with
> > > > > > > > > > another set of fields which should include I/O related
> > ones.
> > > Is
> > > > > it
> > > > > > > > works?
> > > > > > > > > >
> > > > > > > > > > For "KILL QUERY node_id query_id" node_id required as
> part
> > > of
> > > > > > unique
> > > > > > > > key
> > > > > > > > > > of query and help understand Ignite which node start the
> > > > > > distributed
> > > > > > > > > query.
> > > > > > > > > > Use both parameters will allow cheap generate unique key
> > > across
> > > > > all
> > > > > > > > > nodes.
> > > > > > > > > > Node which started a query can cancel it on all nodes
> > > > participate
> > > > > > > > nodes.
> > > > > > > > > > So, to stop any queries initially we need just send the
> > > cancel
> > > > > > > request
> > > > > > > > to
> > > > > > > > > > node who started the query. This mechanism is already in
> > > > Ignite.
> > > > > > > > > >
> > > > > > > > > > Native SQL APIs will automatically support the futures
> > after
> > > > > > > > implementing
> > > > > > > > > > for thin clients. So we are good here.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > вт, 13 нояб. 2018 г. в 18:52, Denis Magda <
> > [hidden email]
> > > >:
> > > > > > > > > >
> > > > > > > > > > > Yury,
> > > > > > > > > > >
> > > > > > > > > > > Please consider the following:
> > > > > > > > > > >
> > > > > > > > > > > - If we record the duration instead of startTime,
> then
> > > the
> > > > > > > former
> > > > > > > > > has
> > > > > > > > > > to
> > > > > > > > > > > be updated frequently - sounds like a performance
> red
> > > > flag.
> > > > > > > Should
> > > > > > > > > we
> > > > > > > > > > > store
> > > > > > > > > > > startTime and endTime instead? This way a query
> record
> > > > will
> > > > > be
> > > > > > > > > updated
> > > > > > > > > > > twice - when the query is started and terminated.
> > > > > > > > > > > - In the IEP you've mentioned I/O related fields
> that
> > > > should
> > > > > > > help
> > > > > > > > to
> > > > > > > > > > > grasp why a query runs that slow. Should they be
> > stored
> > > in
> > > > > > this
> > > > > > > > > view?
> > > > > > > > > > > - "KILL QUERY query_id" is more than enough. Let's
> not
> > > add
> > > > > > > > "node_id"
> > > > > > > > > > > unless it's absolutely required. Our queries are
> > > > distributed
> > > > > > and
> > > > > > > > > > > executed
> > > > > > > > > > > across several nodes that's why the node_id
> parameter
> > is
> > > > > > > > redundant.
> > > > > > > > > > > - This API needs to be supported across all our
> > > > interfaces.
> > > > > We
> > > > > > > can
> > > > > > > > > > start
> > > > > > > > > > > with JDBC/ODBC and thin clients and then support for
> > the
> > > > > > native
> > > > > > > > SQL
> > > > > > > > > > APIs
> > > > > > > > > > > (Java, Net, C++)
> > > > > > > > > > > - Please share examples of SELECTs in the IEP that
> > would
> > > > > show
> > > > > > > how
> > > > > > > > to
> > > > > > > > > > > find long running queries, queries that cause a lot
> of
> > > I/O
> > > > > > > > troubles.
> > > > > > > > > > >
> > > > > > > > > > > --
> > > > > > > > > > > Denis
> > > > > > > > > > >
> > > > > > > > > > > On Tue, Nov 13, 2018 at 1:15 AM Юрий <
> > > > > > [hidden email]>
> > > > > > > > > > wrote:
> > > > > > > > > > >
> > > > > > > > > > > > Igniters,
> > > > > > > > > > > >
> > > > > > > > > > > > Some comments for my original email's.
> > > > > > > > > > > >
> > > > > > > > > > > > The proposal related to part of IEP-29
> > > > > > > > > > > > <
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > > > > >
> > > > > > > > > > > > .
> > > > > > > > > > > >
> > > > > > > > > > > > What purpose are we pursuing of the proposal?
> > > > > > > > > > > > We want to be able check which queries running right
> > now
> > > > > > through
> > > > > > > > thin
> > > > > > > > > > > > clients. Get some information related to the queries
> > and
> > > be
> > > > > > able
> > > > > > > to
> > > > > > > > > > > cancel
> > > > > > > > > > > > a query if it required for some reasons.
> > > > > > > > > > > > So, we need interface to get a running queries. For
> the
> > > > goal
> > > > > we
> > > > > > > > > propose
> > > > > > > > > > > > running_queries system view. The view contains unique
> > > query
> > > > > > > > > identifier
> > > > > > > > > > > > which need to pass to kill query command to cancel
> the
> > > > query.
> > > > > > > > > > > >
> > > > > > > > > > > > What do you think about fields of the running queries
> > > view?
> > > > > May
> > > > > > > be
> > > > > > > > > some
> > > > > > > > > > > > useful fields we could easy add to the view.
> > > > > > > > > > > >
> > > > > > > > > > > > Also let's discuss syntax of cancellation of query. I
> > > > propose
> > > > > > to
> > > > > > > > use
> > > > > > > > > > > MySQL
> > > > > > > > > > > > like syntax as easy to understand and shorter then
> > Oracle
> > > > and
> > > > > > > > > Postgres
> > > > > > > > > > > > syntax ( detailed information in IEP-29
> > > > > > > > > > > > <
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > > > > >
> > > > > > > > > > > > ).
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > пн, 12 нояб. 2018 г. в 19:28, Юрий <
> > > > > > [hidden email]
> > > > > > > >:
> > > > > > > > > > > >
> > > > > > > > > > > > > Igniters,
> > > > > > > > > > > > >
> > > > > > > > > > > > > Below is a proposed design for thin client SQL
> > > management
> > > > > and
> > > > > > > > > > > monitoring
> > > > > > > > > > > > > to cancel a queries.
> > > > > > > > > > > > >
> > > > > > > > > > > > > 1) Ignite expose system SQL view with name
> > > > > *running_queries*
> > > > > > > > > > > > > proposed columns: *node_id, query_id, sql,
> > schema_name,
> > > > > > > > > > connection_id,
> > > > > > > > > > > > > duration*.
> > > > > > > > > > > > >
> > > > > > > > > > > > > node_id - initial node of request
> > > > > > > > > > > > > query_id - unique id of query on node
> > > > > > > > > > > > > sql - text of query
> > > > > > > > > > > > > schema name - name of sql schema
> > > > > > > > > > > > > connection_id - id of client connection from
> > > > > > > > > > > > ClientListenerConnectionContext
> > > > > > > > > > > > > class
> > > > > > > > > > > > > duration - duration in millisecond from start of
> > query
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > > Ignite will gather info about running queries from
> > each
> > > > of
> > > > > > > nodes
> > > > > > > > > and
> > > > > > > > > > > > > collect it during user query. We already have most
> of
> > > the
> > > > > > > > > information
> > > > > > > > > > > at
> > > > > > > > > > > > GridRunningQueryInfo
> > > > > > > > > > > > > on each of nodes.
> > > > > > > > > > > > >
> > > > > > > > > > > > > Instead of duration we can use start_time, but I
> > think
> > > > > > duration
> > > > > > > > > will
> > > > > > > > > > be
> > > > > > > > > > > > > simple to use due to it not depend on a timezone.
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > > 2) Propose to use following syntax to kill a
> running
> > > > query:
> > > > > > > > > > > > >
> > > > > > > > > > > > > *KILL QUERY node_Id query_id*
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > > Both parameters node_id and query_id can be get
> > through
> > > > > > > > > > running_queries
> > > > > > > > > > > > > system view.
> > > > > > > > > > > > >
> > > > > > > > > > > > > When a node receive such request it can be run
> > locally
> > > in
> > > > > > case
> > > > > > > > node
> > > > > > > > > > > have
> > > > > > > > > > > > > given node_id or send message to node with given
> id.
> > > > > Because
> > > > > > > node
> > > > > > > > > > have
> > > > > > > > > > > > > information about local running queries then can
> > cancel
> > > > it
> > > > > -
> > > > > > it
> > > > > > > > > > already
> > > > > > > > > > > > > implemented in
> > > > GridReduceQueryExecutor.cancelQueries(qryId)
> > > > > > > > method.
> > > > > > > > > > > > >
> > > > > > > > > > > > > Comments are welcome.
> > > > > > > > > > > > > --
> > > > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > --
> > > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Живи с улыбкой! :D
> > > > > >
> > > > >
> > > >
> > >
> >
>

--
Живи с улыбкой! :D

Юрий

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

Hey Igniters!

I continue working on IEP-29
<https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring>
in
part related to expose view with currently running queries.

I checked how we track running queries right now and see that it's
complicated and spread by few classes process and we don't track all
running queries. Also there are internal queries which tracked within users
queries and can't be distinguished of user queries , e.g. map queries for
map-reduce queries or DML operation which required first step as select to
modify data.

My proposal is extract logic for working with running queries information
to separate class, like RunningQueryManager. The class will track running
queries and will be single point to retrieve information about running
queries. Currently to keep information about running queries uses
GridRunningQueryInfo class. As of now it can't provide useful information
to distinguish internal queries and users query, so the class need to
extend to keep information about type of query and id of initial user query
for internal query to be able identify it.

New RunningQueryManager should be used for all place where currently we
have tracking of running queries and added for all places which not covered
yet, it's mostly DDL and DML operations.

After implement the proposed change we can simple expose SQL view for all
running queries on a local node.
Collecting information from all node in a cluster currently out of scope
the change and will be described for discussing later.

Is there any objections for described proposal?

чт, 29 нояб. 2018 г. в 09:03, Юрий <[hidden email]>:

> Hi Alex,
>
> I've just started implement of the view. Thanks for the your efforts!
>
> ср, 28 нояб. 2018 г. в 19:00, Alex Plehanov <[hidden email]>:
>
>> Yuriy,
>>
>> If you have plans to implement running queries view in the nearest future,
>> I already have implemented draft for local node queries some time ago [1].
>> Maybe it will help to implement a view for whole cluster queries.
>>
>> [1]:
>>
>> https://github.com/alex-plekhanov/ignite/commit/6231668646a2b0f848373eb4e9dc38d127603e43
>>
>>
>> ср, 28 нояб. 2018 г. в 17:34, Vladimir Ozerov <[hidden email]>:
>>
>> > Denis
>> >
>> > I would wait for running queries view first.
>> >
>> > ср, 28 нояб. 2018 г. в 1:57, Denis Magda <[hidden email]>:
>> >
>> > > Vladimir,
>> > >
>> > > Please see inline
>> > >
>> > > On Mon, Nov 19, 2018 at 8:23 AM Vladimir Ozerov <[hidden email]
>> >
>> > > wrote:
>> > >
>> > > > Denis,
>> > > >
>> > > > I partially agree with you. But there are several problem with
>> syntax
>> > > > proposed by you:
>> > > > 1) This is harder to implement technically - more parsing logic to
>> > > > implement. Ok, this is our internal problem, users do not care
>> about it
>> > > > 2) User will have to consult to docs in any case
>> > > >
>> > >
>> > > Two of these are not a big deal. We just need to invest more time for
>> > > development and during the design phase so that people need to consult
>> > the
>> > > docs rarely.
>> > >
>> > >
>> > > > 3) "nodeId" is not really node ID. For Ignite users node ID is
>> UUID. In
>> > > our
>> > > > case this is node order, and we intentionally avoided any naming
>> here.
>> > > >
>> > >
>> > > Let's use a more loose name such as "node".
>> > >
>> > >
>> > > > Query is just identified by a string, no more than that
>> > > > 4) Proposed syntax is more verbose and open ways for misuse. E.g.
>> what
>> > is
>> > > > "KILL QUERY WHERE queryId=1234"?
>> > > >
>> > > > I am not 100% satisfied with both variants, but the first one looks
>> > > simpler
>> > > > to me. Remember, that user will not guess query ID. Instead, he will
>> > get
>> > > > the list of running queries with some other syntax. What we need to
>> > > > understand for now is how this syntax will look like. I think that
>> we
>> > > > should implement getting list of running queries, and only then
>> start
>> > > > working on cancellation.
>> > > >
>> > >
>> > > That's a good point. Syntax of both running and killing queires
>> commands
>> > > should be tightly coupled. We're going to name a column if running
>> > queries
>> > > IDs somehow anyway and that name might be resued in the WHERE clause
>> of
>> > > KILL.
>> > >
>> > > Should we discuss the syntax in a separate thread?
>> > >
>> > > --
>> > > Denis
>> > >
>> > > >
>> > > > Vladimir.
>> > > >
>> > > >
>> > > > On Mon, Nov 19, 2018 at 7:02 PM Denis Mekhanikov <
>> > [hidden email]>
>> > > > wrote:
>> > > >
>> > > > > Guys,
>> > > > >
>> > > > > Syntax like *KILL QUERY '25.1234'* look a bit cryptic to me.
>> > > > > I'm going to look up in documentation, which parameter goes first
>> in
>> > > this
>> > > > > query every time I use it.
>> > > > > I like the syntax, that Igor suggested more.
>> > > > > Will it be better if we make *nodeId* and *queryId *named
>> properties?
>> > > > >
>> > > > > Something like this:
>> > > > > KILL QUERY WHERE nodeId=25 and queryId=1234
>> > > > >
>> > > > > Denis
>> > > > >
>> > > > > пт, 16 нояб. 2018 г. в 14:12, Юрий <[hidden email]>:
>> > > > >
>> > > > > > I fully agree with last sentences and can start to implement
>> this
>> > > part.
>> > > > > >
>> > > > > > Guys, thanks for your productive participate at discussion.
>> > > > > >
>> > > > > > пт, 16 нояб. 2018 г. в 2:53, Denis Magda <[hidden email]>:
>> > > > > >
>> > > > > > > Vladimir,
>> > > > > > >
>> > > > > > > Thanks, make perfect sense to me.
>> > > > > > >
>> > > > > > >
>> > > > > > > On Thu, Nov 15, 2018 at 12:18 AM Vladimir Ozerov <
>> > > > [hidden email]
>> > > > > >
>> > > > > > > wrote:
>> > > > > > >
>> > > > > > > > Denis,
>> > > > > > > >
>> > > > > > > > The idea is that QueryDetailMetrics will be exposed through
>> > > > separate
>> > > > > > > > "historical" SQL view in addition to current API. So we are
>> on
>> > > the
>> > > > > same
>> > > > > > > > page here.
>> > > > > > > >
>> > > > > > > > As far as query ID I do not see any easy way to operate on a
>> > > single
>> > > > > > > integer
>> > > > > > > > value (even 64bit). This is distributed system - we do not
>> want
>> > > to
>> > > > > have
>> > > > > > > > coordination between nodes to get query ID. And
>> coordination is
>> > > the
>> > > > > > only
>> > > > > > > > possible way to get sexy "long". Instead, I would propose to
>> > form
>> > > > ID
>> > > > > > from
>> > > > > > > > node order and query counter within node. This will be (int,
>> > > long)
>> > > > > > pair.
>> > > > > > > > For use convenience we may convert it to a single string,
>> e.g.
>> > > > > > > > "[node_order].[query_counter]". Then the syntax would be:
>> > > > > > > >
>> > > > > > > > KILL QUERY '25.1234'; // Kill query 1234 on node 25
>> > > > > > > > KILL QUERY '25.*; // Kill all queries on the node 25
>> > > > > > > >
>> > > > > > > > Makes sense?
>> > > > > > > >
>> > > > > > > > Vladimir.
>> > > > > > > >
>> > > > > > > > On Wed, Nov 14, 2018 at 1:25 PM Denis Magda <
>> [hidden email]
>> > >
>> > > > > wrote:
>> > > > > > > >
>> > > > > > > > > Yury,
>> > > > > > > > >
>> > > > > > > > > As I understand you mean that the view should contains
>> both
>> > > > running
>> > > > > > and
>> > > > > > > > > > finished queries. If be honest for the view I was going
>> to
>> > > use
>> > > > > just
>> > > > > > > > > queries
>> > > > > > > > > > running right now. For finished queries I thought about
>> > > another
>> > > > > > view
>> > > > > > > > with
>> > > > > > > > > > another set of fields which should include I/O related
>> > ones.
>> > > Is
>> > > > > it
>> > > > > > > > works?
>> > > > > > > > >
>> > > > > > > > >
>> > > > > > > > > Got you, so if only running queries are there then your
>> > initial
>> > > > > > > proposal
>> > > > > > > > > makes total sense. Not sure we need a view of the finished
>> > > > queries.
>> > > > > > It
>> > > > > > > > will
>> > > > > > > > > be possible to analyze them through the updated
>> > DetailedMetrics
>> > > > > > > approach,
>> > > > > > > > > won't it?
>> > > > > > > > >
>> > > > > > > > > For "KILL QUERY node_id query_id" node_id required as
>> part
>> > of
>> > > > > unique
>> > > > > > > key
>> > > > > > > > > > of query and help understand Ignite which node start the
>> > > > > > distributed
>> > > > > > > > > query.
>> > > > > > > > > > Use both parameters will allow cheap generate unique key
>> > > across
>> > > > > all
>> > > > > > > > > nodes.
>> > > > > > > > > > Node which started a query can cancel it on all nodes
>> > > > participate
>> > > > > > > > nodes.
>> > > > > > > > > > So, to stop any queries initially we need just send the
>> > > cancel
>> > > > > > > request
>> > > > > > > > to
>> > > > > > > > > > node who started the query. This mechanism is already in
>> > > > Ignite.
>> > > > > > > > >
>> > > > > > > > >
>> > > > > > > > > Can we locate node_id behind the scenes if the user
>> supplies
>> > > > > query_id
>> > > > > > > > only?
>> > > > > > > > > A query record in the view already contains query_id and
>> > > node_id
>> > > > > and
>> > > > > > it
>> > > > > > > > > sounds like an extra work for the user to fill in all the
>> > > details
>> > > > > for
>> > > > > > > us.
>> > > > > > > > > Embed node_id into query_id if you'd like to avoid extra
>> > > network
>> > > > > hops
>> > > > > > > for
>> > > > > > > > > query_id to node_id mapping.
>> > > > > > > > >
>> > > > > > > > > --
>> > > > > > > > > Denis
>> > > > > > > > >
>> > > > > > > > > On Wed, Nov 14, 2018 at 1:04 AM Юрий <
>> > > > [hidden email]>
>> > > > > > > > wrote:
>> > > > > > > > >
>> > > > > > > > > > Denis,
>> > > > > > > > > >
>> > > > > > > > > > Under the hood 'time' will be as startTime, but for
>> system
>> > > > view I
>> > > > > > > > planned
>> > > > > > > > > > use duration which will be simple calculated as now -
>> > > > startTime.
>> > > > > > So,
>> > > > > > > > > there
>> > > > > > > > > > is't a performance issue.
>> > > > > > > > > > As I understand you mean that the view should contains
>> both
>> > > > > running
>> > > > > > > and
>> > > > > > > > > > finished queries. If be honest for the view I was going
>> to
>> > > use
>> > > > > just
>> > > > > > > > > queries
>> > > > > > > > > > running right now. For finished queries I thought about
>> > > another
>> > > > > > view
>> > > > > > > > with
>> > > > > > > > > > another set of fields which should include I/O related
>> > ones.
>> > > Is
>> > > > > it
>> > > > > > > > works?
>> > > > > > > > > >
>> > > > > > > > > > For "KILL QUERY node_id query_id" node_id required as
>> part
>> > > of
>> > > > > > unique
>> > > > > > > > key
>> > > > > > > > > > of query and help understand Ignite which node start the
>> > > > > > distributed
>> > > > > > > > > query.
>> > > > > > > > > > Use both parameters will allow cheap generate unique key
>> > > across
>> > > > > all
>> > > > > > > > > nodes.
>> > > > > > > > > > Node which started a query can cancel it on all nodes
>> > > > participate
>> > > > > > > > nodes.
>> > > > > > > > > > So, to stop any queries initially we need just send the
>> > > cancel
>> > > > > > > request
>> > > > > > > > to
>> > > > > > > > > > node who started the query. This mechanism is already in
>> > > > Ignite.
>> > > > > > > > > >
>> > > > > > > > > > Native SQL APIs will automatically support the futures
>> > after
>> > > > > > > > implementing
>> > > > > > > > > > for thin clients. So we are good here.
>> > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > > > вт, 13 нояб. 2018 г. в 18:52, Denis Magda <
>> > [hidden email]
>> > > >:
>> > > > > > > > > >
>> > > > > > > > > > > Yury,
>> > > > > > > > > > >
>> > > > > > > > > > > Please consider the following:
>> > > > > > > > > > >
>> > > > > > > > > > > - If we record the duration instead of startTime,
>> then
>> > > the
>> > > > > > > former
>> > > > > > > > > has
>> > > > > > > > > > to
>> > > > > > > > > > > be updated frequently - sounds like a performance
>> red
>> > > > flag.
>> > > > > > > Should
>> > > > > > > > > we
>> > > > > > > > > > > store
>> > > > > > > > > > > startTime and endTime instead? This way a query
>> record
>> > > > will
>> > > > > be
>> > > > > > > > > updated
>> > > > > > > > > > > twice - when the query is started and terminated.
>> > > > > > > > > > > - In the IEP you've mentioned I/O related fields
>> that
>> > > > should
>> > > > > > > help
>> > > > > > > > to
>> > > > > > > > > > > grasp why a query runs that slow. Should they be
>> > stored
>> > > in
>> > > > > > this
>> > > > > > > > > view?
>> > > > > > > > > > > - "KILL QUERY query_id" is more than enough. Let's
>> not
>> > > add
>> > > > > > > > "node_id"
>> > > > > > > > > > > unless it's absolutely required. Our queries are
>> > > > distributed
>> > > > > > and
>> > > > > > > > > > > executed
>> > > > > > > > > > > across several nodes that's why the node_id
>> parameter
>> > is
>> > > > > > > > redundant.
>> > > > > > > > > > > - This API needs to be supported across all our
>> > > > interfaces.
>> > > > > We
>> > > > > > > can
>> > > > > > > > > > start
>> > > > > > > > > > > with JDBC/ODBC and thin clients and then support
>> for
>> > the
>> > > > > > native
>> > > > > > > > SQL
>> > > > > > > > > > APIs
>> > > > > > > > > > > (Java, Net, C++)
>> > > > > > > > > > > - Please share examples of SELECTs in the IEP that
>> > would
>> > > > > show
>> > > > > > > how
>> > > > > > > > to
>> > > > > > > > > > > find long running queries, queries that cause a
>> lot of
>> > > I/O
>> > > > > > > > troubles.
>> > > > > > > > > > >
>> > > > > > > > > > > --
>> > > > > > > > > > > Denis
>> > > > > > > > > > >
>> > > > > > > > > > > On Tue, Nov 13, 2018 at 1:15 AM Юрий <
>> > > > > > [hidden email]>
>> > > > > > > > > > wrote:
>> > > > > > > > > > >
>> > > > > > > > > > > > Igniters,
>> > > > > > > > > > > >
>> > > > > > > > > > > > Some comments for my original email's.
>> > > > > > > > > > > >
>> > > > > > > > > > > > The proposal related to part of IEP-29
>> > > > > > > > > > > > <
>> > > > > > > > > > > >
>> > > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > >
>> > > > > > > >
>> > > > > > >
>> > > > > >
>> > > > >
>> > > >
>> > >
>> >
>> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
>> > > > > > > > > > > > >
>> > > > > > > > > > > > .
>> > > > > > > > > > > >
>> > > > > > > > > > > > What purpose are we pursuing of the proposal?
>> > > > > > > > > > > > We want to be able check which queries running right
>> > now
>> > > > > > through
>> > > > > > > > thin
>> > > > > > > > > > > > clients. Get some information related to the queries
>> > and
>> > > be
>> > > > > > able
>> > > > > > > to
>> > > > > > > > > > > cancel
>> > > > > > > > > > > > a query if it required for some reasons.
>> > > > > > > > > > > > So, we need interface to get a running queries. For
>> the
>> > > > goal
>> > > > > we
>> > > > > > > > > propose
>> > > > > > > > > > > > running_queries system view. The view contains
>> unique
>> > > query
>> > > > > > > > > identifier
>> > > > > > > > > > > > which need to pass to kill query command to cancel
>> the
>> > > > query.
>> > > > > > > > > > > >
>> > > > > > > > > > > > What do you think about fields of the running
>> queries
>> > > view?
>> > > > > May
>> > > > > > > be
>> > > > > > > > > some
>> > > > > > > > > > > > useful fields we could easy add to the view.
>> > > > > > > > > > > >
>> > > > > > > > > > > > Also let's discuss syntax of cancellation of query.
>> I
>> > > > propose
>> > > > > > to
>> > > > > > > > use
>> > > > > > > > > > > MySQL
>> > > > > > > > > > > > like syntax as easy to understand and shorter then
>> > Oracle
>> > > > and
>> > > > > > > > > Postgres
>> > > > > > > > > > > > syntax ( detailed information in IEP-29
>> > > > > > > > > > > > <
>> > > > > > > > > > > >
>> > > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > >
>> > > > > > > >
>> > > > > > >
>> > > > > >
>> > > > >
>> > > >
>> > >
>> >
>> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
>> > > > > > > > > > > > >
>> > > > > > > > > > > > ).
>> > > > > > > > > > > >
>> > > > > > > > > > > >
>> > > > > > > > > > > >
>> > > > > > > > > > > > пн, 12 нояб. 2018 г. в 19:28, Юрий <
>> > > > > > [hidden email]
>> > > > > > > >:
>> > > > > > > > > > > >
>> > > > > > > > > > > > > Igniters,
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > Below is a proposed design for thin client SQL
>> > > management
>> > > > > and
>> > > > > > > > > > > monitoring
>> > > > > > > > > > > > > to cancel a queries.
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > 1) Ignite expose system SQL view with name
>> > > > > *running_queries*
>> > > > > > > > > > > > > proposed columns: *node_id, query_id, sql,
>> > schema_name,
>> > > > > > > > > > connection_id,
>> > > > > > > > > > > > > duration*.
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > node_id - initial node of request
>> > > > > > > > > > > > > query_id - unique id of query on node
>> > > > > > > > > > > > > sql - text of query
>> > > > > > > > > > > > > schema name - name of sql schema
>> > > > > > > > > > > > > connection_id - id of client connection from
>> > > > > > > > > > > > ClientListenerConnectionContext
>> > > > > > > > > > > > > class
>> > > > > > > > > > > > > duration - duration in millisecond from start of
>> > query
>> > > > > > > > > > > > >
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > Ignite will gather info about running queries from
>> > each
>> > > > of
>> > > > > > > nodes
>> > > > > > > > > and
>> > > > > > > > > > > > > collect it during user query. We already have
>> most of
>> > > the
>> > > > > > > > > information
>> > > > > > > > > > > at
>> > > > > > > > > > > > GridRunningQueryInfo
>> > > > > > > > > > > > > on each of nodes.
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > Instead of duration we can use start_time, but I
>> > think
>> > > > > > duration
>> > > > > > > > > will
>> > > > > > > > > > be
>> > > > > > > > > > > > > simple to use due to it not depend on a timezone.
>> > > > > > > > > > > > >
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > 2) Propose to use following syntax to kill a
>> running
>> > > > query:
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > *KILL QUERY node_Id query_id*
>> > > > > > > > > > > > >
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > Both parameters node_id and query_id can be get
>> > through
>> > > > > > > > > > running_queries
>> > > > > > > > > > > > > system view.
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > When a node receive such request it can be run
>> > locally
>> > > in
>> > > > > > case
>> > > > > > > > node
>> > > > > > > > > > > have
>> > > > > > > > > > > > > given node_id or send message to node with given
>> id.
>> > > > > Because
>> > > > > > > node
>> > > > > > > > > > have
>> > > > > > > > > > > > > information about local running queries then can
>> > cancel
>> > > > it
>> > > > > -
>> > > > > > it
>> > > > > > > > > > already
>> > > > > > > > > > > > > implemented in
>> > > > GridReduceQueryExecutor.cancelQueries(qryId)
>> > > > > > > > method.
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > Comments are welcome.
>> > > > > > > > > > > > > --
>> > > > > > > > > > > > > Живи с улыбкой! :D
>> > > > > > > > > > > > >
>> > > > > > > > > > > >
>> > > > > > > > > > > >
>> > > > > > > > > > > > --
>> > > > > > > > > > > > Живи с улыбкой! :D
>> > > > > > > > > > > >
>> > > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > > > > --
>> > > > > > > > > > Живи с улыбкой! :D
>> > > > > > > > > >
>> > > > > > > > >
>> > > > > > > >
>> > > > > > >
>> > > > > >
>> > > > > >
>> > > > > > --
>> > > > > > Живи с улыбкой! :D
>> > > > > >
>> > > > >
>> > > >
>> > >
>> >
>>
>
>
> --
> Живи с улыбкой! :D
>

--
Живи с улыбкой! :D

Vladimir Ozerov

Re: proposed design for thin client SQL management and monitoring (view running queries and kill it)

Sounds good to me.

On Tue, Dec 4, 2018 at 12:06 PM Юрий <[hidden email]> wrote:

> Hey Igniters!
>
> I continue working on IEP-29
> <
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> >
> in
> part related to expose view with currently running queries.
>
> I checked how we track running queries right now and see that it's
> complicated and spread by few classes process and we don't track all
> running queries. Also there are internal queries which tracked within users
> queries and can't be distinguished of user queries , e.g. map queries for
> map-reduce queries or DML operation which required first step as select to
> modify data.
>
> My proposal is extract logic for working with running queries information
> to separate class, like RunningQueryManager. The class will track running
> queries and will be single point to retrieve information about running
> queries. Currently to keep information about running queries uses
> GridRunningQueryInfo class. As of now it can't provide useful information
> to distinguish internal queries and users query, so the class need to
> extend to keep information about type of query and id of initial user query
> for internal query to be able identify it.
>
> New RunningQueryManager should be used for all place where currently we
> have tracking of running queries and added for all places which not covered
> yet, it's mostly DDL and DML operations.
>
> After implement the proposed change we can simple expose SQL view for all
> running queries on a local node.
> Collecting information from all node in a cluster currently out of scope
> the change and will be described for discussing later.
>
> Is there any objections for described proposal?
>
> чт, 29 нояб. 2018 г. в 09:03, Юрий <[hidden email]>:
>
> > Hi Alex,
> >
> > I've just started implement of the view. Thanks for the your efforts!
> >
> > ср, 28 нояб. 2018 г. в 19:00, Alex Plehanov <[hidden email]>:
> >
> >> Yuriy,
> >>
> >> If you have plans to implement running queries view in the nearest
> future,
> >> I already have implemented draft for local node queries some time ago
> [1].
> >> Maybe it will help to implement a view for whole cluster queries.
> >>
> >> [1]:
> >>
> >>
> https://github.com/alex-plekhanov/ignite/commit/6231668646a2b0f848373eb4e9dc38d127603e43
> >>
> >>
> >> ср, 28 нояб. 2018 г. в 17:34, Vladimir Ozerov <[hidden email]>:
> >>
> >> > Denis
> >> >
> >> > I would wait for running queries view first.
> >> >
> >> > ср, 28 нояб. 2018 г. в 1:57, Denis Magda <[hidden email]>:
> >> >
> >> > > Vladimir,
> >> > >
> >> > > Please see inline
> >> > >
> >> > > On Mon, Nov 19, 2018 at 8:23 AM Vladimir Ozerov <
> [hidden email]
> >> >
> >> > > wrote:
> >> > >
> >> > > > Denis,
> >> > > >
> >> > > > I partially agree with you. But there are several problem with
> >> syntax
> >> > > > proposed by you:
> >> > > > 1) This is harder to implement technically - more parsing logic to
> >> > > > implement. Ok, this is our internal problem, users do not care
> >> about it
> >> > > > 2) User will have to consult to docs in any case
> >> > > >
> >> > >
> >> > > Two of these are not a big deal. We just need to invest more time
> for
> >> > > development and during the design phase so that people need to
> consult
> >> > the
> >> > > docs rarely.
> >> > >
> >> > >
> >> > > > 3) "nodeId" is not really node ID. For Ignite users node ID is
> >> UUID. In
> >> > > our
> >> > > > case this is node order, and we intentionally avoided any naming
> >> here.
> >> > > >
> >> > >
> >> > > Let's use a more loose name such as "node".
> >> > >
> >> > >
> >> > > > Query is just identified by a string, no more than that
> >> > > > 4) Proposed syntax is more verbose and open ways for misuse. E.g.
> >> what
> >> > is
> >> > > > "KILL QUERY WHERE queryId=1234"?
> >> > > >
> >> > > > I am not 100% satisfied with both variants, but the first one
> looks
> >> > > simpler
> >> > > > to me. Remember, that user will not guess query ID. Instead, he
> will
> >> > get
> >> > > > the list of running queries with some other syntax. What we need
> to
> >> > > > understand for now is how this syntax will look like. I think that
> >> we
> >> > > > should implement getting list of running queries, and only then
> >> start
> >> > > > working on cancellation.
> >> > > >
> >> > >
> >> > > That's a good point. Syntax of both running and killing queires
> >> commands
> >> > > should be tightly coupled. We're going to name a column if running
> >> > queries
> >> > > IDs somehow anyway and that name might be resued in the WHERE clause
> >> of
> >> > > KILL.
> >> > >
> >> > > Should we discuss the syntax in a separate thread?
> >> > >
> >> > > --
> >> > > Denis
> >> > >
> >> > > >
> >> > > > Vladimir.
> >> > > >
> >> > > >
> >> > > > On Mon, Nov 19, 2018 at 7:02 PM Denis Mekhanikov <
> >> > [hidden email]>
> >> > > > wrote:
> >> > > >
> >> > > > > Guys,
> >> > > > >
> >> > > > > Syntax like *KILL QUERY '25.1234'* look a bit cryptic to me.
> >> > > > > I'm going to look up in documentation, which parameter goes
> first
> >> in
> >> > > this
> >> > > > > query every time I use it.
> >> > > > > I like the syntax, that Igor suggested more.
> >> > > > > Will it be better if we make *nodeId* and *queryId *named
> >> properties?
> >> > > > >
> >> > > > > Something like this:
> >> > > > > KILL QUERY WHERE nodeId=25 and queryId=1234
> >> > > > >
> >> > > > > Denis
> >> > > > >
> >> > > > > пт, 16 нояб. 2018 г. в 14:12, Юрий <[hidden email]
> >:
> >> > > > >
> >> > > > > > I fully agree with last sentences and can start to implement
> >> this
> >> > > part.
> >> > > > > >
> >> > > > > > Guys, thanks for your productive participate at discussion.
> >> > > > > >
> >> > > > > > пт, 16 нояб. 2018 г. в 2:53, Denis Magda <[hidden email]>:
> >> > > > > >
> >> > > > > > > Vladimir,
> >> > > > > > >
> >> > > > > > > Thanks, make perfect sense to me.
> >> > > > > > >
> >> > > > > > >
> >> > > > > > > On Thu, Nov 15, 2018 at 12:18 AM Vladimir Ozerov <
> >> > > > [hidden email]
> >> > > > > >
> >> > > > > > > wrote:
> >> > > > > > >
> >> > > > > > > > Denis,
> >> > > > > > > >
> >> > > > > > > > The idea is that QueryDetailMetrics will be exposed
> through
> >> > > > separate
> >> > > > > > > > "historical" SQL view in addition to current API. So we
> are
> >> on
> >> > > the
> >> > > > > same
> >> > > > > > > > page here.
> >> > > > > > > >
> >> > > > > > > > As far as query ID I do not see any easy way to operate
> on a
> >> > > single
> >> > > > > > > integer
> >> > > > > > > > value (even 64bit). This is distributed system - we do not
> >> want
> >> > > to
> >> > > > > have
> >> > > > > > > > coordination between nodes to get query ID. And
> >> coordination is
> >> > > the
> >> > > > > > only
> >> > > > > > > > possible way to get sexy "long". Instead, I would propose
> to
> >> > form
> >> > > > ID
> >> > > > > > from
> >> > > > > > > > node order and query counter within node. This will be
> (int,
> >> > > long)
> >> > > > > > pair.
> >> > > > > > > > For use convenience we may convert it to a single string,
> >> e.g.
> >> > > > > > > > "[node_order].[query_counter]". Then the syntax would be:
> >> > > > > > > >
> >> > > > > > > > KILL QUERY '25.1234'; // Kill query 1234 on node 25
> >> > > > > > > > KILL QUERY '25.*; // Kill all queries on the node 25
> >> > > > > > > >
> >> > > > > > > > Makes sense?
> >> > > > > > > >
> >> > > > > > > > Vladimir.
> >> > > > > > > >
> >> > > > > > > > On Wed, Nov 14, 2018 at 1:25 PM Denis Magda <
> >> [hidden email]
> >> > >
> >> > > > > wrote:
> >> > > > > > > >
> >> > > > > > > > > Yury,
> >> > > > > > > > >
> >> > > > > > > > > As I understand you mean that the view should contains
> >> both
> >> > > > running
> >> > > > > > and
> >> > > > > > > > > > finished queries. If be honest for the view I was
> going
> >> to
> >> > > use
> >> > > > > just
> >> > > > > > > > > queries
> >> > > > > > > > > > running right now. For finished queries I thought
> about
> >> > > another
> >> > > > > > view
> >> > > > > > > > with
> >> > > > > > > > > > another set of fields which should include I/O related
> >> > ones.
> >> > > Is
> >> > > > > it
> >> > > > > > > > works?
> >> > > > > > > > >
> >> > > > > > > > >
> >> > > > > > > > > Got you, so if only running queries are there then your
> >> > initial
> >> > > > > > > proposal
> >> > > > > > > > > makes total sense. Not sure we need a view of the
> finished
> >> > > > queries.
> >> > > > > > It
> >> > > > > > > > will
> >> > > > > > > > > be possible to analyze them through the updated
> >> > DetailedMetrics
> >> > > > > > > approach,
> >> > > > > > > > > won't it?
> >> > > > > > > > >
> >> > > > > > > > > For "KILL QUERY node_id query_id" node_id required as
> >> part
> >> > of
> >> > > > > unique
> >> > > > > > > key
> >> > > > > > > > > > of query and help understand Ignite which node start
> the
> >> > > > > > distributed
> >> > > > > > > > > query.
> >> > > > > > > > > > Use both parameters will allow cheap generate unique
> key
> >> > > across
> >> > > > > all
> >> > > > > > > > > nodes.
> >> > > > > > > > > > Node which started a query can cancel it on all nodes
> >> > > > participate
> >> > > > > > > > nodes.
> >> > > > > > > > > > So, to stop any queries initially we need just send
> the
> >> > > cancel
> >> > > > > > > request
> >> > > > > > > > to
> >> > > > > > > > > > node who started the query. This mechanism is already
> in
> >> > > > Ignite.
> >> > > > > > > > >
> >> > > > > > > > >
> >> > > > > > > > > Can we locate node_id behind the scenes if the user
> >> supplies
> >> > > > > query_id
> >> > > > > > > > only?
> >> > > > > > > > > A query record in the view already contains query_id and
> >> > > node_id
> >> > > > > and
> >> > > > > > it
> >> > > > > > > > > sounds like an extra work for the user to fill in all
> the
> >> > > details
> >> > > > > for
> >> > > > > > > us.
> >> > > > > > > > > Embed node_id into query_id if you'd like to avoid extra
> >> > > network
> >> > > > > hops
> >> > > > > > > for
> >> > > > > > > > > query_id to node_id mapping.
> >> > > > > > > > >
> >> > > > > > > > > --
> >> > > > > > > > > Denis
> >> > > > > > > > >
> >> > > > > > > > > On Wed, Nov 14, 2018 at 1:04 AM Юрий <
> >> > > > [hidden email]>
> >> > > > > > > > wrote:
> >> > > > > > > > >
> >> > > > > > > > > > Denis,
> >> > > > > > > > > >
> >> > > > > > > > > > Under the hood 'time' will be as startTime, but for
> >> system
> >> > > > view I
> >> > > > > > > > planned
> >> > > > > > > > > > use duration which will be simple calculated as now -
> >> > > > startTime.
> >> > > > > > So,
> >> > > > > > > > > there
> >> > > > > > > > > > is't a performance issue.
> >> > > > > > > > > > As I understand you mean that the view should contains
> >> both
> >> > > > > running
> >> > > > > > > and
> >> > > > > > > > > > finished queries. If be honest for the view I was
> going
> >> to
> >> > > use
> >> > > > > just
> >> > > > > > > > > queries
> >> > > > > > > > > > running right now. For finished queries I thought
> about
> >> > > another
> >> > > > > > view
> >> > > > > > > > with
> >> > > > > > > > > > another set of fields which should include I/O related
> >> > ones.
> >> > > Is
> >> > > > > it
> >> > > > > > > > works?
> >> > > > > > > > > >
> >> > > > > > > > > > For "KILL QUERY node_id query_id" node_id required as
> >> part
> >> > > of
> >> > > > > > unique
> >> > > > > > > > key
> >> > > > > > > > > > of query and help understand Ignite which node start
> the
> >> > > > > > distributed
> >> > > > > > > > > query.
> >> > > > > > > > > > Use both parameters will allow cheap generate unique
> key
> >> > > across
> >> > > > > all
> >> > > > > > > > > nodes.
> >> > > > > > > > > > Node which started a query can cancel it on all nodes
> >> > > > participate
> >> > > > > > > > nodes.
> >> > > > > > > > > > So, to stop any queries initially we need just send
> the
> >> > > cancel
> >> > > > > > > request
> >> > > > > > > > to
> >> > > > > > > > > > node who started the query. This mechanism is already
> in
> >> > > > Ignite.
> >> > > > > > > > > >
> >> > > > > > > > > > Native SQL APIs will automatically support the futures
> >> > after
> >> > > > > > > > implementing
> >> > > > > > > > > > for thin clients. So we are good here.
> >> > > > > > > > > >
> >> > > > > > > > > >
> >> > > > > > > > > >
> >> > > > > > > > > > вт, 13 нояб. 2018 г. в 18:52, Denis Magda <
> >> > [hidden email]
> >> > > >:
> >> > > > > > > > > >
> >> > > > > > > > > > > Yury,
> >> > > > > > > > > > >
> >> > > > > > > > > > > Please consider the following:
> >> > > > > > > > > > >
> >> > > > > > > > > > > - If we record the duration instead of startTime,
> >> then
> >> > > the
> >> > > > > > > former
> >> > > > > > > > > has
> >> > > > > > > > > > to
> >> > > > > > > > > > > be updated frequently - sounds like a performance
> >> red
> >> > > > flag.
> >> > > > > > > Should
> >> > > > > > > > > we
> >> > > > > > > > > > > store
> >> > > > > > > > > > > startTime and endTime instead? This way a query
> >> record
> >> > > > will
> >> > > > > be
> >> > > > > > > > > updated
> >> > > > > > > > > > > twice - when the query is started and terminated.
> >> > > > > > > > > > > - In the IEP you've mentioned I/O related fields
> >> that
> >> > > > should
> >> > > > > > > help
> >> > > > > > > > to
> >> > > > > > > > > > > grasp why a query runs that slow. Should they be
> >> > stored
> >> > > in
> >> > > > > > this
> >> > > > > > > > > view?
> >> > > > > > > > > > > - "KILL QUERY query_id" is more than enough.
> Let's
> >> not
> >> > > add
> >> > > > > > > > "node_id"
> >> > > > > > > > > > > unless it's absolutely required. Our queries are
> >> > > > distributed
> >> > > > > > and
> >> > > > > > > > > > > executed
> >> > > > > > > > > > > across several nodes that's why the node_id
> >> parameter
> >> > is
> >> > > > > > > > redundant.
> >> > > > > > > > > > > - This API needs to be supported across all our
> >> > > > interfaces.
> >> > > > > We
> >> > > > > > > can
> >> > > > > > > > > > start
> >> > > > > > > > > > > with JDBC/ODBC and thin clients and then support
> >> for
> >> > the
> >> > > > > > native
> >> > > > > > > > SQL
> >> > > > > > > > > > APIs
> >> > > > > > > > > > > (Java, Net, C++)
> >> > > > > > > > > > > - Please share examples of SELECTs in the IEP
> that
> >> > would
> >> > > > > show
> >> > > > > > > how
> >> > > > > > > > to
> >> > > > > > > > > > > find long running queries, queries that cause a
> >> lot of
> >> > > I/O
> >> > > > > > > > troubles.
> >> > > > > > > > > > >
> >> > > > > > > > > > > --
> >> > > > > > > > > > > Denis
> >> > > > > > > > > > >
> >> > > > > > > > > > > On Tue, Nov 13, 2018 at 1:15 AM Юрий <
> >> > > > > > [hidden email]>
> >> > > > > > > > > > wrote:
> >> > > > > > > > > > >
> >> > > > > > > > > > > > Igniters,
> >> > > > > > > > > > > >
> >> > > > > > > > > > > > Some comments for my original email's.
> >> > > > > > > > > > > >
> >> > > > > > > > > > > > The proposal related to part of IEP-29
> >> > > > > > > > > > > > <
> >> > > > > > > > > > > >
> >> > > > > > > > > > >
> >> > > > > > > > > >
> >> > > > > > > > >
> >> > > > > > > >
> >> > > > > > >
> >> > > > > >
> >> > > > >
> >> > > >
> >> > >
> >> >
> >>
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> >> > > > > > > > > > > > >
> >> > > > > > > > > > > > .
> >> > > > > > > > > > > >
> >> > > > > > > > > > > > What purpose are we pursuing of the proposal?
> >> > > > > > > > > > > > We want to be able check which queries running
> right
> >> > now
> >> > > > > > through
> >> > > > > > > > thin
> >> > > > > > > > > > > > clients. Get some information related to the
> queries
> >> > and
> >> > > be
> >> > > > > > able
> >> > > > > > > to
> >> > > > > > > > > > > cancel
> >> > > > > > > > > > > > a query if it required for some reasons.
> >> > > > > > > > > > > > So, we need interface to get a running queries.
> For
> >> the
> >> > > > goal
> >> > > > > we
> >> > > > > > > > > propose
> >> > > > > > > > > > > > running_queries system view. The view contains
> >> unique
> >> > > query
> >> > > > > > > > > identifier
> >> > > > > > > > > > > > which need to pass to kill query command to cancel
> >> the
> >> > > > query.
> >> > > > > > > > > > > >
> >> > > > > > > > > > > > What do you think about fields of the running
> >> queries
> >> > > view?
> >> > > > > May
> >> > > > > > > be
> >> > > > > > > > > some
> >> > > > > > > > > > > > useful fields we could easy add to the view.
> >> > > > > > > > > > > >
> >> > > > > > > > > > > > Also let's discuss syntax of cancellation of
> query.
> >> I
> >> > > > propose
> >> > > > > > to
> >> > > > > > > > use
> >> > > > > > > > > > > MySQL
> >> > > > > > > > > > > > like syntax as easy to understand and shorter then
> >> > Oracle
> >> > > > and
> >> > > > > > > > > Postgres
> >> > > > > > > > > > > > syntax ( detailed information in IEP-29
> >> > > > > > > > > > > > <
> >> > > > > > > > > > > >
> >> > > > > > > > > > >
> >> > > > > > > > > >
> >> > > > > > > > >
> >> > > > > > > >
> >> > > > > > >
> >> > > > > >
> >> > > > >
> >> > > >
> >> > >
> >> >
> >>
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> >> > > > > > > > > > > > >
> >> > > > > > > > > > > > ).
> >> > > > > > > > > > > >
> >> > > > > > > > > > > >
> >> > > > > > > > > > > >
> >> > > > > > > > > > > > пн, 12 нояб. 2018 г. в 19:28, Юрий <
> >> > > > > > [hidden email]
> >> > > > > > > >:
> >> > > > > > > > > > > >
> >> > > > > > > > > > > > > Igniters,
> >> > > > > > > > > > > > >
> >> > > > > > > > > > > > > Below is a proposed design for thin client SQL
> >> > > management
> >> > > > > and
> >> > > > > > > > > > > monitoring
> >> > > > > > > > > > > > > to cancel a queries.
> >> > > > > > > > > > > > >
> >> > > > > > > > > > > > > 1) Ignite expose system SQL view with name
> >> > > > > *running_queries*
> >> > > > > > > > > > > > > proposed columns: *node_id, query_id, sql,
> >> > schema_name,
> >> > > > > > > > > > connection_id,
> >> > > > > > > > > > > > > duration*.
> >> > > > > > > > > > > > >
> >> > > > > > > > > > > > > node_id - initial node of request
> >> > > > > > > > > > > > > query_id - unique id of query on node
> >> > > > > > > > > > > > > sql - text of query
> >> > > > > > > > > > > > > schema name - name of sql schema
> >> > > > > > > > > > > > > connection_id - id of client connection from
> >> > > > > > > > > > > > ClientListenerConnectionContext
> >> > > > > > > > > > > > > class
> >> > > > > > > > > > > > > duration - duration in millisecond from start of
> >> > query
> >> > > > > > > > > > > > >
> >> > > > > > > > > > > > >
> >> > > > > > > > > > > > > Ignite will gather info about running queries
> from
> >> > each
> >> > > > of
> >> > > > > > > nodes
> >> > > > > > > > > and
> >> > > > > > > > > > > > > collect it during user query. We already have
> >> most of
> >> > > the
> >> > > > > > > > > information
> >> > > > > > > > > > > at
> >> > > > > > > > > > > > GridRunningQueryInfo
> >> > > > > > > > > > > > > on each of nodes.
> >> > > > > > > > > > > > >
> >> > > > > > > > > > > > > Instead of duration we can use start_time, but I
> >> > think
> >> > > > > > duration
> >> > > > > > > > > will
> >> > > > > > > > > > be
> >> > > > > > > > > > > > > simple to use due to it not depend on a
> timezone.
> >> > > > > > > > > > > > >
> >> > > > > > > > > > > > >
> >> > > > > > > > > > > > > 2) Propose to use following syntax to kill a
> >> running
> >> > > > query:
> >> > > > > > > > > > > > >
> >> > > > > > > > > > > > > *KILL QUERY node_Id query_id*
> >> > > > > > > > > > > > >
> >> > > > > > > > > > > > >
> >> > > > > > > > > > > > > Both parameters node_id and query_id can be get
> >> > through
> >> > > > > > > > > > running_queries
> >> > > > > > > > > > > > > system view.
> >> > > > > > > > > > > > >
> >> > > > > > > > > > > > > When a node receive such request it can be run
> >> > locally
> >> > > in
> >> > > > > > case
> >> > > > > > > > node
> >> > > > > > > > > > > have
> >> > > > > > > > > > > > > given node_id or send message to node with given
> >> id.
> >> > > > > Because
> >> > > > > > > node
> >> > > > > > > > > > have
> >> > > > > > > > > > > > > information about local running queries then can
> >> > cancel
> >> > > > it
> >> > > > > -
> >> > > > > > it
> >> > > > > > > > > > already
> >> > > > > > > > > > > > > implemented in
> >> > > > GridReduceQueryExecutor.cancelQueries(qryId)
> >> > > > > > > > method.
> >> > > > > > > > > > > > >
> >> > > > > > > > > > > > > Comments are welcome.
> >> > > > > > > > > > > > > --
> >> > > > > > > > > > > > > Живи с улыбкой! :D
> >> > > > > > > > > > > > >
> >> > > > > > > > > > > >
> >> > > > > > > > > > > >
> >> > > > > > > > > > > > --
> >> > > > > > > > > > > > Живи с улыбкой! :D
> >> > > > > > > > > > > >
> >> > > > > > > > > > >
> >> > > > > > > > > >
> >> > > > > > > > > >
> >> > > > > > > > > > --
> >> > > > > > > > > > Живи с улыбкой! :D
> >> > > > > > > > > >
> >> > > > > > > > >
> >> > > > > > > >
> >> > > > > > >
> >> > > > > >
> >> > > > > >
> >> > > > > > --
> >> > > > > > Живи с улыбкой! :D
> >> > > > > >
> >> > > > >
> >> > > >
> >> > >
> >> >
> >>
> >
> >
> > --
> > Живи с улыбкой! :D
> >
>
>
> --
> Живи с улыбкой! :D
>