v_monitor.query_requests performs very slowly

Ramunas_Balukon

Hi, 
select * from v_monitor.query_requests order by start_timestamp desc limit 100
takes ~ 20 seconds. 

query_requests only 20 K rows. 
Any ideas how to workaround?

Ramunas

Navin_C

Hi Ramunas,

The query that you are executing is hitting the system table.
Such queries use sysquery resource pool which has reserved memory of 64MB.
This might be the reason why they are slow.
However, I cannot recommend to increase the memory size of this pool.

Also, just check if you need to do a order by desc clause.....I think the records are already sorted in this table.

Hope this helps. 

Adrian_Oprea_1

 Run a profile on the query and see the memory caps, and compare it to the sysquery pool memory max. 
 You can increase you sysquery  resources(not recommended), or you can limit the number of columns retrieved from the  query_requests table.
 

Adrian_Oprea_1

Here is an even better way for you to see what is going on and why your query is taking to long.
You can use label hint to track your query inside the v_monitor system table, with this you can create then a script that will pullout the data(mem use , exec time, used pool, mas resources , etc..)
See example:

--run your query with the desired label
vsql -U dbadmin -w testselect /*+ label (lbl123)*/ * from query_requests order by start_timestamp desc limit 0;
select QUERY_REQUESTS.request_label,
       RESOURCE_ACQUISITIONS.pool_name as "Pool Used",
       RESOURCE_POOLS.memorysize as "Max Mem Available",
       concat(to_char(sum(RESOURCE_ACQUISITIONS.memory_inuse_kb) / 1048576, '999.999'), ' M') as "Mem Used"
  from RESOURCE_ACQUISITIONS
 inner join QUERY_REQUESTS on 
                    RESOURCE_ACQUISITIONS.transaction_id = QUERY_REQUESTS.transaction_id
 inner join RESOURCE_POOLS on 
                    RESOURCE_POOLS.name = RESOURCE_ACQUISITIONS.pool_name
 where QUERY_REQUESTS.request_label = 'lbl123'
 and  QUERY_REQUESTS.statement_id = (select max(statement_id)-1 from current_session)
 and  QUERY_REQUESTS.transaction_id = (select transaction_id from current_session)
 group by 1, 2, 3;
 \q

 - the query will use the transaction_id  and it's statement_id  to identify the exact amount of resources used during the execution.
 - see the label name.
 - i exit the session just to make sure i capture the exact transaction_id  and statement_id.
I hope i am right - hope the Vertica experts could correct if anything is wrong. 

eric_1

Is there a reason increasing memory size of sysquery is not recommended? Other than blocking memory for non user productive queries?
Regards,
Eric Owhadi

eli_revach

Hi 
Vertica system views are not fully optimize , the way i use to improve their performance  , is  by creation query that is subset of this this views . Below is an example for your case :

select ri.node_name,       ri.user_name,
       ri.session_id,
       ri.request_id,
       ri.transaction_id,
       ri.statement_id,
       ri.request_type,
       replace(replace(ri.request, E'\\n', ' '), E'\\t', ' ') as request,
       ri.label as request_label,
       ri.search_path,       
       ri.time                                   as start_timestamp          
from
     v_internal.dc_requests_issued ri
order by start_timestamp desc limit 100 ;
 
Time: First fetch (100 rows): 4266.142 ms. All rows formatted: 4269.602 ms


select * from v_monitor.query_requests order by start_timestamp desc limit 100

Time: First fetch (100 rows): 33306.925 ms. All rows formatted: 33313.229 ms