What kind of query performance hit should I expect when a node is down?

Aniket_Schneide

We have a 5-node cluster operating on K-safety 1, and we have noticed that query performance drops dramatically when a node is down (as much as 10x slowdown or more). Load performance also drops, but to a much smaller degree (more like 2x). During a recent outage, we generated an EXPLAIN plan for the following query: SELECT pk AS clip_key, SUM(viewDelta) AS view_count, SUM(rateDelta) AS rating_count, SUM(commentDelta) AS comment_count, SUM(ratingDelta) AS rating FROM clip WHERE pk = '86dd59d498a00acf1c0fdecd6db4074b' GROUP BY pk; Access Path: +-GROUPBY PIPELINED [Cost: 107K, Rows: 2K] (PATH ID: 1) | Aggregates: sum(clip.viewDelta), sum(clip.rateDelta), sum(clip.commentDelta), sum(clip.ratingDelta) | Group By: clip.pk | Execute on: v_prod_node0001, v_prod_node0002, v_prod_node0003, v_prod_node0005 | +---> STORAGE ACCESS for clip [Cost: 91K, Rows: 9M] (PATH ID: 2) | | Projection: vid.clip_sp_temp_b1 | | Materialize: clip.pk, clip.viewDelta, clip.commentDelta, clip.rateDelta, clip.ratingDelta | | Filter: (clip.pk = '86dd59d498a00acf1c0fdecd6db4074b') | | Execute on: v_prod_node0001, v_prod_node0002, v_prod_node0003, v_prod_node0005 +-GROUPBY PIPELINED (REPLACEMENT FOR DOWN NODE) [Cost: 142M, Rows: 2K] | Aggregates: sum(clip.viewDelta), sum(clip.rateDelta), sum(clip.commentDelta), sum(clip.ratingDelta) | Group By: clip.pk | Execute on: v_prod_node0003 | +---> STORAGE ACCESS for clip [Cost: 142M, Rows: 9M] | | Projection: vid.clip_sp_temp_b0 | | Materialize: clip.pk, clip.viewDelta, clip.commentDelta, clip.rateDelta, clip.ratingDelta | | Filter: (clip.pk = '86dd59d498a00acf1c0fdecd6db4074b') | | Execute on: v_prod_node0003 I am wondering why the storage access cost for the failover node is 3 orders of magnitude higher than for the original node, and if that is related to the dramatic query slowdown that we experience. I am also wondering what kind of query slowdown is normal for a 5-node cluster with one node in the process of recovering, and whether there are steps we can take to reduce the impact of a node outage.

[Deleted User]

Hi Aniket, Hm... Do your buddy projections have the same sort order? If your buddy projections are not identical, then when a node is down, Vertica is being forced to use a sub-optimal projection; that can slow queries dramatically. Also, how much RAM does this query want to use? When a node has failed, currently its buddy takes over and does twice the work. But it does twice the work with the same amount of RAM. Sometimes this can cause the operation to no longer fit in memory; if a query spills to disk, that can drive the cost up dramatically. You can mitigate this to an extent, if it is the issue, by playing with resource pools and allocating more memory to the query. For the "Cost" number, keep in mind that it's the optimizer's heuristic cost-model value. It doesn't have any real inherent meaning (it certainly doesn't necessarily reflect the actual cost of the query by any meaningful metric when it's actually executed); the intent is simply that, if you compare two values, the larger values should (in the estimation of the optimizer) be more expensive than the smaller value. That's correct here -- the buddy is doing extra work to make up for the node that's down. Adam