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/
