Re: Slow select distinct query on primary key

[yongjec]

Here is the explain plan.

0: jdbc:ignite:thin://127.0.0.1/> EXPLAIN SELECT DISTINCT ACCOUNT_ID FROM
PERF_POSITIONS;
'PLAN'
'SELECT DISTINCT
__Z0.ACCOUNT_ID AS __C0_0
FROM PUBLIC.PERF_POSITIONS __Z0
/* PUBLIC."_key_PK" */'
'SELECT DISTINCT
__C0_0 AS ACCOUNT_ID
FROM PUBLIC.__T0
/* PUBLIC."merge_scan" */'
2 rows selected (0.026 seconds)


Based on your suggestion, I tested below changes, but none of them made a
difference. In all cases, the query took consistently 56-60 seconds.


1. Having the index with inline size 60.

CREATE INDEX PERF_POSITIONS_IDX ON PERF_POSITIONS (ACCOUNT_ID) INLINE_SIZE
60;


2. Re-creating the table with VARCHAR size 4. (all the values in this
particular dataset are 4 chars).

CREATE TABLE PERF_POSITIONS (
ACCOUNT_ID VARCHAR(4) NOT NULL,
...


3. Using index hint.

SELECT DISTINCT ACCOUNT_ID FROM PERF_POSITIONS USE
INDEX(PERF_POSITIONS_IDX);


On a side note, I noticed that while the query is running, only 1 out of the
16 available cores get spiked to 100%, while the rest remain idle. Not sure
whether this is expected.



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

Re: Slow select distinct query on primary key

[yongjec]

Hi,

I tried the additional index as you suggested, but it did not improve the
query time. The query still takes 58-61 seconds.

CREATE INDEX PERF_POSITIONS_IDX ON PERF_POSITIONS (ACCOUNT_ID);
CREATE INDEX PERF_POSITIONS_IDX2 ON PERF_POSITIONS (ACCOUNT_ID,
EFFECTIVE_DATE, FREQUENCY, SOURCE_ID, SECURITY_ALIAS, POSITION_TYPE);


I also tried the single column index only without the composite index. That
did not make any difference in query time, either.

CREATE INDEX PERF_POSITIONS_IDX ON PERF_POSITIONS (ACCOUNT_ID);



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

Re: Slow select distinct query on primary key

[yongjec]

Here is my Ignite server configuration.

http://www.springframework.org/schema/beans";
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="
   http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans.xsd";>




























127.0.0.1:47500..47509


















And here are my JVM heap flags.

ignite.sh -J-Xms8g -J-Xmx16g
/home/ansible/ignite/apache-ignite-fabric-2.6.0-bin/config/poc1.xml




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

Slow select distinct query on primary key

[yongjec]

I am running below SQL query via Sqlline.sh, and I think it is running too
slow (57s). Could someone confirm whether this response time is normal, or I
am doing something wrong?

Here is the query:

0: jdbc:ignite:thin://127.0.0.1/> SELECT DISTINCT ACCOUNT_ID FROM
PERF_POSITIONS;
++
|   ACCOUNT_ID   |
++
| 1684   |
| 1201   |
| 1686   |
...
...
| 1441   |
++
1,001 rows selected (57.453 seconds)


My setup is a single Azure VM (CentOS 7) with 16 Cores and 64GB RAM. The
host is idle other than the Ignite process.

My Dataset has 50 million rows with a total of 1001 distinct ACCOUNT_ID
values. Rows are almost evenly distributed among the account_id's. As you
can see in the below table definition, ACCOUNT_ID is first column of the
primary key and the index.

CREATE TABLE PERF_POSITIONS (
ACCOUNT_ID VARCHAR(50) NOT NULL,
EFFECTIVE_DATE DATE NOT NULL,
FREQUENCY CHAR(1) NOT NULL,
SOURCE_ID INTEGER NOT NULL,
SECURITY_ALIAS BIGINT NOT NULL,
POSITION_TYPE VARCHAR(10),
IT VARCHAR(50),
IN VARCHAR(255),
PAI VARCHAR(100),
TIC VARCHAR(100),
GR DOUBLE,
NR DOUBLE,
GRL DOUBLE,
IR DOUBLE,
ABAL DOUBLE,
BG DOUBLE,
EG DOUBLE,
CGD DOUBLE,
CGC DOUBLE,
CFG DOUBLE,
GLG DOUBLE,
IB DOUBLE,
WT DOUBLE,
BL DOUBLE,
EL DOUBLE,
CDL DOUBLE,
CCL DOUBLE,
CL DOUBLE,
GLL DOUBLE,
IBL DOUBLE,
NC DOUBLE,
BP DOUBLE,
EP DOUBLE,
CP DOUBLE,
PR DOUBLE,
BAI DOUBLE,
EAI DOUBLE,
CI DOUBLE,
SF VARCHAR(10),
US VARCHAR(255),
UD DATE,
PRIMARY KEY (ACCOUNT_ID, EFFECTIVE_DATE, FREQUENCY, SOURCE_ID,
SECURITY_ALIAS, POSITION_TYPE)
)
WITH "template=partitioned, backups=1, affinityKey=ACCOUNT_ID,
KEY_TYPE=ie.models.PerfPositionKey, VALUE_TYPE=ie.models.PerfPosition";
CREATE INDEX PERF_POSITIONS_IDX ON PERF_POSITIONS (ACCOUNT_ID,
EFFECTIVE_DATE, FREQUENCY, SOURCE_ID, SECURITY_ALIAS, POSITION_TYPE);


When I run the query, I see below warning showing up in the log.

[22:32:51,598][WARNING][client-connector-#136][IgniteH2Indexing] Query
execution is too long [time=57260 ms, sql='SELECT DISTINCT
__Z0.ACCOUNT_ID __C0_0
FROM PUBLIC.PERF_POSITIONS __Z0', plan=
SELECT DISTINCT
__Z0.ACCOUNT_ID AS __C0_0
FROM PUBLIC.PERF_POSITIONS __Z0
/* PUBLIC."_key_PK" */
, parameters=[]]






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

Re: Read SQL table via cache api

[yongjec]

Hi Andrei,

I got this to work by setting the server and client time zones to be the
same.

So what happened was that I had created the table and inserted the record
using Sqlline.sh on the server host. That must have stored the
effective_date field in the key based on the server's time zone (UTC).

Then when I queried the key from the client code, which was running on a
host in a different time zone (UTC-5:00), it probably generated the same
field from the local time zone.

I should have known that Timestamp type is zone specific.

Thank you,
Yong



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

Re: Read SQL table via cache api

[yongjec]

Hi Andrei,

I captured below logs with the -DIGNITE_QUIET=false flag. There is a 5-hour
difference between the two time zones.


Server Log 
ignite-ef771a6b.log
 
 


Client Log
ignite-86fd5276.log
 
 

Thank you,
Yong



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

Re: Read SQL table via cache api

[yongjec]

Another observation is that if I take out the effective_date field
(Timestamp) from the key, and use only the Int and Double columns as the
primary keys, the server finds it without any problem.



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

Re: Read SQL table via cache api

[yongjec]

Hi Andrei,

Thank you for your reply. 

Here is the server log. However, I do not see any relevant information in
it. The cache get request was made from the client between line 117 (client
joins the topology) and 127 (client leaves the topology).

ignite-db357bff.log
 
 

Regards,
Yong



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

Re: Read SQL table via cache api

[yongjec]

Hi Andrei,

I have figured this out after several hours of troubleshooting today.

I had the Ignite server process, which hosted the table/cache, running on a
Linux host (CentOS7). On the other hand, I was running the client code on a
Macbook. It seems that the two platform was deserializing the key POJO into
different binary format, so when the client asks for a key, the server
doesn't see the match.

When I run both the server and the client on the Macbook, it finds the key
as expected.

Thank you,
Yong



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

Read SQL table via cache api

[yongjec]

I ran below SQL from Ignite Sqlline which creates a table and inserts one
entry. I am trying to query that entry using cache api. However, the value
always returns null.

## SQL 
CREATE TABLE SIMPLE_TABLE (
ACCOUNT_ID INT,
SECURITY_ALIAS INT,
POSITION_TYPE VARCHAR(50),
EFFECTIVE_DATE TIMESTAMP,
GROSS_RETURN DOUBLE,
PRIMARY KEY (ACCOUNT_ID, SECURITY_ALIAS, POSITION_TYPE, EFFECTIVE_DATE)
)
WITH "template=partitioned, backups=1, affinityKey=ACCOUNT_ID,
KEY_TYPE=com.xyz.ie.SimpleKey, VALUE_TYPE=com.xyz.ie.SimpleVal";

INSERT INTO SIMPLE_TABLE VALUES (100, 200, 'PT0', '2018-11-01', 1000.0);
##


< SimpleKey.java >
package com.xyz.ie;

import org.apache.ignite.cache.affinity.AffinityKeyMapped;
import java.sql.Timestamp;

public class SimpleKey {

@AffinityKeyMapped
private int ACCOUNT_ID;

private int SECURITY_ALIAS;

private String POSITION_TYPE;

private Timestamp EFFECTIVE_DATE;

public SimpleKey(int accountId, int securityAlias, String positionType,
Timestamp effectiveDate) {
this.ACCOUNT_ID = accountId;
this.SECURITY_ALIAS = securityAlias;
this.POSITION_TYPE = positionType;
this.EFFECTIVE_DATE = effectiveDate;
}
}


package com.xyz.ie;

public class SimpleVal {
private double GROSS_RETURN;

public SimpleVal(double grossReturn) {
this.GROSS_RETURN = grossReturn;
}

public double getGrossReturn() {
return GROSS_RETURN;
}

public void setGrossReturn(double grossReturn) {
this.GROSS_RETURN = grossReturn;
}
}


Here is my code that tries to read the entry.

IgniteCache simpleTableCache =
ignite.cache("SQL_PUBLIC_SIMPLE_TABLE");

// cacheSize evaluates to 1 as expected.
int cacheSize = simpleTableCache.size(CachePeekMode.PRIMARY);

SimpleKey key = new SimpleKey(100, 200, "PT0", Timestamp.valueOf("2018-11-01
00:00:00"));

*// I expect val below to contain the one entry in the cache. However, it is
null.*
SimpleVal val = simpleTableCache.get(key);
if (val == null) {
System.out.println("Can't find the key.");
}


Could someone point out what I am doing wrong here and why it is not finding
the key in the cache?



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