proposed realization KILL QUERY command

[Юрий]

Hi Igniters,

Earlier we agreed about syntax KILL QUERY '[node_order].[query_counter]',
e.g. KILL QUERY '25.123' for single query  or KILL QUERY '25.*' for all
queries on the node. Which is part of IEP-29
<https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring>
.

Now I want to discuss internal realization of KILL query feature.

My current vision is following:
After parsing, Ignite create KILL query command with two parameters:
nodeOrderId, nodeQryId. To determine that need to kill all queries on a
node we can use negative value of query id, due to qry id always have
positive values.
The command process at IgniteH2Indexing as native command.
By nodeOrderId we find node which initial for the query and send to the
node new GridQueryKillRequest with nodeQryId to TOPIC_QUERY with not QUERY
POOL executor.
At GridReduceQueryExecutor we add support of processing new
GridQueryKillRequest
which just run already exists cancelQueries method with given qryId or with
all qryIds which currently running at the node in case at initial KILL
QUERY parameters used star symbol.

I have a doubt which of thread pool we should use to process
GridQueryKillRequest.
My opinion it shouldn't be QUERY pool, due to the pool can be fully used by
executing queries, it such case we can't cancel query immediately. May we
use one of already existed pool or create new one? Or may be I'm mistaken
and it should use QUERY pool.

What do you think about proposed plan of implementation?

And please give comments about which of thread pool will be better to use
for kill query requests. It's small, but really important part of the
realization.


Thanks.


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

[Vladimir Ozerov]

Hi Yuriy,

I think we can use MANAGEMENT_POOL for this. It is already used for some
internal Ignite tasks, and it appears to be a good candidate to process
cancel requests.

But there are several things which are not clear enough for me at the
moment:
1) How user is going to get the list of running queries in the first place?
Do we already have any SQL commands/views to get this information?
2) We need to ensure that KILL command will be processed properly by all
kinds of SQL queries - SELECT/DML/DDL, non-transactional or transactional,
local queries and distributed queries. Will we be able to support all these
modes?

Vladimir.

On Mon, Nov 19, 2018 at 6:37 PM Юрий <[hidden email]> wrote:

> Hi Igniters,
>
> Earlier we agreed about syntax KILL QUERY '[node_order].[query_counter]',
> e.g. KILL QUERY '25.123' for single query  or KILL QUERY '25.*' for all
> queries on the node. Which is part of IEP-29
> <
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> >
> .
>
> Now I want to discuss internal realization of KILL query feature.
>
> My current vision is following:
> After parsing, Ignite create KILL query command with two parameters:
> nodeOrderId, nodeQryId. To determine that need to kill all queries on a
> node we can use negative value of query id, due to qry id always have
> positive values.
> The command process at IgniteH2Indexing as native command.
> By nodeOrderId we find node which initial for the query and send to the
> node new GridQueryKillRequest with nodeQryId to TOPIC_QUERY with not QUERY
> POOL executor.
> At GridReduceQueryExecutor we add support of processing new
> GridQueryKillRequest
> which just run already exists cancelQueries method with given qryId or with
> all qryIds which currently running at the node in case at initial KILL
> QUERY parameters used star symbol.
>
> I have a doubt which of thread pool we should use to process
> GridQueryKillRequest.
> My opinion it shouldn't be QUERY pool, due to the pool can be fully used by
> executing queries, it such case we can't cancel query immediately. May we
> use one of already existed pool or create new one? Or may be I'm mistaken
> and it should use QUERY pool.
>
> What do you think about proposed plan of implementation?
>
> And please give comments about which of thread pool will be better to use
> for kill query requests. It's small, but really important part of the
> realization.
>
>
> Thanks.
>
>
> --
> Живи с улыбкой! :D
>

[Юрий]

Hi Vladimir,

Thanks for your suggestion to use MANAGEMENT_POOL for processing
cancellation requests.

About your questions.
1) I'm going to implements SQL view to provide list of running queries. The
SQL VIEW has been a little bit discussed earlier. Proposed name is
*running_queries* with following columns: query_id, node_id, sql,
schema_name, connection_id, duration. Currently most of the information can
be  retrieved through cache API, however it doesn't matter, any case we
need to expose SQL VIEW. Seem's you are right - the part should be
implemented firstly.
2) Fully agree that we need to support all kind of SQL queries
(SLECT/DML/DDL, transactional, non transnational, local, distributed). I
definitely sure that it will possible for all of above, however I'm not
sure about DDL - need to investigate it deeper. Also need to understand
that canceled DML operation can lead to partially updated data for non
transational caches.



пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov <[hidden email]>:

> Hi Yuriy,
>
> I think we can use MANAGEMENT_POOL for this. It is already used for some
> internal Ignite tasks, and it appears to be a good candidate to process
> cancel requests.
>
> But there are several things which are not clear enough for me at the
> moment:
> 1) How user is going to get the list of running queries in the first place?
> Do we already have any SQL commands/views to get this information?
> 2) We need to ensure that KILL command will be processed properly by all
> kinds of SQL queries - SELECT/DML/DDL, non-transactional or transactional,
> local queries and distributed queries. Will we be able to support all these
> modes?
>
> Vladimir.
>
> On Mon, Nov 19, 2018 at 6:37 PM Юрий <[hidden email]> wrote:
>
> > Hi Igniters,
> >
> > Earlier we agreed about syntax KILL QUERY '[node_order].[query_counter]',
> > e.g. KILL QUERY '25.123' for single query  or KILL QUERY '25.*' for all
> > queries on the node. Which is part of IEP-29
> > <
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > >
> > .
> >
> > Now I want to discuss internal realization of KILL query feature.
> >
> > My current vision is following:
> > After parsing, Ignite create KILL query command with two parameters:
> > nodeOrderId, nodeQryId. To determine that need to kill all queries on a
> > node we can use negative value of query id, due to qry id always have
> > positive values.
> > The command process at IgniteH2Indexing as native command.
> > By nodeOrderId we find node which initial for the query and send to the
> > node new GridQueryKillRequest with nodeQryId to TOPIC_QUERY with not
> QUERY
> > POOL executor.
> > At GridReduceQueryExecutor we add support of processing new
> > GridQueryKillRequest
> > which just run already exists cancelQueries method with given qryId or
> with
> > all qryIds which currently running at the node in case at initial KILL
> > QUERY parameters used star symbol.
> >
> > I have a doubt which of thread pool we should use to process
> > GridQueryKillRequest.
> > My opinion it shouldn't be QUERY pool, due to the pool can be fully used
> by
> > executing queries, it such case we can't cancel query immediately. May we
> > use one of already existed pool or create new one? Or may be I'm mistaken
> > and it should use QUERY pool.
> >
> > What do you think about proposed plan of implementation?
> >
> > And please give comments about which of thread pool will be better to use
> > for kill query requests. It's small, but really important part of the
> > realization.
> >
> >
> > Thanks.
> >
> >
> > --
> > Живи с улыбкой! :D
> >
>

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

[dmagda]

Folks,

The decimal syntax is really odd - KILL QUERY '[node_order].[query_counter]'

Confusing, let's use a space to separate parameters.

Also, what if I want to halt a specific query with certain ID? Don't know
the node number, just know that the query is distributed and runs across
several machines. Sounds like the syntax still should consider
[node_order/id] as an optional parameter.

Probably, if you explain to me how an end user will use this command from
the very beginning (how do I look for a query id and node id, etc) then the
things get clearer.

--
Denis

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

> Hi Vladimir,
>
> Thanks for your suggestion to use MANAGEMENT_POOL for processing
> cancellation requests.
>
> About your questions.
> 1) I'm going to implements SQL view to provide list of running queries. The
> SQL VIEW has been a little bit discussed earlier. Proposed name is
> *running_queries* with following columns: query_id, node_id, sql,
> schema_name, connection_id, duration. Currently most of the information can
> be  retrieved through cache API, however it doesn't matter, any case we
> need to expose SQL VIEW. Seem's you are right - the part should be
> implemented firstly.
> 2) Fully agree that we need to support all kind of SQL queries
> (SLECT/DML/DDL, transactional, non transnational, local, distributed). I
> definitely sure that it will possible for all of above, however I'm not
> sure about DDL - need to investigate it deeper. Also need to understand
> that canceled DML operation can lead to partially updated data for non
> transational caches.
>
>
>
> пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov <[hidden email]>:
>
> > Hi Yuriy,
> >
> > I think we can use MANAGEMENT_POOL for this. It is already used for some
> > internal Ignite tasks, and it appears to be a good candidate to process
> > cancel requests.
> >
> > But there are several things which are not clear enough for me at the
> > moment:
> > 1) How user is going to get the list of running queries in the first
> place?
> > Do we already have any SQL commands/views to get this information?
> > 2) We need to ensure that KILL command will be processed properly by all
> > kinds of SQL queries - SELECT/DML/DDL, non-transactional or
> transactional,
> > local queries and distributed queries. Will we be able to support all
> these
> > modes?
> >
> > Vladimir.
> >
> > On Mon, Nov 19, 2018 at 6:37 PM Юрий <[hidden email]>
> wrote:
> >
> > > Hi Igniters,
> > >
> > > Earlier we agreed about syntax KILL QUERY
> '[node_order].[query_counter]',
> > > e.g. KILL QUERY '25.123' for single query  or KILL QUERY '25.*' for all
> > > queries on the node. Which is part of IEP-29
> > > <
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > >
> > > .
> > >
> > > Now I want to discuss internal realization of KILL query feature.
> > >
> > > My current vision is following:
> > > After parsing, Ignite create KILL query command with two parameters:
> > > nodeOrderId, nodeQryId. To determine that need to kill all queries on a
> > > node we can use negative value of query id, due to qry id always have
> > > positive values.
> > > The command process at IgniteH2Indexing as native command.
> > > By nodeOrderId we find node which initial for the query and send to the
> > > node new GridQueryKillRequest with nodeQryId to TOPIC_QUERY with not
> > QUERY
> > > POOL executor.
> > > At GridReduceQueryExecutor we add support of processing new
> > > GridQueryKillRequest
> > > which just run already exists cancelQueries method with given qryId or
> > with
> > > all qryIds which currently running at the node in case at initial KILL
> > > QUERY parameters used star symbol.
> > >
> > > I have a doubt which of thread pool we should use to process
> > > GridQueryKillRequest.
> > > My opinion it shouldn't be QUERY pool, due to the pool can be fully
> used
> > by
> > > executing queries, it such case we can't cancel query immediately. May
> we
> > > use one of already existed pool or create new one? Or may be I'm
> mistaken
> > > and it should use QUERY pool.
> > >
> > > What do you think about proposed plan of implementation?
> > >
> > > And please give comments about which of thread pool will be better to
> use
> > > for kill query requests. It's small, but really important part of the
> > > realization.
> > >
> > >
> > > Thanks.
> > >
> > >
> > > --
> > > Живи с улыбкой! :D
> > >
> >
>
>
> --
> Живи с улыбкой! :D
>

[Юрий]

Denis,

Let's consider parameter of KILL QUERY just a string with some query id,
without any meaning for user. User just need to get the id and pass as
parameter to KILL QUERY command.

Even if query is distributed it have single query id from user perspective
and will killed on all nodes. User just need to known one global query id.

How it can works.
1)SELECT * from running_queries
result is
 query_id | node_id
  | sql               | schema_name | connection_id | duration
123.33     | e0a69cb8-a1a8-45f6-b84d-ead367a00000   | SELECT ...  | ...
                  |   22                 | 23456
333.31     | aaa6acb8-a4a5-42f6-f842-ead111b00020     | UPDATE...  | ...
                  |  321                | 3467777
2) KILL QUERY '123.33'

So, user need select query_id from running_queries view and use it for KILL
QUERY command.

I hope it became clearer.



ср, 21 нояб. 2018 г. в 02:11, Denis Magda <[hidden email]>:

> Folks,
>
> The decimal syntax is really odd - KILL QUERY
> '[node_order].[query_counter]'
>
> Confusing, let's use a space to separate parameters.
>
> Also, what if I want to halt a specific query with certain ID? Don't know
> the node number, just know that the query is distributed and runs across
> several machines. Sounds like the syntax still should consider
> [node_order/id] as an optional parameter.
>
> Probably, if you explain to me how an end user will use this command from
> the very beginning (how do I look for a query id and node id, etc) then the
> things get clearer.
>
> --
> Denis
>
> On Tue, Nov 20, 2018 at 1:03 AM Юрий <[hidden email]> wrote:
>
> > Hi Vladimir,
> >
> > Thanks for your suggestion to use MANAGEMENT_POOL for processing
> > cancellation requests.
> >
> > About your questions.
> > 1) I'm going to implements SQL view to provide list of running queries.
> The
> > SQL VIEW has been a little bit discussed earlier. Proposed name is
> > *running_queries* with following columns: query_id, node_id, sql,
> > schema_name, connection_id, duration. Currently most of the information
> can
> > be  retrieved through cache API, however it doesn't matter, any case we
> > need to expose SQL VIEW. Seem's you are right - the part should be
> > implemented firstly.
> > 2) Fully agree that we need to support all kind of SQL queries
> > (SLECT/DML/DDL, transactional, non transnational, local, distributed). I
> > definitely sure that it will possible for all of above, however I'm not
> > sure about DDL - need to investigate it deeper. Also need to understand
> > that canceled DML operation can lead to partially updated data for non
> > transational caches.
> >
> >
> >
> > пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov <[hidden email]>:
> >
> > > Hi Yuriy,
> > >
> > > I think we can use MANAGEMENT_POOL for this. It is already used for
> some
> > > internal Ignite tasks, and it appears to be a good candidate to process
> > > cancel requests.
> > >
> > > But there are several things which are not clear enough for me at the
> > > moment:
> > > 1) How user is going to get the list of running queries in the first
> > place?
> > > Do we already have any SQL commands/views to get this information?
> > > 2) We need to ensure that KILL command will be processed properly by
> all
> > > kinds of SQL queries - SELECT/DML/DDL, non-transactional or
> > transactional,
> > > local queries and distributed queries. Will we be able to support all
> > these
> > > modes?
> > >
> > > Vladimir.
> > >
> > > On Mon, Nov 19, 2018 at 6:37 PM Юрий <[hidden email]>
> > wrote:
> > >
> > > > Hi Igniters,
> > > >
> > > > Earlier we agreed about syntax KILL QUERY
> > '[node_order].[query_counter]',
> > > > e.g. KILL QUERY '25.123' for single query  or KILL QUERY '25.*' for
> all
> > > > queries on the node. Which is part of IEP-29
> > > > <
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > >
> > > > .
> > > >
> > > > Now I want to discuss internal realization of KILL query feature.
> > > >
> > > > My current vision is following:
> > > > After parsing, Ignite create KILL query command with two parameters:
> > > > nodeOrderId, nodeQryId. To determine that need to kill all queries
> on a
> > > > node we can use negative value of query id, due to qry id always have
> > > > positive values.
> > > > The command process at IgniteH2Indexing as native command.
> > > > By nodeOrderId we find node which initial for the query and send to
> the
> > > > node new GridQueryKillRequest with nodeQryId to TOPIC_QUERY with not
> > > QUERY
> > > > POOL executor.
> > > > At GridReduceQueryExecutor we add support of processing new
> > > > GridQueryKillRequest
> > > > which just run already exists cancelQueries method with given qryId
> or
> > > with
> > > > all qryIds which currently running at the node in case at initial
> KILL
> > > > QUERY parameters used star symbol.
> > > >
> > > > I have a doubt which of thread pool we should use to process
> > > > GridQueryKillRequest.
> > > > My opinion it shouldn't be QUERY pool, due to the pool can be fully
> > used
> > > by
> > > > executing queries, it such case we can't cancel query immediately.
> May
> > we
> > > > use one of already existed pool or create new one? Or may be I'm
> > mistaken
> > > > and it should use QUERY pool.
> > > >
> > > > What do you think about proposed plan of implementation?
> > > >
> > > > And please give comments about which of thread pool will be better to
> > use
> > > > for kill query requests. It's small, but really important part of the
> > > > realization.
> > > >
> > > >
> > > > Thanks.
> > > >
> > > >
> > > > --
> > > > Живи с улыбкой! :D
> > > >
> > >
> >
> >
> > --
> > Живи с улыбкой! :D
> >
>

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

[Vladimir Ozerov]

Denis,

Space is bad candidate because it is a whitespace. Without whitespaces we
can have syntax without quotes at all. Any non-whitespace delimiter will
work, though:

KILL QUERY 45.1
KILL QUERY 45-1
KILL QUERY 45:1

On Wed, Nov 21, 2018 at 3:06 PM Юрий <[hidden email]> wrote:

> Denis,
>
> Let's consider parameter of KILL QUERY just a string with some query id,
> without any meaning for user. User just need to get the id and pass as
> parameter to KILL QUERY command.
>
> Even if query is distributed it have single query id from user perspective
> and will killed on all nodes. User just need to known one global query id.
>
> How it can works.
> 1)SELECT * from running_queries
> result is
>  query_id | node_id
>   | sql               | schema_name | connection_id | duration
> 123.33     | e0a69cb8-a1a8-45f6-b84d-ead367a00000   | SELECT ...  | ...
>                   |   22                 | 23456
> 333.31     | aaa6acb8-a4a5-42f6-f842-ead111b00020     | UPDATE...  | ...
>                   |  321                | 3467777
> 2) KILL QUERY '123.33'
>
> So, user need select query_id from running_queries view and use it for KILL
> QUERY command.
>
> I hope it became clearer.
>
>
>
> ср, 21 нояб. 2018 г. в 02:11, Denis Magda <[hidden email]>:
>
> > Folks,
> >
> > The decimal syntax is really odd - KILL QUERY
> > '[node_order].[query_counter]'
> >
> > Confusing, let's use a space to separate parameters.
> >
> > Also, what if I want to halt a specific query with certain ID? Don't know
> > the node number, just know that the query is distributed and runs across
> > several machines. Sounds like the syntax still should consider
> > [node_order/id] as an optional parameter.
> >
> > Probably, if you explain to me how an end user will use this command from
> > the very beginning (how do I look for a query id and node id, etc) then
> the
> > things get clearer.
> >
> > --
> > Denis
> >
> > On Tue, Nov 20, 2018 at 1:03 AM Юрий <[hidden email]>
> wrote:
> >
> > > Hi Vladimir,
> > >
> > > Thanks for your suggestion to use MANAGEMENT_POOL for processing
> > > cancellation requests.
> > >
> > > About your questions.
> > > 1) I'm going to implements SQL view to provide list of running queries.
> > The
> > > SQL VIEW has been a little bit discussed earlier. Proposed name is
> > > *running_queries* with following columns: query_id, node_id, sql,
> > > schema_name, connection_id, duration. Currently most of the information
> > can
> > > be  retrieved through cache API, however it doesn't matter, any case we
> > > need to expose SQL VIEW. Seem's you are right - the part should be
> > > implemented firstly.
> > > 2) Fully agree that we need to support all kind of SQL queries
> > > (SLECT/DML/DDL, transactional, non transnational, local, distributed).
> I
> > > definitely sure that it will possible for all of above, however I'm not
> > > sure about DDL - need to investigate it deeper. Also need to understand
> > > that canceled DML operation can lead to partially updated data for non
> > > transational caches.
> > >
> > >
> > >
> > > пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov <[hidden email]>:
> > >
> > > > Hi Yuriy,
> > > >
> > > > I think we can use MANAGEMENT_POOL for this. It is already used for
> > some
> > > > internal Ignite tasks, and it appears to be a good candidate to
> process
> > > > cancel requests.
> > > >
> > > > But there are several things which are not clear enough for me at the
> > > > moment:
> > > > 1) How user is going to get the list of running queries in the first
> > > place?
> > > > Do we already have any SQL commands/views to get this information?
> > > > 2) We need to ensure that KILL command will be processed properly by
> > all
> > > > kinds of SQL queries - SELECT/DML/DDL, non-transactional or
> > > transactional,
> > > > local queries and distributed queries. Will we be able to support all
> > > these
> > > > modes?
> > > >
> > > > Vladimir.
> > > >
> > > > On Mon, Nov 19, 2018 at 6:37 PM Юрий <[hidden email]>
> > > wrote:
> > > >
> > > > > Hi Igniters,
> > > > >
> > > > > Earlier we agreed about syntax KILL QUERY
> > > '[node_order].[query_counter]',
> > > > > e.g. KILL QUERY '25.123' for single query  or KILL QUERY '25.*' for
> > all
> > > > > queries on the node. Which is part of IEP-29
> > > > > <
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > >
> > > > > .
> > > > >
> > > > > Now I want to discuss internal realization of KILL query feature.
> > > > >
> > > > > My current vision is following:
> > > > > After parsing, Ignite create KILL query command with two
> parameters:
> > > > > nodeOrderId, nodeQryId. To determine that need to kill all queries
> > on a
> > > > > node we can use negative value of query id, due to qry id always
> have
> > > > > positive values.
> > > > > The command process at IgniteH2Indexing as native command.
> > > > > By nodeOrderId we find node which initial for the query and send to
> > the
> > > > > node new GridQueryKillRequest with nodeQryId to TOPIC_QUERY with
> not
> > > > QUERY
> > > > > POOL executor.
> > > > > At GridReduceQueryExecutor we add support of processing new
> > > > > GridQueryKillRequest
> > > > > which just run already exists cancelQueries method with given qryId
> > or
> > > > with
> > > > > all qryIds which currently running at the node in case at initial
> > KILL
> > > > > QUERY parameters used star symbol.
> > > > >
> > > > > I have a doubt which of thread pool we should use to process
> > > > > GridQueryKillRequest.
> > > > > My opinion it shouldn't be QUERY pool, due to the pool can be fully
> > > used
> > > > by
> > > > > executing queries, it such case we can't cancel query immediately.
> > May
> > > we
> > > > > use one of already existed pool or create new one? Or may be I'm
> > > mistaken
> > > > > and it should use QUERY pool.
> > > > >
> > > > > What do you think about proposed plan of implementation?
> > > > >
> > > > > And please give comments about which of thread pool will be better
> to
> > > use
> > > > > for kill query requests. It's small, but really important part of
> the
> > > > > realization.
> > > > >
> > > > >
> > > > > Thanks.
> > > > >
> > > > >
> > > > > --
> > > > > Живи с улыбкой! :D
> > > > >
> > > >
> > >
> > >
> > > --
> > > Живи с улыбкой! :D
> > >
> >
>
>
> --
> Живи с улыбкой! :D
>

[dmagda]

Vladimir,

All of the alternatives are reminiscent of mathematical operations. Don't
look like a SQL command. What if we use a SQL approach introducing named
parameters:

KILL QUERY query_id=10 [AND node_id=5]

--
Denis

On Wed, Nov 21, 2018 at 4:11 AM Vladimir Ozerov <[hidden email]>
wrote:

> Denis,
>
> Space is bad candidate because it is a whitespace. Without whitespaces we
> can have syntax without quotes at all. Any non-whitespace delimiter will
> work, though:
>
> KILL QUERY 45.1
> KILL QUERY 45-1
> KILL QUERY 45:1
>
> On Wed, Nov 21, 2018 at 3:06 PM Юрий <[hidden email]> wrote:
>
> > Denis,
> >
> > Let's consider parameter of KILL QUERY just a string with some query id,
> > without any meaning for user. User just need to get the id and pass as
> > parameter to KILL QUERY command.
> >
> > Even if query is distributed it have single query id from user
> perspective
> > and will killed on all nodes. User just need to known one global query
> id.
> >
> > How it can works.
> > 1)SELECT * from running_queries
> > result is
> >  query_id | node_id
> >   | sql               | schema_name | connection_id | duration
> > 123.33     | e0a69cb8-a1a8-45f6-b84d-ead367a00000   | SELECT ...  | ...
> >                   |   22                 | 23456
> > 333.31     | aaa6acb8-a4a5-42f6-f842-ead111b00020     | UPDATE...  | ...
> >                   |  321                | 3467777
> > 2) KILL QUERY '123.33'
> >
> > So, user need select query_id from running_queries view and use it for
> KILL
> > QUERY command.
> >
> > I hope it became clearer.
> >
> >
> >
> > ср, 21 нояб. 2018 г. в 02:11, Denis Magda <[hidden email]>:
> >
> > > Folks,
> > >
> > > The decimal syntax is really odd - KILL QUERY
> > > '[node_order].[query_counter]'
> > >
> > > Confusing, let's use a space to separate parameters.
> > >
> > > Also, what if I want to halt a specific query with certain ID? Don't
> know
> > > the node number, just know that the query is distributed and runs
> across
> > > several machines. Sounds like the syntax still should consider
> > > [node_order/id] as an optional parameter.
> > >
> > > Probably, if you explain to me how an end user will use this command
> from
> > > the very beginning (how do I look for a query id and node id, etc) then
> > the
> > > things get clearer.
> > >
> > > --
> > > Denis
> > >
> > > On Tue, Nov 20, 2018 at 1:03 AM Юрий <[hidden email]>
> > wrote:
> > >
> > > > Hi Vladimir,
> > > >
> > > > Thanks for your suggestion to use MANAGEMENT_POOL for processing
> > > > cancellation requests.
> > > >
> > > > About your questions.
> > > > 1) I'm going to implements SQL view to provide list of running
> queries.
> > > The
> > > > SQL VIEW has been a little bit discussed earlier. Proposed name is
> > > > *running_queries* with following columns: query_id, node_id, sql,
> > > > schema_name, connection_id, duration. Currently most of the
> information
> > > can
> > > > be  retrieved through cache API, however it doesn't matter, any case
> we
> > > > need to expose SQL VIEW. Seem's you are right - the part should be
> > > > implemented firstly.
> > > > 2) Fully agree that we need to support all kind of SQL queries
> > > > (SLECT/DML/DDL, transactional, non transnational, local,
> distributed).
> > I
> > > > definitely sure that it will possible for all of above, however I'm
> not
> > > > sure about DDL - need to investigate it deeper. Also need to
> understand
> > > > that canceled DML operation can lead to partially updated data for
> non
> > > > transational caches.
> > > >
> > > >
> > > >
> > > > пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov <[hidden email]
> >:
> > > >
> > > > > Hi Yuriy,
> > > > >
> > > > > I think we can use MANAGEMENT_POOL for this. It is already used for
> > > some
> > > > > internal Ignite tasks, and it appears to be a good candidate to
> > process
> > > > > cancel requests.
> > > > >
> > > > > But there are several things which are not clear enough for me at
> the
> > > > > moment:
> > > > > 1) How user is going to get the list of running queries in the
> first
> > > > place?
> > > > > Do we already have any SQL commands/views to get this information?
> > > > > 2) We need to ensure that KILL command will be processed properly
> by
> > > all
> > > > > kinds of SQL queries - SELECT/DML/DDL, non-transactional or
> > > > transactional,
> > > > > local queries and distributed queries. Will we be able to support
> all
> > > > these
> > > > > modes?
> > > > >
> > > > > Vladimir.
> > > > >
> > > > > On Mon, Nov 19, 2018 at 6:37 PM Юрий <[hidden email]>
> > > > wrote:
> > > > >
> > > > > > Hi Igniters,
> > > > > >
> > > > > > Earlier we agreed about syntax KILL QUERY
> > > > '[node_order].[query_counter]',
> > > > > > e.g. KILL QUERY '25.123' for single query  or KILL QUERY '25.*'
> for
> > > all
> > > > > > queries on the node. Which is part of IEP-29
> > > > > > <
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > >
> > > > > > .
> > > > > >
> > > > > > Now I want to discuss internal realization of KILL query feature.
> > > > > >
> > > > > > My current vision is following:
> > > > > > After parsing, Ignite create KILL query command with two
> > parameters:
> > > > > > nodeOrderId, nodeQryId. To determine that need to kill all
> queries
> > > on a
> > > > > > node we can use negative value of query id, due to qry id always
> > have
> > > > > > positive values.
> > > > > > The command process at IgniteH2Indexing as native command.
> > > > > > By nodeOrderId we find node which initial for the query and send
> to
> > > the
> > > > > > node new GridQueryKillRequest with nodeQryId to TOPIC_QUERY with
> > not
> > > > > QUERY
> > > > > > POOL executor.
> > > > > > At GridReduceQueryExecutor we add support of processing new
> > > > > > GridQueryKillRequest
> > > > > > which just run already exists cancelQueries method with given
> qryId
> > > or
> > > > > with
> > > > > > all qryIds which currently running at the node in case at initial
> > > KILL
> > > > > > QUERY parameters used star symbol.
> > > > > >
> > > > > > I have a doubt which of thread pool we should use to process
> > > > > > GridQueryKillRequest.
> > > > > > My opinion it shouldn't be QUERY pool, due to the pool can be
> fully
> > > > used
> > > > > by
> > > > > > executing queries, it such case we can't cancel query
> immediately.
> > > May
> > > > we
> > > > > > use one of already existed pool or create new one? Or may be I'm
> > > > mistaken
> > > > > > and it should use QUERY pool.
> > > > > >
> > > > > > What do you think about proposed plan of implementation?
> > > > > >
> > > > > > And please give comments about which of thread pool will be
> better
> > to
> > > > use
> > > > > > for kill query requests. It's small, but really important part of
> > the
> > > > > > realization.
> > > > > >
> > > > > >
> > > > > > Thanks.
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Живи с улыбкой! :D
> > > > > >
> > > > >
> > > >
> > > >
> > > > --
> > > > Живи с улыбкой! :D
> > > >
> > >
> >
> >
> > --
> > Живи с улыбкой! :D
> >
>

[Vladimir Ozerov]

Denis,

Problems with separate parameters are explained above.

чт, 22 нояб. 2018 г. в 3:23, Denis Magda <[hidden email]>:

> Vladimir,
>
> All of the alternatives are reminiscent of mathematical operations. Don't
> look like a SQL command. What if we use a SQL approach introducing named
> parameters:
>
> KILL QUERY query_id=10 [AND node_id=5]
>
> --
> Denis
>
> On Wed, Nov 21, 2018 at 4:11 AM Vladimir Ozerov <[hidden email]>
> wrote:
>
> > Denis,
> >
> > Space is bad candidate because it is a whitespace. Without whitespaces we
> > can have syntax without quotes at all. Any non-whitespace delimiter will
> > work, though:
> >
> > KILL QUERY 45.1
> > KILL QUERY 45-1
> > KILL QUERY 45:1
> >
> > On Wed, Nov 21, 2018 at 3:06 PM Юрий <[hidden email]>
> wrote:
> >
> > > Denis,
> > >
> > > Let's consider parameter of KILL QUERY just a string with some query
> id,
> > > without any meaning for user. User just need to get the id and pass as
> > > parameter to KILL QUERY command.
> > >
> > > Even if query is distributed it have single query id from user
> > perspective
> > > and will killed on all nodes. User just need to known one global query
> > id.
> > >
> > > How it can works.
> > > 1)SELECT * from running_queries
> > > result is
> > >  query_id | node_id
> > >   | sql               | schema_name | connection_id | duration
> > > 123.33     | e0a69cb8-a1a8-45f6-b84d-ead367a00000   | SELECT ...  | ...
> > >                   |   22                 | 23456
> > > 333.31     | aaa6acb8-a4a5-42f6-f842-ead111b00020     | UPDATE...  |
> ...
> > >                   |  321                | 3467777
> > > 2) KILL QUERY '123.33'
> > >
> > > So, user need select query_id from running_queries view and use it for
> > KILL
> > > QUERY command.
> > >
> > > I hope it became clearer.
> > >
> > >
> > >
> > > ср, 21 нояб. 2018 г. в 02:11, Denis Magda <[hidden email]>:
> > >
> > > > Folks,
> > > >
> > > > The decimal syntax is really odd - KILL QUERY
> > > > '[node_order].[query_counter]'
> > > >
> > > > Confusing, let's use a space to separate parameters.
> > > >
> > > > Also, what if I want to halt a specific query with certain ID? Don't
> > know
> > > > the node number, just know that the query is distributed and runs
> > across
> > > > several machines. Sounds like the syntax still should consider
> > > > [node_order/id] as an optional parameter.
> > > >
> > > > Probably, if you explain to me how an end user will use this command
> > from
> > > > the very beginning (how do I look for a query id and node id, etc)
> then
> > > the
> > > > things get clearer.
> > > >
> > > > --
> > > > Denis
> > > >
> > > > On Tue, Nov 20, 2018 at 1:03 AM Юрий <[hidden email]>
> > > wrote:
> > > >
> > > > > Hi Vladimir,
> > > > >
> > > > > Thanks for your suggestion to use MANAGEMENT_POOL for processing
> > > > > cancellation requests.
> > > > >
> > > > > About your questions.
> > > > > 1) I'm going to implements SQL view to provide list of running
> > queries.
> > > > The
> > > > > SQL VIEW has been a little bit discussed earlier. Proposed name is
> > > > > *running_queries* with following columns: query_id, node_id, sql,
> > > > > schema_name, connection_id, duration. Currently most of the
> > information
> > > > can
> > > > > be  retrieved through cache API, however it doesn't matter, any
> case
> > we
> > > > > need to expose SQL VIEW. Seem's you are right - the part should be
> > > > > implemented firstly.
> > > > > 2) Fully agree that we need to support all kind of SQL queries
> > > > > (SLECT/DML/DDL, transactional, non transnational, local,
> > distributed).
> > > I
> > > > > definitely sure that it will possible for all of above, however I'm
> > not
> > > > > sure about DDL - need to investigate it deeper. Also need to
> > understand
> > > > > that canceled DML operation can lead to partially updated data for
> > non
> > > > > transational caches.
> > > > >
> > > > >
> > > > >
> > > > > пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov <
> [hidden email]
> > >:
> > > > >
> > > > > > Hi Yuriy,
> > > > > >
> > > > > > I think we can use MANAGEMENT_POOL for this. It is already used
> for
> > > > some
> > > > > > internal Ignite tasks, and it appears to be a good candidate to
> > > process
> > > > > > cancel requests.
> > > > > >
> > > > > > But there are several things which are not clear enough for me at
> > the
> > > > > > moment:
> > > > > > 1) How user is going to get the list of running queries in the
> > first
> > > > > place?
> > > > > > Do we already have any SQL commands/views to get this
> information?
> > > > > > 2) We need to ensure that KILL command will be processed properly
> > by
> > > > all
> > > > > > kinds of SQL queries - SELECT/DML/DDL, non-transactional or
> > > > > transactional,
> > > > > > local queries and distributed queries. Will we be able to support
> > all
> > > > > these
> > > > > > modes?
> > > > > >
> > > > > > Vladimir.
> > > > > >
> > > > > > On Mon, Nov 19, 2018 at 6:37 PM Юрий <
> [hidden email]>
> > > > > wrote:
> > > > > >
> > > > > > > Hi Igniters,
> > > > > > >
> > > > > > > Earlier we agreed about syntax KILL QUERY
> > > > > '[node_order].[query_counter]',
> > > > > > > e.g. KILL QUERY '25.123' for single query  or KILL QUERY '25.*'
> > for
> > > > all
> > > > > > > queries on the node. Which is part of IEP-29
> > > > > > > <
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > >
> > > > > > > .
> > > > > > >
> > > > > > > Now I want to discuss internal realization of KILL query
> feature.
> > > > > > >
> > > > > > > My current vision is following:
> > > > > > > After parsing, Ignite create KILL query command with two
> > > parameters:
> > > > > > > nodeOrderId, nodeQryId. To determine that need to kill all
> > queries
> > > > on a
> > > > > > > node we can use negative value of query id, due to qry id
> always
> > > have
> > > > > > > positive values.
> > > > > > > The command process at IgniteH2Indexing as native command.
> > > > > > > By nodeOrderId we find node which initial for the query and
> send
> > to
> > > > the
> > > > > > > node new GridQueryKillRequest with nodeQryId to TOPIC_QUERY
> with
> > > not
> > > > > > QUERY
> > > > > > > POOL executor.
> > > > > > > At GridReduceQueryExecutor we add support of processing new
> > > > > > > GridQueryKillRequest
> > > > > > > which just run already exists cancelQueries method with given
> > qryId
> > > > or
> > > > > > with
> > > > > > > all qryIds which currently running at the node in case at
> initial
> > > > KILL
> > > > > > > QUERY parameters used star symbol.
> > > > > > >
> > > > > > > I have a doubt which of thread pool we should use to process
> > > > > > > GridQueryKillRequest.
> > > > > > > My opinion it shouldn't be QUERY pool, due to the pool can be
> > fully
> > > > > used
> > > > > > by
> > > > > > > executing queries, it such case we can't cancel query
> > immediately.
> > > > May
> > > > > we
> > > > > > > use one of already existed pool or create new one? Or may be
> I'm
> > > > > mistaken
> > > > > > > and it should use QUERY pool.
> > > > > > >
> > > > > > > What do you think about proposed plan of implementation?
> > > > > > >
> > > > > > > And please give comments about which of thread pool will be
> > better
> > > to
> > > > > use
> > > > > > > for kill query requests. It's small, but really important part
> of
> > > the
> > > > > > > realization.
> > > > > > >
> > > > > > >
> > > > > > > Thanks.
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Живи с улыбкой! :D
> > > > > > >
> > > > > >
> > > > >
> > > > >
> > > > > --
> > > > > Живи с улыбкой! :D
> > > > >
> > > >
> > >
> > >
> > > --
> > > Живи с улыбкой! :D
> > >
> >
>

[Denis Mekhanikov]

Actually, option with separate parameters was mentioned in another thread
http://apache-ignite-developers.2346864.n4.nabble.com/proposed-design-for-thin-client-SQL-management-and-monitoring-view-running-queries-and-kill-it-tp37713p38056.html

Denis

чт, 22 нояб. 2018 г. в 08:51, Vladimir Ozerov <[hidden email]>:

> Denis,
>
> Problems with separate parameters are explained above.
>
> чт, 22 нояб. 2018 г. в 3:23, Denis Magda <[hidden email]>:
>
> > Vladimir,
> >
> > All of the alternatives are reminiscent of mathematical operations. Don't
> > look like a SQL command. What if we use a SQL approach introducing named
> > parameters:
> >
> > KILL QUERY query_id=10 [AND node_id=5]
> >
> > --
> > Denis
> >
> > On Wed, Nov 21, 2018 at 4:11 AM Vladimir Ozerov <[hidden email]>
> > wrote:
> >
> > > Denis,
> > >
> > > Space is bad candidate because it is a whitespace. Without whitespaces
> we
> > > can have syntax without quotes at all. Any non-whitespace delimiter
> will
> > > work, though:
> > >
> > > KILL QUERY 45.1
> > > KILL QUERY 45-1
> > > KILL QUERY 45:1
> > >
> > > On Wed, Nov 21, 2018 at 3:06 PM Юрий <[hidden email]>
> > wrote:
> > >
> > > > Denis,
> > > >
> > > > Let's consider parameter of KILL QUERY just a string with some query
> > id,
> > > > without any meaning for user. User just need to get the id and pass
> as
> > > > parameter to KILL QUERY command.
> > > >
> > > > Even if query is distributed it have single query id from user
> > > perspective
> > > > and will killed on all nodes. User just need to known one global
> query
> > > id.
> > > >
> > > > How it can works.
> > > > 1)SELECT * from running_queries
> > > > result is
> > > >  query_id | node_id
> > > >   | sql               | schema_name | connection_id | duration
> > > > 123.33     | e0a69cb8-a1a8-45f6-b84d-ead367a00000   | SELECT ...  |
> ...
> > > >                   |   22                 | 23456
> > > > 333.31     | aaa6acb8-a4a5-42f6-f842-ead111b00020     | UPDATE...  |
> > ...
> > > >                   |  321                | 3467777
> > > > 2) KILL QUERY '123.33'
> > > >
> > > > So, user need select query_id from running_queries view and use it
> for
> > > KILL
> > > > QUERY command.
> > > >
> > > > I hope it became clearer.
> > > >
> > > >
> > > >
> > > > ср, 21 нояб. 2018 г. в 02:11, Denis Magda <[hidden email]>:
> > > >
> > > > > Folks,
> > > > >
> > > > > The decimal syntax is really odd - KILL QUERY
> > > > > '[node_order].[query_counter]'
> > > > >
> > > > > Confusing, let's use a space to separate parameters.
> > > > >
> > > > > Also, what if I want to halt a specific query with certain ID?
> Don't
> > > know
> > > > > the node number, just know that the query is distributed and runs
> > > across
> > > > > several machines. Sounds like the syntax still should consider
> > > > > [node_order/id] as an optional parameter.
> > > > >
> > > > > Probably, if you explain to me how an end user will use this
> command
> > > from
> > > > > the very beginning (how do I look for a query id and node id, etc)
> > then
> > > > the
> > > > > things get clearer.
> > > > >
> > > > > --
> > > > > Denis
> > > > >
> > > > > On Tue, Nov 20, 2018 at 1:03 AM Юрий <[hidden email]>
> > > > wrote:
> > > > >
> > > > > > Hi Vladimir,
> > > > > >
> > > > > > Thanks for your suggestion to use MANAGEMENT_POOL for processing
> > > > > > cancellation requests.
> > > > > >
> > > > > > About your questions.
> > > > > > 1) I'm going to implements SQL view to provide list of running
> > > queries.
> > > > > The
> > > > > > SQL VIEW has been a little bit discussed earlier. Proposed name
> is
> > > > > > *running_queries* with following columns: query_id, node_id, sql,
> > > > > > schema_name, connection_id, duration. Currently most of the
> > > information
> > > > > can
> > > > > > be  retrieved through cache API, however it doesn't matter, any
> > case
> > > we
> > > > > > need to expose SQL VIEW. Seem's you are right - the part should
> be
> > > > > > implemented firstly.
> > > > > > 2) Fully agree that we need to support all kind of SQL queries
> > > > > > (SLECT/DML/DDL, transactional, non transnational, local,
> > > distributed).
> > > > I
> > > > > > definitely sure that it will possible for all of above, however
> I'm
> > > not
> > > > > > sure about DDL - need to investigate it deeper. Also need to
> > > understand
> > > > > > that canceled DML operation can lead to partially updated data
> for
> > > non
> > > > > > transational caches.
> > > > > >
> > > > > >
> > > > > >
> > > > > > пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov <
> > [hidden email]
> > > >:
> > > > > >
> > > > > > > Hi Yuriy,
> > > > > > >
> > > > > > > I think we can use MANAGEMENT_POOL for this. It is already used
> > for
> > > > > some
> > > > > > > internal Ignite tasks, and it appears to be a good candidate to
> > > > process
> > > > > > > cancel requests.
> > > > > > >
> > > > > > > But there are several things which are not clear enough for me
> at
> > > the
> > > > > > > moment:
> > > > > > > 1) How user is going to get the list of running queries in the
> > > first
> > > > > > place?
> > > > > > > Do we already have any SQL commands/views to get this
> > information?
> > > > > > > 2) We need to ensure that KILL command will be processed
> properly
> > > by
> > > > > all
> > > > > > > kinds of SQL queries - SELECT/DML/DDL, non-transactional or
> > > > > > transactional,
> > > > > > > local queries and distributed queries. Will we be able to
> support
> > > all
> > > > > > these
> > > > > > > modes?
> > > > > > >
> > > > > > > Vladimir.
> > > > > > >
> > > > > > > On Mon, Nov 19, 2018 at 6:37 PM Юрий <
> > [hidden email]>
> > > > > > wrote:
> > > > > > >
> > > > > > > > Hi Igniters,
> > > > > > > >
> > > > > > > > Earlier we agreed about syntax KILL QUERY
> > > > > > '[node_order].[query_counter]',
> > > > > > > > e.g. KILL QUERY '25.123' for single query  or KILL QUERY
> '25.*'
> > > for
> > > > > all
> > > > > > > > queries on the node. Which is part of IEP-29
> > > > > > > > <
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > >
> > > > > > > > .
> > > > > > > >
> > > > > > > > Now I want to discuss internal realization of KILL query
> > feature.
> > > > > > > >
> > > > > > > > My current vision is following:
> > > > > > > > After parsing, Ignite create KILL query command with two
> > > > parameters:
> > > > > > > > nodeOrderId, nodeQryId. To determine that need to kill all
> > > queries
> > > > > on a
> > > > > > > > node we can use negative value of query id, due to qry id
> > always
> > > > have
> > > > > > > > positive values.
> > > > > > > > The command process at IgniteH2Indexing as native command.
> > > > > > > > By nodeOrderId we find node which initial for the query and
> > send
> > > to
> > > > > the
> > > > > > > > node new GridQueryKillRequest with nodeQryId to TOPIC_QUERY
> > with
> > > > not
> > > > > > > QUERY
> > > > > > > > POOL executor.
> > > > > > > > At GridReduceQueryExecutor we add support of processing new
> > > > > > > > GridQueryKillRequest
> > > > > > > > which just run already exists cancelQueries method with given
> > > qryId
> > > > > or
> > > > > > > with
> > > > > > > > all qryIds which currently running at the node in case at
> > initial
> > > > > KILL
> > > > > > > > QUERY parameters used star symbol.
> > > > > > > >
> > > > > > > > I have a doubt which of thread pool we should use to process
> > > > > > > > GridQueryKillRequest.
> > > > > > > > My opinion it shouldn't be QUERY pool, due to the pool can be
> > > fully
> > > > > > used
> > > > > > > by
> > > > > > > > executing queries, it such case we can't cancel query
> > > immediately.
> > > > > May
> > > > > > we
> > > > > > > > use one of already existed pool or create new one? Or may be
> > I'm
> > > > > > mistaken
> > > > > > > > and it should use QUERY pool.
> > > > > > > >
> > > > > > > > What do you think about proposed plan of implementation?
> > > > > > > >
> > > > > > > > And please give comments about which of thread pool will be
> > > better
> > > > to
> > > > > > use
> > > > > > > > for kill query requests. It's small, but really important
> part
> > of
> > > > the
> > > > > > > > realization.
> > > > > > > >
> > > > > > > >
> > > > > > > > Thanks.
> > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > > Живи с улыбкой! :D
> > > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Живи с улыбкой! :D
> > > > > >
> > > > >
> > > >
> > > >
> > > > --
> > > > Живи с улыбкой! :D
> > > >
> > >
> >
>

[Ivan Pavlukhin]

Hi,

May be I am a little bit late with my thoughts about a command syntax.
How do I see it is going to be used:
1. A user is able to kill a query by unique id belonging only to this query.
2. A user is able to kill all queries started by a specific node.
For killing a single query we must identify it by unique id which is
going to be received directly from Ignite (e.g. running queries view)
and not calculated by user. Internally the id is compound but why
cannot we convert it to opaque integer or string which hides real
structure? E.g. base16String(concat(nodeOrder.toString(), ".",
queryIdOnNode.toString())) The syntax could be KILL QUERY '123' or
KILL QUERY WHERE queryId = '123'
For killing all queries started by some node we need to use only node
order (or id). It could be like KILL QUERY WHERE nodeOrder = 34.
чт, 22 нояб. 2018 г. в 12:56, Denis Mekhanikov <[hidden email]>:

>
> Actually, option with separate parameters was mentioned in another thread
> http://apache-ignite-developers.2346864.n4.nabble.com/proposed-design-for-thin-client-SQL-management-and-monitoring-view-running-queries-and-kill-it-tp37713p38056.html
>
> Denis
>
> чт, 22 нояб. 2018 г. в 08:51, Vladimir Ozerov <[hidden email]>:
>
> > Denis,
> >
> > Problems with separate parameters are explained above.
> >
> > чт, 22 нояб. 2018 г. в 3:23, Denis Magda <[hidden email]>:
> >
> > > Vladimir,
> > >
> > > All of the alternatives are reminiscent of mathematical operations. Don't
> > > look like a SQL command. What if we use a SQL approach introducing named
> > > parameters:
> > >
> > > KILL QUERY query_id=10 [AND node_id=5]
> > >
> > > --
> > > Denis
> > >
> > > On Wed, Nov 21, 2018 at 4:11 AM Vladimir Ozerov <[hidden email]>
> > > wrote:
> > >
> > > > Denis,
> > > >
> > > > Space is bad candidate because it is a whitespace. Without whitespaces
> > we
> > > > can have syntax without quotes at all. Any non-whitespace delimiter
> > will
> > > > work, though:
> > > >
> > > > KILL QUERY 45.1
> > > > KILL QUERY 45-1
> > > > KILL QUERY 45:1
> > > >
> > > > On Wed, Nov 21, 2018 at 3:06 PM Юрий <[hidden email]>
> > > wrote:
> > > >
> > > > > Denis,
> > > > >
> > > > > Let's consider parameter of KILL QUERY just a string with some query
> > > id,
> > > > > without any meaning for user. User just need to get the id and pass
> > as
> > > > > parameter to KILL QUERY command.
> > > > >
> > > > > Even if query is distributed it have single query id from user
> > > > perspective
> > > > > and will killed on all nodes. User just need to known one global
> > query
> > > > id.
> > > > >
> > > > > How it can works.
> > > > > 1)SELECT * from running_queries
> > > > > result is
> > > > >  query_id | node_id
> > > > >   | sql               | schema_name | connection_id | duration
> > > > > 123.33     | e0a69cb8-a1a8-45f6-b84d-ead367a00000   | SELECT ...  |
> > ...
> > > > >                   |   22                 | 23456
> > > > > 333.31     | aaa6acb8-a4a5-42f6-f842-ead111b00020     | UPDATE...  |
> > > ...
> > > > >                   |  321                | 3467777
> > > > > 2) KILL QUERY '123.33'
> > > > >
> > > > > So, user need select query_id from running_queries view and use it
> > for
> > > > KILL
> > > > > QUERY command.
> > > > >
> > > > > I hope it became clearer.
> > > > >
> > > > >
> > > > >
> > > > > ср, 21 нояб. 2018 г. в 02:11, Denis Magda <[hidden email]>:
> > > > >
> > > > > > Folks,
> > > > > >
> > > > > > The decimal syntax is really odd - KILL QUERY
> > > > > > '[node_order].[query_counter]'
> > > > > >
> > > > > > Confusing, let's use a space to separate parameters.
> > > > > >
> > > > > > Also, what if I want to halt a specific query with certain ID?
> > Don't
> > > > know
> > > > > > the node number, just know that the query is distributed and runs
> > > > across
> > > > > > several machines. Sounds like the syntax still should consider
> > > > > > [node_order/id] as an optional parameter.
> > > > > >
> > > > > > Probably, if you explain to me how an end user will use this
> > command
> > > > from
> > > > > > the very beginning (how do I look for a query id and node id, etc)
> > > then
> > > > > the
> > > > > > things get clearer.
> > > > > >
> > > > > > --
> > > > > > Denis
> > > > > >
> > > > > > On Tue, Nov 20, 2018 at 1:03 AM Юрий <[hidden email]>
> > > > > wrote:
> > > > > >
> > > > > > > Hi Vladimir,
> > > > > > >
> > > > > > > Thanks for your suggestion to use MANAGEMENT_POOL for processing
> > > > > > > cancellation requests.
> > > > > > >
> > > > > > > About your questions.
> > > > > > > 1) I'm going to implements SQL view to provide list of running
> > > > queries.
> > > > > > The
> > > > > > > SQL VIEW has been a little bit discussed earlier. Proposed name
> > is
> > > > > > > *running_queries* with following columns: query_id, node_id, sql,
> > > > > > > schema_name, connection_id, duration. Currently most of the
> > > > information
> > > > > > can
> > > > > > > be  retrieved through cache API, however it doesn't matter, any
> > > case
> > > > we
> > > > > > > need to expose SQL VIEW. Seem's you are right - the part should
> > be
> > > > > > > implemented firstly.
> > > > > > > 2) Fully agree that we need to support all kind of SQL queries
> > > > > > > (SLECT/DML/DDL, transactional, non transnational, local,
> > > > distributed).
> > > > > I
> > > > > > > definitely sure that it will possible for all of above, however
> > I'm
> > > > not
> > > > > > > sure about DDL - need to investigate it deeper. Also need to
> > > > understand
> > > > > > > that canceled DML operation can lead to partially updated data
> > for
> > > > non
> > > > > > > transational caches.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov <
> > > [hidden email]
> > > > >:
> > > > > > >
> > > > > > > > Hi Yuriy,
> > > > > > > >
> > > > > > > > I think we can use MANAGEMENT_POOL for this. It is already used
> > > for
> > > > > > some
> > > > > > > > internal Ignite tasks, and it appears to be a good candidate to
> > > > > process
> > > > > > > > cancel requests.
> > > > > > > >
> > > > > > > > But there are several things which are not clear enough for me
> > at
> > > > the
> > > > > > > > moment:
> > > > > > > > 1) How user is going to get the list of running queries in the
> > > > first
> > > > > > > place?
> > > > > > > > Do we already have any SQL commands/views to get this
> > > information?
> > > > > > > > 2) We need to ensure that KILL command will be processed
> > properly
> > > > by
> > > > > > all
> > > > > > > > kinds of SQL queries - SELECT/DML/DDL, non-transactional or
> > > > > > > transactional,
> > > > > > > > local queries and distributed queries. Will we be able to
> > support
> > > > all
> > > > > > > these
> > > > > > > > modes?
> > > > > > > >
> > > > > > > > Vladimir.
> > > > > > > >
> > > > > > > > On Mon, Nov 19, 2018 at 6:37 PM Юрий <
> > > [hidden email]>
> > > > > > > wrote:
> > > > > > > >
> > > > > > > > > Hi Igniters,
> > > > > > > > >
> > > > > > > > > Earlier we agreed about syntax KILL QUERY
> > > > > > > '[node_order].[query_counter]',
> > > > > > > > > e.g. KILL QUERY '25.123' for single query  or KILL QUERY
> > '25.*'
> > > > for
> > > > > > all
> > > > > > > > > queries on the node. Which is part of IEP-29
> > > > > > > > > <
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> > https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > >
> > > > > > > > > .
> > > > > > > > >
> > > > > > > > > Now I want to discuss internal realization of KILL query
> > > feature.
> > > > > > > > >
> > > > > > > > > My current vision is following:
> > > > > > > > > After parsing, Ignite create KILL query command with two
> > > > > parameters:
> > > > > > > > > nodeOrderId, nodeQryId. To determine that need to kill all
> > > > queries
> > > > > > on a
> > > > > > > > > node we can use negative value of query id, due to qry id
> > > always
> > > > > have
> > > > > > > > > positive values.
> > > > > > > > > The command process at IgniteH2Indexing as native command.
> > > > > > > > > By nodeOrderId we find node which initial for the query and
> > > send
> > > > to
> > > > > > the
> > > > > > > > > node new GridQueryKillRequest with nodeQryId to TOPIC_QUERY
> > > with
> > > > > not
> > > > > > > > QUERY
> > > > > > > > > POOL executor.
> > > > > > > > > At GridReduceQueryExecutor we add support of processing new
> > > > > > > > > GridQueryKillRequest
> > > > > > > > > which just run already exists cancelQueries method with given
> > > > qryId
> > > > > > or
> > > > > > > > with
> > > > > > > > > all qryIds which currently running at the node in case at
> > > initial
> > > > > > KILL
> > > > > > > > > QUERY parameters used star symbol.
> > > > > > > > >
> > > > > > > > > I have a doubt which of thread pool we should use to process
> > > > > > > > > GridQueryKillRequest.
> > > > > > > > > My opinion it shouldn't be QUERY pool, due to the pool can be
> > > > fully
> > > > > > > used
> > > > > > > > by
> > > > > > > > > executing queries, it such case we can't cancel query
> > > > immediately.
> > > > > > May
> > > > > > > we
> > > > > > > > > use one of already existed pool or create new one? Or may be
> > > I'm
> > > > > > > mistaken
> > > > > > > > > and it should use QUERY pool.
> > > > > > > > >
> > > > > > > > > What do you think about proposed plan of implementation?
> > > > > > > > >
> > > > > > > > > And please give comments about which of thread pool will be
> > > > better
> > > > > to
> > > > > > > use
> > > > > > > > > for kill query requests. It's small, but really important
> > part
> > > of
> > > > > the
> > > > > > > > > realization.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Thanks.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Живи с улыбкой! :D
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Живи с улыбкой! :D
> > > > > > >
> > > > > >
> > > > >
> > > > >
> > > > > --
> > > > > Живи с улыбкой! :D
> > > > >
> > > >
> > >
> >

--
Best regards,
Ivan Pavlukhin

[Vladimir Ozerov]

I propose to start with running queries мшуц first. Once we have it, it
will be easier to agree on final command syntax.

On Fri, Nov 23, 2018 at 9:32 AM Павлухин Иван <[hidden email]> wrote:

> Hi,
>
> May be I am a little bit late with my thoughts about a command syntax.
> How do I see it is going to be used:
> 1. A user is able to kill a query by unique id belonging only to this
> query.
> 2. A user is able to kill all queries started by a specific node.
> For killing a single query we must identify it by unique id which is
> going to be received directly from Ignite (e.g. running queries view)
> and not calculated by user. Internally the id is compound but why
> cannot we convert it to opaque integer or string which hides real
> structure? E.g. base16String(concat(nodeOrder.toString(), ".",
> queryIdOnNode.toString())) The syntax could be KILL QUERY '123' or
> KILL QUERY WHERE queryId = '123'
> For killing all queries started by some node we need to use only node
> order (or id). It could be like KILL QUERY WHERE nodeOrder = 34.
> чт, 22 нояб. 2018 г. в 12:56, Denis Mekhanikov <[hidden email]>:
> >
> > Actually, option with separate parameters was mentioned in another thread
> >
> http://apache-ignite-developers.2346864.n4.nabble.com/proposed-design-for-thin-client-SQL-management-and-monitoring-view-running-queries-and-kill-it-tp37713p38056.html
> >
> > Denis
> >
> > чт, 22 нояб. 2018 г. в 08:51, Vladimir Ozerov <[hidden email]>:
> >
> > > Denis,
> > >
> > > Problems with separate parameters are explained above.
> > >
> > > чт, 22 нояб. 2018 г. в 3:23, Denis Magda <[hidden email]>:
> > >
> > > > Vladimir,
> > > >
> > > > All of the alternatives are reminiscent of mathematical operations.
> Don't
> > > > look like a SQL command. What if we use a SQL approach introducing
> named
> > > > parameters:
> > > >
> > > > KILL QUERY query_id=10 [AND node_id=5]
> > > >
> > > > --
> > > > Denis
> > > >
> > > > On Wed, Nov 21, 2018 at 4:11 AM Vladimir Ozerov <
> [hidden email]>
> > > > wrote:
> > > >
> > > > > Denis,
> > > > >
> > > > > Space is bad candidate because it is a whitespace. Without
> whitespaces
> > > we
> > > > > can have syntax without quotes at all. Any non-whitespace delimiter
> > > will
> > > > > work, though:
> > > > >
> > > > > KILL QUERY 45.1
> > > > > KILL QUERY 45-1
> > > > > KILL QUERY 45:1
> > > > >
> > > > > On Wed, Nov 21, 2018 at 3:06 PM Юрий <[hidden email]>
> > > > wrote:
> > > > >
> > > > > > Denis,
> > > > > >
> > > > > > Let's consider parameter of KILL QUERY just a string with some
> query
> > > > id,
> > > > > > without any meaning for user. User just need to get the id and
> pass
> > > as
> > > > > > parameter to KILL QUERY command.
> > > > > >
> > > > > > Even if query is distributed it have single query id from user
> > > > > perspective
> > > > > > and will killed on all nodes. User just need to known one global
> > > query
> > > > > id.
> > > > > >
> > > > > > How it can works.
> > > > > > 1)SELECT * from running_queries
> > > > > > result is
> > > > > >  query_id | node_id
> > > > > >   | sql               | schema_name | connection_id | duration
> > > > > > 123.33     | e0a69cb8-a1a8-45f6-b84d-ead367a00000   | SELECT
> ...  |
> > > ...
> > > > > >                   |   22                 | 23456
> > > > > > 333.31     | aaa6acb8-a4a5-42f6-f842-ead111b00020     |
> UPDATE...  |
> > > > ...
> > > > > >                   |  321                | 3467777
> > > > > > 2) KILL QUERY '123.33'
> > > > > >
> > > > > > So, user need select query_id from running_queries view and use
> it
> > > for
> > > > > KILL
> > > > > > QUERY command.
> > > > > >
> > > > > > I hope it became clearer.
> > > > > >
> > > > > >
> > > > > >
> > > > > > ср, 21 нояб. 2018 г. в 02:11, Denis Magda <[hidden email]>:
> > > > > >
> > > > > > > Folks,
> > > > > > >
> > > > > > > The decimal syntax is really odd - KILL QUERY
> > > > > > > '[node_order].[query_counter]'
> > > > > > >
> > > > > > > Confusing, let's use a space to separate parameters.
> > > > > > >
> > > > > > > Also, what if I want to halt a specific query with certain ID?
> > > Don't
> > > > > know
> > > > > > > the node number, just know that the query is distributed and
> runs
> > > > > across
> > > > > > > several machines. Sounds like the syntax still should consider
> > > > > > > [node_order/id] as an optional parameter.
> > > > > > >
> > > > > > > Probably, if you explain to me how an end user will use this
> > > command
> > > > > from
> > > > > > > the very beginning (how do I look for a query id and node id,
> etc)
> > > > then
> > > > > > the
> > > > > > > things get clearer.
> > > > > > >
> > > > > > > --
> > > > > > > Denis
> > > > > > >
> > > > > > > On Tue, Nov 20, 2018 at 1:03 AM Юрий <
> [hidden email]>
> > > > > > wrote:
> > > > > > >
> > > > > > > > Hi Vladimir,
> > > > > > > >
> > > > > > > > Thanks for your suggestion to use MANAGEMENT_POOL for
> processing
> > > > > > > > cancellation requests.
> > > > > > > >
> > > > > > > > About your questions.
> > > > > > > > 1) I'm going to implements SQL view to provide list of
> running
> > > > > queries.
> > > > > > > The
> > > > > > > > SQL VIEW has been a little bit discussed earlier. Proposed
> name
> > > is
> > > > > > > > *running_queries* with following columns: query_id, node_id,
> sql,
> > > > > > > > schema_name, connection_id, duration. Currently most of the
> > > > > information
> > > > > > > can
> > > > > > > > be  retrieved through cache API, however it doesn't matter,
> any
> > > > case
> > > > > we
> > > > > > > > need to expose SQL VIEW. Seem's you are right - the part
> should
> > > be
> > > > > > > > implemented firstly.
> > > > > > > > 2) Fully agree that we need to support all kind of SQL
> queries
> > > > > > > > (SLECT/DML/DDL, transactional, non transnational, local,
> > > > > distributed).
> > > > > > I
> > > > > > > > definitely sure that it will possible for all of above,
> however
> > > I'm
> > > > > not
> > > > > > > > sure about DDL - need to investigate it deeper. Also need to
> > > > > understand
> > > > > > > > that canceled DML operation can lead to partially updated
> data
> > > for
> > > > > non
> > > > > > > > transational caches.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov <
> > > > [hidden email]
> > > > > >:
> > > > > > > >
> > > > > > > > > Hi Yuriy,
> > > > > > > > >
> > > > > > > > > I think we can use MANAGEMENT_POOL for this. It is already
> used
> > > > for
> > > > > > > some
> > > > > > > > > internal Ignite tasks, and it appears to be a good
> candidate to
> > > > > > process
> > > > > > > > > cancel requests.
> > > > > > > > >
> > > > > > > > > But there are several things which are not clear enough
> for me
> > > at
> > > > > the
> > > > > > > > > moment:
> > > > > > > > > 1) How user is going to get the list of running queries in
> the
> > > > > first
> > > > > > > > place?
> > > > > > > > > Do we already have any SQL commands/views to get this
> > > > information?
> > > > > > > > > 2) We need to ensure that KILL command will be processed
> > > properly
> > > > > by
> > > > > > > all
> > > > > > > > > kinds of SQL queries - SELECT/DML/DDL, non-transactional or
> > > > > > > > transactional,
> > > > > > > > > local queries and distributed queries. Will we be able to
> > > support
> > > > > all
> > > > > > > > these
> > > > > > > > > modes?
> > > > > > > > >
> > > > > > > > > Vladimir.
> > > > > > > > >
> > > > > > > > > On Mon, Nov 19, 2018 at 6:37 PM Юрий <
> > > > [hidden email]>
> > > > > > > > wrote:
> > > > > > > > >
> > > > > > > > > > Hi Igniters,
> > > > > > > > > >
> > > > > > > > > > Earlier we agreed about syntax KILL QUERY
> > > > > > > > '[node_order].[query_counter]',
> > > > > > > > > > e.g. KILL QUERY '25.123' for single query  or KILL QUERY
> > > '25.*'
> > > > > for
> > > > > > > all
> > > > > > > > > > queries on the node. Which is part of IEP-29
> > > > > > > > > > <
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > > >
> > > > > > > > > > .
> > > > > > > > > >
> > > > > > > > > > Now I want to discuss internal realization of KILL query
> > > > feature.
> > > > > > > > > >
> > > > > > > > > > My current vision is following:
> > > > > > > > > > After parsing, Ignite create KILL query command with two
> > > > > > parameters:
> > > > > > > > > > nodeOrderId, nodeQryId. To determine that need to kill
> all
> > > > > queries
> > > > > > > on a
> > > > > > > > > > node we can use negative value of query id, due to qry id
> > > > always
> > > > > > have
> > > > > > > > > > positive values.
> > > > > > > > > > The command process at IgniteH2Indexing as native
> command.
> > > > > > > > > > By nodeOrderId we find node which initial for the query
> and
> > > > send
> > > > > to
> > > > > > > the
> > > > > > > > > > node new GridQueryKillRequest with nodeQryId to
> TOPIC_QUERY
> > > > with
> > > > > > not
> > > > > > > > > QUERY
> > > > > > > > > > POOL executor.
> > > > > > > > > > At GridReduceQueryExecutor we add support of processing
> new
> > > > > > > > > > GridQueryKillRequest
> > > > > > > > > > which just run already exists cancelQueries method with
> given
> > > > > qryId
> > > > > > > or
> > > > > > > > > with
> > > > > > > > > > all qryIds which currently running at the node in case at
> > > > initial
> > > > > > > KILL
> > > > > > > > > > QUERY parameters used star symbol.
> > > > > > > > > >
> > > > > > > > > > I have a doubt which of thread pool we should use to
> process
> > > > > > > > > > GridQueryKillRequest.
> > > > > > > > > > My opinion it shouldn't be QUERY pool, due to the pool
> can be
> > > > > fully
> > > > > > > > used
> > > > > > > > > by
> > > > > > > > > > executing queries, it such case we can't cancel query
> > > > > immediately.
> > > > > > > May
> > > > > > > > we
> > > > > > > > > > use one of already existed pool or create new one? Or
> may be
> > > > I'm
> > > > > > > > mistaken
> > > > > > > > > > and it should use QUERY pool.
> > > > > > > > > >
> > > > > > > > > > What do you think about proposed plan of implementation?
> > > > > > > > > >
> > > > > > > > > > And please give comments about which of thread pool will
> be
> > > > > better
> > > > > > to
> > > > > > > > use
> > > > > > > > > > for kill query requests. It's small, but really important
> > > part
> > > > of
> > > > > > the
> > > > > > > > > > realization.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > Thanks.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > > Живи с улыбкой! :D
> > > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Живи с улыбкой! :D
> > > > > >
> > > > >
> > > >
> > >
>
>
>
> --
> Best regards,
> Ivan Pavlukhin
>

[Ivan Pavlukhin]

I believe that the meaning was:

> I propose to start with running queries VIEW first.
вт, 27 нояб. 2018 г. в 10:47, Vladimir Ozerov <[hidden email]>:

>
> I propose to start with running queries мшуц first. Once we have it, it
> will be easier to agree on final command syntax.
>
> On Fri, Nov 23, 2018 at 9:32 AM Павлухин Иван <[hidden email]> wrote:
>
> > Hi,
> >
> > May be I am a little bit late with my thoughts about a command syntax.
> > How do I see it is going to be used:
> > 1. A user is able to kill a query by unique id belonging only to this
> > query.
> > 2. A user is able to kill all queries started by a specific node.
> > For killing a single query we must identify it by unique id which is
> > going to be received directly from Ignite (e.g. running queries view)
> > and not calculated by user. Internally the id is compound but why
> > cannot we convert it to opaque integer or string which hides real
> > structure? E.g. base16String(concat(nodeOrder.toString(), ".",
> > queryIdOnNode.toString())) The syntax could be KILL QUERY '123' or
> > KILL QUERY WHERE queryId = '123'
> > For killing all queries started by some node we need to use only node
> > order (or id). It could be like KILL QUERY WHERE nodeOrder = 34.
> > чт, 22 нояб. 2018 г. в 12:56, Denis Mekhanikov <[hidden email]>:
> > >
> > > Actually, option with separate parameters was mentioned in another thread
> > >
> > http://apache-ignite-developers.2346864.n4.nabble.com/proposed-design-for-thin-client-SQL-management-and-monitoring-view-running-queries-and-kill-it-tp37713p38056.html
> > >
> > > Denis
> > >
> > > чт, 22 нояб. 2018 г. в 08:51, Vladimir Ozerov <[hidden email]>:
> > >
> > > > Denis,
> > > >
> > > > Problems with separate parameters are explained above.
> > > >
> > > > чт, 22 нояб. 2018 г. в 3:23, Denis Magda <[hidden email]>:
> > > >
> > > > > Vladimir,
> > > > >
> > > > > All of the alternatives are reminiscent of mathematical operations.
> > Don't
> > > > > look like a SQL command. What if we use a SQL approach introducing
> > named
> > > > > parameters:
> > > > >
> > > > > KILL QUERY query_id=10 [AND node_id=5]
> > > > >
> > > > > --
> > > > > Denis
> > > > >
> > > > > On Wed, Nov 21, 2018 at 4:11 AM Vladimir Ozerov <
> > [hidden email]>
> > > > > wrote:
> > > > >
> > > > > > Denis,
> > > > > >
> > > > > > Space is bad candidate because it is a whitespace. Without
> > whitespaces
> > > > we
> > > > > > can have syntax without quotes at all. Any non-whitespace delimiter
> > > > will
> > > > > > work, though:
> > > > > >
> > > > > > KILL QUERY 45.1
> > > > > > KILL QUERY 45-1
> > > > > > KILL QUERY 45:1
> > > > > >
> > > > > > On Wed, Nov 21, 2018 at 3:06 PM Юрий <[hidden email]>
> > > > > wrote:
> > > > > >
> > > > > > > Denis,
> > > > > > >
> > > > > > > Let's consider parameter of KILL QUERY just a string with some
> > query
> > > > > id,
> > > > > > > without any meaning for user. User just need to get the id and
> > pass
> > > > as
> > > > > > > parameter to KILL QUERY command.
> > > > > > >
> > > > > > > Even if query is distributed it have single query id from user
> > > > > > perspective
> > > > > > > and will killed on all nodes. User just need to known one global
> > > > query
> > > > > > id.
> > > > > > >
> > > > > > > How it can works.
> > > > > > > 1)SELECT * from running_queries
> > > > > > > result is
> > > > > > >  query_id | node_id
> > > > > > >   | sql               | schema_name | connection_id | duration
> > > > > > > 123.33     | e0a69cb8-a1a8-45f6-b84d-ead367a00000   | SELECT
> > ...  |
> > > > ...
> > > > > > >                   |   22                 | 23456
> > > > > > > 333.31     | aaa6acb8-a4a5-42f6-f842-ead111b00020     |
> > UPDATE...  |
> > > > > ...
> > > > > > >                   |  321                | 3467777
> > > > > > > 2) KILL QUERY '123.33'
> > > > > > >
> > > > > > > So, user need select query_id from running_queries view and use
> > it
> > > > for
> > > > > > KILL
> > > > > > > QUERY command.
> > > > > > >
> > > > > > > I hope it became clearer.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > ср, 21 нояб. 2018 г. в 02:11, Denis Magda <[hidden email]>:
> > > > > > >
> > > > > > > > Folks,
> > > > > > > >
> > > > > > > > The decimal syntax is really odd - KILL QUERY
> > > > > > > > '[node_order].[query_counter]'
> > > > > > > >
> > > > > > > > Confusing, let's use a space to separate parameters.
> > > > > > > >
> > > > > > > > Also, what if I want to halt a specific query with certain ID?
> > > > Don't
> > > > > > know
> > > > > > > > the node number, just know that the query is distributed and
> > runs
> > > > > > across
> > > > > > > > several machines. Sounds like the syntax still should consider
> > > > > > > > [node_order/id] as an optional parameter.
> > > > > > > >
> > > > > > > > Probably, if you explain to me how an end user will use this
> > > > command
> > > > > > from
> > > > > > > > the very beginning (how do I look for a query id and node id,
> > etc)
> > > > > then
> > > > > > > the
> > > > > > > > things get clearer.
> > > > > > > >
> > > > > > > > --
> > > > > > > > Denis
> > > > > > > >
> > > > > > > > On Tue, Nov 20, 2018 at 1:03 AM Юрий <
> > [hidden email]>
> > > > > > > wrote:
> > > > > > > >
> > > > > > > > > Hi Vladimir,
> > > > > > > > >
> > > > > > > > > Thanks for your suggestion to use MANAGEMENT_POOL for
> > processing
> > > > > > > > > cancellation requests.
> > > > > > > > >
> > > > > > > > > About your questions.
> > > > > > > > > 1) I'm going to implements SQL view to provide list of
> > running
> > > > > > queries.
> > > > > > > > The
> > > > > > > > > SQL VIEW has been a little bit discussed earlier. Proposed
> > name
> > > > is
> > > > > > > > > *running_queries* with following columns: query_id, node_id,
> > sql,
> > > > > > > > > schema_name, connection_id, duration. Currently most of the
> > > > > > information
> > > > > > > > can
> > > > > > > > > be  retrieved through cache API, however it doesn't matter,
> > any
> > > > > case
> > > > > > we
> > > > > > > > > need to expose SQL VIEW. Seem's you are right - the part
> > should
> > > > be
> > > > > > > > > implemented firstly.
> > > > > > > > > 2) Fully agree that we need to support all kind of SQL
> > queries
> > > > > > > > > (SLECT/DML/DDL, transactional, non transnational, local,
> > > > > > distributed).
> > > > > > > I
> > > > > > > > > definitely sure that it will possible for all of above,
> > however
> > > > I'm
> > > > > > not
> > > > > > > > > sure about DDL - need to investigate it deeper. Also need to
> > > > > > understand
> > > > > > > > > that canceled DML operation can lead to partially updated
> > data
> > > > for
> > > > > > non
> > > > > > > > > transational caches.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov <
> > > > > [hidden email]
> > > > > > >:
> > > > > > > > >
> > > > > > > > > > Hi Yuriy,
> > > > > > > > > >
> > > > > > > > > > I think we can use MANAGEMENT_POOL for this. It is already
> > used
> > > > > for
> > > > > > > > some
> > > > > > > > > > internal Ignite tasks, and it appears to be a good
> > candidate to
> > > > > > > process
> > > > > > > > > > cancel requests.
> > > > > > > > > >
> > > > > > > > > > But there are several things which are not clear enough
> > for me
> > > > at
> > > > > > the
> > > > > > > > > > moment:
> > > > > > > > > > 1) How user is going to get the list of running queries in
> > the
> > > > > > first
> > > > > > > > > place?
> > > > > > > > > > Do we already have any SQL commands/views to get this
> > > > > information?
> > > > > > > > > > 2) We need to ensure that KILL command will be processed
> > > > properly
> > > > > > by
> > > > > > > > all
> > > > > > > > > > kinds of SQL queries - SELECT/DML/DDL, non-transactional or
> > > > > > > > > transactional,
> > > > > > > > > > local queries and distributed queries. Will we be able to
> > > > support
> > > > > > all
> > > > > > > > > these
> > > > > > > > > > modes?
> > > > > > > > > >
> > > > > > > > > > Vladimir.
> > > > > > > > > >
> > > > > > > > > > On Mon, Nov 19, 2018 at 6:37 PM Юрий <
> > > > > [hidden email]>
> > > > > > > > > wrote:
> > > > > > > > > >
> > > > > > > > > > > Hi Igniters,
> > > > > > > > > > >
> > > > > > > > > > > Earlier we agreed about syntax KILL QUERY
> > > > > > > > > '[node_order].[query_counter]',
> > > > > > > > > > > e.g. KILL QUERY '25.123' for single query  or KILL QUERY
> > > > '25.*'
> > > > > > for
> > > > > > > > all
> > > > > > > > > > > queries on the node. Which is part of IEP-29
> > > > > > > > > > > <
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > > > >
> > > > > > > > > > > .
> > > > > > > > > > >
> > > > > > > > > > > Now I want to discuss internal realization of KILL query
> > > > > feature.
> > > > > > > > > > >
> > > > > > > > > > > My current vision is following:
> > > > > > > > > > > After parsing, Ignite create KILL query command with two
> > > > > > > parameters:
> > > > > > > > > > > nodeOrderId, nodeQryId. To determine that need to kill
> > all
> > > > > > queries
> > > > > > > > on a
> > > > > > > > > > > node we can use negative value of query id, due to qry id
> > > > > always
> > > > > > > have
> > > > > > > > > > > positive values.
> > > > > > > > > > > The command process at IgniteH2Indexing as native
> > command.
> > > > > > > > > > > By nodeOrderId we find node which initial for the query
> > and
> > > > > send
> > > > > > to
> > > > > > > > the
> > > > > > > > > > > node new GridQueryKillRequest with nodeQryId to
> > TOPIC_QUERY
> > > > > with
> > > > > > > not
> > > > > > > > > > QUERY
> > > > > > > > > > > POOL executor.
> > > > > > > > > > > At GridReduceQueryExecutor we add support of processing
> > new
> > > > > > > > > > > GridQueryKillRequest
> > > > > > > > > > > which just run already exists cancelQueries method with
> > given
> > > > > > qryId
> > > > > > > > or
> > > > > > > > > > with
> > > > > > > > > > > all qryIds which currently running at the node in case at
> > > > > initial
> > > > > > > > KILL
> > > > > > > > > > > QUERY parameters used star symbol.
> > > > > > > > > > >
> > > > > > > > > > > I have a doubt which of thread pool we should use to
> > process
> > > > > > > > > > > GridQueryKillRequest.
> > > > > > > > > > > My opinion it shouldn't be QUERY pool, due to the pool
> > can be
> > > > > > fully
> > > > > > > > > used
> > > > > > > > > > by
> > > > > > > > > > > executing queries, it such case we can't cancel query
> > > > > > immediately.
> > > > > > > > May
> > > > > > > > > we
> > > > > > > > > > > use one of already existed pool or create new one? Or
> > may be
> > > > > I'm
> > > > > > > > > mistaken
> > > > > > > > > > > and it should use QUERY pool.
> > > > > > > > > > >
> > > > > > > > > > > What do you think about proposed plan of implementation?
> > > > > > > > > > >
> > > > > > > > > > > And please give comments about which of thread pool will
> > be
> > > > > > better
> > > > > > > to
> > > > > > > > > use
> > > > > > > > > > > for kill query requests. It's small, but really important
> > > > part
> > > > > of
> > > > > > > the
> > > > > > > > > > > realization.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > Thanks.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > --
> > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Живи с улыбкой! :D
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Живи с улыбкой! :D
> > > > > > >
> > > > > >
> > > > >
> > > >
> >
> >
> >
> > --
> > Best regards,
> > Ivan Pavlukhin
> >

--
Best regards,
Ivan Pavlukhin

[Vladimir Ozerov]

Yes ("нуы")

On Tue, Nov 27, 2018 at 10:56 AM Павлухин Иван <[hidden email]> wrote:

> I believe that the meaning was:
>
> > I propose to start with running queries VIEW first.
> вт, 27 нояб. 2018 г. в 10:47, Vladimir Ozerov <[hidden email]>:
> >
> > I propose to start with running queries мшуц first. Once we have it, it
> > will be easier to agree on final command syntax.
> >
> > On Fri, Nov 23, 2018 at 9:32 AM Павлухин Иван <[hidden email]>
> wrote:
> >
> > > Hi,
> > >
> > > May be I am a little bit late with my thoughts about a command syntax.
> > > How do I see it is going to be used:
> > > 1. A user is able to kill a query by unique id belonging only to this
> > > query.
> > > 2. A user is able to kill all queries started by a specific node.
> > > For killing a single query we must identify it by unique id which is
> > > going to be received directly from Ignite (e.g. running queries view)
> > > and not calculated by user. Internally the id is compound but why
> > > cannot we convert it to opaque integer or string which hides real
> > > structure? E.g. base16String(concat(nodeOrder.toString(), ".",
> > > queryIdOnNode.toString())) The syntax could be KILL QUERY '123' or
> > > KILL QUERY WHERE queryId = '123'
> > > For killing all queries started by some node we need to use only node
> > > order (or id). It could be like KILL QUERY WHERE nodeOrder = 34.
> > > чт, 22 нояб. 2018 г. в 12:56, Denis Mekhanikov <[hidden email]
> >:
> > > >
> > > > Actually, option with separate parameters was mentioned in another
> thread
> > > >
> > >
> http://apache-ignite-developers.2346864.n4.nabble.com/proposed-design-for-thin-client-SQL-management-and-monitoring-view-running-queries-and-kill-it-tp37713p38056.html
> > > >
> > > > Denis
> > > >
> > > > чт, 22 нояб. 2018 г. в 08:51, Vladimir Ozerov <[hidden email]
> >:
> > > >
> > > > > Denis,
> > > > >
> > > > > Problems with separate parameters are explained above.
> > > > >
> > > > > чт, 22 нояб. 2018 г. в 3:23, Denis Magda <[hidden email]>:
> > > > >
> > > > > > Vladimir,
> > > > > >
> > > > > > All of the alternatives are reminiscent of mathematical
> operations.
> > > Don't
> > > > > > look like a SQL command. What if we use a SQL approach
> introducing
> > > named
> > > > > > parameters:
> > > > > >
> > > > > > KILL QUERY query_id=10 [AND node_id=5]
> > > > > >
> > > > > > --
> > > > > > Denis
> > > > > >
> > > > > > On Wed, Nov 21, 2018 at 4:11 AM Vladimir Ozerov <
> > > [hidden email]>
> > > > > > wrote:
> > > > > >
> > > > > > > Denis,
> > > > > > >
> > > > > > > Space is bad candidate because it is a whitespace. Without
> > > whitespaces
> > > > > we
> > > > > > > can have syntax without quotes at all. Any non-whitespace
> delimiter
> > > > > will
> > > > > > > work, though:
> > > > > > >
> > > > > > > KILL QUERY 45.1
> > > > > > > KILL QUERY 45-1
> > > > > > > KILL QUERY 45:1
> > > > > > >
> > > > > > > On Wed, Nov 21, 2018 at 3:06 PM Юрий <
> [hidden email]>
> > > > > > wrote:
> > > > > > >
> > > > > > > > Denis,
> > > > > > > >
> > > > > > > > Let's consider parameter of KILL QUERY just a string with
> some
> > > query
> > > > > > id,
> > > > > > > > without any meaning for user. User just need to get the id
> and
> > > pass
> > > > > as
> > > > > > > > parameter to KILL QUERY command.
> > > > > > > >
> > > > > > > > Even if query is distributed it have single query id from
> user
> > > > > > > perspective
> > > > > > > > and will killed on all nodes. User just need to known one
> global
> > > > > query
> > > > > > > id.
> > > > > > > >
> > > > > > > > How it can works.
> > > > > > > > 1)SELECT * from running_queries
> > > > > > > > result is
> > > > > > > >  query_id | node_id
> > > > > > > >   | sql               | schema_name | connection_id |
> duration
> > > > > > > > 123.33     | e0a69cb8-a1a8-45f6-b84d-ead367a00000   | SELECT
> > > ...  |
> > > > > ...
> > > > > > > >                   |   22                 | 23456
> > > > > > > > 333.31     | aaa6acb8-a4a5-42f6-f842-ead111b00020     |
> > > UPDATE...  |
> > > > > > ...
> > > > > > > >                   |  321                | 3467777
> > > > > > > > 2) KILL QUERY '123.33'
> > > > > > > >
> > > > > > > > So, user need select query_id from running_queries view and
> use
> > > it
> > > > > for
> > > > > > > KILL
> > > > > > > > QUERY command.
> > > > > > > >
> > > > > > > > I hope it became clearer.
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > ср, 21 нояб. 2018 г. в 02:11, Denis Magda <[hidden email]
> >:
> > > > > > > >
> > > > > > > > > Folks,
> > > > > > > > >
> > > > > > > > > The decimal syntax is really odd - KILL QUERY
> > > > > > > > > '[node_order].[query_counter]'
> > > > > > > > >
> > > > > > > > > Confusing, let's use a space to separate parameters.
> > > > > > > > >
> > > > > > > > > Also, what if I want to halt a specific query with certain
> ID?
> > > > > Don't
> > > > > > > know
> > > > > > > > > the node number, just know that the query is distributed
> and
> > > runs
> > > > > > > across
> > > > > > > > > several machines. Sounds like the syntax still should
> consider
> > > > > > > > > [node_order/id] as an optional parameter.
> > > > > > > > >
> > > > > > > > > Probably, if you explain to me how an end user will use
> this
> > > > > command
> > > > > > > from
> > > > > > > > > the very beginning (how do I look for a query id and node
> id,
> > > etc)
> > > > > > then
> > > > > > > > the
> > > > > > > > > things get clearer.
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Denis
> > > > > > > > >
> > > > > > > > > On Tue, Nov 20, 2018 at 1:03 AM Юрий <
> > > [hidden email]>
> > > > > > > > wrote:
> > > > > > > > >
> > > > > > > > > > Hi Vladimir,
> > > > > > > > > >
> > > > > > > > > > Thanks for your suggestion to use MANAGEMENT_POOL for
> > > processing
> > > > > > > > > > cancellation requests.
> > > > > > > > > >
> > > > > > > > > > About your questions.
> > > > > > > > > > 1) I'm going to implements SQL view to provide list of
> > > running
> > > > > > > queries.
> > > > > > > > > The
> > > > > > > > > > SQL VIEW has been a little bit discussed earlier.
> Proposed
> > > name
> > > > > is
> > > > > > > > > > *running_queries* with following columns: query_id,
> node_id,
> > > sql,
> > > > > > > > > > schema_name, connection_id, duration. Currently most of
> the
> > > > > > > information
> > > > > > > > > can
> > > > > > > > > > be  retrieved through cache API, however it doesn't
> matter,
> > > any
> > > > > > case
> > > > > > > we
> > > > > > > > > > need to expose SQL VIEW. Seem's you are right - the part
> > > should
> > > > > be
> > > > > > > > > > implemented firstly.
> > > > > > > > > > 2) Fully agree that we need to support all kind of SQL
> > > queries
> > > > > > > > > > (SLECT/DML/DDL, transactional, non transnational, local,
> > > > > > > distributed).
> > > > > > > > I
> > > > > > > > > > definitely sure that it will possible for all of above,
> > > however
> > > > > I'm
> > > > > > > not
> > > > > > > > > > sure about DDL - need to investigate it deeper. Also
> need to
> > > > > > > understand
> > > > > > > > > > that canceled DML operation can lead to partially updated
> > > data
> > > > > for
> > > > > > > non
> > > > > > > > > > transational caches.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov <
> > > > > > [hidden email]
> > > > > > > >:
> > > > > > > > > >
> > > > > > > > > > > Hi Yuriy,
> > > > > > > > > > >
> > > > > > > > > > > I think we can use MANAGEMENT_POOL for this. It is
> already
> > > used
> > > > > > for
> > > > > > > > > some
> > > > > > > > > > > internal Ignite tasks, and it appears to be a good
> > > candidate to
> > > > > > > > process
> > > > > > > > > > > cancel requests.
> > > > > > > > > > >
> > > > > > > > > > > But there are several things which are not clear enough
> > > for me
> > > > > at
> > > > > > > the
> > > > > > > > > > > moment:
> > > > > > > > > > > 1) How user is going to get the list of running
> queries in
> > > the
> > > > > > > first
> > > > > > > > > > place?
> > > > > > > > > > > Do we already have any SQL commands/views to get this
> > > > > > information?
> > > > > > > > > > > 2) We need to ensure that KILL command will be
> processed
> > > > > properly
> > > > > > > by
> > > > > > > > > all
> > > > > > > > > > > kinds of SQL queries - SELECT/DML/DDL,
> non-transactional or
> > > > > > > > > > transactional,
> > > > > > > > > > > local queries and distributed queries. Will we be able
> to
> > > > > support
> > > > > > > all
> > > > > > > > > > these
> > > > > > > > > > > modes?
> > > > > > > > > > >
> > > > > > > > > > > Vladimir.
> > > > > > > > > > >
> > > > > > > > > > > On Mon, Nov 19, 2018 at 6:37 PM Юрий <
> > > > > > [hidden email]>
> > > > > > > > > > wrote:
> > > > > > > > > > >
> > > > > > > > > > > > Hi Igniters,
> > > > > > > > > > > >
> > > > > > > > > > > > Earlier we agreed about syntax KILL QUERY
> > > > > > > > > > '[node_order].[query_counter]',
> > > > > > > > > > > > e.g. KILL QUERY '25.123' for single query  or KILL
> QUERY
> > > > > '25.*'
> > > > > > > for
> > > > > > > > > all
> > > > > > > > > > > > queries on the node. Which is part of IEP-29
> > > > > > > > > > > > <
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > > > > >
> > > > > > > > > > > > .
> > > > > > > > > > > >
> > > > > > > > > > > > Now I want to discuss internal realization of KILL
> query
> > > > > > feature.
> > > > > > > > > > > >
> > > > > > > > > > > > My current vision is following:
> > > > > > > > > > > > After parsing, Ignite create KILL query command with
> two
> > > > > > > > parameters:
> > > > > > > > > > > > nodeOrderId, nodeQryId. To determine that need to
> kill
> > > all
> > > > > > > queries
> > > > > > > > > on a
> > > > > > > > > > > > node we can use negative value of query id, due to
> qry id
> > > > > > always
> > > > > > > > have
> > > > > > > > > > > > positive values.
> > > > > > > > > > > > The command process at IgniteH2Indexing as native
> > > command.
> > > > > > > > > > > > By nodeOrderId we find node which initial for the
> query
> > > and
> > > > > > send
> > > > > > > to
> > > > > > > > > the
> > > > > > > > > > > > node new GridQueryKillRequest with nodeQryId to
> > > TOPIC_QUERY
> > > > > > with
> > > > > > > > not
> > > > > > > > > > > QUERY
> > > > > > > > > > > > POOL executor.
> > > > > > > > > > > > At GridReduceQueryExecutor we add support of
> processing
> > > new
> > > > > > > > > > > > GridQueryKillRequest
> > > > > > > > > > > > which just run already exists cancelQueries method
> with
> > > given
> > > > > > > qryId
> > > > > > > > > or
> > > > > > > > > > > with
> > > > > > > > > > > > all qryIds which currently running at the node in
> case at
> > > > > > initial
> > > > > > > > > KILL
> > > > > > > > > > > > QUERY parameters used star symbol.
> > > > > > > > > > > >
> > > > > > > > > > > > I have a doubt which of thread pool we should use to
> > > process
> > > > > > > > > > > > GridQueryKillRequest.
> > > > > > > > > > > > My opinion it shouldn't be QUERY pool, due to the
> pool
> > > can be
> > > > > > > fully
> > > > > > > > > > used
> > > > > > > > > > > by
> > > > > > > > > > > > executing queries, it such case we can't cancel query
> > > > > > > immediately.
> > > > > > > > > May
> > > > > > > > > > we
> > > > > > > > > > > > use one of already existed pool or create new one? Or
> > > may be
> > > > > > I'm
> > > > > > > > > > mistaken
> > > > > > > > > > > > and it should use QUERY pool.
> > > > > > > > > > > >
> > > > > > > > > > > > What do you think about proposed plan of
> implementation?
> > > > > > > > > > > >
> > > > > > > > > > > > And please give comments about which of thread pool
> will
> > > be
> > > > > > > better
> > > > > > > > to
> > > > > > > > > > use
> > > > > > > > > > > > for kill query requests. It's small, but really
> important
> > > > > part
> > > > > > of
> > > > > > > > the
> > > > > > > > > > > > realization.
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > Thanks.
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > --
> > > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > > Живи с улыбкой! :D
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > >
> > >
> > >
> > > --
> > > Best regards,
> > > Ivan Pavlukhin
> > >
>
>
>
> --
> Best regards,
> Ivan Pavlukhin
>

[Юрий]

Hi Igniters,

Earlier we discuss about columns for running queries. Let's summarize it
and continue discussion for not closed questions.

What we had:
*name of view**: *running_queries
*columns and meaning*:
                       query_id -  unique id of query on node
                       node_id - initial node of request.
                       sql - text of query
                       schema_name - name of sql schema
                       duration - duration in milliseconds from start of
execution.

All of this columns are clear, except query_id.
Let's keep in mind that the query_id column of the view coupled with KILL
QUERY command.

We have the following variants what is query_id:
1) It's string, internally with two parts separated by '.'(it can be other
separator): numeric node order and numeric query counter unique for local
node, e.g. '172.67321'. For this case query id will be really unique across
a cluster, but can be looks a strange for a user, especially in case we
will have ability to kill all queries on a node, when user should get first
part before separator to use it, e.g. KILL QUERY '172.*'.

2) Just single numeric id, unique for local node, e.g '127'. In this case
we need more complicated syntax for further KILL QUERY command, which lead
to use two columns from the view, e.g. KILL QUERY WHERE nodeId=
37d7afd8-b87d-4aa1-b3d1-c1c033800000 and queryId=67321

3) Use base16String(concat(node,".",queryID) as query id, e.g. '
3132332E393337'. Then we hide internal structure of id and such id will be
unique across a cluster. However we will need use complicated syntax for
KILL QUERY command as for 2nd case.

4) Just single numeric id, unique for local node, e.g '127'. But user
should use two columns to merge it and create query id unique in a cluster.
Such approach use  by Oracle:ALTER SYSTEM CANCEL SQL 'SID, SERIAL, SQL_ID'.
In this case user will know real meaning of each part of passed parameter
for KILL QUERY command. But it hard to use.

5) Any other approach you can think of....

If be honestly I prefer first variant, it looks simple to use by user (it
require read a docs, but any case it required for any use cases).

Lets discuss it again and chose better approach to expose query_id column
for Ignite. Also confirm list of columns.

вт, 27 нояб. 2018 г. в 11:00, Vladimir Ozerov <[hidden email]>:

> Yes ("нуы")
>
> On Tue, Nov 27, 2018 at 10:56 AM Павлухин Иван <[hidden email]>
> wrote:
>
> > I believe that the meaning was:
> >
> > > I propose to start with running queries VIEW first.
> > вт, 27 нояб. 2018 г. в 10:47, Vladimir Ozerov <[hidden email]>:
> > >
> > > I propose to start with running queries мшуц first. Once we have it, it
> > > will be easier to agree on final command syntax.
> > >
> > > On Fri, Nov 23, 2018 at 9:32 AM Павлухин Иван <[hidden email]>
> > wrote:
> > >
> > > > Hi,
> > > >
> > > > May be I am a little bit late with my thoughts about a command
> syntax.
> > > > How do I see it is going to be used:
> > > > 1. A user is able to kill a query by unique id belonging only to this
> > > > query.
> > > > 2. A user is able to kill all queries started by a specific node.
> > > > For killing a single query we must identify it by unique id which is
> > > > going to be received directly from Ignite (e.g. running queries view)
> > > > and not calculated by user. Internally the id is compound but why
> > > > cannot we convert it to opaque integer or string which hides real
> > > > structure? E.g. base16String(concat(nodeOrder.toString(), ".",
> > > > queryIdOnNode.toString())) The syntax could be KILL QUERY '123' or
> > > > KILL QUERY WHERE queryId = '123'
> > > > For killing all queries started by some node we need to use only node
> > > > order (or id). It could be like KILL QUERY WHERE nodeOrder = 34.
> > > > чт, 22 нояб. 2018 г. в 12:56, Denis Mekhanikov <
> [hidden email]
> > >:
> > > > >
> > > > > Actually, option with separate parameters was mentioned in another
> > thread
> > > > >
> > > >
> >
> http://apache-ignite-developers.2346864.n4.nabble.com/proposed-design-for-thin-client-SQL-management-and-monitoring-view-running-queries-and-kill-it-tp37713p38056.html
> > > > >
> > > > > Denis
> > > > >
> > > > > чт, 22 нояб. 2018 г. в 08:51, Vladimir Ozerov <
> [hidden email]
> > >:
> > > > >
> > > > > > Denis,
> > > > > >
> > > > > > Problems with separate parameters are explained above.
> > > > > >
> > > > > > чт, 22 нояб. 2018 г. в 3:23, Denis Magda <[hidden email]>:
> > > > > >
> > > > > > > Vladimir,
> > > > > > >
> > > > > > > All of the alternatives are reminiscent of mathematical
> > operations.
> > > > Don't
> > > > > > > look like a SQL command. What if we use a SQL approach
> > introducing
> > > > named
> > > > > > > parameters:
> > > > > > >
> > > > > > > KILL QUERY query_id=10 [AND node_id=5]
> > > > > > >
> > > > > > > --
> > > > > > > Denis
> > > > > > >
> > > > > > > On Wed, Nov 21, 2018 at 4:11 AM Vladimir Ozerov <
> > > > [hidden email]>
> > > > > > > wrote:
> > > > > > >
> > > > > > > > Denis,
> > > > > > > >
> > > > > > > > Space is bad candidate because it is a whitespace. Without
> > > > whitespaces
> > > > > > we
> > > > > > > > can have syntax without quotes at all. Any non-whitespace
> > delimiter
> > > > > > will
> > > > > > > > work, though:
> > > > > > > >
> > > > > > > > KILL QUERY 45.1
> > > > > > > > KILL QUERY 45-1
> > > > > > > > KILL QUERY 45:1
> > > > > > > >
> > > > > > > > On Wed, Nov 21, 2018 at 3:06 PM Юрий <
> > [hidden email]>
> > > > > > > wrote:
> > > > > > > >
> > > > > > > > > Denis,
> > > > > > > > >
> > > > > > > > > Let's consider parameter of KILL QUERY just a string with
> > some
> > > > query
> > > > > > > id,
> > > > > > > > > without any meaning for user. User just need to get the id
> > and
> > > > pass
> > > > > > as
> > > > > > > > > parameter to KILL QUERY command.
> > > > > > > > >
> > > > > > > > > Even if query is distributed it have single query id from
> > user
> > > > > > > > perspective
> > > > > > > > > and will killed on all nodes. User just need to known one
> > global
> > > > > > query
> > > > > > > > id.
> > > > > > > > >
> > > > > > > > > How it can works.
> > > > > > > > > 1)SELECT * from running_queries
> > > > > > > > > result is
> > > > > > > > >  query_id | node_id
> > > > > > > > >   | sql               | schema_name | connection_id |
> > duration
> > > > > > > > > 123.33     | e0a69cb8-a1a8-45f6-b84d-ead367a00000   |
> SELECT
> > > > ...  |
> > > > > > ...
> > > > > > > > >                   |   22                 | 23456
> > > > > > > > > 333.31     | aaa6acb8-a4a5-42f6-f842-ead111b00020     |
> > > > UPDATE...  |
> > > > > > > ...
> > > > > > > > >                   |  321                | 3467777
> > > > > > > > > 2) KILL QUERY '123.33'
> > > > > > > > >
> > > > > > > > > So, user need select query_id from running_queries view and
> > use
> > > > it
> > > > > > for
> > > > > > > > KILL
> > > > > > > > > QUERY command.
> > > > > > > > >
> > > > > > > > > I hope it became clearer.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > ср, 21 нояб. 2018 г. в 02:11, Denis Magda <
> [hidden email]
> > >:
> > > > > > > > >
> > > > > > > > > > Folks,
> > > > > > > > > >
> > > > > > > > > > The decimal syntax is really odd - KILL QUERY
> > > > > > > > > > '[node_order].[query_counter]'
> > > > > > > > > >
> > > > > > > > > > Confusing, let's use a space to separate parameters.
> > > > > > > > > >
> > > > > > > > > > Also, what if I want to halt a specific query with
> certain
> > ID?
> > > > > > Don't
> > > > > > > > know
> > > > > > > > > > the node number, just know that the query is distributed
> > and
> > > > runs
> > > > > > > > across
> > > > > > > > > > several machines. Sounds like the syntax still should
> > consider
> > > > > > > > > > [node_order/id] as an optional parameter.
> > > > > > > > > >
> > > > > > > > > > Probably, if you explain to me how an end user will use
> > this
> > > > > > command
> > > > > > > > from
> > > > > > > > > > the very beginning (how do I look for a query id and node
> > id,
> > > > etc)
> > > > > > > then
> > > > > > > > > the
> > > > > > > > > > things get clearer.
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > > Denis
> > > > > > > > > >
> > > > > > > > > > On Tue, Nov 20, 2018 at 1:03 AM Юрий <
> > > > [hidden email]>
> > > > > > > > > wrote:
> > > > > > > > > >
> > > > > > > > > > > Hi Vladimir,
> > > > > > > > > > >
> > > > > > > > > > > Thanks for your suggestion to use MANAGEMENT_POOL for
> > > > processing
> > > > > > > > > > > cancellation requests.
> > > > > > > > > > >
> > > > > > > > > > > About your questions.
> > > > > > > > > > > 1) I'm going to implements SQL view to provide list of
> > > > running
> > > > > > > > queries.
> > > > > > > > > > The
> > > > > > > > > > > SQL VIEW has been a little bit discussed earlier.
> > Proposed
> > > > name
> > > > > > is
> > > > > > > > > > > *running_queries* with following columns: query_id,
> > node_id,
> > > > sql,
> > > > > > > > > > > schema_name, connection_id, duration. Currently most of
> > the
> > > > > > > > information
> > > > > > > > > > can
> > > > > > > > > > > be  retrieved through cache API, however it doesn't
> > matter,
> > > > any
> > > > > > > case
> > > > > > > > we
> > > > > > > > > > > need to expose SQL VIEW. Seem's you are right - the
> part
> > > > should
> > > > > > be
> > > > > > > > > > > implemented firstly.
> > > > > > > > > > > 2) Fully agree that we need to support all kind of SQL
> > > > queries
> > > > > > > > > > > (SLECT/DML/DDL, transactional, non transnational,
> local,
> > > > > > > > distributed).
> > > > > > > > > I
> > > > > > > > > > > definitely sure that it will possible for all of above,
> > > > however
> > > > > > I'm
> > > > > > > > not
> > > > > > > > > > > sure about DDL - need to investigate it deeper. Also
> > need to
> > > > > > > > understand
> > > > > > > > > > > that canceled DML operation can lead to partially
> updated
> > > > data
> > > > > > for
> > > > > > > > non
> > > > > > > > > > > transational caches.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov <
> > > > > > > [hidden email]
> > > > > > > > >:
> > > > > > > > > > >
> > > > > > > > > > > > Hi Yuriy,
> > > > > > > > > > > >
> > > > > > > > > > > > I think we can use MANAGEMENT_POOL for this. It is
> > already
> > > > used
> > > > > > > for
> > > > > > > > > > some
> > > > > > > > > > > > internal Ignite tasks, and it appears to be a good
> > > > candidate to
> > > > > > > > > process
> > > > > > > > > > > > cancel requests.
> > > > > > > > > > > >
> > > > > > > > > > > > But there are several things which are not clear
> enough
> > > > for me
> > > > > > at
> > > > > > > > the
> > > > > > > > > > > > moment:
> > > > > > > > > > > > 1) How user is going to get the list of running
> > queries in
> > > > the
> > > > > > > > first
> > > > > > > > > > > place?
> > > > > > > > > > > > Do we already have any SQL commands/views to get this
> > > > > > > information?
> > > > > > > > > > > > 2) We need to ensure that KILL command will be
> > processed
> > > > > > properly
> > > > > > > > by
> > > > > > > > > > all
> > > > > > > > > > > > kinds of SQL queries - SELECT/DML/DDL,
> > non-transactional or
> > > > > > > > > > > transactional,
> > > > > > > > > > > > local queries and distributed queries. Will we be
> able
> > to
> > > > > > support
> > > > > > > > all
> > > > > > > > > > > these
> > > > > > > > > > > > modes?
> > > > > > > > > > > >
> > > > > > > > > > > > Vladimir.
> > > > > > > > > > > >
> > > > > > > > > > > > On Mon, Nov 19, 2018 at 6:37 PM Юрий <
> > > > > > > [hidden email]>
> > > > > > > > > > > wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > > Hi Igniters,
> > > > > > > > > > > > >
> > > > > > > > > > > > > Earlier we agreed about syntax KILL QUERY
> > > > > > > > > > > '[node_order].[query_counter]',
> > > > > > > > > > > > > e.g. KILL QUERY '25.123' for single query  or KILL
> > QUERY
> > > > > > '25.*'
> > > > > > > > for
> > > > > > > > > > all
> > > > > > > > > > > > > queries on the node. Which is part of IEP-29
> > > > > > > > > > > > > <
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > > > > > >
> > > > > > > > > > > > > .
> > > > > > > > > > > > >
> > > > > > > > > > > > > Now I want to discuss internal realization of KILL
> > query
> > > > > > > feature.
> > > > > > > > > > > > >
> > > > > > > > > > > > > My current vision is following:
> > > > > > > > > > > > > After parsing, Ignite create KILL query command
> with
> > two
> > > > > > > > > parameters:
> > > > > > > > > > > > > nodeOrderId, nodeQryId. To determine that need to
> > kill
> > > > all
> > > > > > > > queries
> > > > > > > > > > on a
> > > > > > > > > > > > > node we can use negative value of query id, due to
> > qry id
> > > > > > > always
> > > > > > > > > have
> > > > > > > > > > > > > positive values.
> > > > > > > > > > > > > The command process at IgniteH2Indexing as native
> > > > command.
> > > > > > > > > > > > > By nodeOrderId we find node which initial for the
> > query
> > > > and
> > > > > > > send
> > > > > > > > to
> > > > > > > > > > the
> > > > > > > > > > > > > node new GridQueryKillRequest with nodeQryId to
> > > > TOPIC_QUERY
> > > > > > > with
> > > > > > > > > not
> > > > > > > > > > > > QUERY
> > > > > > > > > > > > > POOL executor.
> > > > > > > > > > > > > At GridReduceQueryExecutor we add support of
> > processing
> > > > new
> > > > > > > > > > > > > GridQueryKillRequest
> > > > > > > > > > > > > which just run already exists cancelQueries method
> > with
> > > > given
> > > > > > > > qryId
> > > > > > > > > > or
> > > > > > > > > > > > with
> > > > > > > > > > > > > all qryIds which currently running at the node in
> > case at
> > > > > > > initial
> > > > > > > > > > KILL
> > > > > > > > > > > > > QUERY parameters used star symbol.
> > > > > > > > > > > > >
> > > > > > > > > > > > > I have a doubt which of thread pool we should use
> to
> > > > process
> > > > > > > > > > > > > GridQueryKillRequest.
> > > > > > > > > > > > > My opinion it shouldn't be QUERY pool, due to the
> > pool
> > > > can be
> > > > > > > > fully
> > > > > > > > > > > used
> > > > > > > > > > > > by
> > > > > > > > > > > > > executing queries, it such case we can't cancel
> query
> > > > > > > > immediately.
> > > > > > > > > > May
> > > > > > > > > > > we
> > > > > > > > > > > > > use one of already existed pool or create new one?
> Or
> > > > may be
> > > > > > > I'm
> > > > > > > > > > > mistaken
> > > > > > > > > > > > > and it should use QUERY pool.
> > > > > > > > > > > > >
> > > > > > > > > > > > > What do you think about proposed plan of
> > implementation?
> > > > > > > > > > > > >
> > > > > > > > > > > > > And please give comments about which of thread pool
> > will
> > > > be
> > > > > > > > better
> > > > > > > > > to
> > > > > > > > > > > use
> > > > > > > > > > > > > for kill query requests. It's small, but really
> > important
> > > > > > part
> > > > > > > of
> > > > > > > > > the
> > > > > > > > > > > > > realization.
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > > Thanks.
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > > --
> > > > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > --
> > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Живи с улыбкой! :D
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > Best regards,
> > > > Ivan Pavlukhin
> > > >
> >
> >
> >
> > --
> > Best regards,
> > Ivan Pavlukhin
> >
>

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

[Vladimir Ozerov]

Hi Yuriy,

I think all proposed approaches might work. The question is what is the
most convenient from user perspective. Encoded values without special
characters are good because they are easy to copy with mouse (double-click)
or keyboard (Ctrl+Shift+arrow). On the other hand, ability to identify
ID/name of suspicious node from query ID is also a good thing. Several
examples of query ID:

CockroachDB: 14dacc1f9a781e3d0000000000000001
MongoDB: shardB:79014

Also it is important that the same query ID is printed in various log
messages. This will be very useful for debugging purposes, e.g. grep over
logs. So ideally query ID should not have any symbols which interfere with
grep syntax.


On Mon, Jan 14, 2019 at 3:09 PM Юрий <[hidden email]> wrote:

> Hi Igniters,
>
> Earlier we discuss about columns for running queries. Let's summarize it
> and continue discussion for not closed questions.
>
> What we had:
> *name of view**: *running_queries
> *columns and meaning*:
>                        query_id -  unique id of query on node
>                        node_id - initial node of request.
>                        sql - text of query
>                        schema_name - name of sql schema
>                        duration - duration in milliseconds from start of
> execution.
>
> All of this columns are clear, except query_id.
> Let's keep in mind that the query_id column of the view coupled with KILL
> QUERY command.
>
> We have the following variants what is query_id:
> 1) It's string, internally with two parts separated by '.'(it can be other
> separator): numeric node order and numeric query counter unique for local
> node, e.g. '172.67321'. For this case query id will be really unique across
> a cluster, but can be looks a strange for a user, especially in case we
> will have ability to kill all queries on a node, when user should get first
> part before separator to use it, e.g. KILL QUERY '172.*'.
>
> 2) Just single numeric id, unique for local node, e.g '127'. In this case
> we need more complicated syntax for further KILL QUERY command, which lead
> to use two columns from the view, e.g. KILL QUERY WHERE nodeId=
> 37d7afd8-b87d-4aa1-b3d1-c1c033800000 and queryId=67321
>
> 3) Use base16String(concat(node,".",queryID) as query id, e.g. '
> 3132332E393337'. Then we hide internal structure of id and such id will be
> unique across a cluster. However we will need use complicated syntax for
> KILL QUERY command as for 2nd case.
>
> 4) Just single numeric id, unique for local node, e.g '127'. But user
> should use two columns to merge it and create query id unique in a cluster.
> Such approach use  by Oracle:ALTER SYSTEM CANCEL SQL 'SID, SERIAL, SQL_ID'.
> In this case user will know real meaning of each part of passed parameter
> for KILL QUERY command. But it hard to use.
>
> 5) Any other approach you can think of....
>
> If be honestly I prefer first variant, it looks simple to use by user (it
> require read a docs, but any case it required for any use cases).
>
> Lets discuss it again and chose better approach to expose query_id column
> for Ignite. Also confirm list of columns.
>
> вт, 27 нояб. 2018 г. в 11:00, Vladimir Ozerov <[hidden email]>:
>
> > Yes ("нуы")
> >
> > On Tue, Nov 27, 2018 at 10:56 AM Павлухин Иван <[hidden email]>
> > wrote:
> >
> > > I believe that the meaning was:
> > >
> > > > I propose to start with running queries VIEW first.
> > > вт, 27 нояб. 2018 г. в 10:47, Vladimir Ozerov <[hidden email]>:
> > > >
> > > > I propose to start with running queries мшуц first. Once we have it,
> it
> > > > will be easier to agree on final command syntax.
> > > >
> > > > On Fri, Nov 23, 2018 at 9:32 AM Павлухин Иван <[hidden email]>
> > > wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > May be I am a little bit late with my thoughts about a command
> > syntax.
> > > > > How do I see it is going to be used:
> > > > > 1. A user is able to kill a query by unique id belonging only to
> this
> > > > > query.
> > > > > 2. A user is able to kill all queries started by a specific node.
> > > > > For killing a single query we must identify it by unique id which
> is
> > > > > going to be received directly from Ignite (e.g. running queries
> view)
> > > > > and not calculated by user. Internally the id is compound but why
> > > > > cannot we convert it to opaque integer or string which hides real
> > > > > structure? E.g. base16String(concat(nodeOrder.toString(), ".",
> > > > > queryIdOnNode.toString())) The syntax could be KILL QUERY '123' or
> > > > > KILL QUERY WHERE queryId = '123'
> > > > > For killing all queries started by some node we need to use only
> node
> > > > > order (or id). It could be like KILL QUERY WHERE nodeOrder = 34.
> > > > > чт, 22 нояб. 2018 г. в 12:56, Denis Mekhanikov <
> > [hidden email]
> > > >:
> > > > > >
> > > > > > Actually, option with separate parameters was mentioned in
> another
> > > thread
> > > > > >
> > > > >
> > >
> >
> http://apache-ignite-developers.2346864.n4.nabble.com/proposed-design-for-thin-client-SQL-management-and-monitoring-view-running-queries-and-kill-it-tp37713p38056.html
> > > > > >
> > > > > > Denis
> > > > > >
> > > > > > чт, 22 нояб. 2018 г. в 08:51, Vladimir Ozerov <
> > [hidden email]
> > > >:
> > > > > >
> > > > > > > Denis,
> > > > > > >
> > > > > > > Problems with separate parameters are explained above.
> > > > > > >
> > > > > > > чт, 22 нояб. 2018 г. в 3:23, Denis Magda <[hidden email]>:
> > > > > > >
> > > > > > > > Vladimir,
> > > > > > > >
> > > > > > > > All of the alternatives are reminiscent of mathematical
> > > operations.
> > > > > Don't
> > > > > > > > look like a SQL command. What if we use a SQL approach
> > > introducing
> > > > > named
> > > > > > > > parameters:
> > > > > > > >
> > > > > > > > KILL QUERY query_id=10 [AND node_id=5]
> > > > > > > >
> > > > > > > > --
> > > > > > > > Denis
> > > > > > > >
> > > > > > > > On Wed, Nov 21, 2018 at 4:11 AM Vladimir Ozerov <
> > > > > [hidden email]>
> > > > > > > > wrote:
> > > > > > > >
> > > > > > > > > Denis,
> > > > > > > > >
> > > > > > > > > Space is bad candidate because it is a whitespace. Without
> > > > > whitespaces
> > > > > > > we
> > > > > > > > > can have syntax without quotes at all. Any non-whitespace
> > > delimiter
> > > > > > > will
> > > > > > > > > work, though:
> > > > > > > > >
> > > > > > > > > KILL QUERY 45.1
> > > > > > > > > KILL QUERY 45-1
> > > > > > > > > KILL QUERY 45:1
> > > > > > > > >
> > > > > > > > > On Wed, Nov 21, 2018 at 3:06 PM Юрий <
> > > [hidden email]>
> > > > > > > > wrote:
> > > > > > > > >
> > > > > > > > > > Denis,
> > > > > > > > > >
> > > > > > > > > > Let's consider parameter of KILL QUERY just a string with
> > > some
> > > > > query
> > > > > > > > id,
> > > > > > > > > > without any meaning for user. User just need to get the
> id
> > > and
> > > > > pass
> > > > > > > as
> > > > > > > > > > parameter to KILL QUERY command.
> > > > > > > > > >
> > > > > > > > > > Even if query is distributed it have single query id from
> > > user
> > > > > > > > > perspective
> > > > > > > > > > and will killed on all nodes. User just need to known one
> > > global
> > > > > > > query
> > > > > > > > > id.
> > > > > > > > > >
> > > > > > > > > > How it can works.
> > > > > > > > > > 1)SELECT * from running_queries
> > > > > > > > > > result is
> > > > > > > > > >  query_id | node_id
> > > > > > > > > >   | sql               | schema_name | connection_id |
> > > duration
> > > > > > > > > > 123.33     | e0a69cb8-a1a8-45f6-b84d-ead367a00000   |
> > SELECT
> > > > > ...  |
> > > > > > > ...
> > > > > > > > > >                   |   22                 | 23456
> > > > > > > > > > 333.31     | aaa6acb8-a4a5-42f6-f842-ead111b00020     |
> > > > > UPDATE...  |
> > > > > > > > ...
> > > > > > > > > >                   |  321                | 3467777
> > > > > > > > > > 2) KILL QUERY '123.33'
> > > > > > > > > >
> > > > > > > > > > So, user need select query_id from running_queries view
> and
> > > use
> > > > > it
> > > > > > > for
> > > > > > > > > KILL
> > > > > > > > > > QUERY command.
> > > > > > > > > >
> > > > > > > > > > I hope it became clearer.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > ср, 21 нояб. 2018 г. в 02:11, Denis Magda <
> > [hidden email]
> > > >:
> > > > > > > > > >
> > > > > > > > > > > Folks,
> > > > > > > > > > >
> > > > > > > > > > > The decimal syntax is really odd - KILL QUERY
> > > > > > > > > > > '[node_order].[query_counter]'
> > > > > > > > > > >
> > > > > > > > > > > Confusing, let's use a space to separate parameters.
> > > > > > > > > > >
> > > > > > > > > > > Also, what if I want to halt a specific query with
> > certain
> > > ID?
> > > > > > > Don't
> > > > > > > > > know
> > > > > > > > > > > the node number, just know that the query is
> distributed
> > > and
> > > > > runs
> > > > > > > > > across
> > > > > > > > > > > several machines. Sounds like the syntax still should
> > > consider
> > > > > > > > > > > [node_order/id] as an optional parameter.
> > > > > > > > > > >
> > > > > > > > > > > Probably, if you explain to me how an end user will use
> > > this
> > > > > > > command
> > > > > > > > > from
> > > > > > > > > > > the very beginning (how do I look for a query id and
> node
> > > id,
> > > > > etc)
> > > > > > > > then
> > > > > > > > > > the
> > > > > > > > > > > things get clearer.
> > > > > > > > > > >
> > > > > > > > > > > --
> > > > > > > > > > > Denis
> > > > > > > > > > >
> > > > > > > > > > > On Tue, Nov 20, 2018 at 1:03 AM Юрий <
> > > > > [hidden email]>
> > > > > > > > > > wrote:
> > > > > > > > > > >
> > > > > > > > > > > > Hi Vladimir,
> > > > > > > > > > > >
> > > > > > > > > > > > Thanks for your suggestion to use MANAGEMENT_POOL for
> > > > > processing
> > > > > > > > > > > > cancellation requests.
> > > > > > > > > > > >
> > > > > > > > > > > > About your questions.
> > > > > > > > > > > > 1) I'm going to implements SQL view to provide list
> of
> > > > > running
> > > > > > > > > queries.
> > > > > > > > > > > The
> > > > > > > > > > > > SQL VIEW has been a little bit discussed earlier.
> > > Proposed
> > > > > name
> > > > > > > is
> > > > > > > > > > > > *running_queries* with following columns: query_id,
> > > node_id,
> > > > > sql,
> > > > > > > > > > > > schema_name, connection_id, duration. Currently most
> of
> > > the
> > > > > > > > > information
> > > > > > > > > > > can
> > > > > > > > > > > > be  retrieved through cache API, however it doesn't
> > > matter,
> > > > > any
> > > > > > > > case
> > > > > > > > > we
> > > > > > > > > > > > need to expose SQL VIEW. Seem's you are right - the
> > part
> > > > > should
> > > > > > > be
> > > > > > > > > > > > implemented firstly.
> > > > > > > > > > > > 2) Fully agree that we need to support all kind of
> SQL
> > > > > queries
> > > > > > > > > > > > (SLECT/DML/DDL, transactional, non transnational,
> > local,
> > > > > > > > > distributed).
> > > > > > > > > > I
> > > > > > > > > > > > definitely sure that it will possible for all of
> above,
> > > > > however
> > > > > > > I'm
> > > > > > > > > not
> > > > > > > > > > > > sure about DDL - need to investigate it deeper. Also
> > > need to
> > > > > > > > > understand
> > > > > > > > > > > > that canceled DML operation can lead to partially
> > updated
> > > > > data
> > > > > > > for
> > > > > > > > > non
> > > > > > > > > > > > transational caches.
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov <
> > > > > > > > [hidden email]
> > > > > > > > > >:
> > > > > > > > > > > >
> > > > > > > > > > > > > Hi Yuriy,
> > > > > > > > > > > > >
> > > > > > > > > > > > > I think we can use MANAGEMENT_POOL for this. It is
> > > already
> > > > > used
> > > > > > > > for
> > > > > > > > > > > some
> > > > > > > > > > > > > internal Ignite tasks, and it appears to be a good
> > > > > candidate to
> > > > > > > > > > process
> > > > > > > > > > > > > cancel requests.
> > > > > > > > > > > > >
> > > > > > > > > > > > > But there are several things which are not clear
> > enough
> > > > > for me
> > > > > > > at
> > > > > > > > > the
> > > > > > > > > > > > > moment:
> > > > > > > > > > > > > 1) How user is going to get the list of running
> > > queries in
> > > > > the
> > > > > > > > > first
> > > > > > > > > > > > place?
> > > > > > > > > > > > > Do we already have any SQL commands/views to get
> this
> > > > > > > > information?
> > > > > > > > > > > > > 2) We need to ensure that KILL command will be
> > > processed
> > > > > > > properly
> > > > > > > > > by
> > > > > > > > > > > all
> > > > > > > > > > > > > kinds of SQL queries - SELECT/DML/DDL,
> > > non-transactional or
> > > > > > > > > > > > transactional,
> > > > > > > > > > > > > local queries and distributed queries. Will we be
> > able
> > > to
> > > > > > > support
> > > > > > > > > all
> > > > > > > > > > > > these
> > > > > > > > > > > > > modes?
> > > > > > > > > > > > >
> > > > > > > > > > > > > Vladimir.
> > > > > > > > > > > > >
> > > > > > > > > > > > > On Mon, Nov 19, 2018 at 6:37 PM Юрий <
> > > > > > > > [hidden email]>
> > > > > > > > > > > > wrote:
> > > > > > > > > > > > >
> > > > > > > > > > > > > > Hi Igniters,
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Earlier we agreed about syntax KILL QUERY
> > > > > > > > > > > > '[node_order].[query_counter]',
> > > > > > > > > > > > > > e.g. KILL QUERY '25.123' for single query  or
> KILL
> > > QUERY
> > > > > > > '25.*'
> > > > > > > > > for
> > > > > > > > > > > all
> > > > > > > > > > > > > > queries on the node. Which is part of IEP-29
> > > > > > > > > > > > > > <
> > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > .
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Now I want to discuss internal realization of
> KILL
> > > query
> > > > > > > > feature.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > My current vision is following:
> > > > > > > > > > > > > > After parsing, Ignite create KILL query command
> > with
> > > two
> > > > > > > > > > parameters:
> > > > > > > > > > > > > > nodeOrderId, nodeQryId. To determine that need to
> > > kill
> > > > > all
> > > > > > > > > queries
> > > > > > > > > > > on a
> > > > > > > > > > > > > > node we can use negative value of query id, due
> to
> > > qry id
> > > > > > > > always
> > > > > > > > > > have
> > > > > > > > > > > > > > positive values.
> > > > > > > > > > > > > > The command process at IgniteH2Indexing as native
> > > > > command.
> > > > > > > > > > > > > > By nodeOrderId we find node which initial for the
> > > query
> > > > > and
> > > > > > > > send
> > > > > > > > > to
> > > > > > > > > > > the
> > > > > > > > > > > > > > node new GridQueryKillRequest with nodeQryId to
> > > > > TOPIC_QUERY
> > > > > > > > with
> > > > > > > > > > not
> > > > > > > > > > > > > QUERY
> > > > > > > > > > > > > > POOL executor.
> > > > > > > > > > > > > > At GridReduceQueryExecutor we add support of
> > > processing
> > > > > new
> > > > > > > > > > > > > > GridQueryKillRequest
> > > > > > > > > > > > > > which just run already exists cancelQueries
> method
> > > with
> > > > > given
> > > > > > > > > qryId
> > > > > > > > > > > or
> > > > > > > > > > > > > with
> > > > > > > > > > > > > > all qryIds which currently running at the node in
> > > case at
> > > > > > > > initial
> > > > > > > > > > > KILL
> > > > > > > > > > > > > > QUERY parameters used star symbol.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > I have a doubt which of thread pool we should use
> > to
> > > > > process
> > > > > > > > > > > > > > GridQueryKillRequest.
> > > > > > > > > > > > > > My opinion it shouldn't be QUERY pool, due to the
> > > pool
> > > > > can be
> > > > > > > > > fully
> > > > > > > > > > > > used
> > > > > > > > > > > > > by
> > > > > > > > > > > > > > executing queries, it such case we can't cancel
> > query
> > > > > > > > > immediately.
> > > > > > > > > > > May
> > > > > > > > > > > > we
> > > > > > > > > > > > > > use one of already existed pool or create new
> one?
> > Or
> > > > > may be
> > > > > > > > I'm
> > > > > > > > > > > > mistaken
> > > > > > > > > > > > > > and it should use QUERY pool.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > What do you think about proposed plan of
> > > implementation?
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > And please give comments about which of thread
> pool
> > > will
> > > > > be
> > > > > > > > > better
> > > > > > > > > > to
> > > > > > > > > > > > use
> > > > > > > > > > > > > > for kill query requests. It's small, but really
> > > important
> > > > > > > part
> > > > > > > > of
> > > > > > > > > > the
> > > > > > > > > > > > > > realization.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Thanks.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > --
> > > > > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > --
> > > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > Best regards,
> > > > > Ivan Pavlukhin
> > > > >
> > >
> > >
> > >
> > > --
> > > Best regards,
> > > Ivan Pavlukhin
> > >
> >
>
>
> --
> Живи с улыбкой! :D
>

[Юрий]

Thanks Vladimir for your thoughts.

Based on it most convenient ways are first and third.
But with some modifications:
For first variant delimiter should be a letter, e.g. 123X15494, then it
could be simple copy by user.
For 3rd variant can be used convert both numeric to HEX and use a letter
delimiter not included to HEX symbols (ABCDEF), in this case query id will
be shorter and also can be simple copy by user. e.g. 7BX3C86 ( it the same
value as used for first variant), instead of convert all value as string to
base16 due to it will be really long value.

Possible realization for the cases:
1) Concatenation node order id with query id with a letter delimiter.

query_id = 1234X8753 , where *1234* - node order, *8753* - local node query
counter. *X* - delimeter

2) Converting both node order id and query id to HEX.

query_id =  7BX3C86,  value is concat(hex(node),"X",hex(queryID))

For both variants we can use either simple or copmlex KILL QUERY syntax.
Simple:

KILL QUERY 7BX3C86 - for kill concrete query
KILL QUERY 7B - for killing all queries on a node.  May be need extra
symbols for such queries to avoid fault of user and kill all queries by
mistake, like KILL QUERY 7B*

Complex:

KILL QUERY WHERE queryId=7BX3C86 - for killing concrete query.

KILL QUERY WHERE nodeId=37d7afd8-b87d-4aa1-b3d1-c1c033800000  - for kill
all running queries on a given node.



What do you think?


вт, 15 янв. 2019 г. в 11:20, Vladimir Ozerov <[hidden email]>:

> Hi Yuriy,
>
> I think all proposed approaches might work. The question is what is the
> most convenient from user perspective. Encoded values without special
> characters are good because they are easy to copy with mouse (double-click)
> or keyboard (Ctrl+Shift+arrow). On the other hand, ability to identify
> ID/name of suspicious node from query ID is also a good thing. Several
> examples of query ID:
>
> CockroachDB: 14dacc1f9a781e3d0000000000000001
> MongoDB: shardB:79014
>
> Also it is important that the same query ID is printed in various log
> messages. This will be very useful for debugging purposes, e.g. grep over
> logs. So ideally query ID should not have any symbols which interfere with
> grep syntax.
>
>
> On Mon, Jan 14, 2019 at 3:09 PM Юрий <[hidden email]> wrote:
>
> > Hi Igniters,
> >
> > Earlier we discuss about columns for running queries. Let's summarize it
> > and continue discussion for not closed questions.
> >
> > What we had:
> > *name of view**: *running_queries
> > *columns and meaning*:
> >                        query_id -  unique id of query on node
> >                        node_id - initial node of request.
> >                        sql - text of query
> >                        schema_name - name of sql schema
> >                        duration - duration in milliseconds from start of
> > execution.
> >
> > All of this columns are clear, except query_id.
> > Let's keep in mind that the query_id column of the view coupled with KILL
> > QUERY command.
> >
> > We have the following variants what is query_id:
> > 1) It's string, internally with two parts separated by '.'(it can be
> other
> > separator): numeric node order and numeric query counter unique for local
> > node, e.g. '172.67321'. For this case query id will be really unique
> across
> > a cluster, but can be looks a strange for a user, especially in case we
> > will have ability to kill all queries on a node, when user should get
> first
> > part before separator to use it, e.g. KILL QUERY '172.*'.
> >
> > 2) Just single numeric id, unique for local node, e.g '127'. In this case
> > we need more complicated syntax for further KILL QUERY command, which
> lead
> > to use two columns from the view, e.g. KILL QUERY WHERE nodeId=
> > 37d7afd8-b87d-4aa1-b3d1-c1c033800000 and queryId=67321
> >
> > 3) Use base16String(concat(node,".",queryID) as query id, e.g. '
> > 3132332E393337'. Then we hide internal structure of id and such id will
> be
> > unique across a cluster. However we will need use complicated syntax for
> > KILL QUERY command as for 2nd case.
> >
> > 4) Just single numeric id, unique for local node, e.g '127'. But user
> > should use two columns to merge it and create query id unique in a
> cluster.
> > Such approach use  by Oracle:ALTER SYSTEM CANCEL SQL 'SID, SERIAL,
> SQL_ID'.
> > In this case user will know real meaning of each part of passed parameter
> > for KILL QUERY command. But it hard to use.
> >
> > 5) Any other approach you can think of....
> >
> > If be honestly I prefer first variant, it looks simple to use by user (it
> > require read a docs, but any case it required for any use cases).
> >
> > Lets discuss it again and chose better approach to expose query_id column
> > for Ignite. Also confirm list of columns.
> >
> > вт, 27 нояб. 2018 г. в 11:00, Vladimir Ozerov <[hidden email]>:
> >
> > > Yes ("нуы")
> > >
> > > On Tue, Nov 27, 2018 at 10:56 AM Павлухин Иван <[hidden email]>
> > > wrote:
> > >
> > > > I believe that the meaning was:
> > > >
> > > > > I propose to start with running queries VIEW first.
> > > > вт, 27 нояб. 2018 г. в 10:47, Vladimir Ozerov <[hidden email]
> >:
> > > > >
> > > > > I propose to start with running queries мшуц first. Once we have
> it,
> > it
> > > > > will be easier to agree on final command syntax.
> > > > >
> > > > > On Fri, Nov 23, 2018 at 9:32 AM Павлухин Иван <[hidden email]
> >
> > > > wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > May be I am a little bit late with my thoughts about a command
> > > syntax.
> > > > > > How do I see it is going to be used:
> > > > > > 1. A user is able to kill a query by unique id belonging only to
> > this
> > > > > > query.
> > > > > > 2. A user is able to kill all queries started by a specific node.
> > > > > > For killing a single query we must identify it by unique id which
> > is
> > > > > > going to be received directly from Ignite (e.g. running queries
> > view)
> > > > > > and not calculated by user. Internally the id is compound but why
> > > > > > cannot we convert it to opaque integer or string which hides real
> > > > > > structure? E.g. base16String(concat(nodeOrder.toString(), ".",
> > > > > > queryIdOnNode.toString())) The syntax could be KILL QUERY '123'
> or
> > > > > > KILL QUERY WHERE queryId = '123'
> > > > > > For killing all queries started by some node we need to use only
> > node
> > > > > > order (or id). It could be like KILL QUERY WHERE nodeOrder = 34.
> > > > > > чт, 22 нояб. 2018 г. в 12:56, Denis Mekhanikov <
> > > [hidden email]
> > > > >:
> > > > > > >
> > > > > > > Actually, option with separate parameters was mentioned in
> > another
> > > > thread
> > > > > > >
> > > > > >
> > > >
> > >
> >
> http://apache-ignite-developers.2346864.n4.nabble.com/proposed-design-for-thin-client-SQL-management-and-monitoring-view-running-queries-and-kill-it-tp37713p38056.html
> > > > > > >
> > > > > > > Denis
> > > > > > >
> > > > > > > чт, 22 нояб. 2018 г. в 08:51, Vladimir Ozerov <
> > > [hidden email]
> > > > >:
> > > > > > >
> > > > > > > > Denis,
> > > > > > > >
> > > > > > > > Problems with separate parameters are explained above.
> > > > > > > >
> > > > > > > > чт, 22 нояб. 2018 г. в 3:23, Denis Magda <[hidden email]
> >:
> > > > > > > >
> > > > > > > > > Vladimir,
> > > > > > > > >
> > > > > > > > > All of the alternatives are reminiscent of mathematical
> > > > operations.
> > > > > > Don't
> > > > > > > > > look like a SQL command. What if we use a SQL approach
> > > > introducing
> > > > > > named
> > > > > > > > > parameters:
> > > > > > > > >
> > > > > > > > > KILL QUERY query_id=10 [AND node_id=5]
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > > Denis
> > > > > > > > >
> > > > > > > > > On Wed, Nov 21, 2018 at 4:11 AM Vladimir Ozerov <
> > > > > > [hidden email]>
> > > > > > > > > wrote:
> > > > > > > > >
> > > > > > > > > > Denis,
> > > > > > > > > >
> > > > > > > > > > Space is bad candidate because it is a whitespace.
> Without
> > > > > > whitespaces
> > > > > > > > we
> > > > > > > > > > can have syntax without quotes at all. Any non-whitespace
> > > > delimiter
> > > > > > > > will
> > > > > > > > > > work, though:
> > > > > > > > > >
> > > > > > > > > > KILL QUERY 45.1
> > > > > > > > > > KILL QUERY 45-1
> > > > > > > > > > KILL QUERY 45:1
> > > > > > > > > >
> > > > > > > > > > On Wed, Nov 21, 2018 at 3:06 PM Юрий <
> > > > [hidden email]>
> > > > > > > > > wrote:
> > > > > > > > > >
> > > > > > > > > > > Denis,
> > > > > > > > > > >
> > > > > > > > > > > Let's consider parameter of KILL QUERY just a string
> with
> > > > some
> > > > > > query
> > > > > > > > > id,
> > > > > > > > > > > without any meaning for user. User just need to get the
> > id
> > > > and
> > > > > > pass
> > > > > > > > as
> > > > > > > > > > > parameter to KILL QUERY command.
> > > > > > > > > > >
> > > > > > > > > > > Even if query is distributed it have single query id
> from
> > > > user
> > > > > > > > > > perspective
> > > > > > > > > > > and will killed on all nodes. User just need to known
> one
> > > > global
> > > > > > > > query
> > > > > > > > > > id.
> > > > > > > > > > >
> > > > > > > > > > > How it can works.
> > > > > > > > > > > 1)SELECT * from running_queries
> > > > > > > > > > > result is
> > > > > > > > > > >  query_id | node_id
> > > > > > > > > > >   | sql               | schema_name | connection_id |
> > > > duration
> > > > > > > > > > > 123.33     | e0a69cb8-a1a8-45f6-b84d-ead367a00000   |
> > > SELECT
> > > > > > ...  |
> > > > > > > > ...
> > > > > > > > > > >                   |   22                 | 23456
> > > > > > > > > > > 333.31     | aaa6acb8-a4a5-42f6-f842-ead111b00020     |
> > > > > > UPDATE...  |
> > > > > > > > > ...
> > > > > > > > > > >                   |  321                | 3467777
> > > > > > > > > > > 2) KILL QUERY '123.33'
> > > > > > > > > > >
> > > > > > > > > > > So, user need select query_id from running_queries view
> > and
> > > > use
> > > > > > it
> > > > > > > > for
> > > > > > > > > > KILL
> > > > > > > > > > > QUERY command.
> > > > > > > > > > >
> > > > > > > > > > > I hope it became clearer.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > ср, 21 нояб. 2018 г. в 02:11, Denis Magda <
> > > [hidden email]
> > > > >:
> > > > > > > > > > >
> > > > > > > > > > > > Folks,
> > > > > > > > > > > >
> > > > > > > > > > > > The decimal syntax is really odd - KILL QUERY
> > > > > > > > > > > > '[node_order].[query_counter]'
> > > > > > > > > > > >
> > > > > > > > > > > > Confusing, let's use a space to separate parameters.
> > > > > > > > > > > >
> > > > > > > > > > > > Also, what if I want to halt a specific query with
> > > certain
> > > > ID?
> > > > > > > > Don't
> > > > > > > > > > know
> > > > > > > > > > > > the node number, just know that the query is
> > distributed
> > > > and
> > > > > > runs
> > > > > > > > > > across
> > > > > > > > > > > > several machines. Sounds like the syntax still should
> > > > consider
> > > > > > > > > > > > [node_order/id] as an optional parameter.
> > > > > > > > > > > >
> > > > > > > > > > > > Probably, if you explain to me how an end user will
> use
> > > > this
> > > > > > > > command
> > > > > > > > > > from
> > > > > > > > > > > > the very beginning (how do I look for a query id and
> > node
> > > > id,
> > > > > > etc)
> > > > > > > > > then
> > > > > > > > > > > the
> > > > > > > > > > > > things get clearer.
> > > > > > > > > > > >
> > > > > > > > > > > > --
> > > > > > > > > > > > Denis
> > > > > > > > > > > >
> > > > > > > > > > > > On Tue, Nov 20, 2018 at 1:03 AM Юрий <
> > > > > > [hidden email]>
> > > > > > > > > > > wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > > Hi Vladimir,
> > > > > > > > > > > > >
> > > > > > > > > > > > > Thanks for your suggestion to use MANAGEMENT_POOL
> for
> > > > > > processing
> > > > > > > > > > > > > cancellation requests.
> > > > > > > > > > > > >
> > > > > > > > > > > > > About your questions.
> > > > > > > > > > > > > 1) I'm going to implements SQL view to provide list
> > of
> > > > > > running
> > > > > > > > > > queries.
> > > > > > > > > > > > The
> > > > > > > > > > > > > SQL VIEW has been a little bit discussed earlier.
> > > > Proposed
> > > > > > name
> > > > > > > > is
> > > > > > > > > > > > > *running_queries* with following columns: query_id,
> > > > node_id,
> > > > > > sql,
> > > > > > > > > > > > > schema_name, connection_id, duration. Currently
> most
> > of
> > > > the
> > > > > > > > > > information
> > > > > > > > > > > > can
> > > > > > > > > > > > > be  retrieved through cache API, however it doesn't
> > > > matter,
> > > > > > any
> > > > > > > > > case
> > > > > > > > > > we
> > > > > > > > > > > > > need to expose SQL VIEW. Seem's you are right - the
> > > part
> > > > > > should
> > > > > > > > be
> > > > > > > > > > > > > implemented firstly.
> > > > > > > > > > > > > 2) Fully agree that we need to support all kind of
> > SQL
> > > > > > queries
> > > > > > > > > > > > > (SLECT/DML/DDL, transactional, non transnational,
> > > local,
> > > > > > > > > > distributed).
> > > > > > > > > > > I
> > > > > > > > > > > > > definitely sure that it will possible for all of
> > above,
> > > > > > however
> > > > > > > > I'm
> > > > > > > > > > not
> > > > > > > > > > > > > sure about DDL - need to investigate it deeper.
> Also
> > > > need to
> > > > > > > > > > understand
> > > > > > > > > > > > > that canceled DML operation can lead to partially
> > > updated
> > > > > > data
> > > > > > > > for
> > > > > > > > > > non
> > > > > > > > > > > > > transational caches.
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > > пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov <
> > > > > > > > > [hidden email]
> > > > > > > > > > >:
> > > > > > > > > > > > >
> > > > > > > > > > > > > > Hi Yuriy,
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > I think we can use MANAGEMENT_POOL for this. It
> is
> > > > already
> > > > > > used
> > > > > > > > > for
> > > > > > > > > > > > some
> > > > > > > > > > > > > > internal Ignite tasks, and it appears to be a
> good
> > > > > > candidate to
> > > > > > > > > > > process
> > > > > > > > > > > > > > cancel requests.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > But there are several things which are not clear
> > > enough
> > > > > > for me
> > > > > > > > at
> > > > > > > > > > the
> > > > > > > > > > > > > > moment:
> > > > > > > > > > > > > > 1) How user is going to get the list of running
> > > > queries in
> > > > > > the
> > > > > > > > > > first
> > > > > > > > > > > > > place?
> > > > > > > > > > > > > > Do we already have any SQL commands/views to get
> > this
> > > > > > > > > information?
> > > > > > > > > > > > > > 2) We need to ensure that KILL command will be
> > > > processed
> > > > > > > > properly
> > > > > > > > > > by
> > > > > > > > > > > > all
> > > > > > > > > > > > > > kinds of SQL queries - SELECT/DML/DDL,
> > > > non-transactional or
> > > > > > > > > > > > > transactional,
> > > > > > > > > > > > > > local queries and distributed queries. Will we be
> > > able
> > > > to
> > > > > > > > support
> > > > > > > > > > all
> > > > > > > > > > > > > these
> > > > > > > > > > > > > > modes?
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Vladimir.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > On Mon, Nov 19, 2018 at 6:37 PM Юрий <
> > > > > > > > > [hidden email]>
> > > > > > > > > > > > > wrote:
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Hi Igniters,
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Earlier we agreed about syntax KILL QUERY
> > > > > > > > > > > > > '[node_order].[query_counter]',
> > > > > > > > > > > > > > > e.g. KILL QUERY '25.123' for single query  or
> > KILL
> > > > QUERY
> > > > > > > > '25.*'
> > > > > > > > > > for
> > > > > > > > > > > > all
> > > > > > > > > > > > > > > queries on the node. Which is part of IEP-29
> > > > > > > > > > > > > > > <
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > .
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Now I want to discuss internal realization of
> > KILL
> > > > query
> > > > > > > > > feature.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > My current vision is following:
> > > > > > > > > > > > > > > After parsing, Ignite create KILL query command
> > > with
> > > > two
> > > > > > > > > > > parameters:
> > > > > > > > > > > > > > > nodeOrderId, nodeQryId. To determine that need
> to
> > > > kill
> > > > > > all
> > > > > > > > > > queries
> > > > > > > > > > > > on a
> > > > > > > > > > > > > > > node we can use negative value of query id, due
> > to
> > > > qry id
> > > > > > > > > always
> > > > > > > > > > > have
> > > > > > > > > > > > > > > positive values.
> > > > > > > > > > > > > > > The command process at IgniteH2Indexing as
> native
> > > > > > command.
> > > > > > > > > > > > > > > By nodeOrderId we find node which initial for
> the
> > > > query
> > > > > > and
> > > > > > > > > send
> > > > > > > > > > to
> > > > > > > > > > > > the
> > > > > > > > > > > > > > > node new GridQueryKillRequest with nodeQryId to
> > > > > > TOPIC_QUERY
> > > > > > > > > with
> > > > > > > > > > > not
> > > > > > > > > > > > > > QUERY
> > > > > > > > > > > > > > > POOL executor.
> > > > > > > > > > > > > > > At GridReduceQueryExecutor we add support of
> > > > processing
> > > > > > new
> > > > > > > > > > > > > > > GridQueryKillRequest
> > > > > > > > > > > > > > > which just run already exists cancelQueries
> > method
> > > > with
> > > > > > given
> > > > > > > > > > qryId
> > > > > > > > > > > > or
> > > > > > > > > > > > > > with
> > > > > > > > > > > > > > > all qryIds which currently running at the node
> in
> > > > case at
> > > > > > > > > initial
> > > > > > > > > > > > KILL
> > > > > > > > > > > > > > > QUERY parameters used star symbol.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > I have a doubt which of thread pool we should
> use
> > > to
> > > > > > process
> > > > > > > > > > > > > > > GridQueryKillRequest.
> > > > > > > > > > > > > > > My opinion it shouldn't be QUERY pool, due to
> the
> > > > pool
> > > > > > can be
> > > > > > > > > > fully
> > > > > > > > > > > > > used
> > > > > > > > > > > > > > by
> > > > > > > > > > > > > > > executing queries, it such case we can't cancel
> > > query
> > > > > > > > > > immediately.
> > > > > > > > > > > > May
> > > > > > > > > > > > > we
> > > > > > > > > > > > > > > use one of already existed pool or create new
> > one?
> > > Or
> > > > > > may be
> > > > > > > > > I'm
> > > > > > > > > > > > > mistaken
> > > > > > > > > > > > > > > and it should use QUERY pool.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > What do you think about proposed plan of
> > > > implementation?
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > And please give comments about which of thread
> > pool
> > > > will
> > > > > > be
> > > > > > > > > > better
> > > > > > > > > > > to
> > > > > > > > > > > > > use
> > > > > > > > > > > > > > > for kill query requests. It's small, but really
> > > > important
> > > > > > > > part
> > > > > > > > > of
> > > > > > > > > > > the
> > > > > > > > > > > > > > > realization.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Thanks.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > --
> > > > > > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > > --
> > > > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > --
> > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Best regards,
> > > > > > Ivan Pavlukhin
> > > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > Best regards,
> > > > Ivan Pavlukhin
> > > >
> > >
> >
> >
> > --
> > Живи с улыбкой! :D
> >
>

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

[Vladimir Ozerov]

Hi,

Concatenation through a letter looks like a good approach to me. As far as
killing all queries on a specific node, I would put it aside for now - this
looks like a separate command with possibly different parameters.

On Tue, Jan 15, 2019 at 1:30 PM Юрий <[hidden email]> wrote:

> Thanks Vladimir for your thoughts.
>
> Based on it most convenient ways are first and third.
> But with some modifications:
> For first variant delimiter should be a letter, e.g. 123X15494, then it
> could be simple copy by user.
> For 3rd variant can be used convert both numeric to HEX and use a letter
> delimiter not included to HEX symbols (ABCDEF), in this case query id will
> be shorter and also can be simple copy by user. e.g. 7BX3C86 ( it the same
> value as used for first variant), instead of convert all value as string to
> base16 due to it will be really long value.
>
> Possible realization for the cases:
> 1) Concatenation node order id with query id with a letter delimiter.
>
> query_id = 1234X8753 , where *1234* - node order, *8753* - local node query
> counter. *X* - delimeter
>
> 2) Converting both node order id and query id to HEX.
>
> query_id =  7BX3C86,  value is concat(hex(node),"X",hex(queryID))
>
> For both variants we can use either simple or copmlex KILL QUERY syntax.
> Simple:
>
> KILL QUERY 7BX3C86 - for kill concrete query
> KILL QUERY 7B - for killing all queries on a node.  May be need extra
> symbols for such queries to avoid fault of user and kill all queries by
> mistake, like KILL QUERY 7B*
>
> Complex:
>
> KILL QUERY WHERE queryId=7BX3C86 - for killing concrete query.
>
> KILL QUERY WHERE nodeId=37d7afd8-b87d-4aa1-b3d1-c1c033800000  - for kill
> all running queries on a given node.
>
>
>
> What do you think?
>
>
> вт, 15 янв. 2019 г. в 11:20, Vladimir Ozerov <[hidden email]>:
>
> > Hi Yuriy,
> >
> > I think all proposed approaches might work. The question is what is the
> > most convenient from user perspective. Encoded values without special
> > characters are good because they are easy to copy with mouse
> (double-click)
> > or keyboard (Ctrl+Shift+arrow). On the other hand, ability to identify
> > ID/name of suspicious node from query ID is also a good thing. Several
> > examples of query ID:
> >
> > CockroachDB: 14dacc1f9a781e3d0000000000000001
> > MongoDB: shardB:79014
> >
> > Also it is important that the same query ID is printed in various log
> > messages. This will be very useful for debugging purposes, e.g. grep over
> > logs. So ideally query ID should not have any symbols which interfere
> with
> > grep syntax.
> >
> >
> > On Mon, Jan 14, 2019 at 3:09 PM Юрий <[hidden email]>
> wrote:
> >
> > > Hi Igniters,
> > >
> > > Earlier we discuss about columns for running queries. Let's summarize
> it
> > > and continue discussion for not closed questions.
> > >
> > > What we had:
> > > *name of view**: *running_queries
> > > *columns and meaning*:
> > >                        query_id -  unique id of query on node
> > >                        node_id - initial node of request.
> > >                        sql - text of query
> > >                        schema_name - name of sql schema
> > >                        duration - duration in milliseconds from start
> of
> > > execution.
> > >
> > > All of this columns are clear, except query_id.
> > > Let's keep in mind that the query_id column of the view coupled with
> KILL
> > > QUERY command.
> > >
> > > We have the following variants what is query_id:
> > > 1) It's string, internally with two parts separated by '.'(it can be
> > other
> > > separator): numeric node order and numeric query counter unique for
> local
> > > node, e.g. '172.67321'. For this case query id will be really unique
> > across
> > > a cluster, but can be looks a strange for a user, especially in case we
> > > will have ability to kill all queries on a node, when user should get
> > first
> > > part before separator to use it, e.g. KILL QUERY '172.*'.
> > >
> > > 2) Just single numeric id, unique for local node, e.g '127'. In this
> case
> > > we need more complicated syntax for further KILL QUERY command, which
> > lead
> > > to use two columns from the view, e.g. KILL QUERY WHERE nodeId=
> > > 37d7afd8-b87d-4aa1-b3d1-c1c033800000 and queryId=67321
> > >
> > > 3) Use base16String(concat(node,".",queryID) as query id, e.g. '
> > > 3132332E393337'. Then we hide internal structure of id and such id will
> > be
> > > unique across a cluster. However we will need use complicated syntax
> for
> > > KILL QUERY command as for 2nd case.
> > >
> > > 4) Just single numeric id, unique for local node, e.g '127'. But user
> > > should use two columns to merge it and create query id unique in a
> > cluster.
> > > Such approach use  by Oracle:ALTER SYSTEM CANCEL SQL 'SID, SERIAL,
> > SQL_ID'.
> > > In this case user will know real meaning of each part of passed
> parameter
> > > for KILL QUERY command. But it hard to use.
> > >
> > > 5) Any other approach you can think of....
> > >
> > > If be honestly I prefer first variant, it looks simple to use by user
> (it
> > > require read a docs, but any case it required for any use cases).
> > >
> > > Lets discuss it again and chose better approach to expose query_id
> column
> > > for Ignite. Also confirm list of columns.
> > >
> > > вт, 27 нояб. 2018 г. в 11:00, Vladimir Ozerov <[hidden email]>:
> > >
> > > > Yes ("нуы")
> > > >
> > > > On Tue, Nov 27, 2018 at 10:56 AM Павлухин Иван <[hidden email]>
> > > > wrote:
> > > >
> > > > > I believe that the meaning was:
> > > > >
> > > > > > I propose to start with running queries VIEW first.
> > > > > вт, 27 нояб. 2018 г. в 10:47, Vladimir Ozerov <
> [hidden email]
> > >:
> > > > > >
> > > > > > I propose to start with running queries мшуц first. Once we have
> > it,
> > > it
> > > > > > will be easier to agree on final command syntax.
> > > > > >
> > > > > > On Fri, Nov 23, 2018 at 9:32 AM Павлухин Иван <
> [hidden email]
> > >
> > > > > wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > May be I am a little bit late with my thoughts about a command
> > > > syntax.
> > > > > > > How do I see it is going to be used:
> > > > > > > 1. A user is able to kill a query by unique id belonging only
> to
> > > this
> > > > > > > query.
> > > > > > > 2. A user is able to kill all queries started by a specific
> node.
> > > > > > > For killing a single query we must identify it by unique id
> which
> > > is
> > > > > > > going to be received directly from Ignite (e.g. running queries
> > > view)
> > > > > > > and not calculated by user. Internally the id is compound but
> why
> > > > > > > cannot we convert it to opaque integer or string which hides
> real
> > > > > > > structure? E.g. base16String(concat(nodeOrder.toString(), ".",
> > > > > > > queryIdOnNode.toString())) The syntax could be KILL QUERY '123'
> > or
> > > > > > > KILL QUERY WHERE queryId = '123'
> > > > > > > For killing all queries started by some node we need to use
> only
> > > node
> > > > > > > order (or id). It could be like KILL QUERY WHERE nodeOrder =
> 34.
> > > > > > > чт, 22 нояб. 2018 г. в 12:56, Denis Mekhanikov <
> > > > [hidden email]
> > > > > >:
> > > > > > > >
> > > > > > > > Actually, option with separate parameters was mentioned in
> > > another
> > > > > thread
> > > > > > > >
> > > > > > >
> > > > >
> > > >
> > >
> >
> http://apache-ignite-developers.2346864.n4.nabble.com/proposed-design-for-thin-client-SQL-management-and-monitoring-view-running-queries-and-kill-it-tp37713p38056.html
> > > > > > > >
> > > > > > > > Denis
> > > > > > > >
> > > > > > > > чт, 22 нояб. 2018 г. в 08:51, Vladimir Ozerov <
> > > > [hidden email]
> > > > > >:
> > > > > > > >
> > > > > > > > > Denis,
> > > > > > > > >
> > > > > > > > > Problems with separate parameters are explained above.
> > > > > > > > >
> > > > > > > > > чт, 22 нояб. 2018 г. в 3:23, Denis Magda <
> [hidden email]
> > >:
> > > > > > > > >
> > > > > > > > > > Vladimir,
> > > > > > > > > >
> > > > > > > > > > All of the alternatives are reminiscent of mathematical
> > > > > operations.
> > > > > > > Don't
> > > > > > > > > > look like a SQL command. What if we use a SQL approach
> > > > > introducing
> > > > > > > named
> > > > > > > > > > parameters:
> > > > > > > > > >
> > > > > > > > > > KILL QUERY query_id=10 [AND node_id=5]
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > > Denis
> > > > > > > > > >
> > > > > > > > > > On Wed, Nov 21, 2018 at 4:11 AM Vladimir Ozerov <
> > > > > > > [hidden email]>
> > > > > > > > > > wrote:
> > > > > > > > > >
> > > > > > > > > > > Denis,
> > > > > > > > > > >
> > > > > > > > > > > Space is bad candidate because it is a whitespace.
> > Without
> > > > > > > whitespaces
> > > > > > > > > we
> > > > > > > > > > > can have syntax without quotes at all. Any
> non-whitespace
> > > > > delimiter
> > > > > > > > > will
> > > > > > > > > > > work, though:
> > > > > > > > > > >
> > > > > > > > > > > KILL QUERY 45.1
> > > > > > > > > > > KILL QUERY 45-1
> > > > > > > > > > > KILL QUERY 45:1
> > > > > > > > > > >
> > > > > > > > > > > On Wed, Nov 21, 2018 at 3:06 PM Юрий <
> > > > > [hidden email]>
> > > > > > > > > > wrote:
> > > > > > > > > > >
> > > > > > > > > > > > Denis,
> > > > > > > > > > > >
> > > > > > > > > > > > Let's consider parameter of KILL QUERY just a string
> > with
> > > > > some
> > > > > > > query
> > > > > > > > > > id,
> > > > > > > > > > > > without any meaning for user. User just need to get
> the
> > > id
> > > > > and
> > > > > > > pass
> > > > > > > > > as
> > > > > > > > > > > > parameter to KILL QUERY command.
> > > > > > > > > > > >
> > > > > > > > > > > > Even if query is distributed it have single query id
> > from
> > > > > user
> > > > > > > > > > > perspective
> > > > > > > > > > > > and will killed on all nodes. User just need to known
> > one
> > > > > global
> > > > > > > > > query
> > > > > > > > > > > id.
> > > > > > > > > > > >
> > > > > > > > > > > > How it can works.
> > > > > > > > > > > > 1)SELECT * from running_queries
> > > > > > > > > > > > result is
> > > > > > > > > > > >  query_id | node_id
> > > > > > > > > > > >   | sql               | schema_name | connection_id |
> > > > > duration
> > > > > > > > > > > > 123.33     | e0a69cb8-a1a8-45f6-b84d-ead367a00000   |
> > > > SELECT
> > > > > > > ...  |
> > > > > > > > > ...
> > > > > > > > > > > >                   |   22                 | 23456
> > > > > > > > > > > > 333.31     | aaa6acb8-a4a5-42f6-f842-ead111b00020
>  |
> > > > > > > UPDATE...  |
> > > > > > > > > > ...
> > > > > > > > > > > >                   |  321                | 3467777
> > > > > > > > > > > > 2) KILL QUERY '123.33'
> > > > > > > > > > > >
> > > > > > > > > > > > So, user need select query_id from running_queries
> view
> > > and
> > > > > use
> > > > > > > it
> > > > > > > > > for
> > > > > > > > > > > KILL
> > > > > > > > > > > > QUERY command.
> > > > > > > > > > > >
> > > > > > > > > > > > I hope it became clearer.
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > ср, 21 нояб. 2018 г. в 02:11, Denis Magda <
> > > > [hidden email]
> > > > > >:
> > > > > > > > > > > >
> > > > > > > > > > > > > Folks,
> > > > > > > > > > > > >
> > > > > > > > > > > > > The decimal syntax is really odd - KILL QUERY
> > > > > > > > > > > > > '[node_order].[query_counter]'
> > > > > > > > > > > > >
> > > > > > > > > > > > > Confusing, let's use a space to separate
> parameters.
> > > > > > > > > > > > >
> > > > > > > > > > > > > Also, what if I want to halt a specific query with
> > > > certain
> > > > > ID?
> > > > > > > > > Don't
> > > > > > > > > > > know
> > > > > > > > > > > > > the node number, just know that the query is
> > > distributed
> > > > > and
> > > > > > > runs
> > > > > > > > > > > across
> > > > > > > > > > > > > several machines. Sounds like the syntax still
> should
> > > > > consider
> > > > > > > > > > > > > [node_order/id] as an optional parameter.
> > > > > > > > > > > > >
> > > > > > > > > > > > > Probably, if you explain to me how an end user will
> > use
> > > > > this
> > > > > > > > > command
> > > > > > > > > > > from
> > > > > > > > > > > > > the very beginning (how do I look for a query id
> and
> > > node
> > > > > id,
> > > > > > > etc)
> > > > > > > > > > then
> > > > > > > > > > > > the
> > > > > > > > > > > > > things get clearer.
> > > > > > > > > > > > >
> > > > > > > > > > > > > --
> > > > > > > > > > > > > Denis
> > > > > > > > > > > > >
> > > > > > > > > > > > > On Tue, Nov 20, 2018 at 1:03 AM Юрий <
> > > > > > > [hidden email]>
> > > > > > > > > > > > wrote:
> > > > > > > > > > > > >
> > > > > > > > > > > > > > Hi Vladimir,
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Thanks for your suggestion to use MANAGEMENT_POOL
> > for
> > > > > > > processing
> > > > > > > > > > > > > > cancellation requests.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > About your questions.
> > > > > > > > > > > > > > 1) I'm going to implements SQL view to provide
> list
> > > of
> > > > > > > running
> > > > > > > > > > > queries.
> > > > > > > > > > > > > The
> > > > > > > > > > > > > > SQL VIEW has been a little bit discussed earlier.
> > > > > Proposed
> > > > > > > name
> > > > > > > > > is
> > > > > > > > > > > > > > *running_queries* with following columns:
> query_id,
> > > > > node_id,
> > > > > > > sql,
> > > > > > > > > > > > > > schema_name, connection_id, duration. Currently
> > most
> > > of
> > > > > the
> > > > > > > > > > > information
> > > > > > > > > > > > > can
> > > > > > > > > > > > > > be  retrieved through cache API, however it
> doesn't
> > > > > matter,
> > > > > > > any
> > > > > > > > > > case
> > > > > > > > > > > we
> > > > > > > > > > > > > > need to expose SQL VIEW. Seem's you are right -
> the
> > > > part
> > > > > > > should
> > > > > > > > > be
> > > > > > > > > > > > > > implemented firstly.
> > > > > > > > > > > > > > 2) Fully agree that we need to support all kind
> of
> > > SQL
> > > > > > > queries
> > > > > > > > > > > > > > (SLECT/DML/DDL, transactional, non transnational,
> > > > local,
> > > > > > > > > > > distributed).
> > > > > > > > > > > > I
> > > > > > > > > > > > > > definitely sure that it will possible for all of
> > > above,
> > > > > > > however
> > > > > > > > > I'm
> > > > > > > > > > > not
> > > > > > > > > > > > > > sure about DDL - need to investigate it deeper.
> > Also
> > > > > need to
> > > > > > > > > > > understand
> > > > > > > > > > > > > > that canceled DML operation can lead to partially
> > > > updated
> > > > > > > data
> > > > > > > > > for
> > > > > > > > > > > non
> > > > > > > > > > > > > > transational caches.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov <
> > > > > > > > > > [hidden email]
> > > > > > > > > > > >:
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Hi Yuriy,
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > I think we can use MANAGEMENT_POOL for this. It
> > is
> > > > > already
> > > > > > > used
> > > > > > > > > > for
> > > > > > > > > > > > > some
> > > > > > > > > > > > > > > internal Ignite tasks, and it appears to be a
> > good
> > > > > > > candidate to
> > > > > > > > > > > > process
> > > > > > > > > > > > > > > cancel requests.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > But there are several things which are not
> clear
> > > > enough
> > > > > > > for me
> > > > > > > > > at
> > > > > > > > > > > the
> > > > > > > > > > > > > > > moment:
> > > > > > > > > > > > > > > 1) How user is going to get the list of running
> > > > > queries in
> > > > > > > the
> > > > > > > > > > > first
> > > > > > > > > > > > > > place?
> > > > > > > > > > > > > > > Do we already have any SQL commands/views to
> get
> > > this
> > > > > > > > > > information?
> > > > > > > > > > > > > > > 2) We need to ensure that KILL command will be
> > > > > processed
> > > > > > > > > properly
> > > > > > > > > > > by
> > > > > > > > > > > > > all
> > > > > > > > > > > > > > > kinds of SQL queries - SELECT/DML/DDL,
> > > > > non-transactional or
> > > > > > > > > > > > > > transactional,
> > > > > > > > > > > > > > > local queries and distributed queries. Will we
> be
> > > > able
> > > > > to
> > > > > > > > > support
> > > > > > > > > > > all
> > > > > > > > > > > > > > these
> > > > > > > > > > > > > > > modes?
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Vladimir.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > On Mon, Nov 19, 2018 at 6:37 PM Юрий <
> > > > > > > > > > [hidden email]>
> > > > > > > > > > > > > > wrote:
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > Hi Igniters,
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > Earlier we agreed about syntax KILL QUERY
> > > > > > > > > > > > > > '[node_order].[query_counter]',
> > > > > > > > > > > > > > > > e.g. KILL QUERY '25.123' for single query  or
> > > KILL
> > > > > QUERY
> > > > > > > > > '25.*'
> > > > > > > > > > > for
> > > > > > > > > > > > > all
> > > > > > > > > > > > > > > > queries on the node. Which is part of IEP-29
> > > > > > > > > > > > > > > > <
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > .
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > Now I want to discuss internal realization of
> > > KILL
> > > > > query
> > > > > > > > > > feature.
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > My current vision is following:
> > > > > > > > > > > > > > > > After parsing, Ignite create KILL query
> command
> > > > with
> > > > > two
> > > > > > > > > > > > parameters:
> > > > > > > > > > > > > > > > nodeOrderId, nodeQryId. To determine that
> need
> > to
> > > > > kill
> > > > > > > all
> > > > > > > > > > > queries
> > > > > > > > > > > > > on a
> > > > > > > > > > > > > > > > node we can use negative value of query id,
> due
> > > to
> > > > > qry id
> > > > > > > > > > always
> > > > > > > > > > > > have
> > > > > > > > > > > > > > > > positive values.
> > > > > > > > > > > > > > > > The command process at IgniteH2Indexing as
> > native
> > > > > > > command.
> > > > > > > > > > > > > > > > By nodeOrderId we find node which initial for
> > the
> > > > > query
> > > > > > > and
> > > > > > > > > > send
> > > > > > > > > > > to
> > > > > > > > > > > > > the
> > > > > > > > > > > > > > > > node new GridQueryKillRequest with nodeQryId
> to
> > > > > > > TOPIC_QUERY
> > > > > > > > > > with
> > > > > > > > > > > > not
> > > > > > > > > > > > > > > QUERY
> > > > > > > > > > > > > > > > POOL executor.
> > > > > > > > > > > > > > > > At GridReduceQueryExecutor we add support of
> > > > > processing
> > > > > > > new
> > > > > > > > > > > > > > > > GridQueryKillRequest
> > > > > > > > > > > > > > > > which just run already exists cancelQueries
> > > method
> > > > > with
> > > > > > > given
> > > > > > > > > > > qryId
> > > > > > > > > > > > > or
> > > > > > > > > > > > > > > with
> > > > > > > > > > > > > > > > all qryIds which currently running at the
> node
> > in
> > > > > case at
> > > > > > > > > > initial
> > > > > > > > > > > > > KILL
> > > > > > > > > > > > > > > > QUERY parameters used star symbol.
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > I have a doubt which of thread pool we should
> > use
> > > > to
> > > > > > > process
> > > > > > > > > > > > > > > > GridQueryKillRequest.
> > > > > > > > > > > > > > > > My opinion it shouldn't be QUERY pool, due to
> > the
> > > > > pool
> > > > > > > can be
> > > > > > > > > > > fully
> > > > > > > > > > > > > > used
> > > > > > > > > > > > > > > by
> > > > > > > > > > > > > > > > executing queries, it such case we can't
> cancel
> > > > query
> > > > > > > > > > > immediately.
> > > > > > > > > > > > > May
> > > > > > > > > > > > > > we
> > > > > > > > > > > > > > > > use one of already existed pool or create new
> > > one?
> > > > Or
> > > > > > > may be
> > > > > > > > > > I'm
> > > > > > > > > > > > > > mistaken
> > > > > > > > > > > > > > > > and it should use QUERY pool.
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > What do you think about proposed plan of
> > > > > implementation?
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > And please give comments about which of
> thread
> > > pool
> > > > > will
> > > > > > > be
> > > > > > > > > > > better
> > > > > > > > > > > > to
> > > > > > > > > > > > > > use
> > > > > > > > > > > > > > > > for kill query requests. It's small, but
> really
> > > > > important
> > > > > > > > > part
> > > > > > > > > > of
> > > > > > > > > > > > the
> > > > > > > > > > > > > > > > realization.
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > Thanks.
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > --
> > > > > > > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > --
> > > > > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > --
> > > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Best regards,
> > > > > > > Ivan Pavlukhin
> > > > > > >
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > Best regards,
> > > > > Ivan Pavlukhin
> > > > >
> > > >
> > >
> > >
> > > --
> > > Живи с улыбкой! :D
> > >
> >
>
>
> --
> Живи с улыбкой! :D
>

[Юрий]

Denis and other Igniters, do you have any comments for proposed approach?
Which of these ones will be better to use for us - simple numeric  or hex
values (shorter id, but with letters)?

As for me hex values preferable due to it  shorter and looks more unique
across a logs



вт, 15 янв. 2019 г. в 18:35, Vladimir Ozerov <[hidden email]>:

> Hi,
>
> Concatenation through a letter looks like a good approach to me. As far as
> killing all queries on a specific node, I would put it aside for now - this
> looks like a separate command with possibly different parameters.
>
> On Tue, Jan 15, 2019 at 1:30 PM Юрий <[hidden email]> wrote:
>
> > Thanks Vladimir for your thoughts.
> >
> > Based on it most convenient ways are first and third.
> > But with some modifications:
> > For first variant delimiter should be a letter, e.g. 123X15494, then it
> > could be simple copy by user.
> > For 3rd variant can be used convert both numeric to HEX and use a letter
> > delimiter not included to HEX symbols (ABCDEF), in this case query id
> will
> > be shorter and also can be simple copy by user. e.g. 7BX3C86 ( it the
> same
> > value as used for first variant), instead of convert all value as string
> to
> > base16 due to it will be really long value.
> >
> > Possible realization for the cases:
> > 1) Concatenation node order id with query id with a letter delimiter.
> >
> > query_id = 1234X8753 , where *1234* - node order, *8753* - local node
> query
> > counter. *X* - delimeter
> >
> > 2) Converting both node order id and query id to HEX.
> >
> > query_id =  7BX3C86,  value is concat(hex(node),"X",hex(queryID))
> >
> > For both variants we can use either simple or copmlex KILL QUERY syntax.
> > Simple:
> >
> > KILL QUERY 7BX3C86 - for kill concrete query
> > KILL QUERY 7B - for killing all queries on a node.  May be need extra
> > symbols for such queries to avoid fault of user and kill all queries by
> > mistake, like KILL QUERY 7B*
> >
> > Complex:
> >
> > KILL QUERY WHERE queryId=7BX3C86 - for killing concrete query.
> >
> > KILL QUERY WHERE nodeId=37d7afd8-b87d-4aa1-b3d1-c1c033800000  - for kill
> > all running queries on a given node.
> >
> >
> >
> > What do you think?
> >
> >
> > вт, 15 янв. 2019 г. в 11:20, Vladimir Ozerov <[hidden email]>:
> >
> > > Hi Yuriy,
> > >
> > > I think all proposed approaches might work. The question is what is the
> > > most convenient from user perspective. Encoded values without special
> > > characters are good because they are easy to copy with mouse
> > (double-click)
> > > or keyboard (Ctrl+Shift+arrow). On the other hand, ability to identify
> > > ID/name of suspicious node from query ID is also a good thing. Several
> > > examples of query ID:
> > >
> > > CockroachDB: 14dacc1f9a781e3d0000000000000001
> > > MongoDB: shardB:79014
> > >
> > > Also it is important that the same query ID is printed in various log
> > > messages. This will be very useful for debugging purposes, e.g. grep
> over
> > > logs. So ideally query ID should not have any symbols which interfere
> > with
> > > grep syntax.
> > >
> > >
> > > On Mon, Jan 14, 2019 at 3:09 PM Юрий <[hidden email]>
> > wrote:
> > >
> > > > Hi Igniters,
> > > >
> > > > Earlier we discuss about columns for running queries. Let's summarize
> > it
> > > > and continue discussion for not closed questions.
> > > >
> > > > What we had:
> > > > *name of view**: *running_queries
> > > > *columns and meaning*:
> > > >                        query_id -  unique id of query on node
> > > >                        node_id - initial node of request.
> > > >                        sql - text of query
> > > >                        schema_name - name of sql schema
> > > >                        duration - duration in milliseconds from start
> > of
> > > > execution.
> > > >
> > > > All of this columns are clear, except query_id.
> > > > Let's keep in mind that the query_id column of the view coupled with
> > KILL
> > > > QUERY command.
> > > >
> > > > We have the following variants what is query_id:
> > > > 1) It's string, internally with two parts separated by '.'(it can be
> > > other
> > > > separator): numeric node order and numeric query counter unique for
> > local
> > > > node, e.g. '172.67321'. For this case query id will be really unique
> > > across
> > > > a cluster, but can be looks a strange for a user, especially in case
> we
> > > > will have ability to kill all queries on a node, when user should get
> > > first
> > > > part before separator to use it, e.g. KILL QUERY '172.*'.
> > > >
> > > > 2) Just single numeric id, unique for local node, e.g '127'. In this
> > case
> > > > we need more complicated syntax for further KILL QUERY command, which
> > > lead
> > > > to use two columns from the view, e.g. KILL QUERY WHERE nodeId=
> > > > 37d7afd8-b87d-4aa1-b3d1-c1c033800000 and queryId=67321
> > > >
> > > > 3) Use base16String(concat(node,".",queryID) as query id, e.g. '
> > > > 3132332E393337'. Then we hide internal structure of id and such id
> will
> > > be
> > > > unique across a cluster. However we will need use complicated syntax
> > for
> > > > KILL QUERY command as for 2nd case.
> > > >
> > > > 4) Just single numeric id, unique for local node, e.g '127'. But user
> > > > should use two columns to merge it and create query id unique in a
> > > cluster.
> > > > Such approach use  by Oracle:ALTER SYSTEM CANCEL SQL 'SID, SERIAL,
> > > SQL_ID'.
> > > > In this case user will know real meaning of each part of passed
> > parameter
> > > > for KILL QUERY command. But it hard to use.
> > > >
> > > > 5) Any other approach you can think of....
> > > >
> > > > If be honestly I prefer first variant, it looks simple to use by user
> > (it
> > > > require read a docs, but any case it required for any use cases).
> > > >
> > > > Lets discuss it again and chose better approach to expose query_id
> > column
> > > > for Ignite. Also confirm list of columns.
> > > >
> > > > вт, 27 нояб. 2018 г. в 11:00, Vladimir Ozerov <[hidden email]
> >:
> > > >
> > > > > Yes ("нуы")
> > > > >
> > > > > On Tue, Nov 27, 2018 at 10:56 AM Павлухин Иван <
> [hidden email]>
> > > > > wrote:
> > > > >
> > > > > > I believe that the meaning was:
> > > > > >
> > > > > > > I propose to start with running queries VIEW first.
> > > > > > вт, 27 нояб. 2018 г. в 10:47, Vladimir Ozerov <
> > [hidden email]
> > > >:
> > > > > > >
> > > > > > > I propose to start with running queries мшуц first. Once we
> have
> > > it,
> > > > it
> > > > > > > will be easier to agree on final command syntax.
> > > > > > >
> > > > > > > On Fri, Nov 23, 2018 at 9:32 AM Павлухин Иван <
> > [hidden email]
> > > >
> > > > > > wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > May be I am a little bit late with my thoughts about a
> command
> > > > > syntax.
> > > > > > > > How do I see it is going to be used:
> > > > > > > > 1. A user is able to kill a query by unique id belonging only
> > to
> > > > this
> > > > > > > > query.
> > > > > > > > 2. A user is able to kill all queries started by a specific
> > node.
> > > > > > > > For killing a single query we must identify it by unique id
> > which
> > > > is
> > > > > > > > going to be received directly from Ignite (e.g. running
> queries
> > > > view)
> > > > > > > > and not calculated by user. Internally the id is compound but
> > why
> > > > > > > > cannot we convert it to opaque integer or string which hides
> > real
> > > > > > > > structure? E.g. base16String(concat(nodeOrder.toString(),
> ".",
> > > > > > > > queryIdOnNode.toString())) The syntax could be KILL QUERY
> '123'
> > > or
> > > > > > > > KILL QUERY WHERE queryId = '123'
> > > > > > > > For killing all queries started by some node we need to use
> > only
> > > > node
> > > > > > > > order (or id). It could be like KILL QUERY WHERE nodeOrder =
> > 34.
> > > > > > > > чт, 22 нояб. 2018 г. в 12:56, Denis Mekhanikov <
> > > > > [hidden email]
> > > > > > >:
> > > > > > > > >
> > > > > > > > > Actually, option with separate parameters was mentioned in
> > > > another
> > > > > > thread
> > > > > > > > >
> > > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> http://apache-ignite-developers.2346864.n4.nabble.com/proposed-design-for-thin-client-SQL-management-and-monitoring-view-running-queries-and-kill-it-tp37713p38056.html
> > > > > > > > >
> > > > > > > > > Denis
> > > > > > > > >
> > > > > > > > > чт, 22 нояб. 2018 г. в 08:51, Vladimir Ozerov <
> > > > > [hidden email]
> > > > > > >:
> > > > > > > > >
> > > > > > > > > > Denis,
> > > > > > > > > >
> > > > > > > > > > Problems with separate parameters are explained above.
> > > > > > > > > >
> > > > > > > > > > чт, 22 нояб. 2018 г. в 3:23, Denis Magda <
> > [hidden email]
> > > >:
> > > > > > > > > >
> > > > > > > > > > > Vladimir,
> > > > > > > > > > >
> > > > > > > > > > > All of the alternatives are reminiscent of mathematical
> > > > > > operations.
> > > > > > > > Don't
> > > > > > > > > > > look like a SQL command. What if we use a SQL approach
> > > > > > introducing
> > > > > > > > named
> > > > > > > > > > > parameters:
> > > > > > > > > > >
> > > > > > > > > > > KILL QUERY query_id=10 [AND node_id=5]
> > > > > > > > > > >
> > > > > > > > > > > --
> > > > > > > > > > > Denis
> > > > > > > > > > >
> > > > > > > > > > > On Wed, Nov 21, 2018 at 4:11 AM Vladimir Ozerov <
> > > > > > > > [hidden email]>
> > > > > > > > > > > wrote:
> > > > > > > > > > >
> > > > > > > > > > > > Denis,
> > > > > > > > > > > >
> > > > > > > > > > > > Space is bad candidate because it is a whitespace.
> > > Without
> > > > > > > > whitespaces
> > > > > > > > > > we
> > > > > > > > > > > > can have syntax without quotes at all. Any
> > non-whitespace
> > > > > > delimiter
> > > > > > > > > > will
> > > > > > > > > > > > work, though:
> > > > > > > > > > > >
> > > > > > > > > > > > KILL QUERY 45.1
> > > > > > > > > > > > KILL QUERY 45-1
> > > > > > > > > > > > KILL QUERY 45:1
> > > > > > > > > > > >
> > > > > > > > > > > > On Wed, Nov 21, 2018 at 3:06 PM Юрий <
> > > > > > [hidden email]>
> > > > > > > > > > > wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > > Denis,
> > > > > > > > > > > > >
> > > > > > > > > > > > > Let's consider parameter of KILL QUERY just a
> string
> > > with
> > > > > > some
> > > > > > > > query
> > > > > > > > > > > id,
> > > > > > > > > > > > > without any meaning for user. User just need to get
> > the
> > > > id
> > > > > > and
> > > > > > > > pass
> > > > > > > > > > as
> > > > > > > > > > > > > parameter to KILL QUERY command.
> > > > > > > > > > > > >
> > > > > > > > > > > > > Even if query is distributed it have single query
> id
> > > from
> > > > > > user
> > > > > > > > > > > > perspective
> > > > > > > > > > > > > and will killed on all nodes. User just need to
> known
> > > one
> > > > > > global
> > > > > > > > > > query
> > > > > > > > > > > > id.
> > > > > > > > > > > > >
> > > > > > > > > > > > > How it can works.
> > > > > > > > > > > > > 1)SELECT * from running_queries
> > > > > > > > > > > > > result is
> > > > > > > > > > > > >  query_id | node_id
> > > > > > > > > > > > >   | sql               | schema_name |
> connection_id |
> > > > > > duration
> > > > > > > > > > > > > 123.33     | e0a69cb8-a1a8-45f6-b84d-ead367a00000
>  |
> > > > > SELECT
> > > > > > > > ...  |
> > > > > > > > > > ...
> > > > > > > > > > > > >                   |   22                 | 23456
> > > > > > > > > > > > > 333.31     | aaa6acb8-a4a5-42f6-f842-ead111b00020
> >  |
> > > > > > > > UPDATE...  |
> > > > > > > > > > > ...
> > > > > > > > > > > > >                   |  321                | 3467777
> > > > > > > > > > > > > 2) KILL QUERY '123.33'
> > > > > > > > > > > > >
> > > > > > > > > > > > > So, user need select query_id from running_queries
> > view
> > > > and
> > > > > > use
> > > > > > > > it
> > > > > > > > > > for
> > > > > > > > > > > > KILL
> > > > > > > > > > > > > QUERY command.
> > > > > > > > > > > > >
> > > > > > > > > > > > > I hope it became clearer.
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > > ср, 21 нояб. 2018 г. в 02:11, Denis Magda <
> > > > > [hidden email]
> > > > > > >:
> > > > > > > > > > > > >
> > > > > > > > > > > > > > Folks,
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > The decimal syntax is really odd - KILL QUERY
> > > > > > > > > > > > > > '[node_order].[query_counter]'
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Confusing, let's use a space to separate
> > parameters.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Also, what if I want to halt a specific query
> with
> > > > > certain
> > > > > > ID?
> > > > > > > > > > Don't
> > > > > > > > > > > > know
> > > > > > > > > > > > > > the node number, just know that the query is
> > > > distributed
> > > > > > and
> > > > > > > > runs
> > > > > > > > > > > > across
> > > > > > > > > > > > > > several machines. Sounds like the syntax still
> > should
> > > > > > consider
> > > > > > > > > > > > > > [node_order/id] as an optional parameter.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Probably, if you explain to me how an end user
> will
> > > use
> > > > > > this
> > > > > > > > > > command
> > > > > > > > > > > > from
> > > > > > > > > > > > > > the very beginning (how do I look for a query id
> > and
> > > > node
> > > > > > id,
> > > > > > > > etc)
> > > > > > > > > > > then
> > > > > > > > > > > > > the
> > > > > > > > > > > > > > things get clearer.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > --
> > > > > > > > > > > > > > Denis
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > On Tue, Nov 20, 2018 at 1:03 AM Юрий <
> > > > > > > > [hidden email]>
> > > > > > > > > > > > > wrote:
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Hi Vladimir,
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Thanks for your suggestion to use
> MANAGEMENT_POOL
> > > for
> > > > > > > > processing
> > > > > > > > > > > > > > > cancellation requests.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > About your questions.
> > > > > > > > > > > > > > > 1) I'm going to implements SQL view to provide
> > list
> > > > of
> > > > > > > > running
> > > > > > > > > > > > queries.
> > > > > > > > > > > > > > The
> > > > > > > > > > > > > > > SQL VIEW has been a little bit discussed
> earlier.
> > > > > > Proposed
> > > > > > > > name
> > > > > > > > > > is
> > > > > > > > > > > > > > > *running_queries* with following columns:
> > query_id,
> > > > > > node_id,
> > > > > > > > sql,
> > > > > > > > > > > > > > > schema_name, connection_id, duration. Currently
> > > most
> > > > of
> > > > > > the
> > > > > > > > > > > > information
> > > > > > > > > > > > > > can
> > > > > > > > > > > > > > > be  retrieved through cache API, however it
> > doesn't
> > > > > > matter,
> > > > > > > > any
> > > > > > > > > > > case
> > > > > > > > > > > > we
> > > > > > > > > > > > > > > need to expose SQL VIEW. Seem's you are right -
> > the
> > > > > part
> > > > > > > > should
> > > > > > > > > > be
> > > > > > > > > > > > > > > implemented firstly.
> > > > > > > > > > > > > > > 2) Fully agree that we need to support all kind
> > of
> > > > SQL
> > > > > > > > queries
> > > > > > > > > > > > > > > (SLECT/DML/DDL, transactional, non
> transnational,
> > > > > local,
> > > > > > > > > > > > distributed).
> > > > > > > > > > > > > I
> > > > > > > > > > > > > > > definitely sure that it will possible for all
> of
> > > > above,
> > > > > > > > however
> > > > > > > > > > I'm
> > > > > > > > > > > > not
> > > > > > > > > > > > > > > sure about DDL - need to investigate it deeper.
> > > Also
> > > > > > need to
> > > > > > > > > > > > understand
> > > > > > > > > > > > > > > that canceled DML operation can lead to
> partially
> > > > > updated
> > > > > > > > data
> > > > > > > > > > for
> > > > > > > > > > > > non
> > > > > > > > > > > > > > > transational caches.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov <
> > > > > > > > > > > [hidden email]
> > > > > > > > > > > > >:
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > Hi Yuriy,
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > I think we can use MANAGEMENT_POOL for this.
> It
> > > is
> > > > > > already
> > > > > > > > used
> > > > > > > > > > > for
> > > > > > > > > > > > > > some
> > > > > > > > > > > > > > > > internal Ignite tasks, and it appears to be a
> > > good
> > > > > > > > candidate to
> > > > > > > > > > > > > process
> > > > > > > > > > > > > > > > cancel requests.
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > But there are several things which are not
> > clear
> > > > > enough
> > > > > > > > for me
> > > > > > > > > > at
> > > > > > > > > > > > the
> > > > > > > > > > > > > > > > moment:
> > > > > > > > > > > > > > > > 1) How user is going to get the list of
> running
> > > > > > queries in
> > > > > > > > the
> > > > > > > > > > > > first
> > > > > > > > > > > > > > > place?
> > > > > > > > > > > > > > > > Do we already have any SQL commands/views to
> > get
> > > > this
> > > > > > > > > > > information?
> > > > > > > > > > > > > > > > 2) We need to ensure that KILL command will
> be
> > > > > > processed
> > > > > > > > > > properly
> > > > > > > > > > > > by
> > > > > > > > > > > > > > all
> > > > > > > > > > > > > > > > kinds of SQL queries - SELECT/DML/DDL,
> > > > > > non-transactional or
> > > > > > > > > > > > > > > transactional,
> > > > > > > > > > > > > > > > local queries and distributed queries. Will
> we
> > be
> > > > > able
> > > > > > to
> > > > > > > > > > support
> > > > > > > > > > > > all
> > > > > > > > > > > > > > > these
> > > > > > > > > > > > > > > > modes?
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > Vladimir.
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > On Mon, Nov 19, 2018 at 6:37 PM Юрий <
> > > > > > > > > > > [hidden email]>
> > > > > > > > > > > > > > > wrote:
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > Hi Igniters,
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > Earlier we agreed about syntax KILL QUERY
> > > > > > > > > > > > > > > '[node_order].[query_counter]',
> > > > > > > > > > > > > > > > > e.g. KILL QUERY '25.123' for single query
> or
> > > > KILL
> > > > > > QUERY
> > > > > > > > > > '25.*'
> > > > > > > > > > > > for
> > > > > > > > > > > > > > all
> > > > > > > > > > > > > > > > > queries on the node. Which is part of
> IEP-29
> > > > > > > > > > > > > > > > > <
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
> > > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > .
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > Now I want to discuss internal realization
> of
> > > > KILL
> > > > > > query
> > > > > > > > > > > feature.
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > My current vision is following:
> > > > > > > > > > > > > > > > > After parsing, Ignite create KILL query
> > command
> > > > > with
> > > > > > two
> > > > > > > > > > > > > parameters:
> > > > > > > > > > > > > > > > > nodeOrderId, nodeQryId. To determine that
> > need
> > > to
> > > > > > kill
> > > > > > > > all
> > > > > > > > > > > > queries
> > > > > > > > > > > > > > on a
> > > > > > > > > > > > > > > > > node we can use negative value of query id,
> > due
> > > > to
> > > > > > qry id
> > > > > > > > > > > always
> > > > > > > > > > > > > have
> > > > > > > > > > > > > > > > > positive values.
> > > > > > > > > > > > > > > > > The command process at IgniteH2Indexing as
> > > native
> > > > > > > > command.
> > > > > > > > > > > > > > > > > By nodeOrderId we find node which initial
> for
> > > the
> > > > > > query
> > > > > > > > and
> > > > > > > > > > > send
> > > > > > > > > > > > to
> > > > > > > > > > > > > > the
> > > > > > > > > > > > > > > > > node new GridQueryKillRequest with
> nodeQryId
> > to
> > > > > > > > TOPIC_QUERY
> > > > > > > > > > > with
> > > > > > > > > > > > > not
> > > > > > > > > > > > > > > > QUERY
> > > > > > > > > > > > > > > > > POOL executor.
> > > > > > > > > > > > > > > > > At GridReduceQueryExecutor we add support
> of
> > > > > > processing
> > > > > > > > new
> > > > > > > > > > > > > > > > > GridQueryKillRequest
> > > > > > > > > > > > > > > > > which just run already exists cancelQueries
> > > > method
> > > > > > with
> > > > > > > > given
> > > > > > > > > > > > qryId
> > > > > > > > > > > > > > or
> > > > > > > > > > > > > > > > with
> > > > > > > > > > > > > > > > > all qryIds which currently running at the
> > node
> > > in
> > > > > > case at
> > > > > > > > > > > initial
> > > > > > > > > > > > > > KILL
> > > > > > > > > > > > > > > > > QUERY parameters used star symbol.
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > I have a doubt which of thread pool we
> should
> > > use
> > > > > to
> > > > > > > > process
> > > > > > > > > > > > > > > > > GridQueryKillRequest.
> > > > > > > > > > > > > > > > > My opinion it shouldn't be QUERY pool, due
> to
> > > the
> > > > > > pool
> > > > > > > > can be
> > > > > > > > > > > > fully
> > > > > > > > > > > > > > > used
> > > > > > > > > > > > > > > > by
> > > > > > > > > > > > > > > > > executing queries, it such case we can't
> > cancel
> > > > > query
> > > > > > > > > > > > immediately.
> > > > > > > > > > > > > > May
> > > > > > > > > > > > > > > we
> > > > > > > > > > > > > > > > > use one of already existed pool or create
> new
> > > > one?
> > > > > Or
> > > > > > > > may be
> > > > > > > > > > > I'm
> > > > > > > > > > > > > > > mistaken
> > > > > > > > > > > > > > > > > and it should use QUERY pool.
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > What do you think about proposed plan of
> > > > > > implementation?
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > And please give comments about which of
> > thread
> > > > pool
> > > > > > will
> > > > > > > > be
> > > > > > > > > > > > better
> > > > > > > > > > > > > to
> > > > > > > > > > > > > > > use
> > > > > > > > > > > > > > > > > for kill query requests. It's small, but
> > really
> > > > > > important
> > > > > > > > > > part
> > > > > > > > > > > of
> > > > > > > > > > > > > the
> > > > > > > > > > > > > > > > > realization.
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > Thanks.
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > --
> > > > > > > > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > --
> > > > > > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > > --
> > > > > > > > > > > > > Живи с улыбкой! :D
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > > Best regards,
> > > > > > > > Ivan Pavlukhin
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Best regards,
> > > > > > Ivan Pavlukhin
> > > > > >
> > > > >
> > > >
> > > >
> > > > --
> > > > Живи с улыбкой! :D
> > > >
> > >
> >
> >
> > --
> > Живи с улыбкой! :D
> >
>

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

[dmagda]

Yury,

Please give me a couple of days to read through the thread and respond.
Pretty busy these days, bare with me.

-
Denis


On Tue, Jan 15, 2019 at 8:00 AM Юрий <[hidden email]> wrote:

> Denis and other Igniters, do you have any comments for proposed approach?
> Which of these ones will be better to use for us - simple numeric  or hex
> values (shorter id, but with letters)?
>
> As for me hex values preferable due to it  shorter and looks more unique
> across a logs
>
>
>
> вт, 15 янв. 2019 г. в 18:35, Vladimir Ozerov <[hidden email]>:
>
>> Hi,
>>
>> Concatenation through a letter looks like a good approach to me. As far as
>> killing all queries on a specific node, I would put it aside for now -
>> this
>> looks like a separate command with possibly different parameters.
>>
>> On Tue, Jan 15, 2019 at 1:30 PM Юрий <[hidden email]> wrote:
>>
>> > Thanks Vladimir for your thoughts.
>> >
>> > Based on it most convenient ways are first and third.
>> > But with some modifications:
>> > For first variant delimiter should be a letter, e.g. 123X15494, then it
>> > could be simple copy by user.
>> > For 3rd variant can be used convert both numeric to HEX and use a letter
>> > delimiter not included to HEX symbols (ABCDEF), in this case query id
>> will
>> > be shorter and also can be simple copy by user. e.g. 7BX3C86 ( it the
>> same
>> > value as used for first variant), instead of convert all value as
>> string to
>> > base16 due to it will be really long value.
>> >
>> > Possible realization for the cases:
>> > 1) Concatenation node order id with query id with a letter delimiter.
>> >
>> > query_id = 1234X8753 , where *1234* - node order, *8753* - local node
>> query
>> > counter. *X* - delimeter
>> >
>> > 2) Converting both node order id and query id to HEX.
>> >
>> > query_id =  7BX3C86,  value is concat(hex(node),"X",hex(queryID))
>> >
>> > For both variants we can use either simple or copmlex KILL QUERY syntax.
>> > Simple:
>> >
>> > KILL QUERY 7BX3C86 - for kill concrete query
>> > KILL QUERY 7B - for killing all queries on a node.  May be need extra
>> > symbols for such queries to avoid fault of user and kill all queries by
>> > mistake, like KILL QUERY 7B*
>> >
>> > Complex:
>> >
>> > KILL QUERY WHERE queryId=7BX3C86 - for killing concrete query.
>> >
>> > KILL QUERY WHERE nodeId=37d7afd8-b87d-4aa1-b3d1-c1c033800000  - for kill
>> > all running queries on a given node.
>> >
>> >
>> >
>> > What do you think?
>> >
>> >
>> > вт, 15 янв. 2019 г. в 11:20, Vladimir Ozerov <[hidden email]>:
>> >
>> > > Hi Yuriy,
>> > >
>> > > I think all proposed approaches might work. The question is what is
>> the
>> > > most convenient from user perspective. Encoded values without special
>> > > characters are good because they are easy to copy with mouse
>> > (double-click)
>> > > or keyboard (Ctrl+Shift+arrow). On the other hand, ability to identify
>> > > ID/name of suspicious node from query ID is also a good thing. Several
>> > > examples of query ID:
>> > >
>> > > CockroachDB: 14dacc1f9a781e3d0000000000000001
>> > > MongoDB: shardB:79014
>> > >
>> > > Also it is important that the same query ID is printed in various log
>> > > messages. This will be very useful for debugging purposes, e.g. grep
>> over
>> > > logs. So ideally query ID should not have any symbols which interfere
>> > with
>> > > grep syntax.
>> > >
>> > >
>> > > On Mon, Jan 14, 2019 at 3:09 PM Юрий <[hidden email]>
>> > wrote:
>> > >
>> > > > Hi Igniters,
>> > > >
>> > > > Earlier we discuss about columns for running queries. Let's
>> summarize
>> > it
>> > > > and continue discussion for not closed questions.
>> > > >
>> > > > What we had:
>> > > > *name of view**: *running_queries
>> > > > *columns and meaning*:
>> > > >                        query_id -  unique id of query on node
>> > > >                        node_id - initial node of request.
>> > > >                        sql - text of query
>> > > >                        schema_name - name of sql schema
>> > > >                        duration - duration in milliseconds from
>> start
>> > of
>> > > > execution.
>> > > >
>> > > > All of this columns are clear, except query_id.
>> > > > Let's keep in mind that the query_id column of the view coupled with
>> > KILL
>> > > > QUERY command.
>> > > >
>> > > > We have the following variants what is query_id:
>> > > > 1) It's string, internally with two parts separated by '.'(it can be
>> > > other
>> > > > separator): numeric node order and numeric query counter unique for
>> > local
>> > > > node, e.g. '172.67321'. For this case query id will be really unique
>> > > across
>> > > > a cluster, but can be looks a strange for a user, especially in
>> case we
>> > > > will have ability to kill all queries on a node, when user should
>> get
>> > > first
>> > > > part before separator to use it, e.g. KILL QUERY '172.*'.
>> > > >
>> > > > 2) Just single numeric id, unique for local node, e.g '127'. In this
>> > case
>> > > > we need more complicated syntax for further KILL QUERY command,
>> which
>> > > lead
>> > > > to use two columns from the view, e.g. KILL QUERY WHERE nodeId=
>> > > > 37d7afd8-b87d-4aa1-b3d1-c1c033800000 and queryId=67321
>> > > >
>> > > > 3) Use base16String(concat(node,".",queryID) as query id, e.g. '
>> > > > 3132332E393337'. Then we hide internal structure of id and such id
>> will
>> > > be
>> > > > unique across a cluster. However we will need use complicated syntax
>> > for
>> > > > KILL QUERY command as for 2nd case.
>> > > >
>> > > > 4) Just single numeric id, unique for local node, e.g '127'. But
>> user
>> > > > should use two columns to merge it and create query id unique in a
>> > > cluster.
>> > > > Such approach use  by Oracle:ALTER SYSTEM CANCEL SQL 'SID, SERIAL,
>> > > SQL_ID'.
>> > > > In this case user will know real meaning of each part of passed
>> > parameter
>> > > > for KILL QUERY command. But it hard to use.
>> > > >
>> > > > 5) Any other approach you can think of....
>> > > >
>> > > > If be honestly I prefer first variant, it looks simple to use by
>> user
>> > (it
>> > > > require read a docs, but any case it required for any use cases).
>> > > >
>> > > > Lets discuss it again and chose better approach to expose query_id
>> > column
>> > > > for Ignite. Also confirm list of columns.
>> > > >
>> > > > вт, 27 нояб. 2018 г. в 11:00, Vladimir Ozerov <[hidden email]
>> >:
>> > > >
>> > > > > Yes ("нуы")
>> > > > >
>> > > > > On Tue, Nov 27, 2018 at 10:56 AM Павлухин Иван <
>> [hidden email]>
>> > > > > wrote:
>> > > > >
>> > > > > > I believe that the meaning was:
>> > > > > >
>> > > > > > > I propose to start with running queries VIEW first.
>> > > > > > вт, 27 нояб. 2018 г. в 10:47, Vladimir Ozerov <
>> > [hidden email]
>> > > >:
>> > > > > > >
>> > > > > > > I propose to start with running queries мшуц first. Once we
>> have
>> > > it,
>> > > > it
>> > > > > > > will be easier to agree on final command syntax.
>> > > > > > >
>> > > > > > > On Fri, Nov 23, 2018 at 9:32 AM Павлухин Иван <
>> > [hidden email]
>> > > >
>> > > > > > wrote:
>> > > > > > >
>> > > > > > > > Hi,
>> > > > > > > >
>> > > > > > > > May be I am a little bit late with my thoughts about a
>> command
>> > > > > syntax.
>> > > > > > > > How do I see it is going to be used:
>> > > > > > > > 1. A user is able to kill a query by unique id belonging
>> only
>> > to
>> > > > this
>> > > > > > > > query.
>> > > > > > > > 2. A user is able to kill all queries started by a specific
>> > node.
>> > > > > > > > For killing a single query we must identify it by unique id
>> > which
>> > > > is
>> > > > > > > > going to be received directly from Ignite (e.g. running
>> queries
>> > > > view)
>> > > > > > > > and not calculated by user. Internally the id is compound
>> but
>> > why
>> > > > > > > > cannot we convert it to opaque integer or string which hides
>> > real
>> > > > > > > > structure? E.g. base16String(concat(nodeOrder.toString(),
>> ".",
>> > > > > > > > queryIdOnNode.toString())) The syntax could be KILL QUERY
>> '123'
>> > > or
>> > > > > > > > KILL QUERY WHERE queryId = '123'
>> > > > > > > > For killing all queries started by some node we need to use
>> > only
>> > > > node
>> > > > > > > > order (or id). It could be like KILL QUERY WHERE nodeOrder =
>> > 34.
>> > > > > > > > чт, 22 нояб. 2018 г. в 12:56, Denis Mekhanikov <
>> > > > > [hidden email]
>> > > > > > >:
>> > > > > > > > >
>> > > > > > > > > Actually, option with separate parameters was mentioned in
>> > > > another
>> > > > > > thread
>> > > > > > > > >
>> > > > > > > >
>> > > > > >
>> > > > >
>> > > >
>> > >
>> >
>> http://apache-ignite-developers.2346864.n4.nabble.com/proposed-design-for-thin-client-SQL-management-and-monitoring-view-running-queries-and-kill-it-tp37713p38056.html
>> > > > > > > > >
>> > > > > > > > > Denis
>> > > > > > > > >
>> > > > > > > > > чт, 22 нояб. 2018 г. в 08:51, Vladimir Ozerov <
>> > > > > [hidden email]
>> > > > > > >:
>> > > > > > > > >
>> > > > > > > > > > Denis,
>> > > > > > > > > >
>> > > > > > > > > > Problems with separate parameters are explained above.
>> > > > > > > > > >
>> > > > > > > > > > чт, 22 нояб. 2018 г. в 3:23, Denis Magda <
>> > [hidden email]
>> > > >:
>> > > > > > > > > >
>> > > > > > > > > > > Vladimir,
>> > > > > > > > > > >
>> > > > > > > > > > > All of the alternatives are reminiscent of
>> mathematical
>> > > > > > operations.
>> > > > > > > > Don't
>> > > > > > > > > > > look like a SQL command. What if we use a SQL approach
>> > > > > > introducing
>> > > > > > > > named
>> > > > > > > > > > > parameters:
>> > > > > > > > > > >
>> > > > > > > > > > > KILL QUERY query_id=10 [AND node_id=5]
>> > > > > > > > > > >
>> > > > > > > > > > > --
>> > > > > > > > > > > Denis
>> > > > > > > > > > >
>> > > > > > > > > > > On Wed, Nov 21, 2018 at 4:11 AM Vladimir Ozerov <
>> > > > > > > > [hidden email]>
>> > > > > > > > > > > wrote:
>> > > > > > > > > > >
>> > > > > > > > > > > > Denis,
>> > > > > > > > > > > >
>> > > > > > > > > > > > Space is bad candidate because it is a whitespace.
>> > > Without
>> > > > > > > > whitespaces
>> > > > > > > > > > we
>> > > > > > > > > > > > can have syntax without quotes at all. Any
>> > non-whitespace
>> > > > > > delimiter
>> > > > > > > > > > will
>> > > > > > > > > > > > work, though:
>> > > > > > > > > > > >
>> > > > > > > > > > > > KILL QUERY 45.1
>> > > > > > > > > > > > KILL QUERY 45-1
>> > > > > > > > > > > > KILL QUERY 45:1
>> > > > > > > > > > > >
>> > > > > > > > > > > > On Wed, Nov 21, 2018 at 3:06 PM Юрий <
>> > > > > > [hidden email]>
>> > > > > > > > > > > wrote:
>> > > > > > > > > > > >
>> > > > > > > > > > > > > Denis,
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > Let's consider parameter of KILL QUERY just a
>> string
>> > > with
>> > > > > > some
>> > > > > > > > query
>> > > > > > > > > > > id,
>> > > > > > > > > > > > > without any meaning for user. User just need to
>> get
>> > the
>> > > > id
>> > > > > > and
>> > > > > > > > pass
>> > > > > > > > > > as
>> > > > > > > > > > > > > parameter to KILL QUERY command.
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > Even if query is distributed it have single query
>> id
>> > > from
>> > > > > > user
>> > > > > > > > > > > > perspective
>> > > > > > > > > > > > > and will killed on all nodes. User just need to
>> known
>> > > one
>> > > > > > global
>> > > > > > > > > > query
>> > > > > > > > > > > > id.
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > How it can works.
>> > > > > > > > > > > > > 1)SELECT * from running_queries
>> > > > > > > > > > > > > result is
>> > > > > > > > > > > > >  query_id | node_id
>> > > > > > > > > > > > >   | sql               | schema_name |
>> connection_id |
>> > > > > > duration
>> > > > > > > > > > > > > 123.33     |
>> e0a69cb8-a1a8-45f6-b84d-ead367a00000   |
>> > > > > SELECT
>> > > > > > > > ...  |
>> > > > > > > > > > ...
>> > > > > > > > > > > > >                   |   22                 | 23456
>> > > > > > > > > > > > > 333.31     | aaa6acb8-a4a5-42f6-f842-ead111b00020
>> >  |
>> > > > > > > > UPDATE...  |
>> > > > > > > > > > > ...
>> > > > > > > > > > > > >                   |  321                | 3467777
>> > > > > > > > > > > > > 2) KILL QUERY '123.33'
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > So, user need select query_id from running_queries
>> > view
>> > > > and
>> > > > > > use
>> > > > > > > > it
>> > > > > > > > > > for
>> > > > > > > > > > > > KILL
>> > > > > > > > > > > > > QUERY command.
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > I hope it became clearer.
>> > > > > > > > > > > > >
>> > > > > > > > > > > > >
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > ср, 21 нояб. 2018 г. в 02:11, Denis Magda <
>> > > > > [hidden email]
>> > > > > > >:
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > > Folks,
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > > > The decimal syntax is really odd - KILL QUERY
>> > > > > > > > > > > > > > '[node_order].[query_counter]'
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > > > Confusing, let's use a space to separate
>> > parameters.
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > > > Also, what if I want to halt a specific query
>> with
>> > > > > certain
>> > > > > > ID?
>> > > > > > > > > > Don't
>> > > > > > > > > > > > know
>> > > > > > > > > > > > > > the node number, just know that the query is
>> > > > distributed
>> > > > > > and
>> > > > > > > > runs
>> > > > > > > > > > > > across
>> > > > > > > > > > > > > > several machines. Sounds like the syntax still
>> > should
>> > > > > > consider
>> > > > > > > > > > > > > > [node_order/id] as an optional parameter.
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > > > Probably, if you explain to me how an end user
>> will
>> > > use
>> > > > > > this
>> > > > > > > > > > command
>> > > > > > > > > > > > from
>> > > > > > > > > > > > > > the very beginning (how do I look for a query id
>> > and
>> > > > node
>> > > > > > id,
>> > > > > > > > etc)
>> > > > > > > > > > > then
>> > > > > > > > > > > > > the
>> > > > > > > > > > > > > > things get clearer.
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > > > --
>> > > > > > > > > > > > > > Denis
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > > > On Tue, Nov 20, 2018 at 1:03 AM Юрий <
>> > > > > > > > [hidden email]>
>> > > > > > > > > > > > > wrote:
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > Hi Vladimir,
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > Thanks for your suggestion to use
>> MANAGEMENT_POOL
>> > > for
>> > > > > > > > processing
>> > > > > > > > > > > > > > > cancellation requests.
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > About your questions.
>> > > > > > > > > > > > > > > 1) I'm going to implements SQL view to provide
>> > list
>> > > > of
>> > > > > > > > running
>> > > > > > > > > > > > queries.
>> > > > > > > > > > > > > > The
>> > > > > > > > > > > > > > > SQL VIEW has been a little bit discussed
>> earlier.
>> > > > > > Proposed
>> > > > > > > > name
>> > > > > > > > > > is
>> > > > > > > > > > > > > > > *running_queries* with following columns:
>> > query_id,
>> > > > > > node_id,
>> > > > > > > > sql,
>> > > > > > > > > > > > > > > schema_name, connection_id, duration.
>> Currently
>> > > most
>> > > > of
>> > > > > > the
>> > > > > > > > > > > > information
>> > > > > > > > > > > > > > can
>> > > > > > > > > > > > > > > be  retrieved through cache API, however it
>> > doesn't
>> > > > > > matter,
>> > > > > > > > any
>> > > > > > > > > > > case
>> > > > > > > > > > > > we
>> > > > > > > > > > > > > > > need to expose SQL VIEW. Seem's you are right
>> -
>> > the
>> > > > > part
>> > > > > > > > should
>> > > > > > > > > > be
>> > > > > > > > > > > > > > > implemented firstly.
>> > > > > > > > > > > > > > > 2) Fully agree that we need to support all
>> kind
>> > of
>> > > > SQL
>> > > > > > > > queries
>> > > > > > > > > > > > > > > (SLECT/DML/DDL, transactional, non
>> transnational,
>> > > > > local,
>> > > > > > > > > > > > distributed).
>> > > > > > > > > > > > > I
>> > > > > > > > > > > > > > > definitely sure that it will possible for all
>> of
>> > > > above,
>> > > > > > > > however
>> > > > > > > > > > I'm
>> > > > > > > > > > > > not
>> > > > > > > > > > > > > > > sure about DDL - need to investigate it
>> deeper.
>> > > Also
>> > > > > > need to
>> > > > > > > > > > > > understand
>> > > > > > > > > > > > > > > that canceled DML operation can lead to
>> partially
>> > > > > updated
>> > > > > > > > data
>> > > > > > > > > > for
>> > > > > > > > > > > > non
>> > > > > > > > > > > > > > > transational caches.
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov
>> <
>> > > > > > > > > > > [hidden email]
>> > > > > > > > > > > > >:
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > Hi Yuriy,
>> > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > I think we can use MANAGEMENT_POOL for
>> this. It
>> > > is
>> > > > > > already
>> > > > > > > > used
>> > > > > > > > > > > for
>> > > > > > > > > > > > > > some
>> > > > > > > > > > > > > > > > internal Ignite tasks, and it appears to be
>> a
>> > > good
>> > > > > > > > candidate to
>> > > > > > > > > > > > > process
>> > > > > > > > > > > > > > > > cancel requests.
>> > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > But there are several things which are not
>> > clear
>> > > > > enough
>> > > > > > > > for me
>> > > > > > > > > > at
>> > > > > > > > > > > > the
>> > > > > > > > > > > > > > > > moment:
>> > > > > > > > > > > > > > > > 1) How user is going to get the list of
>> running
>> > > > > > queries in
>> > > > > > > > the
>> > > > > > > > > > > > first
>> > > > > > > > > > > > > > > place?
>> > > > > > > > > > > > > > > > Do we already have any SQL commands/views to
>> > get
>> > > > this
>> > > > > > > > > > > information?
>> > > > > > > > > > > > > > > > 2) We need to ensure that KILL command will
>> be
>> > > > > > processed
>> > > > > > > > > > properly
>> > > > > > > > > > > > by
>> > > > > > > > > > > > > > all
>> > > > > > > > > > > > > > > > kinds of SQL queries - SELECT/DML/DDL,
>> > > > > > non-transactional or
>> > > > > > > > > > > > > > > transactional,
>> > > > > > > > > > > > > > > > local queries and distributed queries. Will
>> we
>> > be
>> > > > > able
>> > > > > > to
>> > > > > > > > > > support
>> > > > > > > > > > > > all
>> > > > > > > > > > > > > > > these
>> > > > > > > > > > > > > > > > modes?
>> > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > Vladimir.
>> > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > On Mon, Nov 19, 2018 at 6:37 PM Юрий <
>> > > > > > > > > > > [hidden email]>
>> > > > > > > > > > > > > > > wrote:
>> > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > Hi Igniters,
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > Earlier we agreed about syntax KILL QUERY
>> > > > > > > > > > > > > > > '[node_order].[query_counter]',
>> > > > > > > > > > > > > > > > > e.g. KILL QUERY '25.123' for single
>> query  or
>> > > > KILL
>> > > > > > QUERY
>> > > > > > > > > > '25.*'
>> > > > > > > > > > > > for
>> > > > > > > > > > > > > > all
>> > > > > > > > > > > > > > > > > queries on the node. Which is part of
>> IEP-29
>> > > > > > > > > > > > > > > > > <
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > >
>> > > > > > > > > > > >
>> > > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > >
>> > > > > >
>> > > > >
>> > > >
>> > >
>> >
>> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
>> > > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > .
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > Now I want to discuss internal
>> realization of
>> > > > KILL
>> > > > > > query
>> > > > > > > > > > > feature.
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > My current vision is following:
>> > > > > > > > > > > > > > > > > After parsing, Ignite create KILL query
>> > command
>> > > > > with
>> > > > > > two
>> > > > > > > > > > > > > parameters:
>> > > > > > > > > > > > > > > > > nodeOrderId, nodeQryId. To determine that
>> > need
>> > > to
>> > > > > > kill
>> > > > > > > > all
>> > > > > > > > > > > > queries
>> > > > > > > > > > > > > > on a
>> > > > > > > > > > > > > > > > > node we can use negative value of query
>> id,
>> > due
>> > > > to
>> > > > > > qry id
>> > > > > > > > > > > always
>> > > > > > > > > > > > > have
>> > > > > > > > > > > > > > > > > positive values.
>> > > > > > > > > > > > > > > > > The command process at IgniteH2Indexing as
>> > > native
>> > > > > > > > command.
>> > > > > > > > > > > > > > > > > By nodeOrderId we find node which initial
>> for
>> > > the
>> > > > > > query
>> > > > > > > > and
>> > > > > > > > > > > send
>> > > > > > > > > > > > to
>> > > > > > > > > > > > > > the
>> > > > > > > > > > > > > > > > > node new GridQueryKillRequest with
>> nodeQryId
>> > to
>> > > > > > > > TOPIC_QUERY
>> > > > > > > > > > > with
>> > > > > > > > > > > > > not
>> > > > > > > > > > > > > > > > QUERY
>> > > > > > > > > > > > > > > > > POOL executor.
>> > > > > > > > > > > > > > > > > At GridReduceQueryExecutor we add support
>> of
>> > > > > > processing
>> > > > > > > > new
>> > > > > > > > > > > > > > > > > GridQueryKillRequest
>> > > > > > > > > > > > > > > > > which just run already exists
>> cancelQueries
>> > > > method
>> > > > > > with
>> > > > > > > > given
>> > > > > > > > > > > > qryId
>> > > > > > > > > > > > > > or
>> > > > > > > > > > > > > > > > with
>> > > > > > > > > > > > > > > > > all qryIds which currently running at the
>> > node
>> > > in
>> > > > > > case at
>> > > > > > > > > > > initial
>> > > > > > > > > > > > > > KILL
>> > > > > > > > > > > > > > > > > QUERY parameters used star symbol.
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > I have a doubt which of thread pool we
>> should
>> > > use
>> > > > > to
>> > > > > > > > process
>> > > > > > > > > > > > > > > > > GridQueryKillRequest.
>> > > > > > > > > > > > > > > > > My opinion it shouldn't be QUERY pool,
>> due to
>> > > the
>> > > > > > pool
>> > > > > > > > can be
>> > > > > > > > > > > > fully
>> > > > > > > > > > > > > > > used
>> > > > > > > > > > > > > > > > by
>> > > > > > > > > > > > > > > > > executing queries, it such case we can't
>> > cancel
>> > > > > query
>> > > > > > > > > > > > immediately.
>> > > > > > > > > > > > > > May
>> > > > > > > > > > > > > > > we
>> > > > > > > > > > > > > > > > > use one of already existed pool or create
>> new
>> > > > one?
>> > > > > Or
>> > > > > > > > may be
>> > > > > > > > > > > I'm
>> > > > > > > > > > > > > > > mistaken
>> > > > > > > > > > > > > > > > > and it should use QUERY pool.
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > What do you think about proposed plan of
>> > > > > > implementation?
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > And please give comments about which of
>> > thread
>> > > > pool
>> > > > > > will
>> > > > > > > > be
>> > > > > > > > > > > > better
>> > > > > > > > > > > > > to
>> > > > > > > > > > > > > > > use
>> > > > > > > > > > > > > > > > > for kill query requests. It's small, but
>> > really
>> > > > > > important
>> > > > > > > > > > part
>> > > > > > > > > > > of
>> > > > > > > > > > > > > the
>> > > > > > > > > > > > > > > > > realization.
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > Thanks.
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > --
>> > > > > > > > > > > > > > > > > Живи с улыбкой! :D
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > --
>> > > > > > > > > > > > > > > Живи с улыбкой! :D
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > >
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > --
>> > > > > > > > > > > > > Живи с улыбкой! :D
>> > > > > > > > > > > > >
>> > > > > > > > > > > >
>> > > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > > > > --
>> > > > > > > > Best regards,
>> > > > > > > > Ivan Pavlukhin
>> > > > > > > >
>> > > > > >
>> > > > > >
>> > > > > >
>> > > > > > --
>> > > > > > Best regards,
>> > > > > > Ivan Pavlukhin
>> > > > > >
>> > > > >
>> > > >
>> > > >
>> > > > --
>> > > > Живи с улыбкой! :D
>> > > >
>> > >
>> >
>> >
>> > --
>> > Живи с улыбкой! :D
>> >
>>
>
>
> --
> Живи с улыбкой! :D
>

[dmagda]

Yury,

I do support the latter concatenation approach. It's simple and correlates
with what other DBs do. Plus, it can be passed to KILL command without
complications. Thanks for thinking this through!

As for the killing of all queries on a particular node, not sure that's a
relevant use case. I would put this off. Usually, you want to stop a
specific query (it's slow or resources consuming) and have to know its id,
the query runs across multiple nodes and a single KILL command with the id
can halt it everywhere. If someone decided to shut all queries on the node,
then it sounds like the node is experiencing big troubles and it might be
better just to shut it down completely.

-
Denis


On Tue, Jan 15, 2019 at 8:00 AM Юрий <[hidden email]> wrote:

> Denis and other Igniters, do you have any comments for proposed approach?
> Which of these ones will be better to use for us - simple numeric  or hex
> values (shorter id, but with letters)?
>
> As for me hex values preferable due to it  shorter and looks more unique
> across a logs
>
>
>
> вт, 15 янв. 2019 г. в 18:35, Vladimir Ozerov <[hidden email]>:
>
>> Hi,
>>
>> Concatenation through a letter looks like a good approach to me. As far as
>> killing all queries on a specific node, I would put it aside for now -
>> this
>> looks like a separate command with possibly different parameters.
>>
>> On Tue, Jan 15, 2019 at 1:30 PM Юрий <[hidden email]> wrote:
>>
>> > Thanks Vladimir for your thoughts.
>> >
>> > Based on it most convenient ways are first and third.
>> > But with some modifications:
>> > For first variant delimiter should be a letter, e.g. 123X15494, then it
>> > could be simple copy by user.
>> > For 3rd variant can be used convert both numeric to HEX and use a letter
>> > delimiter not included to HEX symbols (ABCDEF), in this case query id
>> will
>> > be shorter and also can be simple copy by user. e.g. 7BX3C86 ( it the
>> same
>> > value as used for first variant), instead of convert all value as
>> string to
>> > base16 due to it will be really long value.
>> >
>> > Possible realization for the cases:
>> > 1) Concatenation node order id with query id with a letter delimiter.
>> >
>> > query_id = 1234X8753 , where *1234* - node order, *8753* - local node
>> query
>> > counter. *X* - delimeter
>> >
>> > 2) Converting both node order id and query id to HEX.
>> >
>> > query_id =  7BX3C86,  value is concat(hex(node),"X",hex(queryID))
>> >
>> > For both variants we can use either simple or copmlex KILL QUERY syntax.
>> > Simple:
>> >
>> > KILL QUERY 7BX3C86 - for kill concrete query
>> > KILL QUERY 7B - for killing all queries on a node.  May be need extra
>> > symbols for such queries to avoid fault of user and kill all queries by
>> > mistake, like KILL QUERY 7B*
>> >
>> > Complex:
>> >
>> > KILL QUERY WHERE queryId=7BX3C86 - for killing concrete query.
>> >
>> > KILL QUERY WHERE nodeId=37d7afd8-b87d-4aa1-b3d1-c1c033800000  - for kill
>> > all running queries on a given node.
>> >
>> >
>> >
>> > What do you think?
>> >
>> >
>> > вт, 15 янв. 2019 г. в 11:20, Vladimir Ozerov <[hidden email]>:
>> >
>> > > Hi Yuriy,
>> > >
>> > > I think all proposed approaches might work. The question is what is
>> the
>> > > most convenient from user perspective. Encoded values without special
>> > > characters are good because they are easy to copy with mouse
>> > (double-click)
>> > > or keyboard (Ctrl+Shift+arrow). On the other hand, ability to identify
>> > > ID/name of suspicious node from query ID is also a good thing. Several
>> > > examples of query ID:
>> > >
>> > > CockroachDB: 14dacc1f9a781e3d0000000000000001
>> > > MongoDB: shardB:79014
>> > >
>> > > Also it is important that the same query ID is printed in various log
>> > > messages. This will be very useful for debugging purposes, e.g. grep
>> over
>> > > logs. So ideally query ID should not have any symbols which interfere
>> > with
>> > > grep syntax.
>> > >
>> > >
>> > > On Mon, Jan 14, 2019 at 3:09 PM Юрий <[hidden email]>
>> > wrote:
>> > >
>> > > > Hi Igniters,
>> > > >
>> > > > Earlier we discuss about columns for running queries. Let's
>> summarize
>> > it
>> > > > and continue discussion for not closed questions.
>> > > >
>> > > > What we had:
>> > > > *name of view**: *running_queries
>> > > > *columns and meaning*:
>> > > >                        query_id -  unique id of query on node
>> > > >                        node_id - initial node of request.
>> > > >                        sql - text of query
>> > > >                        schema_name - name of sql schema
>> > > >                        duration - duration in milliseconds from
>> start
>> > of
>> > > > execution.
>> > > >
>> > > > All of this columns are clear, except query_id.
>> > > > Let's keep in mind that the query_id column of the view coupled with
>> > KILL
>> > > > QUERY command.
>> > > >
>> > > > We have the following variants what is query_id:
>> > > > 1) It's string, internally with two parts separated by '.'(it can be
>> > > other
>> > > > separator): numeric node order and numeric query counter unique for
>> > local
>> > > > node, e.g. '172.67321'. For this case query id will be really unique
>> > > across
>> > > > a cluster, but can be looks a strange for a user, especially in
>> case we
>> > > > will have ability to kill all queries on a node, when user should
>> get
>> > > first
>> > > > part before separator to use it, e.g. KILL QUERY '172.*'.
>> > > >
>> > > > 2) Just single numeric id, unique for local node, e.g '127'. In this
>> > case
>> > > > we need more complicated syntax for further KILL QUERY command,
>> which
>> > > lead
>> > > > to use two columns from the view, e.g. KILL QUERY WHERE nodeId=
>> > > > 37d7afd8-b87d-4aa1-b3d1-c1c033800000 and queryId=67321
>> > > >
>> > > > 3) Use base16String(concat(node,".",queryID) as query id, e.g. '
>> > > > 3132332E393337'. Then we hide internal structure of id and such id
>> will
>> > > be
>> > > > unique across a cluster. However we will need use complicated syntax
>> > for
>> > > > KILL QUERY command as for 2nd case.
>> > > >
>> > > > 4) Just single numeric id, unique for local node, e.g '127'. But
>> user
>> > > > should use two columns to merge it and create query id unique in a
>> > > cluster.
>> > > > Such approach use  by Oracle:ALTER SYSTEM CANCEL SQL 'SID, SERIAL,
>> > > SQL_ID'.
>> > > > In this case user will know real meaning of each part of passed
>> > parameter
>> > > > for KILL QUERY command. But it hard to use.
>> > > >
>> > > > 5) Any other approach you can think of....
>> > > >
>> > > > If be honestly I prefer first variant, it looks simple to use by
>> user
>> > (it
>> > > > require read a docs, but any case it required for any use cases).
>> > > >
>> > > > Lets discuss it again and chose better approach to expose query_id
>> > column
>> > > > for Ignite. Also confirm list of columns.
>> > > >
>> > > > вт, 27 нояб. 2018 г. в 11:00, Vladimir Ozerov <[hidden email]
>> >:
>> > > >
>> > > > > Yes ("нуы")
>> > > > >
>> > > > > On Tue, Nov 27, 2018 at 10:56 AM Павлухин Иван <
>> [hidden email]>
>> > > > > wrote:
>> > > > >
>> > > > > > I believe that the meaning was:
>> > > > > >
>> > > > > > > I propose to start with running queries VIEW first.
>> > > > > > вт, 27 нояб. 2018 г. в 10:47, Vladimir Ozerov <
>> > [hidden email]
>> > > >:
>> > > > > > >
>> > > > > > > I propose to start with running queries мшуц first. Once we
>> have
>> > > it,
>> > > > it
>> > > > > > > will be easier to agree on final command syntax.
>> > > > > > >
>> > > > > > > On Fri, Nov 23, 2018 at 9:32 AM Павлухин Иван <
>> > [hidden email]
>> > > >
>> > > > > > wrote:
>> > > > > > >
>> > > > > > > > Hi,
>> > > > > > > >
>> > > > > > > > May be I am a little bit late with my thoughts about a
>> command
>> > > > > syntax.
>> > > > > > > > How do I see it is going to be used:
>> > > > > > > > 1. A user is able to kill a query by unique id belonging
>> only
>> > to
>> > > > this
>> > > > > > > > query.
>> > > > > > > > 2. A user is able to kill all queries started by a specific
>> > node.
>> > > > > > > > For killing a single query we must identify it by unique id
>> > which
>> > > > is
>> > > > > > > > going to be received directly from Ignite (e.g. running
>> queries
>> > > > view)
>> > > > > > > > and not calculated by user. Internally the id is compound
>> but
>> > why
>> > > > > > > > cannot we convert it to opaque integer or string which hides
>> > real
>> > > > > > > > structure? E.g. base16String(concat(nodeOrder.toString(),
>> ".",
>> > > > > > > > queryIdOnNode.toString())) The syntax could be KILL QUERY
>> '123'
>> > > or
>> > > > > > > > KILL QUERY WHERE queryId = '123'
>> > > > > > > > For killing all queries started by some node we need to use
>> > only
>> > > > node
>> > > > > > > > order (or id). It could be like KILL QUERY WHERE nodeOrder =
>> > 34.
>> > > > > > > > чт, 22 нояб. 2018 г. в 12:56, Denis Mekhanikov <
>> > > > > [hidden email]
>> > > > > > >:
>> > > > > > > > >
>> > > > > > > > > Actually, option with separate parameters was mentioned in
>> > > > another
>> > > > > > thread
>> > > > > > > > >
>> > > > > > > >
>> > > > > >
>> > > > >
>> > > >
>> > >
>> >
>> http://apache-ignite-developers.2346864.n4.nabble.com/proposed-design-for-thin-client-SQL-management-and-monitoring-view-running-queries-and-kill-it-tp37713p38056.html
>> > > > > > > > >
>> > > > > > > > > Denis
>> > > > > > > > >
>> > > > > > > > > чт, 22 нояб. 2018 г. в 08:51, Vladimir Ozerov <
>> > > > > [hidden email]
>> > > > > > >:
>> > > > > > > > >
>> > > > > > > > > > Denis,
>> > > > > > > > > >
>> > > > > > > > > > Problems with separate parameters are explained above.
>> > > > > > > > > >
>> > > > > > > > > > чт, 22 нояб. 2018 г. в 3:23, Denis Magda <
>> > [hidden email]
>> > > >:
>> > > > > > > > > >
>> > > > > > > > > > > Vladimir,
>> > > > > > > > > > >
>> > > > > > > > > > > All of the alternatives are reminiscent of
>> mathematical
>> > > > > > operations.
>> > > > > > > > Don't
>> > > > > > > > > > > look like a SQL command. What if we use a SQL approach
>> > > > > > introducing
>> > > > > > > > named
>> > > > > > > > > > > parameters:
>> > > > > > > > > > >
>> > > > > > > > > > > KILL QUERY query_id=10 [AND node_id=5]
>> > > > > > > > > > >
>> > > > > > > > > > > --
>> > > > > > > > > > > Denis
>> > > > > > > > > > >
>> > > > > > > > > > > On Wed, Nov 21, 2018 at 4:11 AM Vladimir Ozerov <
>> > > > > > > > [hidden email]>
>> > > > > > > > > > > wrote:
>> > > > > > > > > > >
>> > > > > > > > > > > > Denis,
>> > > > > > > > > > > >
>> > > > > > > > > > > > Space is bad candidate because it is a whitespace.
>> > > Without
>> > > > > > > > whitespaces
>> > > > > > > > > > we
>> > > > > > > > > > > > can have syntax without quotes at all. Any
>> > non-whitespace
>> > > > > > delimiter
>> > > > > > > > > > will
>> > > > > > > > > > > > work, though:
>> > > > > > > > > > > >
>> > > > > > > > > > > > KILL QUERY 45.1
>> > > > > > > > > > > > KILL QUERY 45-1
>> > > > > > > > > > > > KILL QUERY 45:1
>> > > > > > > > > > > >
>> > > > > > > > > > > > On Wed, Nov 21, 2018 at 3:06 PM Юрий <
>> > > > > > [hidden email]>
>> > > > > > > > > > > wrote:
>> > > > > > > > > > > >
>> > > > > > > > > > > > > Denis,
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > Let's consider parameter of KILL QUERY just a
>> string
>> > > with
>> > > > > > some
>> > > > > > > > query
>> > > > > > > > > > > id,
>> > > > > > > > > > > > > without any meaning for user. User just need to
>> get
>> > the
>> > > > id
>> > > > > > and
>> > > > > > > > pass
>> > > > > > > > > > as
>> > > > > > > > > > > > > parameter to KILL QUERY command.
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > Even if query is distributed it have single query
>> id
>> > > from
>> > > > > > user
>> > > > > > > > > > > > perspective
>> > > > > > > > > > > > > and will killed on all nodes. User just need to
>> known
>> > > one
>> > > > > > global
>> > > > > > > > > > query
>> > > > > > > > > > > > id.
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > How it can works.
>> > > > > > > > > > > > > 1)SELECT * from running_queries
>> > > > > > > > > > > > > result is
>> > > > > > > > > > > > >  query_id | node_id
>> > > > > > > > > > > > >   | sql               | schema_name |
>> connection_id |
>> > > > > > duration
>> > > > > > > > > > > > > 123.33     |
>> e0a69cb8-a1a8-45f6-b84d-ead367a00000   |
>> > > > > SELECT
>> > > > > > > > ...  |
>> > > > > > > > > > ...
>> > > > > > > > > > > > >                   |   22                 | 23456
>> > > > > > > > > > > > > 333.31     | aaa6acb8-a4a5-42f6-f842-ead111b00020
>> >  |
>> > > > > > > > UPDATE...  |
>> > > > > > > > > > > ...
>> > > > > > > > > > > > >                   |  321                | 3467777
>> > > > > > > > > > > > > 2) KILL QUERY '123.33'
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > So, user need select query_id from running_queries
>> > view
>> > > > and
>> > > > > > use
>> > > > > > > > it
>> > > > > > > > > > for
>> > > > > > > > > > > > KILL
>> > > > > > > > > > > > > QUERY command.
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > I hope it became clearer.
>> > > > > > > > > > > > >
>> > > > > > > > > > > > >
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > ср, 21 нояб. 2018 г. в 02:11, Denis Magda <
>> > > > > [hidden email]
>> > > > > > >:
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > > Folks,
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > > > The decimal syntax is really odd - KILL QUERY
>> > > > > > > > > > > > > > '[node_order].[query_counter]'
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > > > Confusing, let's use a space to separate
>> > parameters.
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > > > Also, what if I want to halt a specific query
>> with
>> > > > > certain
>> > > > > > ID?
>> > > > > > > > > > Don't
>> > > > > > > > > > > > know
>> > > > > > > > > > > > > > the node number, just know that the query is
>> > > > distributed
>> > > > > > and
>> > > > > > > > runs
>> > > > > > > > > > > > across
>> > > > > > > > > > > > > > several machines. Sounds like the syntax still
>> > should
>> > > > > > consider
>> > > > > > > > > > > > > > [node_order/id] as an optional parameter.
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > > > Probably, if you explain to me how an end user
>> will
>> > > use
>> > > > > > this
>> > > > > > > > > > command
>> > > > > > > > > > > > from
>> > > > > > > > > > > > > > the very beginning (how do I look for a query id
>> > and
>> > > > node
>> > > > > > id,
>> > > > > > > > etc)
>> > > > > > > > > > > then
>> > > > > > > > > > > > > the
>> > > > > > > > > > > > > > things get clearer.
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > > > --
>> > > > > > > > > > > > > > Denis
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > > > On Tue, Nov 20, 2018 at 1:03 AM Юрий <
>> > > > > > > > [hidden email]>
>> > > > > > > > > > > > > wrote:
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > Hi Vladimir,
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > Thanks for your suggestion to use
>> MANAGEMENT_POOL
>> > > for
>> > > > > > > > processing
>> > > > > > > > > > > > > > > cancellation requests.
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > About your questions.
>> > > > > > > > > > > > > > > 1) I'm going to implements SQL view to provide
>> > list
>> > > > of
>> > > > > > > > running
>> > > > > > > > > > > > queries.
>> > > > > > > > > > > > > > The
>> > > > > > > > > > > > > > > SQL VIEW has been a little bit discussed
>> earlier.
>> > > > > > Proposed
>> > > > > > > > name
>> > > > > > > > > > is
>> > > > > > > > > > > > > > > *running_queries* with following columns:
>> > query_id,
>> > > > > > node_id,
>> > > > > > > > sql,
>> > > > > > > > > > > > > > > schema_name, connection_id, duration.
>> Currently
>> > > most
>> > > > of
>> > > > > > the
>> > > > > > > > > > > > information
>> > > > > > > > > > > > > > can
>> > > > > > > > > > > > > > > be  retrieved through cache API, however it
>> > doesn't
>> > > > > > matter,
>> > > > > > > > any
>> > > > > > > > > > > case
>> > > > > > > > > > > > we
>> > > > > > > > > > > > > > > need to expose SQL VIEW. Seem's you are right
>> -
>> > the
>> > > > > part
>> > > > > > > > should
>> > > > > > > > > > be
>> > > > > > > > > > > > > > > implemented firstly.
>> > > > > > > > > > > > > > > 2) Fully agree that we need to support all
>> kind
>> > of
>> > > > SQL
>> > > > > > > > queries
>> > > > > > > > > > > > > > > (SLECT/DML/DDL, transactional, non
>> transnational,
>> > > > > local,
>> > > > > > > > > > > > distributed).
>> > > > > > > > > > > > > I
>> > > > > > > > > > > > > > > definitely sure that it will possible for all
>> of
>> > > > above,
>> > > > > > > > however
>> > > > > > > > > > I'm
>> > > > > > > > > > > > not
>> > > > > > > > > > > > > > > sure about DDL - need to investigate it
>> deeper.
>> > > Also
>> > > > > > need to
>> > > > > > > > > > > > understand
>> > > > > > > > > > > > > > > that canceled DML operation can lead to
>> partially
>> > > > > updated
>> > > > > > > > data
>> > > > > > > > > > for
>> > > > > > > > > > > > non
>> > > > > > > > > > > > > > > transational caches.
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > пн, 19 нояб. 2018 г. в 19:17, Vladimir Ozerov
>> <
>> > > > > > > > > > > [hidden email]
>> > > > > > > > > > > > >:
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > Hi Yuriy,
>> > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > I think we can use MANAGEMENT_POOL for
>> this. It
>> > > is
>> > > > > > already
>> > > > > > > > used
>> > > > > > > > > > > for
>> > > > > > > > > > > > > > some
>> > > > > > > > > > > > > > > > internal Ignite tasks, and it appears to be
>> a
>> > > good
>> > > > > > > > candidate to
>> > > > > > > > > > > > > process
>> > > > > > > > > > > > > > > > cancel requests.
>> > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > But there are several things which are not
>> > clear
>> > > > > enough
>> > > > > > > > for me
>> > > > > > > > > > at
>> > > > > > > > > > > > the
>> > > > > > > > > > > > > > > > moment:
>> > > > > > > > > > > > > > > > 1) How user is going to get the list of
>> running
>> > > > > > queries in
>> > > > > > > > the
>> > > > > > > > > > > > first
>> > > > > > > > > > > > > > > place?
>> > > > > > > > > > > > > > > > Do we already have any SQL commands/views to
>> > get
>> > > > this
>> > > > > > > > > > > information?
>> > > > > > > > > > > > > > > > 2) We need to ensure that KILL command will
>> be
>> > > > > > processed
>> > > > > > > > > > properly
>> > > > > > > > > > > > by
>> > > > > > > > > > > > > > all
>> > > > > > > > > > > > > > > > kinds of SQL queries - SELECT/DML/DDL,
>> > > > > > non-transactional or
>> > > > > > > > > > > > > > > transactional,
>> > > > > > > > > > > > > > > > local queries and distributed queries. Will
>> we
>> > be
>> > > > > able
>> > > > > > to
>> > > > > > > > > > support
>> > > > > > > > > > > > all
>> > > > > > > > > > > > > > > these
>> > > > > > > > > > > > > > > > modes?
>> > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > Vladimir.
>> > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > On Mon, Nov 19, 2018 at 6:37 PM Юрий <
>> > > > > > > > > > > [hidden email]>
>> > > > > > > > > > > > > > > wrote:
>> > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > Hi Igniters,
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > Earlier we agreed about syntax KILL QUERY
>> > > > > > > > > > > > > > > '[node_order].[query_counter]',
>> > > > > > > > > > > > > > > > > e.g. KILL QUERY '25.123' for single
>> query  or
>> > > > KILL
>> > > > > > QUERY
>> > > > > > > > > > '25.*'
>> > > > > > > > > > > > for
>> > > > > > > > > > > > > > all
>> > > > > > > > > > > > > > > > > queries on the node. Which is part of
>> IEP-29
>> > > > > > > > > > > > > > > > > <
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > >
>> > > > > > > > > > > >
>> > > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > >
>> > > > > >
>> > > > >
>> > > >
>> > >
>> >
>> https://cwiki.apache.org/confluence/display/IGNITE/IEP-29%3A+SQL+management+and+monitoring
>> > > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > .
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > Now I want to discuss internal
>> realization of
>> > > > KILL
>> > > > > > query
>> > > > > > > > > > > feature.
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > My current vision is following:
>> > > > > > > > > > > > > > > > > After parsing, Ignite create KILL query
>> > command
>> > > > > with
>> > > > > > two
>> > > > > > > > > > > > > parameters:
>> > > > > > > > > > > > > > > > > nodeOrderId, nodeQryId. To determine that
>> > need
>> > > to
>> > > > > > kill
>> > > > > > > > all
>> > > > > > > > > > > > queries
>> > > > > > > > > > > > > > on a
>> > > > > > > > > > > > > > > > > node we can use negative value of query
>> id,
>> > due
>> > > > to
>> > > > > > qry id
>> > > > > > > > > > > always
>> > > > > > > > > > > > > have
>> > > > > > > > > > > > > > > > > positive values.
>> > > > > > > > > > > > > > > > > The command process at IgniteH2Indexing as
>> > > native
>> > > > > > > > command.
>> > > > > > > > > > > > > > > > > By nodeOrderId we find node which initial
>> for
>> > > the
>> > > > > > query
>> > > > > > > > and
>> > > > > > > > > > > send
>> > > > > > > > > > > > to
>> > > > > > > > > > > > > > the
>> > > > > > > > > > > > > > > > > node new GridQueryKillRequest with
>> nodeQryId
>> > to
>> > > > > > > > TOPIC_QUERY
>> > > > > > > > > > > with
>> > > > > > > > > > > > > not
>> > > > > > > > > > > > > > > > QUERY
>> > > > > > > > > > > > > > > > > POOL executor.
>> > > > > > > > > > > > > > > > > At GridReduceQueryExecutor we add support
>> of
>> > > > > > processing
>> > > > > > > > new
>> > > > > > > > > > > > > > > > > GridQueryKillRequest
>> > > > > > > > > > > > > > > > > which just run already exists
>> cancelQueries
>> > > > method
>> > > > > > with
>> > > > > > > > given
>> > > > > > > > > > > > qryId
>> > > > > > > > > > > > > > or
>> > > > > > > > > > > > > > > > with
>> > > > > > > > > > > > > > > > > all qryIds which currently running at the
>> > node
>> > > in
>> > > > > > case at
>> > > > > > > > > > > initial
>> > > > > > > > > > > > > > KILL
>> > > > > > > > > > > > > > > > > QUERY parameters used star symbol.
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > I have a doubt which of thread pool we
>> should
>> > > use
>> > > > > to
>> > > > > > > > process
>> > > > > > > > > > > > > > > > > GridQueryKillRequest.
>> > > > > > > > > > > > > > > > > My opinion it shouldn't be QUERY pool,
>> due to
>> > > the
>> > > > > > pool
>> > > > > > > > can be
>> > > > > > > > > > > > fully
>> > > > > > > > > > > > > > > used
>> > > > > > > > > > > > > > > > by
>> > > > > > > > > > > > > > > > > executing queries, it such case we can't
>> > cancel
>> > > > > query
>> > > > > > > > > > > > immediately.
>> > > > > > > > > > > > > > May
>> > > > > > > > > > > > > > > we
>> > > > > > > > > > > > > > > > > use one of already existed pool or create
>> new
>> > > > one?
>> > > > > Or
>> > > > > > > > may be
>> > > > > > > > > > > I'm
>> > > > > > > > > > > > > > > mistaken
>> > > > > > > > > > > > > > > > > and it should use QUERY pool.
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > What do you think about proposed plan of
>> > > > > > implementation?
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > And please give comments about which of
>> > thread
>> > > > pool
>> > > > > > will
>> > > > > > > > be
>> > > > > > > > > > > > better
>> > > > > > > > > > > > > to
>> > > > > > > > > > > > > > > use
>> > > > > > > > > > > > > > > > > for kill query requests. It's small, but
>> > really
>> > > > > > important
>> > > > > > > > > > part
>> > > > > > > > > > > of
>> > > > > > > > > > > > > the
>> > > > > > > > > > > > > > > > > realization.
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > Thanks.
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > > > --
>> > > > > > > > > > > > > > > > > Живи с улыбкой! :D
>> > > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > > > --
>> > > > > > > > > > > > > > > Живи с улыбкой! :D
>> > > > > > > > > > > > > > >
>> > > > > > > > > > > > > >
>> > > > > > > > > > > > >
>> > > > > > > > > > > > >
>> > > > > > > > > > > > > --
>> > > > > > > > > > > > > Живи с улыбкой! :D
>> > > > > > > > > > > > >
>> > > > > > > > > > > >
>> > > > > > > > > > >
>> > > > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > > > > --
>> > > > > > > > Best regards,
>> > > > > > > > Ivan Pavlukhin
>> > > > > > > >
>> > > > > >
>> > > > > >
>> > > > > >
>> > > > > > --
>> > > > > > Best regards,
>> > > > > > Ivan Pavlukhin
>> > > > > >
>> > > > >
>> > > >
>> > > >
>> > > > --
>> > > > Живи с улыбкой! :D
>> > > >
>> > >
>> >
>> >
>> > --
>> > Живи с улыбкой! :D
>> >
>>
>
>
> --
> Живи с улыбкой! :D
>