how to improve query performance in apache ignite

Question

I have setup an ignite cluster of two servers and one client. The cluster configuration is as follows :

IgniteConfiguration icfg = new IgniteConfiguration();

icfg.setIncludeEventTypes(EventType.EVT_TASK_STARTED,EventType.EVT_TASK_FINISHED,EventType.EVT_TASK_FAILED);

icfg.setMetricsUpdateFrequency(-1);

Ignite ignite = Ignition.start(icfg);

I have disabled the metrics update frequency and only enabled a few events notifications as suggested in the Performance Tips section of the Ignite documentation.

I have created a cache in the cluster with the following configurations :

CacheConfiguration<Integer,DataPOJO> cacheConfiguration = new CacheConfiguration<>();

cacheConfiguration.setStatisticsEnabled(false);

cacheConfiguration.setName("testCache");

I am loading the cache from the client with 100 000 entries.

The code for the POJO class used is as follows:

import java.io.Externalizable;
import java.io.IOException;
import java.io.ObjectInput;
import java.io.ObjectOutput;
import java.sql.Date;

import org.apache.ignite.binary.BinaryObjectException;
import org.apache.ignite.binary.BinaryReader;
import org.apache.ignite.binary.BinaryWriter;
import org.apache.ignite.binary.Binarylizable;
import org.apache.ignite.cache.query.annotations.QuerySqlField;

public class DataPOJO implements Externalizable,Binarylizable{

    private static final long serialVersionUID = 1L;

    @QuerySqlField(index = true)
    private int primaryKey;

    @QuerySqlField
    private int foreignKey1;

    @QuerySqlField
    private int foreignKey2;

    @QuerySqlField
    private String stringField1;

    @QuerySqlField
    private String stringField2;

    @QuerySqlField
    private String stringField3;

    @QuerySqlField
    private Date dateField;

    @QuerySqlField(index = true)
    private String stringField4;

    public int getPrimaryKey() {
        return primaryKey;
    }

    public void setPrimaryKey(int primaryKey) {
        this.primaryKey = primaryKey;
    }

    public int getForeignKey1() {
        return foreignKey1;
    }

    public void setForeignKey1(int foreignKey1) {
        this.foreignKey1 = foreignKey1;
    }

    public int getForeignKey2() {
        return foreignKey2;
    }

    public void setForeignKey2(int foreignKey2) {
        this.foreignKey2 = foreignKey2;
    }

    public String getStringField1() {
        return stringField1;
    }

    public void setStringField1(String stringField1) {
        this.stringField1 = stringField1;
    }

    public String getStringField2() {
        return stringField2;
    }

    public void setStringField2(String stringField2) {
        this.stringField2 = stringField2;
    }

    public String getStringField3() {
        return stringField3;
    }

    public void setStringField3(String stringField3) {
        this.stringField3 = stringField3;
    }

    public Date getDateField() {
        return dateField;
    }

    public void setDateField(Date dateField) {
        this.dateField = dateField;
    }

    public String getStringField4() {
        return stringField4;
    }

    public void setStringField4(String stringField4) {
        this.stringField4 = stringField4;
    }

    @Override
    public String toString() {
        return "DataPOJO [primaryKey=" + primaryKey + ", foreignKey1=" + foreignKey1 + ", foreignKey2=" + foreignKey2
                + ", stringField1=" + stringField1 + ", stringField2=" + stringField2 + ", stringField3=" + stringField3
                + ", dateField=" + dateField + ", stringField4=" + stringField4 + "]";
    }

    @Override
    public void readExternal(ObjectInput in) throws IOException, ClassNotFoundException {
        primaryKey = in.readInt();
        foreignKey1 = in.readInt();
        foreignKey2 = in.readInt();
        stringField1 = (String) in.readObject();
        stringField2 = (String) in.readObject();
        stringField3 = (String) in.readObject();
        stringField4 = (String) in.readObject();
        dateField = (Date) in.readObject();
    }

    @Override
    public void writeExternal(ObjectOutput out) throws IOException {
        out.writeInt(primaryKey);
        out.writeInt(foreignKey1);
        out.writeInt(foreignKey2);
        out.writeObject(stringField1);
        out.writeObject(stringField2);
        out.writeObject(stringField3);
        out.writeObject(stringField4);
        out.writeObject(dateField);
    }

    @Override
    public void writeBinary(BinaryWriter writer) throws BinaryObjectException {
        writer.writeInt("primarykey",primaryKey);
        writer.writeInt("foreignkey1",foreignKey1);
        writer.writeInt("foreignkey2",foreignKey2);
        writer.writeString("stringField1",stringField1);
        writer.writeString("stringField2",stringField2);
        writer.writeString("stringField3",stringField3);
        writer.writeString("stringField4",stringField4);
        writer.writeDate("dateField",new java.util.Date(dateField.getTime()));
    }

    @Override
    public void readBinary(BinaryReader reader) throws BinaryObjectException {
        primaryKey = reader.readInt("primarykey");
        foreignKey1 = reader.readInt("foreignkey1");
        foreignKey2 = reader.readInt("foreignkey2");
        stringField1 = reader.readString("stringField1");
        stringField2 = reader.readString("stringField2");
        stringField3 = reader.readString("stringField3");
        stringField4 = reader.readString("stringField4");
        dateField = new Date(reader.readDate("dateField").getTime());
    }

}

I didn't want to make use of java.io.Serializable as it hinders the performance. I created indices on two fields namely "primaryKey" and "stringField4".

The field stringField4 will be the string representation of the field primaryKey for every entry. If primaryKey = 1, then stringField4 = "1".

primaryKey is also the key for the any entry in the cache.

The query I performed from the client was to fetch 10,000 entries from the cache based upon 10,000 values of the field stringField4. The query is as follows :

IgniteCache<Integer,DataPOJO> testCache= ignite.getOrCreateCache("testCache");

SqlQuery<Integer,DataPOJO> query = new SqlQuery<>(DataPOJO.class,"stringField4 = ?");

long startTime,totalTimeElapsed = 0;

QueryCursor<Entry<Integer,DataPOJO>> cursor;

for(int i=1;i<=10000;i++){

    startTime = System.nanoTime();

    cursor = testCache.query(query.setArgs(i));

    for(Entry<Integer,DataPOJO> entry : cursor){
        System.out.println("Entry fetched with key "+entry.getKey());
        totalTimeElapsed += System.nanoTime() - startTime;
    }
}

System.out.println("Total time taken to execute query is "+totalTimeElapsed+"ns");

All the queries on an average took 9 to 10 seconds to execute.

Based upon the given data, Can Ignite give the same result in 100 to 200 milliseconds? Can I make in my cluster or cache configuration other than the one's mentioned in the Ignite Docs (I have tried all those) to improve the performance?

I don't want this to be a key based fetch and I know that it is way faster than queries.

score 1 · Answer 1 · answered Nov 18 '16 at 10:16

1

I was also facing the same issue like queries was running infinitely. so i will use correct indexes to run query in sub seconds so please check your query explain plan and check there is any full cache scan in query execution plan if you see that then put appropriate individual or grouped indexes. If you can share your one query and its explain plan then we can give you mode idea on it.

answered Nov 18 '16 at 10:16

Sandeep Purohit

3,652
18
22

I have shared the explain plan in the comment to @Valentin and the query in my question – Aniketh Jain Nov 18 '16 at 11:11
@AnikethJain can you please share query soo we can help you out for for indexes – Sandeep Purohit Nov 18 '16 at 11:24
please see my question the third section in it contains the query. Anyways here is the query : SqlQuery query = new SqlQuery<>(DataPOJO.class,"stringField4 = ?"); – Aniketh Jain Nov 18 '16 at 12:40
I have used the annotations and set the indexed types and also the H2 execution plan shows that the query is using index. Could you see my code and tell me what changes can I make? – Aniketh Jain Nov 20 '16 at 17:12

score 0 · Answer 2 · answered Nov 17 '16 at 21:44

0

It should be much faster if the index is used. I would recommend to run EXPLAIN and check the execution plan.

answered Nov 17 '16 at 21:44

Valentin Kulichenko

8,365
1
16
12

Thanks for the quick reply @Valentin. I have set the indexed types and the h2 plan for one of the query is as follows: – Aniketh Jain Nov 18 '16 at 06:37
[[SELECT DATAPOJO._KEY AS __C0, DATAPOJO._VAL AS __C1, DATAPOJO.PRIMARYKEY AS __C2, DATAPOJO.FOREIGNKEY1 AS __C3, DATAPOJO.FOREIGNKEY2 AS __C4, DATAPOJO.STRINGFIELD1 AS __C5, DATAPOJO.STRINGFIELD2 AS __C6, DATAPOJO.STRINGFIELD3 AS __C7, DATAPOJO.DATEFIELD AS __C8, DATAPOJO.STRINGFIELD4 AS __C9 FROM "cache1".DATAPOJO /* "cache1"."stringField4_idx": STRINGFIELD4 = ?1 */ WHERE STRINGFIELD4 = ?1] – Aniketh Jain Nov 18 '16 at 06:42
, [SELECT __C0 AS _KEY, __C1 AS _VAL, __C2 AS PRIMARYKEY, __C3 AS FOREIGNKEY1, __C4 AS FOREIGNKEY2, __C5 AS STRINGFIELD1, __C6 AS STRINGFIELD2, __C7 AS STRINGFIELD3, __C8 AS DATEFIELD, __C9 AS STRINGFIELD4 FROM PUBLIC.__T0 /* "cache1"."merge_scan" */]] – Aniketh Jain Nov 18 '16 at 06:42
Sorry for multiple comments the entire plan was not fitting in one comment. Looks like it is using the index from the query execution plan. – Aniketh Jain Nov 18 '16 at 06:43
Can you create a small project on GitHub so that I can run and reproduce the behavior? – Valentin Kulichenko Nov 23 '16 at 17:14
Aniketh Jain @Valentin Kulichenko. Any luck with this? Having a very similar problem – EugeneMi Aug 22 '18 at 14:06

how to improve query performance in apache ignite

2 Answers2