Page 1 of 1

Queries slowdown on server load

Posted: Wed Dec 02, 2015 11:04 am
by andylens
Hi!
I have troubles with cluster performance during load testing.
I have some typical queries which are using projections and they execute in nice 50-60ms. Then I open about 150 connections and start to execute my queries in it periodically (~30 req/min per session, ~5000 req/min total). As a result, average execution time rises badly (~1500ms). Even "SELECT 1" executes more than 1000ms. But I don't see any lack of resources. CPU time, memory and net capacity are far from depletion, connection limit is nor reached. Where else should I search for the bottleneck?

Re: Queries slowdown on server load

Posted: Wed Dec 02, 2015 3:09 pm
by NorbertKrupa
How are your resource pools configured? Did you configure any? Are your connections load balanced? You might want to look at a slow/medium/fast configuration for starters.

Re: Queries slowdown on server load

Posted: Wed Dec 02, 2015 3:48 pm
by andylens
I use one resource pool:

pool_id | 45035996273737108
name | queries
is_internal | f
memorysize | 8156M
maxmemorysize |
executionparallelism | AUTO
priority | 0
runtimepriority | MEDIUM
runtimeprioritythreshold | 2
queuetimeout | 300
plannedconcurrency | 80
maxconcurrency | 100
runtimecap |
singleinitiator | f
cpuaffinityset |
cpuaffinitymode | ANY
cascadeto |

Balancing simply by choose random cluster node in application (all queries are relatively equal).

Re: Queries slowdown on server load

Posted: Wed Dec 02, 2015 4:22 pm
by NorbertKrupa
Here's an example of cascading resource pools.

I would recommend reading the Vertica Concurrency and Workload Management paper.

If you have multiple nodes, make sure that both server and client side load balancing are enabled.

Re: Queries slowdown on server load

Posted: Wed Dec 02, 2015 6:51 pm
by scutter
You can check the dc_query_executions table to see where the time is being spent when the queries slow down - whether it’s waiting on resources, query planning, or in the actual execution.

—Sharon

Re: Queries slowdown on server load

Posted: Sun Dec 06, 2015 8:02 am
by VerDBA
I also have similar situations....I dont see any contention or lack of resources but sometimes query run in 6 Seconds sometimes 26 seconds.....

When I looked more I saw in query_request or query profile table, query start_timestamp is 23:47:11. But when I look at resourec_acquistions table for same transaction I see queue_entry_timestamp 23:47:32.

Where did this (23:47:32 - 23:47:11) = 21 Seconds spend or go? This additional 21 seconds got added to execution time....