Re: SQL query performance with JOIN and ORDER BY or WHERE

[Thomas Kramer]

Hi Ilya,

unfortunately this also didn't help improving query performance. Not
sure what else I can try. Or maybe it is expected? In my opinion it
shouldn't take that long as the query without the ORDER BY clause is
super fast. Since there is a index on the order field I would expect
this should be fast.

Btw, I noticed that for some other queries the first call has this long
execution while on every following call with the same SQL statement it
returns within half a second. But I guess this is caching related and
not the issue I see here?

Best Regards,
Thomas.


On 12.04.21 13:15, Ilya Kasnacheev wrote:

Hello!

I think you can try a (QUEUEID, STATUS) index.

Or maybe a (STATUS, QUEUEID), probably makes sense to try both.

Regards,
--
Ilya Kasnacheev


сб, 10 апр. 2021 г. в 00:22, mailto:don.tequ...@gmx.de>>:

The QUEUED field is a BIGINT that contains timestamp from
System.currentTimeMillis(), so it should be pretty easy to sort,
shouldn’t it? Looks like the field STATUS (used in where
clause) and field QUEUED (used in order clause) are not working
optimal when used together. Does this make sense? Do I need to
create an index on both together?

I will take a look at UNION and WHERE EXISTS, I‘m not familiar
with these statements.

Thanks!


On 09.04.21 at 17:37, Ilya Kasnacheev wrote:

From: "Ilya Kasnacheev" mailto:ilya.kasnach...@gmail.com>>
Date: 9. April 2021
To: user@ignite.apache.org 
Cc:
Subject: Re: SQL query performance with JOIN and ORDER BY or WHERE
Hello!

ORDER BY will have to sort the whole table.

I think that using index on QUEUED will be optimal here. What is
the selectivity of this field? If it s boolean, you might as well
use UNION queries.

Have you tried joining JOBS via WHERE EXISTS?

Regards,
--
Ilya Kasnacheev



пт, 9 апр. 2021 г. в 01:03, DonTequila mailto:don.tequ...@gmx.de>>:

Hi,

I have a SQL performance issue. There are indexes on both
fields that are
used in the ORDER BY clause and the WHERE clause.

The following statement takes about 133941 ms with several
warnings from
IgniteH2Indexing:

SELECT JQ._KEY
FROM "JobQueue".JOBQUEUE AS JQ
INNER JOIN "Jobs".JOBS AS J ON JQ.jobid=J._key
WHERE JQ.STATUS = 2
ORDER BY JQ.QUEUED ASC
LIMIT 20

But when I remove the ORDER BY part or the WHERE part from the
statement it
returns in <10ms.

What may I do wrong?

Thanks,
Thomas.




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

RE: slow node discovering on Kubernetes

[Alexandr Shapkin]

Hi Maxim, Not really, I haven’t used flannel plugin and I’m not quite sure about network recommendations as well. Well, besides something basic, like avoid stretching between multiple availability zones and some persistence tuning,Like disabling MMAP for WAL (IGNITE_WAL_MMAP=false) > The problem still actual, we deployed same 10 nodes on google kubernetes and>got a normal time. Do you mean, that switching on GKE makes it working or it was the initial setup and nothing has changed since that? > For now we did not define any limits or requests, our ignite is the onlydeployment on the kubernetes. I was wondering cause if I remember correctly there might be some issues if your pods have insufficient resources, but unfortunately, no direct numbers. Well, default GKE instances should works ok on a default cluster, anyway. > Do you have any idea how to estimate network or hardware performance to find>possible bottlenecks? I think enabling DEBUG logs for discovery might help to see what’s really happening to the grid.      category> Btw, is it a persistent cluster or pure in-memory one?  From: mvolkomorovSent: Friday, April 9, 2021 10:26 AMTo: user@ignite.apache.orgSubject: RE: slow node discovering on Kubernetes Hello, Alexandr! The problem still actual, we deployed same 10 nodes on google kubernetes andgot a normal time.For now we did not define any limits or requests, our ignite is the onlydeployment on the kubernetes.We use flannel network plugin (host-gw), are there any recommendations forthe network plugin?Do you have any idea how to estimate network or hardware performance to findpossible bottlenecks?   --Sent from: http://apache-ignite-users.70518.x6.nabble.com/ 

Is Geo redundancy supported in Ignite

[vbm]

Hi,

Is geo-redundancy supported in Ignite ?

Can we  bring up 2 clusters in different location(geo) and make one cluster
as backup for another ?
Basically can ignite cluster in different location act as a active/ passive
setup ?

If one of the cluster goes down due to some issue in one location, can
another cluster across geo become active ?


Regards,
Vishwas



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

Re: Re: SQL query performance with JOIN and ORDER BY or WHERE

[Ilya Kasnacheev]

Hello!

I think you can try a (QUEUEID, STATUS) index.

Or maybe a (STATUS, QUEUEID), probably makes sense to try both.

Regards,
-- 
Ilya Kasnacheev


сб, 10 апр. 2021 г. в 00:22, :

> The QUEUED field is a BIGINT that contains timestamp from
> System.currentTimeMillis(), so it should be pretty easy to sort, shouldn’t
> it? Looks like the field STATUS (used in where clause) and field QUEUED
> (used in order clause) are not working optimal when used together. Does
> this make sense? Do I need to create an index on both together?
>
> I will take a look at UNION and WHERE EXISTS, I‘m not familiar with these
> statements.
>
> Thanks!
>
>
> On 09.04.21 at 17:37, Ilya Kasnacheev wrote:
>
> From: "Ilya Kasnacheev" 
> Date: 9. April 2021
> To: user@ignite.apache.org
> Cc:
> Subject: Re: SQL query performance with JOIN and ORDER BY or WHERE
> Hello!
>
> ORDER BY will have to sort the whole table.
>
> I think that using index on QUEUED will be optimal here. What is the
> selectivity of this field? If it s boolean, you might as well use UNION
> queries.
>
> Have you tried joining JOBS via WHERE EXISTS?
>
> Regards,
> --
> Ilya Kasnacheev
>
>
>
> пт, 9 апр. 2021 г. в 01:03, DonTequila :
>
>> Hi,
>>
>> I have a SQL performance issue. There are indexes on both fields that are
>> used in the ORDER BY clause and the WHERE clause.
>>
>> The following statement takes about 133941 ms with several warnings from
>> IgniteH2Indexing:
>>
>> SELECT JQ._KEY
>> FROM "JobQueue".JOBQUEUE AS JQ
>> INNER JOIN "Jobs".JOBS AS J ON JQ.jobid=J._key
>> WHERE JQ.STATUS = 2
>> ORDER BY JQ.QUEUED ASC
>> LIMIT 20
>>
>> But when I remove the ORDER BY part or the WHERE part from the statement
>> it
>> returns in <10ms.
>>
>> What may I do wrong?
>>
>> Thanks,
>> Thomas.
>>
>>
>>
>>
>> --
>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>
>

Re: Stand-alone Mode Ignite Installation

[Ilya Kasnacheev]

Hello!

Can you please highlight any specific issues that you are facing?

Regards,
-- 
Ilya Kasnacheev


сб, 10 апр. 2021 г. в 20:40, sri hari kali charan Tummala <
kali.tumm...@gmail.com>:

> Hi Ignite Users,
>
> Below we link is not working spark ignite standalone mode installation
> page.
>
> https://ignite.apache.org/docs/latest/installation/
>
>
> https://ignite.apache.org/docs/latest/extensions-and-integrations/ignite-for-spark/installation
>
>
> Thanks
> Sri
> --
> Thanks & Regards
> Sri Tummala
>
>

[2.10]If affinityKey is used as a where condition, cannot retrieve the result set

[38797715]

Hi team,

test.zip Is a single node snapshot compressed file.

Unzip the file to the work directory of ignite, and then use the 
example.xml start a node and activate the cluster.


You will see a table called viewsortcontrol.

Execute the following SQL, the result is normal:

SELECT * FROM viewsortcontrol

However, if you execute the following SQL, there is no query result:

SELECT * FROM viewsortcontrol WHERE tableid= '0x8d20e'

The tableid field is the affinitykey of the table.

What are the possible reasons?


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";>























































<>


VIEWSORTCONTROL.sql
Description: application/sql

Re: Persistent Atomic types

[Pavel Tupitsyn]

> Do I need to put a simple ICache.Put() inside a transaction?
No. Individual entry operations are always atomic.
Note that ICache has methods like PutIfAbsent(key, val), Replace(key, old,
new),
which are useful for conditional atomic updates without transactions and
locks.

- Transactions are the best way to update multiple entries atomically (all
or nothing).
- EntryProcessor is useful for updating a single entry based on a complex
logic (e.g. "if field1 > 10 then field2 = 20")

I can give more specific advice if you share more details on your use case.

On Sun, Apr 11, 2021 at 9:56 PM Kathryn Hogg  wrote:

> I have a need for some atomic types to be persistent so they survive a
> full cluster restart.  We are using Ignite.Net 2.10
>
>
>
> My plan is to
>
> · create a persistent region
>
> · create an ICache or ICache for each
> value type.  The latter will be easier to add Sql support in case I ever
> want to dig around via SQL Api for support/debugging.
>
>
>
> I see at least 3 different ways to make the operations atomic:
>
>
>
> · Use explicit locks on each key.  The main issue with this
> approach is getting lock requests right in the presence of failures
> (exceptions).  Nothing new or ground breaking but a good amount of boiler
> plate to correctly manage.
>
> · Use transactions.  The code for this is pretty clean.  Do I
> need to put a simple ICache.Put() inside a transaction?
>
> · Use EntryProcessor.  It seems like this would lock the key
> while the entry processor is running but the documentation doesn't make
> that explicit.
>
>
>
> What's the preferred way of implementing this?
>
>
>
> --
>
> Kathryn Hogg
>
> Principal Technology Architect
>
>
>