SQL_QUERIES_HISTORY doesn't display most of queries

classic Classic list List threaded Threaded
3 messages Options
Alex Panchenko Alex Panchenko
Reply | Threaded
Open this post in threaded view
|

SQL_QUERIES_HISTORY doesn't display most of queries

Hi,

I have an Ignite cluster with 3rd party persistence enabled (Postgres db).
My service is reading all data from the Ignite which contains ~20 caches.
Loadtests revealed horrible performance - the app which reads data from
Ignite has requests throughput  which is in 3-4x lower than in the same app
which reads data directly from Postgres (the same service logic, but 1st app
reads data from Ignite, 2nd one - directly from Postgres)

There is no much data in the cluster - up to 2000 records in the biggest
tables, most of the tables contain up to 100 records. I tried the same with
a database which has millions of records but performance expectedly worse,
so I wanted to figure out what is going on using small db first

The following things helped to increase performance a little bit:
  - increasing thread pool zise
<bean class="org.apache.ignite.configuration.IgniteConfiguration">
       
        <property name="systemThreadPoolSize" value="64"/>
        <property name="queryThreadPoolSize" value="64"/>
   ....

   - using ATOMIC atomicityMode instead of TRANSCTIONAL for most of caches
It hasn't helped much so I decided to profile all running queries

I've enabled metrics
https://www.gridgain.com/docs/latest/administrators-guide/monitoring-metrics/configuring-metrics

But when I look at SYS.SQL_QUERIES_HISTORY table I didn't find details about
99% of queries which were running in the cluster. Why? What I'm doing wrong?





--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Alex Panchenko Alex Panchenko
Reply | Threaded
Open this post in threaded view
|

Re: SQL_QUERIES_HISTORY doesn't display most of queries

Can somebody help to figure out why SYS.SQL_QUERIES_HISTORY table doesn't
contain records for queries that are executed by ignite? Is there any other
way to get stats for running queries (like execution duration, min max, the
quantity of queries and so on)?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Konstantin Orlov Konstantin Orlov
Reply | Threaded
Open this post in threaded view
|

Re: SQL_QUERIES_HISTORY doesn't display most of queries

Hi Alex,

The SQL_QUERIES_HISTORY view contains only those queries that were initiated on the current node (the query started running on this node). Hence result of SQL_QUERIES_HISTORY from node1 != result from node2. Also it contains only finished queries, so if you are interested in running queries, you should take a look at SQL_QUERIES view.

-- 
Regards,
Konstantin Orlov
Software Engineer, St. Petersburg
+7 (921) 445-65-75
https://www.gridgain.com
Powered by Apache® Ignite™



On 29 Jul 2020, at 10:00, Alex Panchenko <[hidden email]> wrote:

Can somebody help to figure out why SYS.SQL_QUERIES_HISTORY table doesn't
contain records for queries that are executed by ignite? Is there any other
way to get stats for running queries (like execution duration, min max, the
quantity of queries and so on)?



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