Can we able to find memory and cpu for running queries?

Answers

• Options

  mosheg Vertica Employee Administrator

  October 2019 edited October 2019

  Vertica MC provide this in GUI, here are some CLI examples.
  Top 10 queries with high CPU Cycles:
  SELECT transaction_id,statement_id, cpu_cycles_us, duration_ms
  FROM QUERY_CONSUMPTION
  WHERE request_type='QUERY' AND
  success AND
  ( NOW() - end_time) < '6 hour'
  ORDER BY 3 DESC,4
  LIMIT 10;

  Top 10 queries with high acquired memory:
  select
  qr.transaction_id, qr.statement_id,
  ra.pool_name,
  sum(qr.request_duration_ms) as request_duration_ms,
  sum(qr.memory_acquired_mb) as memory_acquired_mb
  from
  v_monitor.query_requests qr
  left outer join v_monitor.resource_acquisitions ra
  on qr.statement_id = ra.statement_id and
  qr.transaction_id = ra.transaction_id
  where
  qr.is_executing is false and
  ( NOW() - qr.start_timestamp) < '6 hour' and
  upper(regexp_substr(qr.request, '\w+')::char(8)) in ('SELECT', 'WITH') and
  qr.memory_acquired_mb > 0
  group by 1,2,3
  order by 5 DESC
  limit 10;

  0
• Options

  hsaxena20 Community Edition User

  October 2019

  Thanks. Also help me to get answer for below query
  Objective Question
  There is a batch of 2000 similar files with 10 rows each. We would like to insert all these files into a single table using COPY /*+ Direct */ with in default mergeout time interval. What are the likely hood of data loading?
  Answer: Which one is correct?
  1. Data will be loaded by executing 2000 COPY Direct commands using VSQL
  2. Data can not be loaded using COPY Direct for these many number of files.

  0
• Options

  mosheg Vertica Employee Administrator

  October 2019

  Here is an example, how to load data efficiently on all nodes using one COPY command.
  The speed and the load throughput will be determined by the following parameters you can change:
  Each Data file size (10-20GB recommended), the number of COPY commands (COPY streams) and the number of files mentioned in each COPY command.
  Higher number of paralleled COPY commands will speed the load but will create more ROS containers and occupy more CPU cores.
  There were ‘NULL’ strings which needed to be interpreted as NULL.

  COPY MYSCHEMA.MYTABLE
          (
          ComputerID,
          f2 FILLER varchar(36),
          KeyColumn as hash(f2),
          LastWriteTime 
          )
          FROM 
                  'ROOT_DIR/File_name*' ON v_vdb_node0001,
                  'ROOT_DIR/File_name*' ON v_vdb_node0002,
                  'ROOT_DIR/File_name*' ON v_vdb_node0003
               DIRECT 
               DELIMITER ','
               NULL '' 
  REJECTED DATA AS TABLE 'reject_table_name';
  

  0
• Options

  mosheg Vertica Employee Administrator

  October 2019

  Using the ON ANY NODE clause indicates that the source file to load is available on all of the nodes. If you specify this clause, COPY opens the file and parses it from any node in the cluster.
  For better performance, if you are using NFS, then you can create an NFS mount point on each node, which will allow all nodes to participate in the load without requiring files to be copied to all nodes.
  An example is shown here:
  https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/SpecifyingCOPYFROMOptions.htm
  

  1