slow sysquery requests

Pe_Leroy

Hello,

I continuously load data and execute queries on them in Vertica and I rely on sysquery tables to perform regular house-keeping. So I launch a lot of requests towards the v_monitor tables, especially the v_monitor schema.partition_status.
These requests looks quite slow to me.
I checked they have enough memory to perform ( I tried to increase sysquery memorysize or I also checked that when default sysquery memorysize is used, the general pool is free enough to accept that sysquery borrows from there).

As the requests below shows it, these sysquery requests are queued and a lot of them waits for more than 5 sec in the queue.
=> select * from(SELECT pool_name, queue_entry_timestamp, acquisition_timestamp, (acquisition_timestamp-queue_entry_timestamp) AS queue_wait, node_name FROM V_MONITOR.RESOURCE_ACQUISITIONS WHERE node_name ILIKE '%node000%' and pool_name LIKE '%query%') foo where queue_wait >= '00:00:05'::INTERVAL;

 pool_name |     queue_entry_timestamp     |     acquisition_timestamp     |   queue_wait    |       node_name-----------+-------------------------------+-------------------------------+-----------------+-----------------------
 sysquery  | 2014-08-06 09:21:10.716849+02 | 2014-08-06 09:21:15.838563+02 | 00:00:05.121714 | v_verticadb7_node0001
 sysquery  | 2014-08-06 09:21:10.701133+02 | 2014-08-06 09:21:15.813646+02 | 00:00:05.112513 | v_verticadb7_node0003
 sysquery  | 2014-08-06 09:20:55.607019+02 | 2014-08-06 09:21:00.73524+02  | 00:00:05.128221 | v_verticadb7_node0001
 sysquery  | 2014-08-06 09:20:49.255327+02 | 2014-08-06 09:20:55.551017+02 | 00:00:06.29569  | v_verticadb7_node0002
 sysquery  | 2014-08-06 09:20:49.707672+02 | 2014-08-06 09:20:56.185577+02 | 00:00:06.477905 | v_verticadb7_node0003
 sysquery  | 2014-08-06 09:20:36.080848+02 | 2014-08-06 09:20:41.577282+02 | 00:00:05.496434 | v_verticadb7_node0001
 sysquery  | 2014-08-06 09:18:44.384916+02 | 2014-08-06 09:18:52.310069+02 | 00:00:07.925153 | v_verticadb7_node0001
 sysquery  | 2014-08-06 09:18:44.375139+02 | 2014-08-06 09:18:52.356248+02 | 00:00:07.981109 | v_verticadb7_node0002
 sysquery  | 2014-08-06 09:18:44.37937+02  | 2014-08-06 09:18:52.294924+02 | 00:00:07.915554 | v_verticadb7_node0003
 sysquery  | 2014-08-06 09:18:30.285131+02 | 2014-08-06 09:18:36.990072+02 | 00:00:06.704941 | v_verticadb7_node0002
 sysquery  | 2014-08-06 09:18:13.14312+02  | 2014-08-06 09:18:18.94346+02  | 00:00:05.80034  | v_verticadb7_node0003
 sysquery  | 2014-08-06 09:17:43.348301+02 | 2014-08-06 09:17:53.708614+02 | 00:00:10.360313 | v_verticadb7_node0001
 sysquery  | 2014-08-06 09:17:43.784276+02 | 2014-08-06 09:17:53.697057+02 | 00:00:09.912781 | v_verticadb7_node0002
 sysquery  | 2014-08-06 09:17:43.353602+02 | 2014-08-06 09:17:53.689592+02 | 00:00:10.33599  | v_verticadb7_node0003
 sysquery  | 2014-08-06 09:17:37.47863+02  | 2014-08-06 09:17:46.867509+02 | 00:00:09.388879 | v_verticadb7_node0001
 sysquery  | 2014-08-06 09:17:37.47559+02  | 2014-08-06 09:17:46.902676+02 | 00:00:09.427086 | v_verticadb7_node0002
...
(188 rows)


I'd like to know whether it is normal or not that they are queud so long and if there is a means to speed them up.

Thanks a lot for any feedback,
Br

eli_revach

Hi ,
Below is the view content that define for partition_status , as you can is it include many joins and logic . Query system table is slow and it known issue , my recommendation  for you is to try find what you look for on the underline tables that define the view (most view are define very generic to fit many use cases ) , in most of the cases  your can get what you need with them .

create view "v_monitor"."partition_status" as select       projection_info.projection_id,
       projection_info.table_schema,
       projection_info.table_name,
       projection_info.table_id,
       projection_info.projection_schema,
       projection_info.projection_name,
       partition_info.partition_reorganize_percent
from
       (select *
       from v_catalog.tables t join v_catalog.projections p
       on t.table_id=p.anchor_table_id and partition_expression <> '')
       as projection_info
left outer join
     ((select
              v1.projection_id,
              (100.00*v2.sum/v1.sum)::int as partition_reorganize_percent
       from
            (select
                    r1.projection_id,
                    sum(used_bytes)
                    from v_monitor.partitions p1
                    join v_monitor.storage_containers r1 on ros_id=storage_oid group by r1.projection_id) v1
       join
            (select
                    r2.projection_id,
                    sum(used_bytes)
             from v_monitor.partitions p2 join v_monitor.storage_containers r2 on ros_id=storage_oid where partition_key is not null group by r2.projection_id) v2
       on v1.projection_id=v2.projection_id)
      union all
      (select distinct
                p1.projection_id,
                0
        from v_monitor.partitions p1 where p1.partition_key is null and not exists (select * from v_monitor.partitions p2 where p1.projection_id=p2.projection_id and p2.partition_key is not null)))
        as partition_info
on partition_info.projection_id=projection_info.projection_id;