Skip to main content

slow sysquery requests

  • August 6, 2014
  • 1 reply
  • 10 views
Pe_Leroy
Forum|alt.badge.img
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


    1 reply

    eli_revach
    Forum|alt.badge.img+2
    • eli_revach
    • Participating Frequently
    • August 7, 2014
    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;


    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement