[
https://issues.apache.org/jira/browse/TRAFODION-1824?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15145958#comment-15145958
]
Narendra Goyal commented on TRAFODION-1824:
-------------------------------------------
Looked at it after setting up tracing of the SQL processes (master executor
'sqlci' and the ESP processes) for a query that would use 96 ESPs.
h2. Scenario:
*Execute via sqlci
** prepare a query with a name, say 'qry'
** first execution of the query ('execute qry;') : 16 seconds (say: *TIE*)
*** this is when the 96 ESPs are spawned, they initialize, the ESPs receive
their plan fragment from the master(sqlci, in this case) and then the plan
fragment is executed, and the results returned back to the master
** next execution of the query ('execute qry;'): 9 seconds (say *TNE*)
*** here, the master simply requests the ESPs to execute the plan that they had
previously received once again
** another execution of the query ('execute qry;'): ~9 seconds
** try to 'fixup' the query again: prepare another query with a name 'qry',
execute it and then prepare the original query again and then execute it: 11
seconds (say *TRE*)
*** In the above case, the query fixup is done again (where the master executor
sends the execution plan to the 96 ESPs again)
** yet another execution in sqlci ('execute qry;'): ~9 seconds
*** again, no plan info is re-transmitted to the ESPs
h2. ESP initialization times
The trace files were used to obtain this info.
* ESP process spawn: We do not have any issue here. All the ESPs started up
within 50 ms of the issue of the 'execute' command
* Process initialization: This is where most of the time is spent. It can be
further divided into two parts:
** process creating a JVM: ~0.5 seconds
** process creating its first connection to the HBase environment: varies:~3
seconds
The above info was obtained via the trace files.
h2. Fixup time
This is the time that it takes for the master executor to download the
execution plan to the 96 ESPs. This can be obtained by subtracting TNE(9
seconds) from TRE (11 seconds_) = 2 seconds. Please note that the fixup time
will be a function of the number of ESPs, query plan complexity and
environmental factors (hardware, loaded on the system, etc.). In this case, the
cluster was dedicated and no other query was running.
h2. Summary
Thus we find that in the test case, the first execution of the query took ~6
seonds for the process initialization (~4 seonds) and plan fixup (~2 seconds).
> Slow startup of queries with many ESPs
> --------------------------------------
>
> Key: TRAFODION-1824
> URL: https://issues.apache.org/jira/browse/TRAFODION-1824
> Project: Apache Trafodion
> Issue Type: Improvement
> Components: sql-exe
> Affects Versions: 1.3-incubating
> Reporter: Atanu Mishra
>
> When first executing a query with many ESPs, there is a high startup cost,
> which is substantially reduced in later executions.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
