[ 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)