ROS cache size and Key-Value queries

Comments

• Options

  Jim_Knicely - Select Field - Administrator

  February 2018 edited February 2018

  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

  0
• Options

  phil2 ✭

  February 2018

  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

  0
• Options

  Jim_Knicely - Select Field - Administrator

  February 2018 edited February 2018

  @phil2 - What Vertica version are you using?

  0