SQL performance very slow compared to KV in pure-in-memory cache?

[summasumma <by...@gmail.com>]

Hi,

I was trying to benchmark ignite for with YCSB in both KV and SQL model.

Ignite is 2 node cluster with 4CPU and 24GB ram and capable 10Gig n/w cards.
Persistance=false(disabled) and backup=1 

With this setup, when i ran ycsb for insert and read operations i get
following:
1) 100% insert performance: 
    KV Store ---> 150k ops
    SQL        --->   50k ops

2) 100% read performance:  
   KV Store ---> 430k ops
   SQL        --->142k ops

Can i know if these numbers are expected in ignite? why is SQL performance
almost 1/3rd of KV performance?
Is there anything/configuration etc can boost the performance for sql? 

(note: i just tried key_index using xml config: <bean
class="org.apache.ignite.cache.QueryIndex">
                            <constructor-arg value="actual_key"/> which
infact degraded insert operation a little)

This is my ignite configuration:
ignite-sql.xml
<http://apache-ignite-users.70518.x6.nabble.com/file/t2137/ignite-sql.xml>  

Any help highly appreciated.

Thanks,
...summa



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

RE: SQL performance very slow compared to KV in pure-in-memory cache?

[Stanislav Lukyanov <st...@gmail.com>]

Do you use the same config for both runs?
If you use ignite-sql.xml for SQL and ignite.xml for key-value than key-value keys 
indeed should be much faster, because SQL features are not used there and Ignite
doesn’t have to maintain all the SQL indexes, etc.

Stan

From: summasumma
Sent: 7 января 2019 г. 12:24
To: user@ignite.apache.org
Subject: Re: SQL performance very slow compared to KV in pure-in-memory cache?

Thanks Naveen.

I am definitely not looking for better performance in SQL than KV store. But
just wanted to know if the results im getting in scale of 1:4 performance
between SQL and KV store is as expected or not, say a particular operations
give 400k ops in KV but same size records with same operations gives only
100k ops in SQL.

Furthermore, is there any performance tuning which we can do to improve the
SQL performance slightly?

Note: i have tried sqlOnheapCacheEnabled in cache configuration, but still
performance didn't improve.

Thanks,
...summa



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

Re: SQL performance very slow compared to KV in pure-in-memory cache?

[summasumma <by...@gmail.com>]

Thanks Naveen.

I am definitely not looking for better performance in SQL than KV store. But
just wanted to know if the results im getting in scale of 1:4 performance
between SQL and KV store is as expected or not, say a particular operations
give 400k ops in KV but same size records with same operations gives only
100k ops in SQL.

Furthermore, is there any performance tuning which we can do to improve the
SQL performance slightly?

Note: i have tried sqlOnheapCacheEnabled in cache configuration, but still
performance didn't improve.

Thanks,
...summa



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

Re: SQL performance very slow compared to KV in pure-in-memory cache?

[Naveen <na...@gmail.com>]

As per my knowledge, results are quite obvious and expected, KV definitely
gives better performance compared to SQL, since SQL (table) is wrapper built
on top of cache. 

If you are looking for better performance than KV, you can even go for
storing data in the binary form. if we store data in binary, we can even
avoid serialization and de-serialization while doing all the operations like
GETs/PUTs. 

Thanks
Naveen



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

Re: SQL performance very slow compared to KV in pure-in-memory cache?

[summasumma <by...@gmail.com>]

Can anyone throw some light on this please?



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