ROS cache size and Key-Value queries

phil2

Hello. As I know there are two types of cache vertica uses while reading data from disk:

• a file system cache
• and a cache of uncompressed data blocks (ROS cache) Vertica maintains internally to avoid decompression on blocks reused quickly.

They mentioned at MC MonitoringSystemMemoryUsage page:
https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/AdministratorsGuide/Monitoring/Vertica/MonitoringSystemMemoryUsage.htm?TocPath=Administrator's%20Guide|Monitoring%20HP%20Vertica|Monitoring%20HP%20Vertica%20Using%20MC|Monitoring%20System%20Resources|_____4

My concern is related with key-value queries. Generaly I see them performing on 300ms per query. But sometime there is a 4-10 seconds skipe. And if I rerun that kind of query after a skipe I get the same 300ms performance. I belive it is somehow connected with disk skan operations and cache failure.

Does anybody know the query MC is using to determine ROS cache size? Are there any setting available for ROS cache?

Jim_Knicely

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

phil2

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

Jim_Knicely

@phil2 - What Vertica version are you using?