You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@ignite.apache.org by rssah <77...@gmail.com> on 2020/01/29 09:28:12 UTC

Query execution is too long issue!

When querying via some java application and if the topology is in such a way
that two clients connect to one server node, then some times we are getting
an exception saying query execution is too long.

 

This is the SQL schema for table

 

stmt.executeUpdate("CREATE TABLE DOCIDS (" +
" id LONG PRIMARY KEY, url VARCHAR, score LONG, appname VARCHAR) " +
" WITH \"template=replicated\"");

 

stmt.executeUpdate("CREATE INDEX idx_doc_name_url ON DOCIDS (appname,
url)");

 

Query -> 

SqlFieldsQuery query = new SqlFieldsQuery("SELECT count(id) FROM DOCIDS");

FieldsQueryCursor<List&lt;?>> cursor = cache.query(query);

For warning prints, please check the attachment. uploadthis.txt
<http://apache-ignite-users.70518.x6.nabble.com/file/t2760/uploadthis.txt>  

The Igntie metric when the warning is thrown is as follows:

 

Metrics for local node (to disable set 'metricsLogFrequency' to 0)
^-- Node [id=a7924eda, uptime=04:15:01.531]
^-- H/N/C [hosts=3, nodes=3, CPUs=16]
^-- CPU [cur=78.93%, avg=68.4%, GC=0.1%]
^-- PageMemory [pages=429194]
^-- Heap [used=754MB, free=78.68%, comm=2183MB]
^-- Off-heap [used=1696MB, free=51.32%, comm=3384MB]
^-- sysMemPlc region [used=0MB, free=99.99%, comm=100MB]
^-- default region [used=1696MB, free=46.73%, comm=3184MB]
^-- metastoreMemPlc region [used=0MB, free=99.96%, comm=0MB]
^-- TxLog region [used=0MB, free=100%, comm=100MB]
^-- Ignite persistence [used=1732MB]
^-- sysMemPlc region [used=0MB]
^-- default region [used=1732MB]
^-- metastoreMemPlc region [used=0MB]
^-- TxLog region [used=0MB]
^-- Outbound messages queue [size=0]
^-- Public thread pool [active=0, idle=0, qSize=0]
^-- System thread pool [active=0, idle=6, qSize=0]



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

Re: Query execution is too long issue!

Posted by Denis Magda <dm...@apache.org>.

Hi,

Most likely you need to optimize the query or do revisit your Ignite memory
and cluster configuration. My suggestion would be to start by checking
these performance notes. Let us know if you have any specific questions:
https://www.gridgain.com/docs/latest/perf-troubleshooting-guide/general-perf-tips
https://www.gridgain.com/docs/latest/perf-troubleshooting-guide/sql-tuning

-
Denis


On Wed, Jan 29, 2020 at 1:28 AM rssah <77...@gmail.com> wrote:

> When querying via some java application and if the topology is in such a
> way
> that two clients connect to one server node, then some times we are getting
> an exception saying query execution is too long.
>
>
>
> This is the SQL schema for table
>
>
>
> stmt.executeUpdate("CREATE TABLE DOCIDS (" +
> " id LONG PRIMARY KEY, url VARCHAR, score LONG, appname VARCHAR) " +
> " WITH \"template=replicated\"");
>
>
>
> stmt.executeUpdate("CREATE INDEX idx_doc_name_url ON DOCIDS (appname,
> url)");
>
>
>
> Query ->
>
> SqlFieldsQuery query = new SqlFieldsQuery("SELECT count(id) FROM DOCIDS");
>
> FieldsQueryCursor<List&lt;?>> cursor = cache.query(query);
>
> For warning prints, please check the attachment. uploadthis.txt
> <http://apache-ignite-users.70518.x6.nabble.com/file/t2760/uploadthis.txt>
>
>
> The Igntie metric when the warning is thrown is as follows:
>
>
>
> Metrics for local node (to disable set 'metricsLogFrequency' to 0)
> ^-- Node [id=a7924eda, uptime=04:15:01.531]
> ^-- H/N/C [hosts=3, nodes=3, CPUs=16]
> ^-- CPU [cur=78.93%, avg=68.4%, GC=0.1%]
> ^-- PageMemory [pages=429194]
> ^-- Heap [used=754MB, free=78.68%, comm=2183MB]
> ^-- Off-heap [used=1696MB, free=51.32%, comm=3384MB]
> ^-- sysMemPlc region [used=0MB, free=99.99%, comm=100MB]
> ^-- default region [used=1696MB, free=46.73%, comm=3184MB]
> ^-- metastoreMemPlc region [used=0MB, free=99.96%, comm=0MB]
> ^-- TxLog region [used=0MB, free=100%, comm=100MB]
> ^-- Ignite persistence [used=1732MB]
> ^-- sysMemPlc region [used=0MB]
> ^-- default region [used=1732MB]
> ^-- metastoreMemPlc region [used=0MB]
> ^-- TxLog region [used=0MB]
> ^-- Outbound messages queue [size=0]
> ^-- Public thread pool [active=0, idle=0, qSize=0]
> ^-- System thread pool [active=0, idle=6, qSize=0]
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Re: Query execution is too long issue!

Posted by Ilya Kasnacheev <il...@gmail.com>.

Hello!

What happens if you do COUNT(*) instead? I think we have some specific
optimizations for this case.

Regards,
-- 
Ilya Kasnacheev


ср, 29 янв. 2020 г. в 12:28, rssah <77...@gmail.com>:

> When querying via some java application and if the topology is in such a
> way
> that two clients connect to one server node, then some times we are getting
> an exception saying query execution is too long.
>
>
>
> This is the SQL schema for table
>
>
>
> stmt.executeUpdate("CREATE TABLE DOCIDS (" +
> " id LONG PRIMARY KEY, url VARCHAR, score LONG, appname VARCHAR) " +
> " WITH \"template=replicated\"");
>
>
>
> stmt.executeUpdate("CREATE INDEX idx_doc_name_url ON DOCIDS (appname,
> url)");
>
>
>
> Query ->
>
> SqlFieldsQuery query = new SqlFieldsQuery("SELECT count(id) FROM DOCIDS");
>
> FieldsQueryCursor<List&lt;?>> cursor = cache.query(query);
>
> For warning prints, please check the attachment. uploadthis.txt
> <http://apache-ignite-users.70518.x6.nabble.com/file/t2760/uploadthis.txt>
>
>
> The Igntie metric when the warning is thrown is as follows:
>
>
>
> Metrics for local node (to disable set 'metricsLogFrequency' to 0)
> ^-- Node [id=a7924eda, uptime=04:15:01.531]
> ^-- H/N/C [hosts=3, nodes=3, CPUs=16]
> ^-- CPU [cur=78.93%, avg=68.4%, GC=0.1%]
> ^-- PageMemory [pages=429194]
> ^-- Heap [used=754MB, free=78.68%, comm=2183MB]
> ^-- Off-heap [used=1696MB, free=51.32%, comm=3384MB]
> ^-- sysMemPlc region [used=0MB, free=99.99%, comm=100MB]
> ^-- default region [used=1696MB, free=46.73%, comm=3184MB]
> ^-- metastoreMemPlc region [used=0MB, free=99.96%, comm=0MB]
> ^-- TxLog region [used=0MB, free=100%, comm=100MB]
> ^-- Ignite persistence [used=1732MB]
> ^-- sysMemPlc region [used=0MB]
> ^-- default region [used=1732MB]
> ^-- metastoreMemPlc region [used=0MB]
> ^-- TxLog region [used=0MB]
> ^-- Outbound messages queue [size=0]
> ^-- Public thread pool [active=0, idle=0, qSize=0]
> ^-- System thread pool [active=0, idle=6, qSize=0]
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>