You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by se...@apache.org on 2017/07/18 00:38:02 UTC
[1/2] incubator-trafodion git commit: [TRAFODION-2688] Need a way to
find queries that are stuck in compilation via RMS
Repository: incubator-trafodion
Updated Branches:
refs/heads/master a3e19b46d -> 6fb5676cd
[TRAFODION-2688] Need a way to find queries that are stuck in compilation via RMS
When a query is stuck in compilation, the mxosrvr remains in connected state forever.
Connection idle timer doesn't kick in. The query timeout can't cancel this query
because the query hasn't start executing. There is a need to find the process id of the
mxosrvr(master) process that is stuck in compilation via RMS. Then the mxosrvr process can be killed.
To find the queries stuck in compile for more than 30 seconds
cd $TRAF_HOME/export/limited-support-tools/LSO
./offender -s queries_in_compile
This script lists queries that are being compiled for more than the given number of seconds
CURRENT_TIMESTAMP TIME_IN_SECONDS NODE PID QUERY_ID ` SOURCE_TEXT
-------------------------- -------------------- ----------- ----------- ---------------------------------------------------------------------------- ----------------------
2017-07-15 01:54:14.909780 1860 0 5505 MXID11000005505212366841736965000000000000206U3333300_278___SQLCI_DML_LAST__ "select * from partsupp ;
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/df0be644
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/df0be644
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/df0be644
Branch: refs/heads/master
Commit: df0be6449e45d5fed13e796402f435460fb40040
Parents: a3e19b4
Author: selvaganesang <se...@esgyn.com>
Authored: Sat Jul 15 02:57:08 2017 +0000
Committer: selvaganesang <se...@esgyn.com>
Committed: Sat Jul 15 02:57:08 2017 +0000
----------------------------------------------------------------------
.../sqf/export/limited-support-tools/LSO/README | 23 ++++++++---
.../export/limited-support-tools/LSO/offender | 7 +++-
.../LSO/queries_in_compile.sql | 42 ++++++++++++++++++++
core/sql/cli/sqlcli.h | 3 +-
core/sql/executor/ExStats.cpp | 26 +++++++++++-
5 files changed, 92 insertions(+), 9 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/df0be644/core/sqf/export/limited-support-tools/LSO/README
----------------------------------------------------------------------
diff --git a/core/sqf/export/limited-support-tools/LSO/README b/core/sqf/export/limited-support-tools/LSO/README
index 098ea1e..0ca240a 100644
--- a/core/sqf/export/limited-support-tools/LSO/README
+++ b/core/sqf/export/limited-support-tools/LSO/README
@@ -27,6 +27,7 @@ e) DEAD_QUERIES
f) UNMONITORED_QUERIES
g) SE_BLOCKED_QUERIES
h) SE_OFFENDER_QUERIES
+i) QUERIES_IN_COMPILE
All dynamic SQL queries including child queries can be monitored using this
mechanism, because it uses direct access to the RMS infrastructure. RMS
@@ -62,6 +63,7 @@ Here is the help from the offender script:
in_client
unmonitored
dead
+ in_compile
-t|--time Optional Default 30
values: 0 < <time> < 32768
@@ -191,7 +193,7 @@ CURRENT_TIMESTAMP TIME_IN_SECONDS QUERY_ID
ACTIVE QUERIES
================
-This statement lists queries which either have finished executing within the
+This script lists queries which either have finished executing within the
given number of seconds or still being executed. The negative number in
LAST_ACTIVITY_SECS means that the query has completed that many seconds ago.
@@ -203,7 +205,7 @@ CURRENT_TIMESTAMP LAST_ACTIVITY_SECS QUERY_ID
INACTIVE QUERIES
================
-This statement lists allocated, prepared queries which either have finished
+This script lists allocated, prepared queries which either have finished
executing, or are not yet executed. If the query is not yet executed, it will
be selected only if it was compiled the given number of seconds ago. Similarly,
if the query has been executed, it will be selected only if it finished
@@ -223,7 +225,7 @@ CURRENT_TIMESTAMP LAST_ACTIVITY_SECS QUERY_ID
DEAD QUERIES
============
-This statement lists queries that have not been de-allocated by the master
+This script lists queries that have not been de-allocated by the master
executor process when it exited (normally or abnormally), and which have
persisted after the process exit for a period longer than the given number of
seconds.
@@ -240,7 +242,7 @@ CURRENT_TIMESTAMP LAST_ACTIVITY_SECS BLOCKED_IN_SQL BLOCKED_
UNMONITORED QUERIES
===================
-This statement lists the queries for which the statistics collection type is
+This script lists the queries for which the statistics collection type is
set to NO_STATS or ALL_STATS, either by SQL engine or by the user.
The SQL commands to list UNMONITORED queries are available at:
@@ -265,7 +267,7 @@ CURRENT_TIMESTAMP LAST_ACTIVITY_SECS QUERY_TYPE QUERY_SU
SE_BLOCKED_QUERIES
==================
-This statement lists queries which are blocked in the storage engine APIs
+This script lists queries which are blocked in the storage engine APIs
for more than given number of seconds. The BLOCKED_FOR_SECS denotes how
long the storage engine API is blocked.
@@ -281,7 +283,7 @@ CURRENT_TIMESTAMP NO_OF_PROCESSES BLOCKED_FOR_SECS QUERY_ID
SE_OFFENDING_QUERIES
====================
-This statement lists queries which has a total IO time of any operator
+This script lists queries which has a total IO time of any operator
accessing the storage engine longer than the given number of seconds
The SQL commands to list SE_BLOCKED queries are available at:
@@ -293,6 +295,15 @@ CURRENT_TIMESTAMP TOTAL_IO_TIME_IN_SECS PROCESS_ID QUERY_ID
2017-01-18 14:20:03.604532 1 000, 06390 MXID11000026007212351477539057002000000000206U3333300_107___SQLCI_DML_LAST__ TRAFODION.SELVA.CUSTOMER
2017-01-18 14:20:03.604532 1 001, 06391 MXID11000026007212351477539057002000000000206U3333300_107___SQLCI_DML_LAST__ TRAFODION.SELVA.CUSTOMER
+QUERIES_IN_COMPILE
+==================
+
+This script lists queries that are being compiled for more than the given number of seconds
+
+CURRENT_TIMESTAMP TIME_IN_SECONDS NODE PID QUERY_ID ` SOURCE_TEXT
+-------------------------- -------------------- ----------- ----------- ---------------------------------------------------------------------------- ----------------------
+
+2017-07-15 01:54:14.909780 1860 0 5505 MXID11000005505212366841736965000000000000206U3333300_278___SQLCI_DML_LAST__ "select * from partsupp ;
LIMITING THE OUTPUT TO A NODE
=============================
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/df0be644/core/sqf/export/limited-support-tools/LSO/offender
----------------------------------------------------------------------
diff --git a/core/sqf/export/limited-support-tools/LSO/offender b/core/sqf/export/limited-support-tools/LSO/offender
index ecd9e45..51fc965 100755
--- a/core/sqf/export/limited-support-tools/LSO/offender
+++ b/core/sqf/export/limited-support-tools/LSO/offender
@@ -50,11 +50,12 @@ function usage()
in_client
dead
unmonitored
+ in_compile
-t|--time Optional Default $DEFAULT_TIME
values: 0 < <time> < $MAX_TIME_P1
Time span (in seconds) to search for matching offenders.
- Applies to scripts: dead,inactive,in_sql,in_client,unmonitored
+ Applies to scripts: dead,inactive,in_sql,in_client,unmonitored,in_compile
-m|--memsize Optional Default $DEFAULT_MEMSIZE
Processes exceeding the given memory threshold in MB
@@ -191,6 +192,10 @@ case $SCRIPT in
PREAMBLE="set param ?filter 'QUERIES_IN_CLIENT=$TIME$NODE'; "
QUERY_FILE="$LSO_DIR/queries_in_client.sql"
;;
+ in_compile)
+ PREAMBLE="set param ?filter 'QUERIES_IN_COMPILE=$TIME$NODE'; "
+ QUERY_FILE="$LSO_DIR/queries_in_compile.sql"
+ ;;
se_blocked)
PREAMBLE="set param ?filter 'SE_BLOCKED=$TIME$NODE'; "
QUERY_FILE="$LSO_DIR/se_blocked.sql"
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/df0be644/core/sqf/export/limited-support-tools/LSO/queries_in_compile.sql
----------------------------------------------------------------------
diff --git a/core/sqf/export/limited-support-tools/LSO/queries_in_compile.sql b/core/sqf/export/limited-support-tools/LSO/queries_in_compile.sql
new file mode 100644
index 0000000..e2e8c6f
--- /dev/null
+++ b/core/sqf/export/limited-support-tools/LSO/queries_in_compile.sql
@@ -0,0 +1,42 @@
+--
+-- @@@ START COPYRIGHT @@@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+-- @@@ END COPYRIGHT @@@
+--
+
+-- set param ?filter 'QUERIES_IN_COMPILE=10'; -- 10 seconds
+set param ?lsq ' sqlSrc: ';
+
+select current_timestamp "CURRENT_TIMESTAMP" -- (1) Now
+ ,cast(tokenstr('lastActivity:', variable_info) -- (2) Last activity
+ as NUMERIC(18)) TIME_IN_SECONDS
+ ,cast(queryid_extract(cast(tokenstr('Qid:', variable_info)
+ as varchar(175)CHARACTER SET UTF8), 'CPU') as INTEGER) NODE -- (3) Node
+ ,cast(queryid_extract(cast(tokenstr('Qid:', variable_info)
+ as varchar(175)CHARACTER SET UTF8), 'PIN') as INTEGER) PID -- (4) CPU
+ ,cast(tokenstr('Qid:', variable_info) -- (5) QID
+ as varchar(175)CHARACTER SET UTF8) QUERY_ID
+ ,cast(substr(variable_info, -- (6) SQL Source
+ position(?lsq in variable_info) + char_length(?lsq),
+ char_length(variable_info) -
+ ( position(?lsq in variable_info) + char_length(?lsq) ))
+ as char(256)CHARACTER SET UTF8) SOURCE_TEXT
+from table (statistics(NULL, ?filter))
+order by 2 descending;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/df0be644/core/sql/cli/sqlcli.h
----------------------------------------------------------------------
diff --git a/core/sql/cli/sqlcli.h b/core/sql/cli/sqlcli.h
index 19d2f1b..1487680 100644
--- a/core/sql/cli/sqlcli.h
+++ b/core/sql/cli/sqlcli.h
@@ -979,7 +979,8 @@ enum STATS_SUB_REQ_TYPE {
SQLCLI_STATS_REQ_MEM_HIGH_WM = 12,
SQLCLI_STATS_REQ_MEM_ALLOC = 13,
SQLCLI_STATS_REQ_PFS_USE = 14,
- SQLCLI_STATS_REQ_ACTIVE_QUERIES = 15
+ SQLCLI_STATS_REQ_ACTIVE_QUERIES = 15,
+ SQLCLI_STATS_REQ_QUERIES_IN_COMPILE = 16
};
enum SCRATCH_OVERFLOW_MODE {
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/df0be644/core/sql/executor/ExStats.cpp
----------------------------------------------------------------------
diff --git a/core/sql/executor/ExStats.cpp b/core/sql/executor/ExStats.cpp
index e97e5ef..6b6824f 100644
--- a/core/sql/executor/ExStats.cpp
+++ b/core/sql/executor/ExStats.cpp
@@ -9401,7 +9401,8 @@ NABoolean ExMasterStats::filterForCpuStats(short subReqType,
if (queryId_ == NULL)
return FALSE;
- if (subReqType != SQLCLI_STATS_REQ_UNMONITORED_QUERIES &&
+ if (subReqType != SQLCLI_STATS_REQ_UNMONITORED_QUERIES &&
+ subReqType != SQLCLI_STATS_REQ_QUERIES_IN_COMPILE &&
(collectStatsType_ == (UInt16)ComTdb::ALL_STATS ||
collectStatsType_ == (UInt16)ComTdb::NO_STATS))
return FALSE;
@@ -9509,6 +9510,20 @@ NABoolean ExMasterStats::filterForCpuStats(short subReqType,
retcode = TRUE;
}
}
+ else
+ if (subReqType == SQLCLI_STATS_REQ_QUERIES_IN_COMPILE)
+ {
+ if (stmtState_ != Statement::PROCESS_ENDED_)
+ {
+ if (compStartTime_ != -1 && compEndTime_ == -1)
+ {
+ tsToCompare = compStartTime_;
+ lastActivity_ = (Int32)((currTimestamp-tsToCompare) / (Int64)1000000);
+ if (lastActivity_ >= etTimeInSecs)
+ retcode = TRUE;
+ }
+ }
+ }
return retcode;
}
@@ -9775,6 +9790,15 @@ Lng32 ExStatsTcb::str_parse_stmt_name(char *string, Lng32 len, char *nodeName,
*subReqType = (short)SQLCLI_STATS_REQ_UNMONITORED_QUERIES;
retcode = SQLCLI_STATS_REQ_ET_OFFENDER;
}
+ else
+ if (strncasecmp(ptr, "QUERIES_IN_COMPILE", 21) == 0)
+ {
+ ptr = str_tok(NULL, ',', &internal);
+ etTemp = ptr;
+ etOffender = TRUE;
+ *subReqType = (short)SQLCLI_STATS_REQ_QUERIES_IN_COMPILE;
+ retcode = SQLCLI_STATS_REQ_ET_OFFENDER;
+ }
else
if (strncasecmp(ptr, "PID", 3) == 0)
{
[2/2] incubator-trafodion git commit: Merge PR 1182 [TRAFODION-2688]
Need a way to find queries that are stuck in compilation via RMS
Posted by se...@apache.org.
Merge PR 1182 [TRAFODION-2688] Need a way to find queries that are stuck in compilation via RMS
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/6fb5676c
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/6fb5676c
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/6fb5676c
Branch: refs/heads/master
Commit: 6fb5676cdd41ec4cef4e4770eda649398b450c5a
Parents: a3e19b4 df0be64
Author: selvaganesang <se...@apache.org>
Authored: Tue Jul 18 00:37:16 2017 +0000
Committer: selvaganesang <se...@apache.org>
Committed: Tue Jul 18 00:37:16 2017 +0000
----------------------------------------------------------------------
.../sqf/export/limited-support-tools/LSO/README | 23 ++++++++---
.../export/limited-support-tools/LSO/offender | 7 +++-
.../LSO/queries_in_compile.sql | 42 ++++++++++++++++++++
core/sql/cli/sqlcli.h | 3 +-
core/sql/executor/ExStats.cpp | 26 +++++++++++-
5 files changed, 92 insertions(+), 9 deletions(-)
----------------------------------------------------------------------