Queries slowdown on server load

Moderator: NorbertKrupa

Post Reply
andylens
Newbie
Newbie
Posts: 4
Joined: Sat Nov 14, 2015 12:57 pm

Queries slowdown on server load

Post by andylens » Wed Dec 02, 2015 11:04 am

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?

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Queries slowdown on server load

Post by NorbertKrupa » Wed Dec 02, 2015 3:09 pm

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.

andylens
Newbie
Newbie
Posts: 4
Joined: Sat Nov 14, 2015 12:57 pm

Re: Queries slowdown on server load

Post by andylens » Wed Dec 02, 2015 3:48 pm

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).

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Queries slowdown on server load

Post by NorbertKrupa » Wed Dec 02, 2015 4:22 pm

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.
Checkout vertica.tips for more Vertica resources.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Queries slowdown on server load

Post by scutter » Wed Dec 02, 2015 6:51 pm

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 Cutter
Vertica Consultant, Zazz Technologies LLC

VerDBA
Newbie
Newbie
Posts: 5
Joined: Wed Nov 25, 2015 4:34 am

Re: Queries slowdown on server load

Post by VerDBA » Sun Dec 06, 2015 8:02 am

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....

Post Reply

Return to “Vertica Performance Tuning”