ignite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Vladimir Ozerov (JIRA)" <j...@apache.org>
Subject [jira] [Created] (IGNITE-6025) SQL: improve CREATE INDEX performance
Date Thu, 10 Aug 2017 09:21:00 GMT
Vladimir Ozerov created IGNITE-6025:

             Summary: SQL: improve CREATE INDEX performance
                 Key: IGNITE-6025
                 URL: https://issues.apache.org/jira/browse/IGNITE-6025
             Project: Ignite
          Issue Type: Improvement
          Components: sql
    Affects Versions: 2.1
            Reporter: Vladimir Ozerov
             Fix For: 2.2

When bulk data load is performed, it is considered a good practice to bypass certain facilities
of underlying engine to achieve greater throughput. E.g., TX or MVCC managers can by bypassed,
global table lock can be held instead of fine-grained page/row/field locks, etc.. 

Another widely used technique is to drop table indexes and re-build them form scratch when
load finished. This is now possible with help of {{CREATE INDEX}} command which could be executed
in runtime. However, experiments with large data sets shown that {{DROP INDEX}} -> load
-> {{CREATE INDEX}} is *much slower* than simple load to indexed table. The reasons for
this are both inefficient implementation of {{CREATE INDEX}} command, as well as some storage
architectural decisions.

1) Index is created by a single thread; probably multiple threads could speed it up and the
cost of higher CPU usage. But how to split iteration between several threads?
2) Cache iteration happens through primary index. So we read an index page, but to read entries
we have to navigate to data page. If single data page is referenced from N places in the index
tree, we will read it N times. This leads to bad cache locality in memory-only case, and to
excessive disk IO in case of persistence. This could be avoided, if we iterate over data pages,
and index all data from a single page at once.
3) Another widely used technique is building BTree in bottom-up approach. That is, we sort
all data rows first, then build leafs, then go one level up, etc.. This approach could give
us the best build performance possible, especially if p.2 is implemented. However it is the
most difficult optimization, which will require implementation of spilling to disk if result
set is too large.

This message was sent by Atlassian JIRA

View raw message