Canary Query Queuing

Moderator: NorbertKrupa

Post Reply
VerDBA
Newbie
Newbie
Posts: 5
Joined: Wed Nov 25, 2015 4:34 am

Canary Query Queuing

Post by VerDBA » Wed Nov 25, 2015 4:43 am

I have a Canaray query that runs every minute in database. This query run every minutes. It just does: select count(1) from sessions; Most of the time i see this query hitting database on first or sencond seconds. Like 10:09:01, 10:10:01, 10:11:02. But sometimes i see this query wait and gets in the database like 10:12:08. I dont understand why it takes 8 seconds to get in the database. What is blocking it to get in the db?

Need some help on this.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Canary Query Queuing

Post by NorbertKrupa » Wed Nov 25, 2015 3:46 pm

It's probably awaiting resources. Did you check locks, resource acquisitions? If you want it to run immediately, then put it in a higher priority pool. If you're just doing an alive check, consider just using SELECT 1 or date.
Checkout vertica.tips for more Vertica resources.

VerDBA
Newbie
Newbie
Posts: 5
Joined: Wed Nov 25, 2015 4:34 am

Re: Canary Query Queuing

Post by VerDBA » Thu Nov 26, 2015 1:25 am

Thanks for Reply...After more analysis I found Input Queue Wait number is outstanding large....Do you ahve any information why or how to tune this ?

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Canary Query Queuing

Post by NorbertKrupa » Thu Nov 26, 2015 8:03 pm

The Input Queue Wait is "time in microseconds that an operator spends waiting for upstream operators." What are the upstream operations taking place in the query?
Checkout vertica.tips for more Vertica resources.

VerDBA
Newbie
Newbie
Posts: 5
Joined: Wed Nov 25, 2015 4:34 am

Re: Canary Query Queuing

Post by VerDBA » Thu Nov 26, 2015 9:57 pm

this is the query I am running: select count(1) from sessions; I dont understand why there will be upstream perations. Is there any way to check: What are the upstream operations taking place in the query?

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Canary Query Queuing

Post by NorbertKrupa » Sat Nov 28, 2015 10:52 pm

Code: Select all

 dbadmin=> EXPLAIN SELECT COUNT(1) FROM SESSIONS;
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 EXPLAIN SELECT COUNT(1) FROM SESSIONS;

 Access Path:
 +-GROUPBY NOTHING [Cost: 3, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
 |  Aggregates: count(1)
 |  Execute on: Query Initiator
 | +---> STORAGE ACCESS for SESSIONS [Cost: 2, Rows: 10K (1 RLE) (NO STATISTICS)] (PATH ID: 2)
 | |      Projection: v_monitor.sessions_p
 | |      Execute on: Query Initiator
Storage access probably. You should try adding a predicate if you insist on using sessions.

Otherwise, SELECT COUNT(1);

Code: Select all

dbadmin=> EXPLAIN SELECT COUNT(1);
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 EXPLAIN SELECT COUNT(1);

 Access Path:
 +-GROUPBY NOTHING [Cost: 11, Rows: 1] (PATH ID: 1)
 |  Aggregates: count(1)
 |  Execute on: Query Initiator
 | +---> STORAGE ACCESS for dual [Cost: 10, Rows: 1] (PATH ID: 2)
 | |      Projection: v_catalog.dual_p
 | |      Execute on: Query Initiator
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “Vertica Performance Tuning”