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.
Canary Query Queuing
Moderator: NorbertKrupa
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Canary Query Queuing
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.
Re: Canary Query Queuing
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 ?
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Canary Query Queuing
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.
Re: Canary Query Queuing
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?
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Canary Query Queuing
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
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.