You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "ASF GitHub Bot (JIRA)" <ji...@apache.org> on 2017/12/21 07:44:00 UTC
[jira] [Commented] (DRILL-5068) Create a system table for completed
profiles - sys.profiles
[ https://issues.apache.org/jira/browse/DRILL-5068?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16299666#comment-16299666 ]
ASF GitHub Bot commented on DRILL-5068:
---------------------------------------
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":[{"metricId":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 <kk...@...>
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.
----
> Create a system table for completed profiles - sys.profiles
> -----------------------------------------------------------
>
> Key: DRILL-5068
> URL: https://issues.apache.org/jira/browse/DRILL-5068
> Project: Apache Drill
> Issue Type: Sub-task
> Components: Storage - Information Schema
> Affects Versions: 1.8.0
> Environment: Fedora 25
> OpenJDK 8
> Firefox 50.0
> Reporter: Hongze Zhang
> Assignee: Hongze Zhang
> Fix For: 1.13.0
>
>
> Hi,
> Currently the profile page on UI is still not detailed enough for some complicated uses (eg. show all failed queries during these three days), we can only access latest 100 query profiles on this page.
> We may sometimes need a specific system table for querying completed profiles.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)