回复： Fetched result use too much time

Mon, 18 Sep 2017 19:08:36 -0700 


Please see the attachment.
I have set query parallelism  to 30. it took 42 seconds.
But it is not enough.
I excepted it took less than 3 seconds.


then,I have 3 nodes.


As for the 3589 number, we need to check the number of ID using in conditions. 
Only the number of times used is equal to the record in the in condition. 
That's the record we need. This is the business scenario required. I can't 
change this.


Thanks for your suggestion.
Lucky




2017年09月18日 21:55,Vladimir Ozerov<voze...@gridgain.com> :
Hi Lucky,


Could you please share you data model and node/cache configuration? I want to 
make sure that proper indexes are set. I will be able to advise something then. 
As I quick suggestion you may try to increase query parallelism on your 
"databaseDAssignCache". Please try setting it to the number of cores on your 
server nodes. Relevant property - CacheConfifuration.queryParallelism. Btw, how 
many nodes do you have?


Also I am struggling to understand the number "3589". Why this number appears 
both as ">= 3589" condition and as a number of parameters inside "IN" clause? 


Vladimir.


package com.kingdee.ignite.module.basedata.customer.entity;

import java.io.Serializable;
import java.sql.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

import org.apache.ignite.cache.query.annotations.QuerySqlField;
import org.hibernate.annotations.GenericGenerator;

@Entity
@Table(name = "t_Bd_Databasedassign")
public class DatabaseDAssign implements Serializable {

        private static final long serialVersionUID = 7117461037272988577L;

        @QuerySqlField(index = true)
        private String FID;

        @QuerySqlField
        private String FCREATORID;

        @QuerySqlField
        private Date FCREATETIME;

        @QuerySqlField
        private String FLASTUPDATEUSERID;

        @QuerySqlField
        private Date FLASTUPDATETIME;

        @QuerySqlField
        private String FCONTROLUNITID;

        @QuerySqlField(index=true, orderedGroups={@QuerySqlField.Group(
                    name = "IX_BDFW_DBDAACUD", order = 1), @QuerySqlField.Group(
                                    name = "IX_BDFW_FASS", order = 2)})
        private String FDATABASEDID;

        @QuerySqlField(index = true, orderedGroups={@QuerySqlField.Group(
                    name = "IX_BDFW_DBDAACUD", order = 0), @QuerySqlField.Group(
                                    name = "IX_BDFW_FASS", order = 0)})
        private String FASSIGNCUID;

        @QuerySqlField(orderedGroups={@QuerySqlField.Group(
                                    name = "IX_BDFW_FASS", order = 1)})
        private String FBOSOBJECTTYPE;

        @QuerySqlField
        private Integer FSTATUS;

        /********** Getters and Setters ******************/
        @Id
        @GeneratedValue(generator = "personGenerator")
        @GenericGenerator(name = "personGenerator", strategy = "assigned")
        @Column(name = "FID")
        public String getFID() {
                return FID;
        }

        public void setFID(String id) {
                this.FID = id;
        }

        @Column(name = "FCONTROLUNITID")
        public String getFCONTROLUNITID() {
                return FCONTROLUNITID;
        }

        public void setFCONTROLUNITID(String fCONTROLUNITID) {
                FCONTROLUNITID = fCONTROLUNITID;
        }

        @Column(name = "FLASTUPDATETIME")
        public Date getFLASTUPDATETIME() {
                return FLASTUPDATETIME;
        }

        public void setFLASTUPDATETIME(Date fLASTUPDATETIME) {
                FLASTUPDATETIME = fLASTUPDATETIME;
        }

        @Column(name = "FLASTUPDATEUSERID")
        public String getFLASTUPDATEUSERID() {
                return FLASTUPDATEUSERID;
        }

        public void setFLASTUPDATEUSERID(String fLASTUPDATEUSERID) {
                FLASTUPDATEUSERID = fLASTUPDATEUSERID;
        }

        @Column(name = "FCREATETIME")
        public Date getFCREATETIME() {
                return FCREATETIME;
        }

        public void setFCREATETIME(Date fCREATETIME) {
                FCREATETIME = fCREATETIME;
        }

        @Column(name = "FCREATORID")
        public String getFCREATORID() {
                return FCREATORID;
        }

        public void setFCREATORID(String fCREATORID) {
                FCREATORID = fCREATORID;
        }

        @Column(name = "FDATABASEDID")
        public String getFDATABASEDID() {
                return FDATABASEDID;
        }

        public void setFDATABASEDID(String fDATABASEDID) {
                FDATABASEDID = fDATABASEDID;
        }

        @Column(name = "FASSIGNCUID")
        public String getFASSIGNCUID() {
                return FASSIGNCUID;
        }

        public void setFASSIGNCUID(String fASSIGNCUID) {
                FASSIGNCUID = fASSIGNCUID;
        }

        @Column(name = "FBOSOBJECTTYPE")
        public String getFBOSOBJECTTYPE() {
                return FBOSOBJECTTYPE;
        }

        public void setFBOSOBJECTTYPE(String fBOSOBJECTTYPE) {
                FBOSOBJECTTYPE = fBOSOBJECTTYPE;
        }

        @Column(name = "FSTATUS")
        public Integer getFSTATUS() {
                return FSTATUS;
        }

        public void setFSTATUS(Integer fSTATUS) {
                FSTATUS = fSTATUS;
        }

}

package com.kingdee.ignite.common.config;

import javax.cache.configuration.MutableConfiguration;

import org.apache.ignite.cache.CacheMode;
import org.apache.ignite.cache.CacheWriteSynchronizationMode;
import org.apache.ignite.configuration.CacheConfiguration;

import com.kingdee.ignite.common.dao.SqlCustomFunction;

public abstract class AbstractCacheConfiguration<K, V> extends 
CacheConfiguration<K, V>{
        private static final long serialVersionUID = 6000710247906756326L;

        public AbstractCacheConfiguration(){

        initialization();
    }

    public void initialization(){
        //设置默认备份节点0
        setDefaultBackups();
        //设置默认分区缓存
        setDefaultCacheMode();
        //设置默认同步备份
        //等待主节点的写或者提交操作完成
        //但是不等待备份节点更新完成
        setDefaultWriteSynchronizationMode();
        //通写
        setDefaultWriteThrough();
        //默认启用后写
        setDefaultWriteBehindEnabled();
        //默认启用通读
        setDefaultReadThrough();

        //后写刷新默认设置为10240
        setDefaultWriteBehindFlushSize();

        //每隔10秒 后写刷新
        setDefaultWriteBehindFlushFrequency();
        //设置持久化到数据库的记录数：10000
        setWriteBehindBatchSize();
        
        setWriteBehindFlushThreadCount();
        
        //再平衡消息的大小，设置为4M
        setRebalanceBatchSize();
        setIndexedTypes();
        //再平衡延迟时间，设置为100 ms
        setRebalanceThrottle();
        
        //定义获取bosUid的sql函数
        setSqlFunctionBosUid();
        
        //查询并行化
        super.setQueryParallelism(30);
        

    }

    /**
     * 使用默认分区模式
     * @return CacheConfiguration实例
     */
    public CacheConfiguration<K, V> setDefaultCacheMode() {

        CacheMode partitioned = CacheMode.PARTITIONED;

        return super.setCacheMode(partitioned);
    }

    /**
     * 出于性能原因, 设置备份节点为1
     * @return CacheConfiguration实例
     */
    public CacheConfiguration<K, V> setDefaultBackups() {
        return super.setBackups(1);
    }

    /**
     * 默认设置客户端节点会等待主节点的写或者
提交操纵完成，但不会等待备份节点的更新完成
     * @return CacheConfiguration实例
     */
    public CacheConfiguration<K, V> setDefaultWriteSynchronizationMode() {
        CacheWriteSynchronizationMode primarySync = 
CacheWriteSynchronizationMode.FULL_ASYNC;
        return super.setWriteSynchronizationMode(primarySync);
    }

    /**
     * 设置后写启用
     * @return CacheConfiguration实例
     */
    public CacheConfiguration<K, V> setDefaultWriteBehindEnabled() {
        return super.setWriteBehindEnabled(true);
    }

    /**
     * 设置通读启用
     * @return CacheConfiguration实例
     */
    public MutableConfiguration<K, V> setDefaultReadThrough() {
        return super.setReadThrough(true);
    }

    public MutableConfiguration<K, V> setDefaultWriteThrough() {
        return super.setWriteThrough(true);
    }

    /**
     * 后写缓存的最大值，如果超过了这个限值，
     * 所有的缓存数据都会被刷入缓存存储然后写缓存被清
空。
     * 如果值为0，刷新操作将会依据刷新频率间隔，
     * 注意不能将写缓存大小和刷新频率都设置为0
     * @return CacheConfiguration实例
     */
    public CacheConfiguration<K, V> setDefaultWriteBehindFlushSize() {
        return super.setWriteBehindFlushSize(10240);
    }
    
    public CacheConfiguration<K, V> setWriteBehindBatchSize() {
        return super.setWriteBehindBatchSize(10000);
    }
    
    public CacheConfiguration<K, V> setDefaultWriteBehindFlushFrequency() {
        return super.setWriteBehindFlushFrequency(10000);
    }
    
    public CacheConfiguration<K, V> setIndexedTypes(Class<?> clazz){

        return super.setIndexedTypes(String.class,clazz);
        
    }
    
    public CacheConfiguration<K, V> setWriteBehindFlushThreadCount(){
        return super.setWriteBehindFlushThreadCount(3);
    }
    
    public CacheConfiguration<K,V> setRebalanceBatchSize(){
        
        return super.setRebalanceBatchSize(4*1024*1024);
    }
    
    public CacheConfiguration<K,V> setRebalanceThrottle(){
        return super.setRebalanceThrottle(100);
    }
    
    public CacheConfiguration<K,V> setSqlFunctionBosUid(){
        return super.setSqlFunctionClasses(SqlCustomFunction.class);
    }
    
}

Reply via email to