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

hsaxena20

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

mosheg

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;

hsaxena20

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.

mosheg

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';

mosheg

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