GitHub user kkhatua opened a pull request:
https://github.com/apache/drill/pull/1077
DRILL-5068: Create sys.profiles table
(Thanks to Hongze Zhang for the initial work)
Introduced a non-distributed (i.e. the foreman Drillbit executes a single
fragment query) system table to list the completed profiles that are visible to
it in the persistent store. This allows for querying the profiles on the
following common metrics:
queryId
startTime
foreman
fragments
user
queue
planTime
queueTime
executeTime
totalTime
state
query
An additional system table - `sys.profiles_json` has also been introduced.
This only has the following fields:
queryId
json
This allows for more detail metrics to be queried on the profile as a JSON
document. Users are expected to use `sys.profiles` to identify profiles on the
available metrics (such as long executeTime or fragments), and use the queryId
to find the corresponding JSON document representation for further drill down
into the profiles.
Here is what a sample output looks like:
```
[root@kk127 ~]# /opt/drill/apache-drill-1.13.0/bin/sqlline -u
"jdbc:drill:drillbit=`hostname -i`"
...
apache drill 1.13.0-SNAPSHOT
"just drill it"
0: jdbc:drill:drillbit=10.10.100.127> select * from sys.profiles where
startTime < date '2017-12-18' limit 10;
+---------------------------------------+--------------------------+---------------+------------+------------+--------+-----------+------------+--------------+------------+------------+--------------------------------------------------------+
| queryId | startTime |
foreman | fragments | user | queue | planTime | queueTime |
executeTime | totalTime | state | query
|
+---------------------------------------+--------------------------+---------------+------------+------------+--------+-----------+------------+--------------+------------+------------+--------------------------------------------------------+
| 25cdcede-410d-6399-97f9-00ec2048f247 | 2017-12-14 08:06:57.062 |
kk127.qa.lab | 1 | anonymous | - | 35 | 0 |
7718 | 7753 | COMPLETED | select queryId from profiles limit 1
|
| 25cdd1a2-7928-bcf9-a8cd-8e0fa653e2ce | 2017-12-14 07:55:09.551 |
kk127.qa.lab | 1 | anonymous | - | 41 | 0 |
7580 | 7621 | COMPLETED | select queryId from profiles limit 1
|
| 25cdd1b6-3a56-e2b3-a6a1-4d1006ec8fac | 2017-12-14 07:54:49.403 |
kk127.qa.lab | 1 | anonymous | - | 77 | 0 |
7452 | 7529 | COMPLETED | select queryId from profiles limit 1
|
| 25cdd1bd-963f-7a57-6a2d-cf7a3eac3f00 | 2017-12-14 07:54:41.065 |
kk127.qa.lab | 0 | anonymous | - | 0 | 0 | 0
| 0 | FAILED | select profileJson from profiles limit 1
|
| 25cdd1e5-de23-0cfe-5c01-86621b31d8d5 | 2017-12-14 07:54:01.576 |
kk127.qa.lab | 1 | anonymous | - | 1184 | 0 |
10860 | 12044 | COMPLETED | select profileJson from profiles_json
limit 1 |
| 25cdd1e7-7c3a-5a25-0860-a86246bbc9ed | 2017-12-14 07:54:00.186 |
kk127.qa.lab | 1 | anonymous | - | 102 | 0 | 231
| 333 | COMPLETED | use sys
|
| 25cdd1f0-4473-55aa-a5eb-c85194321118 | 2017-12-14 07:53:51.966 |
kk127.qa.lab | 0 | anonymous | - | 0 | 0 | 0
| 0 | FAILED | select profileJson from profiles_json
limit 1 |
| 25cdd442-04cc-65fe-7a25-ac075fa22467 | 2017-12-14 07:43:57.251 |
kk127.qa.lab | 1 | anonymous | - | 40 | 0 |
10553 | 10593 | COMPLETED | select profileJson from profiles_json
limit 1 |
| 25cdd7c8-614d-8bcc-3b89-08df12761a3a | 2017-12-14 07:28:55.359 |
kk127.qa.lab | 1 | anonymous | - | 170 | 0 |
10508 | 10678 | COMPLETED | select count(*) from (select * from
profiles_json) |
| 25cdd7e0-34ea-9040-ab2a-ef8c6a136440 | 2017-12-14 07:28:31.464 |
kk127.qa.lab | 0 | anonymous | - | 0 | 0 | 0
| 0 | FAILED | select count(*) from (pwdselect * from
profiles_json) |
+---------------------------------------+--------------------------+---------------+------------+------------+--------+-----------+------------+--------------+------------+------------+--------------------------------------------------------+
10 rows selected (8.336 seconds)
0: jdbc:drill:drillbit=10.10.100.127> select * from sys.profiles_json where
queryId = '25cdd1e7-7c3a-5a25-0860-a86246bbc9ed';
+---------+------+
| queryId | json |
+---------+------+
| 25cdd1e7-7c3a-5a25-0860-a86246bbc9ed |
{"id":{"part1":2724064141780867621,"part2":603667490114619885},"type":1,"start":1513238040186,"end":1513238040519,"query":"use
sys","foreman":{"address":"kk127.qa.lab","userPort":31010,"controlPort":31011,"dataPort":31012,"version":"1.12.0-SNAPSHOT","state":0},"state":2,"totalFragments":1,"finishedFragments":0,"fragmentProfile":[{"majorFragmentId":0,"minorFragmentProfile":[{"state":3,"minorFragmentId":0,"operatorProfile":[{"inputProfile":[{"records":1,"batches":2,"schemas":1}],"operatorId":0,"operatorType":26,"setupNanos":0,"processNanos":67070036,"peakLocalMemoryAllocated":4530688,"waitNanos":0},{"inputProfile":[{"records":0,"batches":0,"schemas":0}],"operatorId":0,"operatorType":13,"setupNanos":0,"processNanos":0,"peakLocalMemoryAllocated":4530176,"waitNanos":0},{"inputProfile":[{"records":1,"batches":1,"schemas":1}],"operatorId":0,"operatorType":13,"setupNanos":0,"processNanos":15787437,"peakLocalMemoryAllocated":4530176,"metric":[{"met
ricId":0,"longValue":41}],"waitNanos":18796208}],"startTime":1513238040329,"endTime":1513238040482,"memoryUsed":0,"maxMemoryUsed":9060864,"endpoint":{"address":"kk127.qa.lab","userPort":31010,"controlPort":31011,"dataPort":31012,"version":"1.12.0-SNAPSHOT","state":0},"lastUpdate":1513238040487,"lastProgress":1513238040487}]}],"user":"anonymous","optionsJson":"[
]","planEnd":1513238040288,"queueWaitEnd":1513238040288,"totalCost":0.0,"queueName":"-"}
|
+---------+------+
1 row selected (11.667 seconds)
```
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/kkhatua/drill DRILL-5068
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/drill/pull/1077.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1077
----
commit 56f2cad756125923cd5f5e381ff17f5fd4ea5970
Author: Kunal Khatua <kkhatua@...>
Date: 2017-12-20T19:27:34Z
DRILL-5068: Create sys.profiles table
(Thanks to Hongze Zhang for the initial work)
Introduced a non-distributed (i.e. the foreman Drillbit executes a single
fragment query) system table to list the completed profiles that are visible to
it in the persistent store. This allows for querying the profiles on the
following common metrics:
queryId
startTime
foreman
fragments
user
queue
planTime
queueTime
executeTime
totalTime
state
query
An additional system table - `sys.profiles_json` has also been introduced.
This only has the following fields:
queryId
json
This allows for more detail metrics to be queried on the profile as a JSON
document. Users are expected to use `sys.profiles` to identify profiles on the
available metrics (such as long executeTime or fragments), and use the queryId
to find the corresponding JSON document representation for further drill down
into the profiles.
----
---
