[
https://issues.apache.org/jira/browse/TRAFODION-27?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14622896#comment-14622896
]
Selvaganesan commented on TRAFODION-27:
---------------------------------------
obey trafodion27_setup.sql in trafci or sqlci to create the needed tables
obey trafodion27_qry.sql to reproduce the problem.
It is observed that the fixup takes around 2 seconds when the size of data
attribute in t_event table is around 100k. The select statement in this script
is deallocated and hence it is difficult the capture the memory allocated while
the query is being executed. If the statement is changed to prepare and
execute, then it is easier to capture the memory allocated.
sqlci, when started needs around 1.4 gb of virtual memory. pmap -x <pid> shows
Kbytes RSS Dirty
total kB 1437160 268908 216752
When this query is prepared and executed, the same sqlci shows
total kB 5081920 870624 8145808
A large amount of memory is allocated by probe cache operators at the time of
fixup. This large allocation is taking more time to execute the query. The fix
is to limit this allocation. Currently, you can workaround this issue by
setting a cqd gen_probe_cache_num_inner to 400 or less. The default value is 0
meaning the optimizer decides the number of tuples to be cached. It chooses
around 18K which translates into 1.8 gb of memory with tuple size of 100k.
I will be changing the generator to consider the tuple size also while arriving
at the number of tuples to be cached.
in the earlier incarnation of Trafodion, the fixup was done as part of the
compilation of the query when the query plan is picked up from the query cache
in certain cases. Fixup is not needed as part of the compilation in Trafodion,
because the query cache is invalidated using RMS if there a change in the DDL
definition of the table in the cached query. This happens at real time and
hence it is not necessary to detect this condition at the time of prepare.
Hence, I will be removing this code.
> Time consuming compilation for queries of BLOB with JOINs
> ----------------------------------------------------------
>
> Key: TRAFODION-27
> URL: https://issues.apache.org/jira/browse/TRAFODION-27
> Project: Apache Trafodion
> Issue Type: Bug
> Environment: Trafodion 1.2, HDP 2.2, CentOS 6
> Reporter: Haifeng Li
> Priority: Minor
> Attachments: trafodion27_qry.sql, trafodion27_setup.sql
>
>
> It takes 3 ~ 5 seconds to compile a query that returns a BLOB field by
> joining two tables. If a query is cached and subsequent queries are 'similar'
> to
> the initial query, those subsequence compilation times will usually be
> much faster. However, sometimes we observe that subsequent queries with
> literal difference still take long time in sqlci.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
