IEP-19: Optimize SQL indexes

[Vladimir Ozerov]

Igniters,

I heard a lot of complains around performance of our SQL indexes. Notably -
slow updates and slow execution of CREATE INDEX command on large data sets.
I summarized all known possible optimizations under a single IEP [1]. We
need to start working in this direction, starting from the most simple and
obvious things.

Please feel free to review IEP tickets and share additional suggestions or
comments or what else could be done to make our indexes faster.

Vladimir.

[1]
https://cwiki.apache.org/confluence/display/IGNITE/IEP-19%3A+SQL+index+update+optimizations

[dsetrakyan]

Thanks, Vladimir!

Looking at this IEP, it is not clear which tickets are more critical than
others. Also, the complexity of each ticket is unknown. Is there a way to
provide this information?

D.

On Wed, Apr 25, 2018 at 10:21 PM, Vladimir Ozerov <[hidden email]>
wrote:

> Igniters,
>
> I heard a lot of complains around performance of our SQL indexes. Notably -
> slow updates and slow execution of CREATE INDEX command on large data sets.
> I summarized all known possible optimizations under a single IEP [1]. We
> need to start working in this direction, starting from the most simple and
> obvious things.
>
> Please feel free to review IEP tickets and share additional suggestions or
> comments or what else could be done to make our indexes faster.
>
> Vladimir.
>
> [1]
> https://cwiki.apache.org/confluence/display/IGNITE/IEP-
> 19%3A+SQL+index+update+optimizations
>

[Vladimir Ozerov]

It is impossible to estimate what is more critical because it would require
prototypes for every idea to estimate the impact. Instead, we should start
working on the simplest things, such as IGINTE-8386 [1] or IGNITE-8384 [2].
And then gradually swtich to more and more complex changes.

[1] https://issues.apache.org/jira/browse/IGNITE-8386
[2] https://issues.apache.org/jira/browse/IGNITE-8384

On Wed, Apr 25, 2018 at 10:02 PM, Dmitriy Setrakyan <[hidden email]>
wrote:

> Thanks, Vladimir!
>
> Looking at this IEP, it is not clear which tickets are more critical than
> others. Also, the complexity of each ticket is unknown. Is there a way to
> provide this information?
>
> D.
>
> On Wed, Apr 25, 2018 at 10:21 PM, Vladimir Ozerov <[hidden email]>
> wrote:
>
> > Igniters,
> >
> > I heard a lot of complains around performance of our SQL indexes.
> Notably -
> > slow updates and slow execution of CREATE INDEX command on large data
> sets.
> > I summarized all known possible optimizations under a single IEP [1]. We
> > need to start working in this direction, starting from the most simple
> and
> > obvious things.
> >
> > Please feel free to review IEP tickets and share additional suggestions
> or
> > comments or what else could be done to make our indexes faster.
> >
> > Vladimir.
> >
> > [1]
> > https://cwiki.apache.org/confluence/display/IGNITE/IEP-
> > 19%3A+SQL+index+update+optimizations
> >
>

[dsetrakyan]

On Thu, Apr 26, 2018 at 9:09 PM, Vladimir Ozerov <[hidden email]>
wrote:

> It is impossible to estimate what is more critical because it would require
> prototypes for every idea to estimate the impact. Instead, we should start
> working on the simplest things, such as IGINTE-8386 [1] or IGNITE-8384 [2].
> And then gradually swtich to more and more complex changes.
>
> [1] https://issues.apache.org/jira/browse/IGNITE-8386
> [2] https://issues.apache.org/jira/browse/IGNITE-8384


Vladimir, there is no way for me to tell how his tickets affect any Ignite
users. What will change for our users.

I completely disagree about  about not prioritizing. We should identify how
critical the issues are for our users and start working on them in that
order.

D.

[npordash]

I think any ticket that results in less work being done is important, even if
it's small, because the accumulation of these savings can have significant
impact.

I can't comment on how a lot of these may impact users like myself, but I'd
like to echo that this is indeed a severe pain point that I recently ran
into while trying to improve write rates and index updates was the single
biggest limiting factor.

For example, I have a table with two indices on it and the columns in the
index are only comprised of columns that make up the PK. Writes are done
using K/V APIs and on average only about 15% of the writes would involve
creating a new key, the rest are updating the value only. At a write rate of
about 60k/sec (to a node consisting of 16 cores) the cpu utilization is at
about 60%. However, if I remove the indices the cpu utilization drops to
about 30%. That's really significant and unexpected.

AFAICT this scenario will largely benefit from
https://issues.apache.org/jira/browse/IGNITE-7015.

I think for any workload that's heavy on writes and requires low latency
reads against large data sets any performance improvement you can make to
index updates/traversal/etc is a huge huge win.

-Nick



--
Sent from: http://apache-ignite-developers.2346864.n4.nabble.com/