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. ---- ---