Unkillable zombie query
Hi,
I'm using vertica 7.1.1 on single node (ram 256Gb 32 core. O.S. centoS 6.6). I have run a query that never ends and the command close_session('idsession') is uneffective on it. I have also tried INTERRUPT_STATEMENT().
If I run the top on the terminal I have no activity for dbadmin, then vertica is not computing the query :
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
126078 dbadmin 20 0 15.8g 2.5g 34m S 0.7 1.0 1:36.31 vertica
Morever the expected running time for this query is less than 10s.
The only way that I have found to kill the query is this procedure:
1)shut down the database using the admitool
2)Because the shutting down process doesn't end (I have waited few hours) I have stopped it (ctr+c)
3)killed the vertica process on the host
4)restart of the database
This is the query:
select PK_ID
from (
select PK_ID
from stg.DB_1
where CITY like 'MILANO') a
inner join
(select a.USER_PK from (
SELECT USER_PK
FROM
tnt.MLR_TA_EVENTS_SENT_EXT e
GROUP BY USER_PK
HAVING COUNT( e.DELIVERY_ID) > 3 ) a
full outer join (
SELECT USER_PK
FROM
stg.MLR_TA_DELIVERY_EXT d
INNER JOIN tnt.MLR_TA_EVENTS_SENT_EXT e
ON d.DELIVERY_ID = e.DELIVERY_ID
WHERE
d.DELIVERY_ID = 7283) b on a.USER_PK=b.USER_PK where (b.USER_PK is null or a.USER_PK is null) )b
on a.pk_id=b.USER_PK;
This is the result of the explain:
Access Path:
+-JOIN HASH [Cost: 4M, Rows: 705M (NO STATISTICS)] (PATH ID: 1) Outer (LOCAL ROUND ROBIN)
| Join Cond: (DB_1.PK_ID = b.USER_PK)
| +-- Outer -> SELECT [Cost: 3M, Rows: 705M (NO STATISTICS)] (PATH ID: 2)
| | +---> JOIN MERGEJOIN(inputs presorted) [FullOuter] [Cost: 3M, Rows: 705M (NO STATISTICS)] (PATH ID: 3)
| | | Join Cond: (a.USER_PK = b.USER_PK)
| | | Filter: ((b.USER_PK IS NULL) OR (a.USER_PK IS NULL))
| | | Runtime Filter: (SIP2(HashJoin): b.USER_PK)
| | | +-- Outer -> SELECT [Cost: 1M, Rows: 705M (NO STATISTICS)] (PATH ID: 4)
| | | | +---> JOIN MERGEJOIN(inputs presorted) [Cost: 1M, Rows: 705M (NO STATISTICS)] (PATH ID: 5)
| | | | | Join Cond: (d.DELIVERY_ID = e.delivery_id)
| | | | | Materialize at Output: e.USER_PK
| | | | | +-- Outer -> STORAGE ACCESS for e [Cost: 654K, Rows: 705M (NO STATISTICS)] (PATH ID: 6)
| | | | | | Projection: tnt.MLR_TA_EVENTS_SENT_EXT_super2
| | | | | | Materialize: e.delivery_id
| | | | | | Filter: (e.delivery_id = 7283)
| | | | | | Runtime Filter: (SIP1(MergeJoin): e.delivery_id)
| | | | | +-- Inner -> STORAGE ACCESS for d [Cost: 14, Rows: 12K (NO STATISTICS)] (PATH ID: 7)
| | | | | | Projection: stg.MLR_TA_DELIVERY_EXT_super
| | | | | | Materialize: d.DELIVERY_ID
| | | | | | Filter: (d.DELIVERY_ID = 7283)
| | | +-- Inner -> SELECT [Cost: 2M, Rows: 10K (NO STATISTICS)] (PATH ID: 8)
| | | | +---> GROUPBY PIPELINED [Cost: 2M, Rows: 10K (NO STATISTICS)] (PATH ID: 9)
| | | | | Aggregates: count(e.delivery_id)
| | | | | Group By: e.USER_PK
| | | | | Filter: (<SVAR> > 3)
| | | | | +---> STORAGE ACCESS for e [Cost: 1M, Rows: 705M (NO STATISTICS)] (PATH ID: 10)
| | | | | | Projection: tnt.MLR_TA_EVENTS_SENT_EXT_super
| | | | | | Materialize: e.USER_PK, e.delivery_id
| +-- Inner -> STORAGE ACCESS for DB_1 [Cost: 161K, Rows: 12M (NO STATISTICS)] (PATH ID: 11)
| | Projection: stg.DB_1_super
| | Materialize: DB_1.PK_ID
| | Filter: (DB_1.CITY ~~ 'MILANO')
If I create two tables with the two main subqueries the new query run in 1s. The time taked to create the two tables is 34ms and 3s.
I have tried to increase the PRIORITYRUNTIMECAP of the general reource pool, but the result is the same.
Any Idea of what is happening here?
Comments
First try to get rid of the
messeges from your explain plan and run the query again.
Run this:
select analyze_statistics('schema_name.table_name');If you still not satisfied with the times run the an incremental DBD using the query you are about to run to create optimized projecitons to address this query.
Why does it take to long to kill it ?!! no idea