I need to check which query is taking lots of cpu and memory.
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
( NOW() - end_time) < '6 hour'
ORDER BY 3 DESC,4
Top 10 queries with high acquired memory:
sum(qr.request_duration_ms) as request_duration_ms,
sum(qr.memory_acquired_mb) as memory_acquired_mb
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
Thanks. Also help me to get answer for below query
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.
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
REJECTED DATA AS TABLE 'reject_table_name';
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