ROS cache size and Key-Value queries

Hi,

You can see all of the queries executed by MC in the QUERY_REQUESTS table.

Here is how the rcache is determined:

select
mem.node_name, mem.ts,
cache_size*100/baseline as rcache
from
(
  (
    select
      node_name,
      timestamp_trunc(time, 'SS') as ts,
      (total_swap_max_value-free_swap_min_value+total_memory_max_value)::float as baseline
    from dc_memory_info_by_minute
  ) mem
  JOIN
  (
    select
      node_name,
      timestamp_trunc(time, 'SS') as ts,
      sum(total_size_kb_max_value-free_size_kb_min_value)*1024 as cache_size
    from dc_block_memory_manager_statistics_by_minute
    group by 1, 2
  ) block
  using (node_name, ts)
) sq
order by 2, 1;

What parameters control ROS cache?

dbadmin=> select distinct parameter_name, current_value, description from vs_configuration_parameters where parameter_name ilike '%ROScache%';
   parameter_name    | current_value |                                             description
---------------------+---------------+------------------------------------------------------------------------------------------------------
 ROSCacheBlocks      | 4096          | Number of 64K ROS blocks that may be kept in the decompression cache (default is 4096 blocks; 256MB)
 ROSCacheLargeBlocks | 32            | Number of large (>64K) ROS blocks that may be kept in the decompression cache (for Grouped ROSs)
(2 rows)

But, I doubt the issue is with the cache. Maybe a resource pool issue, or your K/V query might not be running on a single node?

Makes sure to read through the "Key/Value Performance and Troubleshooting" section of the doc here:

https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/ProgrammersGuide/ClientJDBC/KVApi/troubleshootingKV.htm

Hi
Yeah, I have figured out already thet EXECUTIONPARALLELISM is wasting threads. In addition I've set HIGH priority, exclusive CPU and gave 3/4 of cpu.
Here are the settings I have now:

ALTER RESOURCE POOL backend MAXCONCURRENCY 42;
ALTER RESOURCE POOL backend PLANNEDCONCURRENCY 28;
ALTER RESOURCE POOL backend CPUAFFINITYMODE EXCLUSIVE CPUAFFINITYSET '0-41';
ALTER RESOURCE POOL backend EXECUTIONPARALLELISM 1;
ALTER RESOURCE POOL backend PRIORITY 100;
ALTER RESOURCE POOL backend RUNTIMEPRIORITY HIGH;
ALTER RESOURCE POOL backend MAXMEMORYSIZE NONE;

And I'm still facing the same issue with spikes. Most key-value queries run fast, but some hung for few seconds and tens of seconds. And yaeh it looks like throttling after all when there are few outliers at the same time. I'm just out of clue how can I fix this Any advice is appreciated. Guessing postgres will be much of help for me.

Thank you

@phil2 - What Vertica version are you using?