ROS cache size and Key-Value queries

phil2phil2 Registered User
edited February 4 in Vertica Forum

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?

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited February 4

    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

  • phil2phil2 Registered User

    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_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited February 9

    @phil2 - What Vertica version are you using?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file