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

hsaxena20hsaxena20 Community Edition User

I need to check which query is taking lots of cpu and memory.


  • Options
    moshegmosheg Vertica Employee Administrator
    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
    WHERE request_type='QUERY' AND
    success AND
    ( NOW() - end_time) < '6 hour'
    LIMIT 10;

    Top 10 queries with high acquired memory:
    qr.transaction_id, qr.statement_id,
    sum(qr.request_duration_ms) as request_duration_ms,
    sum(qr.memory_acquired_mb) as memory_acquired_mb
    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
    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;

  • Options
    hsaxena20hsaxena20 Community Edition User

    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.

  • Options
    moshegmosheg Vertica Employee Administrator

    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.

            f2 FILLER varchar(36),
            KeyColumn as hash(f2),
                    'ROOT_DIR/File_name*' ON v_vdb_node0001,
                    'ROOT_DIR/File_name*' ON v_vdb_node0002,
                    'ROOT_DIR/File_name*' ON v_vdb_node0003
                 DELIMITER ','
                 NULL '' 
    REJECTED DATA AS TABLE 'reject_table_name';
  • Options
    moshegmosheg Vertica Employee Administrator

    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:

Leave a Comment

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