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?
Queries slowdown on server load
Moderator: NorbertKrupa
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Queries slowdown on server load
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.
Checkout vertica.tips for more Vertica resources.
Re: Queries slowdown on server load
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).
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).
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Queries slowdown on server load
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.
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.
Checkout vertica.tips for more Vertica resources.
Re: Queries slowdown on server load
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
—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC
Re: Queries slowdown on server load
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....
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....