Re: Failed to map keys for cache (all partition nodes left the grid)

[Alex Plehanov]

Hello,

Does baseline topology include both of your server nodes?

2018-08-21 3:32 GMT+03:00 scottmf :

> Hi, I'm seeing the errors below in my ignite cluster. This occurs
> consistently in my env when I apply a "rolling upgrade" where I take down
> one node at a time by updating them with a new jar file then start it up.
> Once the node is up I then apply this to the next node and so on.
>
> Notes:
> - The cluster is using persistence and the cache is partitioned with one
> backup.
> - The error below is from a client node that is trying to access its
> cache.
> - I see that all nodes in the cluster are using the same topology version
> and they are all sync'd - they show two server nodes and one client node.
>
> here is the DataStorageConfiguration:
>
> DataStorageConfiguration dataStorageConfiguration = new 
> DataStorageConfiguration();
> 
> dataStorageConfiguration.setDefaultDataRegionConfiguration(defaultDataRegion);
> dataStorageConfiguration.setPageSize(8192);
> dataStorageConfiguration.setMetricsEnabled(true);
> dataStorageConfiguration.setWriteThrottlingEnabled(true);
> 
> dataStorageConfiguration.setStoragePath("/var/lib/ignite/persistence");
> dataStorageConfiguration.setWalMode(WALMode.LOG_ONLY);
> dataStorageConfiguration.setWalFlushFrequency(2000);
> dataStorageConfiguration.setWalPath("/var/lib/ignite/wal");
> 
> dataStorageConfiguration.setWalArchivePath("/var/lib/ignite/wal/archive");
>
> Any idea of what could be happening?
>
> [priority='ERROR' thread='http-nio-8080-exec-9' 
> class='org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[/].[dispatcherServlet]@182']
>  Servlet.service() for servlet [dispatcherServlet] in context with path [] 
> threw exception [Request processing failed; nested exception is 
> org.apache.ignite.cache.CacheServerNotFoundException: Failed to map keys for 
> cache (all partition nodes left the grid) [topVer=AffinityTopologyVersion 
> [topVer=27, minorTopVer=0], cache=helloCache]] with root cause
> org.apache.ignite.internal.cluster.ClusterTopologyServerNotFoundException: 
> Failed to map keys for cache (all partition nodes left the grid) 
> [topVer=AffinityTopologyVersion [topVer=27, minorTopVer=0], cache=helloCache]
>   at 
> org.apache.ignite.internal.processors.cache.distributed.dht.GridPartitionedSingleGetFuture.serverNotFoundError(GridPartitionedSingleGetFuture.java:711)
>  ~[ignite-core-2.6.0.jar:2.6.0]
>   at 
> org.apache.ignite.internal.processors.cache.distributed.dht.GridPartitionedSingleGetFuture.mapKeyToNode(GridPartitionedSingleGetFuture.java:332)
>  ~[ignite-core-2.6.0.jar:2.6.0]
>   at 
> org.apache.ignite.internal.processors.cache.distributed.dht.GridPartitionedSingleGetFuture.map(GridPartitionedSingleGetFuture.java:216)
>  ~[ignite-core-2.6.0.jar:2.6.0]
>   at 
> org.apache.ignite.internal.processors.cache.distributed.dht.GridPartitionedSingleGetFuture.init(GridPartitionedSingleGetFuture.java:208)
>  ~[ignite-core-2.6.0.jar:2.6.0]
>   at 
> org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache.getAsync0(GridDhtAtomicCache.java:1391)
>  ~[ignite-core-2.6.0.jar:2.6.0]
>   at 
> org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache.access$1600(GridDhtAtomicCache.java:130)
>  ~[ignite-core-2.6.0.jar:2.6.0]
>   at 
> org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache$16.apply(GridDhtAtomicCache.java:469)
>  ~[ignite-core-2.6.0.jar:2.6.0]
>   at 
> org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache$16.apply(GridDhtAtomicCache.java:467)
>  ~[ignite-core-2.6.0.jar:2.6.0]
>   at 
> org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache.asyncOp(GridDhtAtomicCache.java:756)
>  ~[ignite-core-2.6.0.jar:2.6.0]
>   at 
> org.apache.ignite.internal.processors.cache.distributed.dht.atomic.GridDhtAtomicCache.getAsync(GridDhtAtomicCache.java:467)
>  ~[ignite-core-2.6.0.jar:2.6.0]
>   at 
> org.apache.ignite.internal.processors.cache.GridCacheAdapter.get0(GridCacheAdapter.java:4556)
>  ~[ignite-core-2.6.0.jar:2.6.0]
>   at 
> org.apache.ignite.internal.processors.cache.GridCacheAdapter.get(GridCacheAdapter.java:4537)
>  ~[ignite-core-2.6.0.jar:2.6.0]
>   at 
> org.apache.ignite.internal.processors.cache.GridCacheAdapter.get(GridCacheAdapter.java:1350)
>  ~[ignite-core-2.6.0.jar:2.6.0]
>   at 
> org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.get(IgniteCacheProxyImpl.java:907)
>  ~[ignite-core-2.6.0.jar:2.6.0]
>   at 
> org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.get(GatewayProtectedCacheProxy.java:608)
>  ~[ignite-core-2.6.0.jar:2.6.0]
>   at com.example.cache.ignite.IgniteCache.get(IgniteCache.java:67) 
> ~[ignite-cache-manager-1.5.14.1.15-SNAPSHOT.jar:?]
>   at 
> org.springframework.cache.intercepto

Re: NPE exception in KMeansTrainer

[DocDVZ]

@zaleslaw
I have a Map> where outer map is userId ->
hitsMap and inner hitsMap is domain -> hits. So I need to do domain vectors
for each of the user. Cache populated through Cache.put operation.

private Map> hits;
<...>
storageName = "profile-clustering-" + LocalDateTime.now();
IgniteCache cache =
ignite.getOrCreateCache(storageName);
<...>
AtomicInteger ai = new AtomicInteger(0);
hits.forEach((profileId, hit) -> {
List features = new ArrayList<>();
// surrogate label for now
features.add(Long.valueOf(ai.incrementAndGet()));
ai.compareAndSet(5, 0);
sortedDomains.forEach(d -> features.add(hit.getOrDefault(d,
0L)));
double[] doubles = features.stream().mapToDouble(p ->
p).toArray();
if (profileId != null && !profileId.isEmpty()){
cache.put(profileId, doubles);
}
});

No specific affinity function defined, total dataset contains ~3.1k users &
~8k domains.




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

Re: NPE exception in KMeansTrainer

[DocDVZ]

Also, is there any ways to workaround this issue in 2.6 to make it works?



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

Data index loss problem

[hulitao198758]

Recently discovered a problem, don't know what the reason, one of the data in
the Table a Long field with the Select * from Table where nameid = value
when the query data, but the use of Select * from Table where nameid like '%
value can find data, prove the existence of the data is the data, but do not
check number is equal to the data, it is strange that do not check this one,
other data no problem. Can the index of this data be reconstructed or
repaired? Or is there any way to query the index data in the cache and the
specific storage of this data in the index?



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

Re: Ignite.NET how to cancel tasks

[slava.koptilin]

Hello Maksym,

Sorry for the delay. I'll try the modified example.

I am not an expert in C#/.Net, so I cannot guarantee that the following
topic will be helpful, but
please take a look at
https://apacheignite-net.readme.io/v2.6/docs/asynchronous-support#section-async-continuations

Thanks,
S.




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

does ignite.net plugin provider only work with OnIgniteStart and OnIgniteStop

[wt]

i am looking at adding custom security using Kerberos but it doesn't look
like there are any methods that can be used to add authorization logic to
when accessing cache. Am i wrong here because it looks like it can only work
in a limited scope (startup and shutdown)

documentation:

IPluginProvider implementation is the work-horse of the newly added plugin.
It deals with the Ignite node life cycle by processing the calls to
OnIgniteStart and OnIgniteStop methods. In addition, it can provide an
optional API to be used by an end user via the GetPlugin() method.


Does anyone know how a plugin from .net can intercept client calls to apply
authorization?



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

Ignite Indexes

[Prasad Bhalerao]

Hi,

I have gone through this link
,
but still have some doubts.

Please check the following example.

public class TestData
  @QuerySqlField
  private long id;
  @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "data_idx1",
*order* = 1)})
  private long field1;
  @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "data_idx1",
*order* = 4)})
  private int field2;
  @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = "data_idx1",
*order* = 2)})
  private int field3;

1) In this code I have created a group index on field1, field2 and field3.
field1 has order=1.
Does it mean that the rows will be sorted on first on *field1*(order=1) and
then *field3*(order=2) and then *field2*(order=4) ?
I am just trying to understand the significance of "ORDER".

2) Keeping the indexes as above, Will ignite be able to use the indexes in
following cases :
Please explain why if the ignite is not able to use the indexes.

I am trying to understand how ignite decides if it can use a index or not?

a) select * from TestData where field1 = ?

b) select * from  TestData where field1 = ? and field2 = ?

c) select * from  TestData where field2 = ?

d) select * from  TestData where field3 = ?

e) select * from  TestData where field2 = ? and field3 = ?

3) Should the columns in where clause be in order in which the group
indexes are defined to make use of indexes?


Prasad

Query execution too slow

[Prasad Bhalerao]

Hi,
Following SQL is taking too long to execute. It seems that highlighted
condition is not using indexes.

I have created the indexes as follows. What should be done to makes use of
index created on value column/property?

Can someone please advise?

public class DnsNetBiosAssetGroupData implements
Data,UpdatableData {

  @QuerySqlField
  private long id;
  @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"dns_nb_asset_group_data_idx1", order = 1)})
  private long assetGroupId;
  @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"dns_nb_asset_group_data_idx1", order = 2)})
  private int assetTypeInd;
  private int partitionId;
  @QuerySqlField
  private long subscriptionId;
  @QuerySqlField
  private long updatedDate;
  @QuerySqlField (index = true)
  private String value;


2018-08-21 16:53:37,765 143847 [pub-#72%springDataNode%] WARN
o.a.i.i.p.query.h2.IgniteH2Indexing - Query execution is too long
[time=30638 ms,
sql='SELECT
DNSNB__Z0.VALUE __C0_0,
DNSNB__Z0.ID __C0_1
FROM DNS_NB_ASSET_GROUP_DETAIL_CACHE.DNSNETBIOSASSETGROUPDATA DNSNB__Z0
 INNER JOIN TABLE(VALUE VARCHAR=?1) TEMP__Z1
 ON TRUE
WHERE (DNSNB__Z0.VALUE = TEMP__Z1.VALUE) AND ((DNSNB__Z0.ASSETTYPEIND = ?4)
AND ((DNSNB__Z0.SUBSCRIPTIONID = ?2) AND (DNSNB__Z0.ASSETGROUPID = ?3)))',

plan=
SELECT
DNSNB__Z0.VALUE AS __C0_0,
DNSNB__Z0.ID AS __C0_1
FROM DNS_NB_ASSET_GROUP_DETAIL_CACHE.DNSNETBIOSASSETGROUPDATA DNSNB__Z0
/* DNS_NB_ASSET_GROUP_DETAIL_CACHE.DNS_NB_ASSET_GROUP_DATA_IDX1:
ASSETTYPEIND = ?4
AND ASSETGROUPID = ?3
 */
/* WHERE (DNSNB__Z0.ASSETGROUPID = ?3)
AND ((DNSNB__Z0.ASSETTYPEIND = ?4)
AND (DNSNB__Z0.SUBSCRIPTIONID = ?2))
*/
INNER JOIN TABLE(VALUE VARCHAR=?1) TEMP__Z1
/* function: VALUE = DNSNB__Z0.VALUE */
ON 1=1
WHERE (DNSNB__Z0.VALUE = TEMP__Z1.VALUE)
AND ((DNSNB__Z0.ASSETTYPEIND = ?4)
AND ((DNSNB__Z0.SUBSCRIPTIONID = ?2)
AND (DNSNB__Z0.ASSETGROUPID = ?3)))

Thanks,
Prasad

Re: Apache Ignite SQL- read Only User Creation

[aealexsandrov]

Hi,

I believe that you said about
https://apacheignite.readme.io/docs/advanced-security

It will provide the possibility to create/drop/alter the users. Also, it
provides the only simple authentification with the password. You can't set
the rules like read-only using it.

Also, you can try to implement the GridSecurityProcessor interface and
integrate this logic there or use some third-party plugin for Ignite that
already contains the security functionality. For example  this one
  .

Some information about how it could be done you can see here:

http://apache-ignite-users.70518.x6.nabble.com/Authentication-for-Apache-Ignite-2-5-td22565.html

BR,
Andrei



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

ignite.net config (applying spring and .net config)

[wt]

Hi

I am trying to work out if i can start ignite using the spring config but
then register a custom .net plugin through .net but i can't find any
documentation on how this can be done apart from this section from the
following documentation (see in bold for the specific reference to this)

https://apacheignite-net.readme.io/docs/configuration

/Spring XML
Spring XML allows native java-based Ignite configuration. Spring config file
can be provided via Ignition.Start(string) method and
IgniteConfiguration.SpringConfigUrl property.

When IgniteConfiguration.SpringConfigUrl property is used, Spring config is
loaded first, *and other IgniteConfiguration properties are applied on top
of it.* This is useful when some Java property is not supported in
Ignite.NET directly./

What i would like to know is how can i register a plugin using .net classes
i have written after i have started the services with this code


/ var r = Ignition.Start(@"C:\ignite_rmdbs.xml");

 r.GetCluster().SetActive(true);
/

There is a useful plugin example but it is referring to a .net plugin that
calls an existing java plugin. The problem is in that example the config is
all done in .net. I have searched around and not getting much luck with an
example of this if it is possible. 







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

Re: ignite.net config (applying spring and .net config)

[Ilya Kasnacheev]

Hello!

Here's the sample snippet:

var cfg = new IgniteConfiguration()
{
SpringConfigUrl = "ignite-config.xml",
BinaryConfiguration = new
BinaryConfiguration
{
NameMapper = new
BinaryBasicNameMapper { IsSimpleName = true }
}
// Set other properties here
};

Regards,

-- 
Ilya Kasnacheev

2018-08-21 16:25 GMT+03:00 wt :

> Hi
>
> I am trying to work out if i can start ignite using the spring config but
> then register a custom .net plugin through .net but i can't find any
> documentation on how this can be done apart from this section from the
> following documentation (see in bold for the specific reference to this)
>
> https://apacheignite-net.readme.io/docs/configuration
>
> /Spring XML
> Spring XML allows native java-based Ignite configuration. Spring config
> file
> can be provided via Ignition.Start(string) method and
> IgniteConfiguration.SpringConfigUrl property.
>
> When IgniteConfiguration.SpringConfigUrl property is used, Spring config
> is
> loaded first, *and other IgniteConfiguration properties are applied on top
> of it.* This is useful when some Java property is not supported in
> Ignite.NET directly./
>
> What i would like to know is how can i register a plugin using .net classes
> i have written after i have started the services with this code
>
>
> / var r = Ignition.Start(@"C:\ignite_rmdbs.xml");
>
>  r.GetCluster().SetActive(true);
> /
>
> There is a useful plugin example but it is referring to a .net plugin that
> calls an existing java plugin. The problem is in that example the config is
> all done in .net. I have searched around and not getting much luck with an
> example of this if it is possible.
>
>
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Re: Ignite and Persistent Collections

[ilya.kasnacheev]

Hello!

My recommendation is to re-send this question to developer list, since it
concerns design considerations and not already shipped features.

Regards,



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

Re: Ignite Indexes

[ilya.kasnacheev]

Hello!

Regarding index order:

Index order basically means that records in the data_idx1 index will be
sorted by field1; if two records have the same field1 value, field3 will be
used as sorting tier, and if both field1 and field3 are same, field2 will be
used as sorting tier. Index is basically a sorted list of row ids with some
bells and whistles on top.

This means, you can look up data when you specify field1 (using binary
search). If you don't specify field1, you can't use this index. If you use
field3 also, you can look data up more precisely. Then you can also use
field2. You can't use field2 without field3 and either one without field1.

So in your case, a) and b) will benefit from having index data_idx1, but
neither one will use fields other than field1. All other statements won't
use this index at all.

Regards,



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

Re: ignite.net config (applying spring and .net config)

[wt]

Brilliant thanks a million Ilya. pts working with the plugin 



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

Re: REST service command LOG

[ekraynov]

Log command of the REST API provides access just for logs of the node where
the service raised.  
Logs can be retrieved from IGNITE_HOME/ or from the path defined in the log
configuration of the node.
Log configuration should be defined expliciltly when Ignite-log4j module is
enabled.
 
XML:

  

  

  
  
  ...

 
 Java:
IgniteConfiguration cfg = new IgniteConfiguration();
IgniteLogger log = new Log4JLogger("log4j.xml");
cfg.setGridLogger(log);
 
In this way, Log command will provide by default (without Path parameter)
logs from the file specified 
in  log4j.xml
 
I think, the current behavior is correct



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

Re: Query execution too slow

[ilya.kasnacheev]

Hello!

Can you please show the original query that you are running?

Regards,



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

Data modeling for segmenting a huge data set: precomputing vs real time computations

[eugene miretsky]

Hello,

We have a very big data set (200M+ users), for each user we store their
activity (views, sales, etc.) and we need to be able to segment the users
based on that data.

One (very simplified) use case looks something like:
- Data: customer_id, date, category, sub_category, action
- Query: All customers that had X views in last Y days in sub_category Z.

The problem becomes huge very fast since we have hundereds of categories
and actions and several aggreagation functions (total views, min purchase
amount, etc.)

There are 2 ways to go about it
1) Pre-compute everything (memory intensive):
-  Very wide rows for each user: sub_categories x features x aggregation
functions  (we don't need every single permutation, but we still end up
with 1000+ columns).
- The tables are very sparse since most of the columns are null for most
users
2) Store the row data and perform the grouping in real time (compute
intensive)

Currently we are trying a middle version
-  Precompute all the features every day: (customer_id, day,
man_fashion_views, electronics_views, electronics_purchase_amount)
-  Filter by date and perform the aggregation in real time:  (SELECT
customer_id FROM testTable where date > '2018-08-02' GROUP BY customer_id
HAVING SUM(man_fashion_views) > 2 AND MAX(electronics_purchase_amount) <
100) )

Currently we are trying to understand the internals of Ignite a bit better
in order to understand how to model and optimize the above use case
1) How are sparse rows stored? Do nulls take the same amount of space as
actual values?
2) What is the cost of bringing data from off-heap memory to the heap?
3) How are filtering, group_by and aggregates performed?
 -- Does all the data get moved from off-heap to heap on every query?
 -- Is any filtering performed off-heap?
 -- If we need to calculate  aggregate A over a 3 day period and aggregate
B over 2 weeks period. Should we do it in 2 queries and then join the
results or in 1 query?
 -- Are indexes used during group_by? (say we are grouping by category,
would an index on category column help significantly?)
 -- Are there any optimization tricks to speed group_by?
4) One big table vs many smaller tables (all with customer_id as
affinityKey)
-- What is the cost of joining across colocated tables?

We are going to benchmark most of these, but we just started testing Ignite
and don't have a good intuition about what is likely to work.

Would apprecaite any tips you can provide.

Cheers,
Eugene



Cheers,
Eugene

Re: Apache Ignite SQL- read Only User Creation

[ApacheUser]

Thanks Andrei,

I ceated user but can't alter user except for changing password, The user is
able to delete rows or truncate tables which I dont want except ignite user.

Thanks




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

Re: Query execution too slow

[Prasad Bhalerao]

Original Sql query:

SELECT dnsnb.value,
  dnsnb.id
FROM DnsNetBiosAssetGroupData dnsnb
JOIN TABLE (value VARCHAR  = ? ) temp
ON dnsnb.value = temp.value
WHERE dnsnb.subscriptionId = ?
AND dnsnb.assetGroupId = ?
AND dnsnb.assetTypeInd = ?

temp table list has around 1_00_000 values.

I also tried changing the indexes as follows. But it did not work.

public class DnsNetBiosAssetGroupData implements
Data,UpdatableData {

  @QuerySqlField
  private long id;
  @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"dns_nb_asset_group_data_idx1", order = 1)})
  private long assetGroupId;
  @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"dns_nb_asset_group_data_idx1", order = 2)})
  private int assetTypeInd;
  private int partitionId;
  @QuerySqlField
  private long subscriptionId;
  @QuerySqlField
  private long updatedDate;
  //@QuerySqlField (index = true)
  @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"dns_nb_asset_group_data_idx1", order = 3)})
  private String value;


Thanks,
Prasad

On Tue, Aug 21, 2018 at 7:35 PM ilya.kasnacheev 
wrote:

> Hello!
>
> Can you please show the original query that you are running?
>
> Regards,
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Re: Query execution too slow

[Ilya Kasnacheev]

Hello!

I think that value should have order of 1 to be used here. Can you try that?

Regards,

-- 
Ilya Kasnacheev

2018-08-21 18:56 GMT+03:00 Prasad Bhalerao :

> Original Sql query:
>
> SELECT dnsnb.value,
>   dnsnb.id
> FROM DnsNetBiosAssetGroupData dnsnb
> JOIN TABLE (value VARCHAR  = ? ) temp
> ON dnsnb.value = temp.value
> WHERE dnsnb.subscriptionId = ?
> AND dnsnb.assetGroupId = ?
> AND dnsnb.assetTypeInd = ?
>
> temp table list has around 1_00_000 values.
>
> I also tried changing the indexes as follows. But it did not work.
>
> public class DnsNetBiosAssetGroupData implements 
> Data,UpdatableData {
>
>   @QuerySqlField
>   private long id;
>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
> "dns_nb_asset_group_data_idx1", order = 1)})
>   private long assetGroupId;
>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
> "dns_nb_asset_group_data_idx1", order = 2)})
>   private int assetTypeInd;
>   private int partitionId;
>   @QuerySqlField
>   private long subscriptionId;
>   @QuerySqlField
>   private long updatedDate;
>   //@QuerySqlField (index = true)
>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
> "dns_nb_asset_group_data_idx1", order = 3)})
>   private String value;
>
>
> Thanks,
> Prasad
>
> On Tue, Aug 21, 2018 at 7:35 PM ilya.kasnacheev 
> wrote:
>
>> Hello!
>>
>> Can you please show the original query that you are running?
>>
>> Regards,
>>
>>
>>
>> --
>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>
>

Re: Ignite and Persistent Collections

[steve.hostettler]

Will do thanks



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

Re: Failed to map keys for cache (all partition nodes left the grid)

[scottmf]

Hi Alex,Yes, the baseline topology contains both the server nodes and the
client node.  Everything is looks as I would expect including the IP
addresses.

A couple more notes:
when the nodes get restarted the IP addresses will disappear from the
baseline topology, then will come back.  The IP address may change when it
comes back but the filesystem will still be intact.
once the second node is back up the cache comes back as well, meaning that
the client can successfully query it again.  The client is not able to query
the cache while the first node is down when this exception occurs.  I'm
confused how that could be the case.  I even experimented with changing my
cache to be simply REPLICATED, rather than PARTITIONED, with backups > 1
 in my igniteConfiguration I do set the number of threads in the public and
system thread pools to 2 and the rebalance pool size to 1 as this is a
simple dev setupAny idea?



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

Re: Query execution too slow

[Prasad Bhalerao]

Hi,

Thank you for pointing out the mistake.
After changing the order to 1 as follows, SQL executed quickly.

public class DnsNetBiosAssetGroupData implements
Data,UpdatableData {

  @QuerySqlField
  private long id;
  @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"dns_nb_asset_group_data_idx1", order = 2)})
  private long assetGroupId;
  @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"dns_nb_asset_group_data_idx1", order = 3)})
  private int assetTypeInd;
  private int partitionId;
  @QuerySqlField
  private long subscriptionId;
  @QuerySqlField
  private long updatedDate;
  //@QuerySqlField (index = true)
  @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
"dns_nb_asset_group_data_idx1", order = 1)})
  private String value;


But now with this change I cannot use the same index for condition, "wh re
assetGroupId=? and assetTypeInd=?" . Do I have to create separate group
index on assetGroupId and assetTypeInd ?






Thanks,
Prasad

On Tue, Aug 21, 2018 at 9:29 PM Ilya Kasnacheev 
wrote:

> Hello!
>
> I think that value should have order of 1 to be used here. Can you try
> that?
>
> Regards,
>
> --
> Ilya Kasnacheev
>
> 2018-08-21 18:56 GMT+03:00 Prasad Bhalerao :
>
>> Original Sql query:
>>
>> SELECT dnsnb.value,
>>   dnsnb.id
>> FROM DnsNetBiosAssetGroupData dnsnb
>> JOIN TABLE (value VARCHAR  = ? ) temp
>> ON dnsnb.value = temp.value
>> WHERE dnsnb.subscriptionId = ?
>> AND dnsnb.assetGroupId = ?
>> AND dnsnb.assetTypeInd = ?
>>
>> temp table list has around 1_00_000 values.
>>
>> I also tried changing the indexes as follows. But it did not work.
>>
>> public class DnsNetBiosAssetGroupData implements 
>> Data,UpdatableData {
>>
>>   @QuerySqlField
>>   private long id;
>>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
>> "dns_nb_asset_group_data_idx1", order = 1)})
>>   private long assetGroupId;
>>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
>> "dns_nb_asset_group_data_idx1", order = 2)})
>>   private int assetTypeInd;
>>   private int partitionId;
>>   @QuerySqlField
>>   private long subscriptionId;
>>   @QuerySqlField
>>   private long updatedDate;
>>   //@QuerySqlField (index = true)
>>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
>> "dns_nb_asset_group_data_idx1", order = 3)})
>>   private String value;
>>
>>
>> Thanks,
>> Prasad
>>
>> On Tue, Aug 21, 2018 at 7:35 PM ilya.kasnacheev <
>> ilya.kasnach...@gmail.com> wrote:
>>
>>> Hello!
>>>
>>> Can you please show the original query that you are running?
>>>
>>> Regards,
>>>
>>>
>>>
>>> --
>>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>>
>>
>

Configurations precedence and consistency across the cluster

[eugene miretsky]

Hello,

It looks like there are several ways to set cluster configuration (I am
currently looking at CacheConfiguration in particualr)

   1. Via the configuration XML file provided at startup
   2. Via the Java client (CacheConfiguration object)
   3. Via SQL commands

I have a few questions about how these configuartions works

   1. What is the precedence of configrations? What if I specify configs in
   xml file, and then create a cache via SQL or Java client - will the config
   get overriten?
   2. What happens when a node restarts? Where does it get the config?
   Would it pick up the configs from the XML config?
   3. Are configs syncronized across the cluster in any way? What happens
   if 2 nodes have different XML configs? What happens if the
CacheConfiguration
   temple used in a SQL create table requests is avalible only on one node?

Cheers,
Eugene

Affinity key in SQL execution

[Prasad Bhalerao]

Hi,

I am using key value store and I have configured multiple caches. These
caches have different affinity key. To store the values in cache I am using
following key. Id and affinityId column can be different for different
caches.

public class DefaultDataAffinityKey implements DataKey {

  private static final long serialVersionUID = -6858038049181201872L;
  private long id;

  @AffinityKeyMapped
  private long affinityId;



As per this doc

SQL
engine automatically optimizes queries that use primary or affinity keys in
the condition section of a SELECT statement.

Now consider this sql: select * from cache_1 where id=? and subscriptionId=?

In this case id is primary key and subscriptionId is a affinitykey for this
cache_1.

But how does the ignite identify that id column is primary key of this
cache and subscriptionId is a affinityKey of this cache?


Thanks,
Prasad

Loading Cache

[Skollur]

Currently I am loading data with one line in java. Below ocmmand will load
all data from database to Cache.
Example: ignite.cache("CustomerCache").loadCache(null);

I would like to load data for specific period. How would I do that?




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

Re: Failed to map keys for cache (all partition nodes left the grid)

[scottmf]

This also occurs when I simply restart one of the ignite nodes without
changing the IP address.  It has nothing to do with the upgrade process.

Here is my cacheconfiguration:
CacheConfiguration configuration = new
CacheConfiguration<>();configuration.setStatisticsEnabled(true);   
configuration.setBackups(1);   
configuration.setCacheMode(CacheMode.PARTITIONED);   
configuration.setOnheapCacheEnabled(false);   
configuration.setName("helloCache");return configuration;
I'd like to think I'm doing something stupid, but I've been working with
Ignite for several months now and have tested this scenario and everything
has worked as it should.  I am running on 2.6.0 and I haven't tested it on
this version but I can't imagine it's that since it is basic functionality. 
I'm sure it is probably something that I'm doing.

Any idea?



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

Re: Configurations precedence and consistency across the cluster

[akurbanov]

Hello,

1. No. Cache with configuration specified in xml file will be pre-created
and won't change if you will try to get it using changed configuration. 
2. If you are asking about a single node, yes, that is correct.
3. You will get an exception on startup in case you have different
configurations for the same cache.

Caches were not designed to support configuration changes on the fly, you
should destroy/recreate cache in order to apply configuration change which
means you should also delete data from the cache. In the future, there will
be some possibilities to change some configuration parameters in runtime,
but definitely not the ones responsible for data flow and distribution in
cluster.

Could you please clarify what are your requirements for cache configuration
updates?



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

Re: Failed to map keys for cache (all partition nodes left the grid)

[Alex Plehanov]

I think you are confusing the "current cluster topology" and the "baseline
topology" terms. Baseline topology cannot contain client nodes. Current
baseline topology can be printed or changed by "control.sh" script.
You can read more about baseline topology here: [1]

[1]: https://apacheignite.readme.io/docs/baseline-topology

вт, 21 авг. 2018 г. в 19:40, scottmf :

> Hi Alex, Yes, the baseline topology contains both the server nodes and the
> client node. Everything is looks as I would expect including the IP
> addresses.
>
> A couple more notes:
>
>- when the nodes get restarted the IP addresses will disappear from
>the baseline topology, then will come back. The IP address may change when
>it comes back but the filesystem will still be intact.
>- once the second node is back up the cache comes back as well,
>meaning that the client can successfully query it again. The client is not
>able to query the cache while the first node is down when this exception
>occurs. I'm confused how that could be the case. I even experimented with
>changing my cache to be simply REPLICATED, rather than PARTITIONED, with
>backups > 1
>- in my igniteConfiguration I do set the number of threads in the
>public and system thread pools to 2 and the rebalance pool size to 1 as
>this is a simple dev setup Any idea?
>--
>Sent from the Apache Ignite Users mailing list archive
> at Nabble.com.
>
>

Re: Failed to map keys for cache (all partition nodes left the grid)

[akurbanov]

Hi,

Do you see the same exception for a REPLICATED cache?
And this exception also appears in two node setup with backups = 1, when you
simply shut down one node, is that correct?



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

Re: Query execution too slow

[Ilya Kasnacheev]

Hello!

Yes, I am afraid that you will need another index.

Regards,

-- 
Ilya Kasnacheev

2018-08-21 19:55 GMT+03:00 Prasad Bhalerao :

> Hi,
>
> Thank you for pointing out the mistake.
> After changing the order to 1 as follows, SQL executed quickly.
>
> public class DnsNetBiosAssetGroupData implements 
> Data,UpdatableData {
>
>   @QuerySqlField
>   private long id;
>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
> "dns_nb_asset_group_data_idx1", order = 2)})
>   private long assetGroupId;
>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
> "dns_nb_asset_group_data_idx1", order = 3)})
>   private int assetTypeInd;
>   private int partitionId;
>   @QuerySqlField
>   private long subscriptionId;
>   @QuerySqlField
>   private long updatedDate;
>   //@QuerySqlField (index = true)
>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
> "dns_nb_asset_group_data_idx1", order = 1)})
>   private String value;
>
>
> But now with this change I cannot use the same index for condition, "wh re
> assetGroupId=? and assetTypeInd=?" . Do I have to create separate group
> index on assetGroupId and assetTypeInd ?
>
>
>
>
>
>
> Thanks,
> Prasad
>
> On Tue, Aug 21, 2018 at 9:29 PM Ilya Kasnacheev 
> wrote:
>
>> Hello!
>>
>> I think that value should have order of 1 to be used here. Can you try
>> that?
>>
>> Regards,
>>
>> --
>> Ilya Kasnacheev
>>
>> 2018-08-21 18:56 GMT+03:00 Prasad Bhalerao 
>> :
>>
>>> Original Sql query:
>>>
>>> SELECT dnsnb.value,
>>>   dnsnb.id
>>> FROM DnsNetBiosAssetGroupData dnsnb
>>> JOIN TABLE (value VARCHAR  = ? ) temp
>>> ON dnsnb.value = temp.value
>>> WHERE dnsnb.subscriptionId = ?
>>> AND dnsnb.assetGroupId = ?
>>> AND dnsnb.assetTypeInd = ?
>>>
>>> temp table list has around 1_00_000 values.
>>>
>>> I also tried changing the indexes as follows. But it did not work.
>>>
>>> public class DnsNetBiosAssetGroupData implements 
>>> Data,UpdatableData {
>>>
>>>   @QuerySqlField
>>>   private long id;
>>>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
>>> "dns_nb_asset_group_data_idx1", order = 1)})
>>>   private long assetGroupId;
>>>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
>>> "dns_nb_asset_group_data_idx1", order = 2)})
>>>   private int assetTypeInd;
>>>   private int partitionId;
>>>   @QuerySqlField
>>>   private long subscriptionId;
>>>   @QuerySqlField
>>>   private long updatedDate;
>>>   //@QuerySqlField (index = true)
>>>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
>>> "dns_nb_asset_group_data_idx1", order = 3)})
>>>   private String value;
>>>
>>>
>>> Thanks,
>>> Prasad
>>>
>>> On Tue, Aug 21, 2018 at 7:35 PM ilya.kasnacheev <
>>> ilya.kasnach...@gmail.com> wrote:
>>>
 Hello!

 Can you please show the original query that you are running?

 Regards,



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

>>>
>>

Re: Affinity key in SQL execution

[vkulichenko]

Prasad,

Affinity key is always a part of primary key. For example, in your case
primary key consists of two fields - id and subscriptionId. Therefore you
can query by primary key without providing the affinity key. On the other
hand, however, query can be routed to a single node even if only affinity
key is provided in select clause.

-Val



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

Re: Loading Cache

[akurbanov]

Hi,

Call a method like this and pass closure that returns true for data entries
that match your criteria in apply() method:

cache.loadCache(new P2() {
@Override public boolean apply(Integer key, String val) {
// Accept only even numbers.
return key % 2 == 0;
}
}, cnt);

Variable cnt is an number you may use to limit entry count, you may omit it.



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

Spark Dataframe write is hanging

[eugene miretsky]

Hi,


When I am saving Spark Dataframe to Ignite, the job sometimes get
stuck with the attached error.

It seems to happen at random, and usualy at the end of the job (all
the data has already been writen to Ignite)


Has anybody encountered this before?


java.net.ConnectException: Connection refused
> at sun.nio.ch.SocketChannelImpl.checkConnect(Native Method)
> at sun.nio.ch.SocketChannelImpl.finishConnect(SocketChannelImpl.java:717)
> at
> org.apache.ignite.internal.util.nio.GridNioServer$AbstractNioClientWorker.processConnect(GridNioServer.java:2714)
> at
> org.apache.ignite.internal.util.nio.GridNioServer$AbstractNioClientWorker.processSelectedKeysOptimized(GridNioServer.java:2333)
> at
> org.apache.ignite.internal.util.nio.GridNioServer$AbstractNioClientWorker.bodyInternal(GridNioServer.java:2110)
> at
> org.apache.ignite.internal.util.nio.GridNioServer$AbstractNioClientWorker.body(GridNioServer.java:1764)
> at
> org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)
> at java.lang.Thread.run(Thread.java:748)

Re: Query execution too slow

[Andrey Mashenkov]

Hi,

Composite index field order matters. Try to set order=3 for "value" column.

On Tue, Aug 21, 2018 at 10:43 PM Ilya Kasnacheev 
wrote:

> Hello!
>
> Yes, I am afraid that you will need another index.
>
> Regards,
>
> --
> Ilya Kasnacheev
>
> 2018-08-21 19:55 GMT+03:00 Prasad Bhalerao :
>
>> Hi,
>>
>> Thank you for pointing out the mistake.
>> After changing the order to 1 as follows, SQL executed quickly.
>>
>> public class DnsNetBiosAssetGroupData implements 
>> Data,UpdatableData {
>>
>>   @QuerySqlField
>>   private long id;
>>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
>> "dns_nb_asset_group_data_idx1", order = 2)})
>>   private long assetGroupId;
>>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
>> "dns_nb_asset_group_data_idx1", order = 3)})
>>   private int assetTypeInd;
>>   private int partitionId;
>>   @QuerySqlField
>>   private long subscriptionId;
>>   @QuerySqlField
>>   private long updatedDate;
>>   //@QuerySqlField (index = true)
>>   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
>> "dns_nb_asset_group_data_idx1", order = 1)})
>>   private String value;
>>
>>
>> But now with this change I cannot use the same index for condition, "wh
>> re assetGroupId=? and assetTypeInd=?" . Do I have to create separate
>> group index on assetGroupId and assetTypeInd ?
>>
>>
>>
>>
>>
>>
>> Thanks,
>> Prasad
>>
>> On Tue, Aug 21, 2018 at 9:29 PM Ilya Kasnacheev <
>> ilya.kasnach...@gmail.com> wrote:
>>
>>> Hello!
>>>
>>> I think that value should have order of 1 to be used here. Can you try
>>> that?
>>>
>>> Regards,
>>>
>>> --
>>> Ilya Kasnacheev
>>>
>>> 2018-08-21 18:56 GMT+03:00 Prasad Bhalerao >> >:
>>>
 Original Sql query:

 SELECT dnsnb.value,
   dnsnb.id
 FROM DnsNetBiosAssetGroupData dnsnb
 JOIN TABLE (value VARCHAR  = ? ) temp
 ON dnsnb.value = temp.value
 WHERE dnsnb.subscriptionId = ?
 AND dnsnb.assetGroupId = ?
 AND dnsnb.assetTypeInd = ?

 temp table list has around 1_00_000 values.

 I also tried changing the indexes as follows. But it did not work.

 public class DnsNetBiosAssetGroupData implements 
 Data,UpdatableData {

   @QuerySqlField
   private long id;
   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
 "dns_nb_asset_group_data_idx1", order = 1)})
   private long assetGroupId;
   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
 "dns_nb_asset_group_data_idx1", order = 2)})
   private int assetTypeInd;
   private int partitionId;
   @QuerySqlField
   private long subscriptionId;
   @QuerySqlField
   private long updatedDate;
   //@QuerySqlField (index = true)
   @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = 
 "dns_nb_asset_group_data_idx1", order = 3)})
   private String value;


 Thanks,
 Prasad

 On Tue, Aug 21, 2018 at 7:35 PM ilya.kasnacheev <
 ilya.kasnach...@gmail.com> wrote:

> Hello!
>
> Can you please show the original query that you are running?
>
> Regards,
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

>>>
>

-- 
Best regards,
Andrey V. Mashenkov

Re: Failed to map keys for cache (all partition nodes left the grid)

[Scott Feldstein]

Yes that is correct.  I see the same exceptions when the cache is
REPLICATED with backups = 1 in my two node cluster.

On Tue, Aug 21, 2018 at 12:37 PM akurbanov  wrote:

> Hi,
>
> Do you see the same exception for a REPLICATED cache?
> And this exception also appears in two node setup with backups = 1, when
> you
> simply shut down one node, is that correct?
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Re: Failed to map keys for cache (all partition nodes left the grid)

[scottmf]

hi Alex,Sorry for the confusing comment.  My baseline topology does not have
a client node.  I was indeed referring to the current cluster topology in my
previous comment.

These ignite cluster nodes are programmed to come up and immediately run
ignite.cluster.active(true) but that is not coordinated to wait for both
nodes to come up.  Based on the output of control.sh I assume that I'm not
bringing up the cluster correctly.  Let me know what you think and how to
fix.  Thanks!

Here is the output of control.sh:
root [ /opt/ignite/apache-ignite-fabric-2.6.0-bin/bin ]# ./control.sh
--baselineControl utility [ver. 2.6.0#20180710-sha1:669feacc]2018
Copyright(C) Apache Software FoundationUser:
rootCluster
state: activeCurrent topology version: 7Baseline nodes:   
ConsistentID=416b00e2-4344-4fc7-bb1e-7333977cfbf8,
STATE=OFFLINENumber
of baseline nodes: 1Other nodes:   
ConsistentID=51b15af0-613c-4bd3-a0bc-41c06a0fd654   
ConsistentID=b8e5512d-13dd-46f5-be28-ea73314ac9aeNumber of other nodes: 2




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

Re: Failed to map keys for cache (all partition nodes left the grid)

[scottmf]

Yes that is correct.  I see the same exceptions when the cache is REPLICATED
with backups = 1 in my two node cluster.




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

Re: Failed to map keys for cache (all partition nodes left the grid)

[scottmf]

hi Alex,
That was it.  I read a little deeper into the doc and I wasn't setting the
baseline topology.  I think I had it in my head that
ignite.cluster().active(true) set the topology.  I see now that was
incorrect.

I just tested it and I'm good.

thanks a lot!!



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

Recommended HW on AWS EC2 - vertical vs horizontal scaling

[eugene miretsky]

Hello,

We are looking to set up a fairly large (a few TB) cluster in AWS for OLAP
and transactional use cases.


   1. Are there any Ignite benchmarks on horizontally vs vertical scaling?
   2. What EC2 instances are other people using in prod? (I am assuming
   that one of the memory optimized
   

   )


Cheers,
Eugene

Slow SQL query uses only a single CPU

[eugene miretsky]

Hi,

We have a cache called GAL3EC1, it has

   1. A composite pKey consisting of customer_id and date
   2. An Index on the date column
   3. 300 sparse columns

We are running a single EC2 4x8xlarge node.

The following query takes 8min to finish
Select COUNT (*) FROM (SELECT customer_id FROM GAl3ec1 where dt >
'2018-05-12' GROUP BY customer_id having
SUM(ec1_bknt_total_product_views_app) > 2 AND
MAX(ec1_hnk_total_product_clicks_app) < 1)

I have a few questions:

   1. 'top' command shows %100 cpu utilization (i.e only one of the 32 CPUs
   is used). How can I get the query to use all 32 CPUs? I have tried setting
   Query Parallelism to 32, but it didn't help,
   2. Adding the index on date column seems to have slowed down the query.
   The 8min time from above was without the index, with the index the query
   doesn't finish (I gave up after 30min). A similar query on a smaller date
   range showed a 10x slow down with the index. Why?
   3. Our loads from Spark are very slow as well, and also seem to not use
   the system resource properly, can that be related?
   4. What are some good tools and techniques to troubleshoot these
   problems in Ignite?


All the relevant info is attached (configs, cache stats, node stats, etc.).

Cheers,
Eugene
Cache

++
| Name(@) |Mode | Nodes |   Entries (Heap / 
Off-heap)   |   Hits|Misses|Reads |  Writes  |
++
| cache1(@c0) | PARTITIONED | 1 | min: 0 (0 / 0)
| min: 0| min: 0   | min: 0   | min: 0   |
| | |   | avg: 0.00 (0.00 / 0.00)   
| avg: 0.00 | avg: 0.00| avg: 0.00| avg: 0.00|
| | |   | max: 0 (0 / 0)
| max: 0| max: 0   | max: 0   | max: 0   |
+-+-+---+---+---+--+--+--+
| cache2(@c1) | PARTITIONED | 1 | min: 0 (0 / 0)
| min: 0| min: 0   | min: 0   | min: 0   |
| | |   | avg: 0.00 (0.00 / 0.00)   
| avg: 0.00 | avg: 0.00| avg: 0.00| avg: 0.00|
| | |   | max: 0 (0 / 0)
| max: 0| max: 0   | max: 0   | max: 0   |
+-+-+---+---+---+--+--+--+
| SQL_PUBLIC_GAL2RU(@c2)  | PARTITIONED | 1 | min: 8229539 (0 / 8229539)
| min: 0| min: 0   | min: 0   | min: 8229539 |
| | |   | avg: 8229539.00 (0.00 / 
8229539.00)   | avg: 0.00 | avg: 0.00| avg: 0.00| avg: 8229539.00  |
| | |   | max: 8229539 (0 / 8229539)
| max: 0| max: 0   | max: 0   | max: 8229539 |
+-+-+---+---+---+--+--+--+
| SQL_PUBLIC_GAL3EC1(@c3) | PARTITIONED | 1 | min: 63991599 (0 / 63991599)  
| min: 0| min: 9028| min: 9028| min: 83335247|
| | |   | avg: 63991599.00 (0.00 / 
63991599.00) | avg: 0.00 | avg: 9028.00 | avg: 9028.00 | avg: 83335247.00 |
| | |   | max: 63991599 (0 / 63991599)  
| max: 0| max: 9028| max: 9028| max: 83335247|
+-+-+---+---+---+--+--+--+
| SQL_PUBLIC_PERSON(@c4)  | PARTITIONED | 1 | min: 0 (0 / 0)
| min: 0| min: 0   | min: 0   | min: 0   |
| | |   | avg: 0.00 (0.00 / 0.00)   
| avg: 0.00 | avg: 0.00| avg: 0.00| avg: 0.00|
| | |   | max: 0 (0 / 0)
| max: 0| max: 0   | max: 0   | max: 0   |
++

Node

+---+
| ID  | 2dff6868-b253-48d1-aa7b-128082a26332
|
| ID8 | 2DFF686

Re: Affinity key in SQL execution

[Prasad Bhalerao]

I could not find any cache config setting where one can provide the primary
key column name or affinity column name of a given cache.

How does ignite identify that column provided in select query's where
clause is a primary key or affinity key?

Can you give an example?


Thanks,
Prasad


On Wed, Aug 22, 2018, 1:36 AM vkulichenko 
wrote:

> Prasad,
>
> Affinity key is always a part of primary key. For example, in your case
> primary key consists of two fields - id and subscriptionId. Therefore you
> can query by primary key without providing the affinity key. On the other
> hand, however, query can be routed to a single node even if only affinity
> key is provided in select clause.
>
> -Val
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Re: Affinity key in SQL execution

[vkulichenko]

There are several ways to do this.

First one is shown in your first message - DefaultDataAffinityKey instance
plays the role of primary key, while enclosed affinityId field plays the of
affinity key, because it's annotated with @AffinityKeyMapped.

In case you don't have classes at all and therefore can't use annotations
(for example, if you create all your objects using BinaryObjectBuilder), you
can specify field name to be used as affinity key via CacheKeyConfiguration.

Finally, if you use DDL to create your tables, affinity key field name can
be specified as a part of CREATE TABLE command:
https://apacheignite-sql.readme.io/docs/create-table

Either way, affinity key is ALWAYS a part of the primary key, so there is no
way to select by primary key without specifying the value for affinity key.
But it's enough to provide ONLY affinity key to achieve the routing.

-Val



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

Re: Affinity key in SQL execution

[Prasad Bhalerao]

I am still not getting it.

The field affinityId in my data key is just like any other field which hold
the value of affinity key column. For cache1 it will hold subscriptionId
and for other cache it might hold value of some other affinity key.

But when I write SQL like 'select * from Cahce_1 where subscriptionId = ?'
, how does ignite understands that column name subscriptionId is a affifiny
key of cache_1. I have not mentioned anywhere that column name
subscriptionId is a affinity key.

I put an entries in my caches by creating key as follows.

new DefaultDataAffinityKey(id1, subscriptionId);

new DefaultDataAffinityKey(id2, someotherId);

Here  I am just passing the values to this key. Nowhere I am mentioning the
column name of primary key or affinity key.

How ignite idetifies that out of 5 columns provided in where clause of SQL
, this particular column is a affinity key or primary key.



On Wed, Aug 22, 2018, 7:52 AM vkulichenko 
wrote:

> There are several ways to do this.
>
> First one is shown in your first message - DefaultDataAffinityKey instance
> plays the role of primary key, while enclosed affinityId field plays the of
> affinity key, because it's annotated with @AffinityKeyMapped.
>
> In case you don't have classes at all and therefore can't use annotations
> (for example, if you create all your objects using BinaryObjectBuilder),
> you
> can specify field name to be used as affinity key via
> CacheKeyConfiguration.
>
> Finally, if you use DDL to create your tables, affinity key field name can
> be specified as a part of CREATE TABLE command:
> https://apacheignite-sql.readme.io/docs/create-table
>
> Either way, affinity key is ALWAYS a part of the primary key, so there is
> no
> way to select by primary key without specifying the value for affinity key.
> But it's enough to provide ONLY affinity key to achieve the routing.
>
> -Val
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Re: Slow SQL query uses only a single CPU

[eugene miretsky]

Here is the result of EXPLAIN for the afermantioned query:

SELECT
__Z0.CUSTOMER_ID AS __C0_0,
SUM(__Z0.EC1_BKNT_TOTAL_PRODUCT_VIEWS_APP) AS __C0_1,
MAX(__Z0.EC1_HNK_TOTAL_PRODUCT_CLICKS_APP) AS __C0_2
FROM PUBLIC.GAL3EC1 __Z0
/* PUBLIC.DT_IDX: DT > '2018-05-12' */
WHERE __Z0.DT > '2018-05-12'
GROUP BY __Z0.CUSTOMER_ID
SELECT
COUNT(*)
FROM (
SELECT
__C0_0 AS CUSTOMER_ID
FROM PUBLIC.__T0
GROUP BY __C0_0
HAVING (SUM(__C0_1) > 2)
AND (MAX(__C0_2) < 1)
) _0__Z1



On Tue, Aug 21, 2018 at 8:18 PM, eugene miretsky 
wrote:

> Hi,
>
> We have a cache called GAL3EC1, it has
>
>1. A composite pKey consisting of customer_id and date
>2. An Index on the date column
>3. 300 sparse columns
>
> We are running a single EC2 4x8xlarge node.
>
> The following query takes 8min to finish
> Select COUNT (*) FROM (SELECT customer_id FROM GAl3ec1 where dt >
> '2018-05-12' GROUP BY customer_id having SUM(ec1_bknt_total_product_views_app)
> > 2 AND MAX(ec1_hnk_total_product_clicks_app) < 1)
>
> I have a few questions:
>
>1. 'top' command shows %100 cpu utilization (i.e only one of the 32
>CPUs is used). How can I get the query to use all 32 CPUs? I have tried
>setting Query Parallelism to 32, but it didn't help,
>2. Adding the index on date column seems to have slowed down the
>query. The 8min time from above was without the index, with the index the
>query doesn't finish (I gave up after 30min). A similar query on a
>smaller date range showed a 10x slow down with the index. Why?
>3. Our loads from Spark are very slow as well, and also seem to not
>use the system resource properly, can that be related?
>4. What are some good tools and techniques to troubleshoot these
>problems in Ignite?
>
>
> All the relevant info is attached (configs, cache stats, node stats,
> etc.).
>
> Cheers,
> Eugene
>
>
>
>
>
>
>