Slow cache updates with indexing module enabled

classic Classic list List threaded Threaded
4 messages Options
xero xero
Reply | Threaded
Open this post in threaded view
|

Slow cache updates with indexing module enabled

Hi,
We are experiencing slow updates to a cache with multiple indexed fields
(around 25 indexes during testing but we expect to have many more) for
updates that are only changing one field. Basically, we have a
customer*->belongsto->*segment relationship and we have one column per
segment. Only one column is updated with a 1 or 0 if the customer belongs to
the segment.

During testing, we tried dropping half of the unrelated indexes (indexes
over fields that are not being updated) and we duplicate the performance. We
went from 1k ops to 2k ops approximately.

We found these cases may be related:
https://cwiki.apache.org/confluence/display/IGNITE/IEP-19%3A+SQL+index+update+optimizations
https://issues.apache.org/jira/browse/IGNITE-7015?src=confmacro

Could you please confirm us if IGNITE-7015 could be related to this
scenario? If yes, do you have any plans to continue the development of the
fix?


We are using Ignite 2.7.6 with 10 nodes, 2 backups, indexing module enabled
and persistence.

Cache Configuration: [name=xdp-contactcomcast-1, grpName=null,
memPlcName=xdp, storeConcurrentLoadAllThreshold=5, rebalancePoolSize=2,
rebalanceTimeout=10000, evictPlc=null, evictPlcFactory=null,
onheapCache=false, sqlOnheapCache=false, sqlOnheapCacheMaxSize=0,
evictFilter=null, eagerTtl=true, dfltLockTimeout=0, nearCfg=null,
writeSync=PRIMARY_SYNC, storeFactory=null, storeKeepBinary=false,
loadPrevVal=false, aff=RendezvousAffinityFunction [parts=1024, mask=1023,
exclNeighbors=false, exclNeighborsWarn=false, backupFilter=null,
affinityBackupFilter=null], cacheMode=PARTITIONED, atomicityMode=ATOMIC,
backups=2, invalidate=false, tmLookupClsName=null, rebalanceMode=ASYNC,
rebalanceOrder=0, rebalanceBatchSize=524288, rebalanceBatchesPrefetchCnt=2,
maxConcurrentAsyncOps=500, sqlIdxMaxInlineSize=-1, writeBehindEnabled=false,
writeBehindFlushSize=10240, writeBehindFlushFreq=5000,
writeBehindFlushThreadCnt=1, writeBehindBatchSize=512,
writeBehindCoalescing=true, maxQryIterCnt=1024,
affMapper=org.apache.ignite.internal.processors.cache.CacheDefaultBinaryAffinityKeyMapper@db5e319,
rebalanceDelay=0, rebalanceThrottle=0, interceptor=null,
longQryWarnTimeout=3000, qryDetailMetricsSz=0, readFromBackup=true,
nodeFilter=IgniteAllNodesPredicate [], sqlSchema=XDP_CONTACTCOMCAST_1,
sqlEscapeAll=false, cpOnRead=true, topValidator=null, partLossPlc=IGNORE,
qryParallelism=1, evtsDisabled=false, encryptionEnabled=false]


Thanks,








--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
aealexsandrov aealexsandrov
Reply | Threaded
Open this post in threaded view
|

Re: Slow cache updates with indexing module enabled

Hi,

SQL query performance can be not great because of several cases:

1)Incorrect indexes. Please check that your EXPLAIN contains indexes and doesn't have scans for joins:

INNER JOIN PUBLIC.PERSON P__Z1
    /* PUBLIC.PERSON.__SCAN_ */

Probably the inline size for used index is incorrect or wrong index used.

To solve this problem you should calculate the Inline for every index and check that your correct index used in EXPLAIN of your query. Here is the example of how inline for the field can be calculated:

long

0     1   9

| tag | value |

Total: 9 bytes


int 

0     1   5

| tag | value |

Total: 5 bytes


String

0     1 3             N

| tag | size | UTF-8 value |

Total: 3 + string length


POJO (BinaryObejct)

0     1 3     4 8     12 16 20       24 32 N

| tag | size | tag | size | BO flags | type ID | hash | length | schema info | BO body |

                          |               Binary object header               |

Total: 32 + N


2)GC pauses because of query execution without LAZY flag.

3)In the case of multiple joins the order of these joins can be incorrect because of H2 optimizer specific used in Ignite.

To fix this problem you should prepare the correct join order and set the "enforce join order" flag. When the BIG table will be joined to SMALL then it will be faster than otherwise:

select * from SMALLTABLE, BIGTABLE where SMALLTABLE.id = BIGTABLE.id - correct
select * from BIGTABLE , SMALLTABLEwhere SMALLTABLE.id = BIGTABLE.id - incorrect

Check the join order using the EXPLAIN command.

BR,
Andrei

2/12/2020 11:24 PM, xero пишет:
Hi,
We are experiencing slow updates to a cache with multiple indexed fields
(around 25 indexes during testing but we expect to have many more) for
updates that are only changing one field. Basically, we have a
customer*->belongsto->*segment relationship and we have one column per
segment. Only one column is updated with a 1 or 0 if the customer belongs to
the segment. 

During testing, we tried dropping half of the unrelated indexes (indexes
over fields that are not being updated) and we duplicate the performance. We
went from 1k ops to 2k ops approximately.

We found these cases may be related:
https://cwiki.apache.org/confluence/display/IGNITE/IEP-19%3A+SQL+index+update+optimizations
https://issues.apache.org/jira/browse/IGNITE-7015?src=confmacro

Could you please confirm us if IGNITE-7015 could be related to this
scenario? If yes, do you have any plans to continue the development of the
fix?


We are using Ignite 2.7.6 with 10 nodes, 2 backups, indexing module enabled
and persistence.

Cache Configuration: [name=xdp-contactcomcast-1, grpName=null,
memPlcName=xdp, storeConcurrentLoadAllThreshold=5, rebalancePoolSize=2,
rebalanceTimeout=10000, evictPlc=null, evictPlcFactory=null,
onheapCache=false, sqlOnheapCache=false, sqlOnheapCacheMaxSize=0,
evictFilter=null, eagerTtl=true, dfltLockTimeout=0, nearCfg=null,
writeSync=PRIMARY_SYNC, storeFactory=null, storeKeepBinary=false,
loadPrevVal=false, aff=RendezvousAffinityFunction [parts=1024, mask=1023,
exclNeighbors=false, exclNeighborsWarn=false, backupFilter=null,
affinityBackupFilter=null], cacheMode=PARTITIONED, atomicityMode=ATOMIC,
backups=2, invalidate=false, tmLookupClsName=null, rebalanceMode=ASYNC,
rebalanceOrder=0, rebalanceBatchSize=524288, rebalanceBatchesPrefetchCnt=2,
maxConcurrentAsyncOps=500, sqlIdxMaxInlineSize=-1, writeBehindEnabled=false,
writeBehindFlushSize=10240, writeBehindFlushFreq=5000,
writeBehindFlushThreadCnt=1, writeBehindBatchSize=512,
writeBehindCoalescing=true, maxQryIterCnt=1024,
affMapper=org.apache.ignite.internal.processors.cache.CacheDefaultBinaryAffinityKeyMapper@db5e319,
rebalanceDelay=0, rebalanceThrottle=0, interceptor=null,
longQryWarnTimeout=3000, qryDetailMetricsSz=0, readFromBackup=true,
nodeFilter=IgniteAllNodesPredicate [], sqlSchema=XDP_CONTACTCOMCAST_1,
sqlEscapeAll=false, cpOnRead=true, topValidator=null, partLossPlc=IGNORE,
qryParallelism=1, evtsDisabled=false, encryptionEnabled=false]


Thanks,








--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
xero xero
Reply | Threaded
Open this post in threaded view
|

Re: Slow cache updates with indexing module enabled

Hi Andrei, thanks for taking the time to answer my question. I will consider
your suggestion if we decide to switch to a multiple tables approach that
will require those JOIN considerations. But, in this case we have only 1
cache and the operation that we are executing is an update. We tried using
SQL-Update but we also tried using a CacheEntryProcessor directly. My
question is what is happening with all those indexes when an entry is
updated but, none of the indexed fields (except one) are being changed? In
our case, we are only flipping a boolean value of only 1 field. Is this
change triggering updates in ALL the indexes associated with the cache?

Cache is like this (with indexes on all fields):
id|(other fields)|segment_1|segment_2|segment_2|...|segment_99|segment_100

Then we try updating a batch of entries with an invokeAll using a
CacheEntryProcessor:
        public Void process(MutableEntry<Long, BinaryObject> entry, Object...
arguments) {
                        final BinaryObjectBuilder builder =
entry.getValue().toBuilder().setField("SEGMENT_1", true);
                        entry.setValue(builder.build());

                        return null;
                }
When we update entry field SEGMENT_1 field with a True, are the other 99
indexes updated?
Those tickets I mentioned seem to be related but I would like to have your
confirmation.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
aealexsandrov aealexsandrov
Reply | Threaded
Open this post in threaded view
|

Re: Slow cache updates with indexing module enabled

Hi,

Some recommendations should be applied to every SQL query:

1)You should avoid using __SCAN_
2)You should use LAZY if the result set is big.
3)You should inline the indexes

Please read my comments:

1)My question is what is happening with all those indexes when an entry is
updated but, none of the indexed fields (except one) are being changed? In
our case, we are only flipping a boolean value of only 1 field. Is this
change triggering updates in ALL the indexes associated with the cache?

Yes, all your indexes will be rebuilt (new value will be inserted in the index tree).

2)When we update entry field SEGMENT_1 field with a True, are the other 99
indexes updated?

It looks like yes.

3)Those tickets I mentioned seem to be related but I would like to have your
confirmation.

Yes, https://issues.apache.org/jira/browse/IGNITE-7015 is related to this behavior. You can try to highlite it on the development mail list.

BR,
Andrei
2/13/2020 5:50 PM, xero пишет:
Hi Andrei, thanks for taking the time to answer my question. I will consider
your suggestion if we decide to switch to a multiple tables approach that
will require those JOIN considerations. But, in this case we have only 1
cache and the operation that we are executing is an update. We tried using
SQL-Update but we also tried using a CacheEntryProcessor directly. My
question is what is happening with all those indexes when an entry is
updated but, none of the indexed fields (except one) are being changed? In
our case, we are only flipping a boolean value of only 1 field. Is this
change triggering updates in ALL the indexes associated with the cache?

Cache is like this (with indexes on all fields):
id|(other fields)|segment_1|segment_2|segment_2|...|segment_99|segment_100

Then we try updating a batch of entries with an invokeAll using a
CacheEntryProcessor:
	public Void process(MutableEntry<Long, BinaryObject> entry, Object...
arguments) {
			final BinaryObjectBuilder builder =
entry.getValue().toBuilder().setField("SEGMENT_1", true);
			entry.setValue(builder.build());

			return null;
		}
When we update entry field SEGMENT_1 field with a True, are the other 99
indexes updated?
Those tickets I mentioned seem to be related but I would like to have your
confirmation.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/