Performance troubleshooting of the queries on a dashboard.

kfruchtman

Hi Experts
I am trying to optimize my dashboard that runs too slow.
I have 6 queries on my dashboard.Each of them run up to 10 seconds when I execute them alone as a single query
on my dashboard or in the MC.
When I run all of them together it runs a full minute and even a bit more.

My configuration:
--one node cluster
--no other queries running on the db at the time when I run the queries on the dashboard.
--I have also created a dedicated resource pool for the dashboard with resource configuration as explained at

https://www.vertica.com/kb/Low-Latency-Optimizations/Content/BestPractices/Low-Latency-Optimizations.htm

--I have changed "execution parallelism" to AUTO after seeing that putting "1" is not helping.

Attached is my resource pool configuration.

what could be the problem? it seems that the db is not running any of the queries in parallel and very very slow when
all together.

any ideas?

thanks
Keren

Bryan_H

Hi, could you connect with vsql (or any client) as the grafana user and run EXPLAIN on the query?

The issue may not be resources, but rather the query plan is not efficient. The EXPLAIN plan could show other options such as generating a projection optimized for the queries, or a directed query that runs a more efficient plan.

kfruchtman

Hi Bryan!
Thanks for answering!!!
Well, that is why I took the queries that run the fastest.I tested them standalone on the MC to see they are running less than 10 seconds each.
But, I got some tips from Vertica employees that told me to create and change to other pool configurations and upgrade the resources of the server dramatically (RAM,CPU and SSD disk).That all helped a lot.

I am left with one question regarding my dashboard performance and maybe you can help me:
How do I control the JDBC pools that are connected to my dashboard.How do I control it and don't let it be overloaded so the system can continue working at the best performance all the time?
I read in the documentation -
https://www.vertica.com/kb/Low-Latency-Optimizations/Content/BestPractices/Low-Latency-Optimizations.htm
In Application Configuration section:
"Use JDBC connection pools to reduce the overhead of repeatedly opening network connections between the client and the server. Opening a new connection for each request takes time and is costly. When a request comes in, Vertica assigns the request to a pre-existing connection in the pool. If there are no free connections, Vertica creates a new connection. When the request completes, the connection returns to the pool, awaiting another request. Using JDBC connection pools eliminates the time it takes to authenticate a connection every time a new request is made. This lowers the latency of the query because it reduces the amount of time it takes to connect"

How do I do that exactly ? I was looking in the documentation but it was not clear enough.

Thank you so much for helping me!
Keren