delete is too slow, sometimes even causes OOM

[frank li]

Current code logic for DELETE is as follows:
if WHERE clause contains a condition as "key=xxx", it uses fastUpdate which remove the related item directly.

else
    do select for update;
    for each row, call closure code "RMV" to remove it.

1. As "executeSelectForDml" get _KEY and _VAL columns for all condidate rows, it often causes OOM when there are a lot of data  to delete. Why do we verify "val" during remove operation?

2. After selection,  why don't we just remove it with cache.remove as fastUpdate does?

[dmagda]

Hi Frank,

Thanks for starting this discussion. Our contributors with SQL experience
should step in.

Generally, I came across this performance issue a week or so ago while
doing training about Ignite. In my case, a simple DELETE was running for a
minute over a table of negligible size.

-
Denis


On Tue, Nov 3, 2020 at 7:53 AM frank li <[hidden email]> wrote:

> Current code logic for DELETE is as follows:
> if WHERE clause contains a condition as "key=xxx", it uses fastUpdate
> which remove the related item directly.
>
> else
>     do select for update;
>     for each row, call closure code "RMV" to remove it.
>
> 1. As "executeSelectForDml" get _KEY and _VAL columns for all condidate
> rows, it often causes OOM when there are a lot of data  to delete. Why do
> we verify "val" during remove operation?
>
> 2. After selection,  why don't we just remove it with cache.remove as
> fastUpdate does?
>
>
>

[Юрий]

Hi Frank!

There is an old ticket [1] - We will try to prioritize it to finish before
the end of the year it should prevent OOM for most cases.

[1] https://issues.apache.org/jira/browse/IGNITE-9182

вт, 3 нояб. 2020 г. в 18:53, frank li <[hidden email]>:

> Current code logic for DELETE is as follows:
> if WHERE clause contains a condition as "key=xxx", it uses fastUpdate
> which remove the related item directly.
>
> else
>     do select for update;
>     for each row, call closure code "RMV" to remove it.
>
> 1. As "executeSelectForDml" get _KEY and _VAL columns for all condidate
> rows, it often causes OOM when there are a lot of data  to delete. Why do
> we verify "val" during remove operation?
>
> 2. After selection,  why don't we just remove it with cache.remove as
> fastUpdate does?
>
>
>

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

[frank li]

I enforced  a lazy flag in DELETE code for tesing, but it is stil running very slow. I mean that "Lazy" flag cannot solve the problem of running too slow.

On 2020/11/06 09:50:15, Юрий <[hidden email]> wrote:

> Hi Frank!
>
> There is an old ticket [1] - We will try to prioritize it to finish before
> the end of the year it should prevent OOM for most cases.
>
> [1] https://issues.apache.org/jira/browse/IGNITE-9182
>
> вт, 3 нояб. 2020 г. в 18:53, frank li <[hidden email]>:
>
> > Current code logic for DELETE is as follows:
> > if WHERE clause contains a condition as "key=xxx", it uses fastUpdate
> > which remove the related item directly.
> >
> > else
> >     do select for update;
> >     for each row, call closure code "RMV" to remove it.
> >
> > 1. As "executeSelectForDml" get _KEY and _VAL columns for all condidate
> > rows, it often causes OOM when there are a lot of data  to delete. Why do
> > we verify "val" during remove operation?
> >
> > 2. After selection,  why don't we just remove it with cache.remove as
> > fastUpdate does?
> >
> >
> >
>
> --
> Живи с улыбкой! :D
>

[dmagda]

Frank,

The ticket doesn't suggest the lazy flag as a workaround. The flag is
supposed to be used to address the performance issue.

How about a workaround on your application side while you're waiting for
this improvement?

   - Query all the records for a deletion - "SELECT record_primary_key
   WHERE delete_condition"
   - Delete the records using the key-value API -
   cache.removeAll(all_primary_keys).

-
Denis


On Mon, Nov 9, 2020 at 8:20 AM frank li <[hidden email]> wrote:

> I enforced  a lazy flag in DELETE code for tesing, but it is stil running
> very slow. I mean that "Lazy" flag cannot solve the problem of running too
> slow.
>
> On 2020/11/06 09:50:15, Юрий <[hidden email]> wrote:
> > Hi Frank!
> >
> > There is an old ticket [1] - We will try to prioritize it to finish
> before
> > the end of the year it should prevent OOM for most cases.
> >
> > [1] https://issues.apache.org/jira/browse/IGNITE-9182
> >
> > вт, 3 нояб. 2020 г. в 18:53, frank li <[hidden email]>:
> >
> > > Current code logic for DELETE is as follows:
> > > if WHERE clause contains a condition as "key=xxx", it uses fastUpdate
> > > which remove the related item directly.
> > >
> > > else
> > >     do select for update;
> > >     for each row, call closure code "RMV" to remove it.
> > >
> > > 1. As "executeSelectForDml" get _KEY and _VAL columns for all condidate
> > > rows, it often causes OOM when there are a lot of data  to delete. Why
> do
> > > we verify "val" during remove operation?
> > >
> > > 2. After selection,  why don't we just remove it with cache.remove as
> > > fastUpdate does?
> > >
> > >
> > >
> >
> > --
> > Живи с улыбкой! :D
> >
>

[Юрий]

Frank,

Tiket [1] has been resolved. Try to use LAZY flag for your DML query on the
new nightly build.

[1] https://issues.apache.org/jira/browse/IGNITE-9182

пн, 9 нояб. 2020 г. в 19:28, Denis Magda <[hidden email]>:

> Frank,
>
> The ticket doesn't suggest the lazy flag as a workaround. The flag is
> supposed to be used to address the performance issue.
>
> How about a workaround on your application side while you're waiting for
> this improvement?
>
>    - Query all the records for a deletion - "SELECT record_primary_key
>    WHERE delete_condition"
>    - Delete the records using the key-value API -
>    cache.removeAll(all_primary_keys).
>
> -
> Denis
>
>
> On Mon, Nov 9, 2020 at 8:20 AM frank li <[hidden email]> wrote:
>
> > I enforced  a lazy flag in DELETE code for tesing, but it is stil running
> > very slow. I mean that "Lazy" flag cannot solve the problem of running
> too
> > slow.
> >
> > On 2020/11/06 09:50:15, Юрий <[hidden email]> wrote:
> > > Hi Frank!
> > >
> > > There is an old ticket [1] - We will try to prioritize it to finish
> > before
> > > the end of the year it should prevent OOM for most cases.
> > >
> > > [1] https://issues.apache.org/jira/browse/IGNITE-9182
> > >
> > > вт, 3 нояб. 2020 г. в 18:53, frank li <[hidden email]>:
> > >
> > > > Current code logic for DELETE is as follows:
> > > > if WHERE clause contains a condition as "key=xxx", it uses fastUpdate
> > > > which remove the related item directly.
> > > >
> > > > else
> > > >     do select for update;
> > > >     for each row, call closure code "RMV" to remove it.
> > > >
> > > > 1. As "executeSelectForDml" get _KEY and _VAL columns for all
> condidate
> > > > rows, it often causes OOM when there are a lot of data  to delete.
> Why
> > do
> > > > we verify "val" during remove operation?
> > > >
> > > > 2. After selection,  why don't we just remove it with cache.remove as
> > > > fastUpdate does?
> > > >
> > > >
> > > >
> > >
> > > --
> > > Живи с улыбкой! :D
> > >
> >
>

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