5 Replies Latest reply on Feb 13, 2017 10:39 AM by shawkins

    Aggregation over big table consuming memory

    mtawk

      I'm using Teiid 9.0.0 with MSSQL translator

      I have a table containing around 8 million records.

      I'm running a query with sum aggregation and grouped by 5 fields.

      While executing the query, Teiid is consuming almost all the JVM memory (7 out of 8GB)

      When the query finishes execution, the memory do not drop down to its initial state before the query execution. Which is affecting the application performance.

      Is there a way to release the memory used by Teiid for the cases simular to this one?

        • 1. Re: Aggregation over big table consuming memory
          rareddy

          Mark,

           

          In Teiid single should not be monopoly the all the JVM available. It should be in bounds defined by the processor sizes and buffer manager settings. So, something anomaly is at play. You can

           

          1) Do memory profiling and see who is holding on to the memory. That would be helpful insight to debug or point where the culprit is. See VisualVM or JConsole tools.

          2) You can also test with latest version of Teiid and see if the behavior is same, as could have been fixes in buffer manager area. Typically if we found any bugs like these they also go into dot releases like 9.0.1, 9.0.2 etc, but that depends upon the found bugs. You can look through the JIRA history.

           

          Ramesh..

          • 2. Re: Aggregation over big table consuming memory
            mtawk

            After investigation, Teiid is dumping the table in the memory when using LPAD function into the group by.

             

            Here is a simplified query that illustrates the case:

             

                 SELECT sum("StreamCards"."Amount") AS SumAmount ,

                      LPAD(Month("StreamCards"."DateTime"),2,'0') as "MonthNo"

                   FROM "implify"."dbo"."StreamCards" "StreamCards"

                 group by  LPAD(Month("StreamCards"."DateTime"),2,'0')

             

            The group by clause is not sent by Teiid in the executed JDBC query;

            when removing LPAD , group by is sent in the JDBC query.

             

            You find attached the query plan.

            • 3. Re: Aggregation over big table consuming memory
              shawkins

              Mark, there are two things to follow up on here.  The first is that ldap is not pushed down to sql server.  That can be compensated for with an expression, so that needs captured as a jira.

               

              The other issue is to determine if the processing is appropriate when it is not pushed down.  That will largely depend on your memory settings - have you changed anything from the defaults and are you experiences an out of memory error?

              • 4. Re: Aggregation over big table consuming memory
                mtawk

                No i haven't change anything in the default memory settings.

                • 5. Re: Aggregation over big table consuming memory
                  shawkins

                  As long as you are not seeing an out of memory error, it is entirely possible that Teiid may be using that amount of memory - although it may not all be held through hard references.  You'd have to take a heap dump to understand further what is happening.

                   

                  [TEIID-4761] Support lpad/rpad pushdown to sql server - JBoss Issue Tracker was logged to track the pushdown.