12 Replies Latest reply on Apr 10, 2013 2:41 PM by shawkins

    TEIID making multiple calls to DB when invoking VDB

    aabrham

      I'm running TEIID 8.1.3 and have VDB using Postgres native driver, where our PG version is 9.0.4.  When the VDB is invoked, it's making mutliple calls to the db for a simple query ... ex: Select * from table.  I see it show up 1700 times.  I'm not sure why this happens.    I'm relatively new to TEIID.

       

      The VDB has sometimes a cardinality set with a ttl set for long time in cache ... ex:

       

      OPTIONS (CARDINALITY 100000, MATERIALIZED 'true')

                      AS

                          /*+ cache(ttl:3600000) */

       

       

      Is there something I'm missing as to why when invoking the VDB call, that it makes 1700 calls to the db of the same query?  Any help here would be appreciated.

      Thanks...

        • 1. Re: TEIID making multiple calls to DB when invoking VDB
          rareddy

          Anson,

           

          Teiid 8.1.3? There is no such released version from Teiid.

           

          You can take look at query plan. Check the documentation on how to get query plan. Also, how did you figured out it is called 1700 times? Did you turn on command logging?

           

          Ramesh..

          • 2. Re: TEIID making multiple calls to DB when invoking VDB
            aabrham

            I'm sorry I meant 8.1.0.  The 3 got in there somehow.

            I found the # of times, by looking @ the postgresql logging. 

            • 3. Re: TEIID making multiple calls to DB when invoking VDB
              rareddy

              Turn on command logging that should also print out each time the query is submitted to the source.

              • 4. Re: TEIID making multiple calls to DB when invoking VDB
                shawkins

                Can you give the full ddl for your view and a sample user query?

                • 5. Re: TEIID making multiple calls to DB when invoking VDB
                  aabrham

                  Here's a sample VDB:

                   

                  <model name="External" visible="true" type="VIRTUAL">

                          <metadata type = "DDL"><![CDATA[

                   

                   

                                CREATE VIEW v_states OPTIONS OPTIONS (CARDINALITY 50000000) AS

                                  /*+ cache(ttl:3600000) */

                                      SELECT

                                      id AS state_id, name AS state_name, "short" AS state_short_code

                                      FROM Main.us_states;

                   

                   

                          ]]>

                          </metadata>

                      </model>

                   

                   

                   

                   

                  and I how invoke it:

                   

                   

                  import groovy.sql.Sql

                   

                   

                  def db = [url:'jdbc:teiid:TestTeid@mm://192.169.1.1:31000', user:'RedHat', password:'Jboss', driver:'org.teiid.jdbc.TeiidDriver']

                  def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)

                   

                   

                  sql.withTransaction {

                     sql.execute 'select * from External.v_states;'

                  }

                  • 6. Re: TEIID making multiple calls to DB when invoking VDB
                    shawkins

                    Your options should also have MATERIALIZED 'true'.  You should check the SYSADMIN.MatViews table for information related to mat views.  Also if you set logging to a more detailed level you should see INFO logs about the matview loading process.

                     

                    Steve

                    • 7. Re: TEIID making multiple calls to DB when invoking VDB
                      aabrham

                      Sorry, I didn't copy all of the VDB DDL, but MATERIALIZED 'true' is there.  I just invoke a call to SYSADMIN.MatViews?

                      I'm relatively new to TEIID, so how would i set the logging to more detail?

                      • 8. Re: TEIID making multiple calls to DB when invoking VDB
                        rareddy

                        By editing the standalone-teiid.xml file. You should see a subsection for logging, just change log level for "org.teiid"

                         

                        <logger category="org.teiid">
                            <level name="ERROR"/>
                        </logger>
                        

                         

                        All the logging contexts are defined here, if you need to only open up certain modules. https://docs.jboss.org/author/display/TEIID/Logging

                         

                        Ramesh..

                        • 9. Re: TEIID making multiple calls to DB when invoking VDB
                          aabrham

                          So i looked @ command log and saw only 1 entry:

                           

                          13:46:47,331 DEBUG [org.teiid.COMMAND_LOG] (Worker478_QueryProcessorQueue3454748)       START DATA SRC COMMAND: startTime=2013-04-10 13:46:47.33        requestID=3oDIh32560yF.2072     sourceCommandID=6       txID=null       modelName=External  translatorName=postgresql-conf  sessionID=3oDIh32560yF  principal=RedHat@teiid-security   sql=SELECT id AS state_id, name AS state_name, "short" AS state_short_code FROM Main.us_states

                           

                          13:46:47,425 DEBUG [org.teiid.COMMAND_LOG] (Worker478_QueryProcessorQueue3454752)       END SRC COMMAND:        endTime=2013-04-10 13:46:47.425 requestID=3oDIh32560yF.2072     sourceCommandID=6       txID=null       modelName=External  translatorName=postgresql-conf  sessionID=3oDIh32560yF  principal=RedHat@teiid-security   finalRowCount=50

                           

                           

                          But when I looked @ the postgres logs I saw a 1700 calls of the same query SELECT id AS state_id, name AS state_name, "short" AS state_short_code FROM Main.us_states to the database.  with different pids.  1700 calls not @ once, but consecutively.

                          • 10. Re: TEIID making multiple calls to DB when invoking VDB
                            shawkins

                            > But when I looked @ the postgres logs I saw a 1700 calls of the same query SELECT id AS state_id, name AS state_name, "short" AS state_short_code FROM Main.us_states to the database.  with different pids.  1700 calls not @ once, but consecutively.

                             

                            As far as Teiid is concerned we're only making 1 call if there is 1 entry.  Is there something odd about your pg datasource configuration that would issue multiple calls?  Do different pids correspond to different connections - and are all of the connections coming from the app server?

                             

                            Do you see an entry for your materialized view in SYSADMIN.MatViews?

                             

                            Steve

                            • 11. Re: TEIID making multiple calls to DB when invoking VDB
                              aabrham

                              PID in postgres = Connection.

                              All the calls are coming from the app servers (based on IP address), and occurs when I initiate the call to VDB.

                               

                              Sysadmin.matviews shows

                              Valid = True

                              LoadState=Loaded

                              Updated is basically about 2 minutes ago, when I queried it.

                              and Cardinality= 2707

                               

                               

                              when you mean pg datasource config, do you mean the database or the jdbc driver?

                              • 12. Re: TEIID making multiple calls to DB when invoking VDB
                                shawkins

                                At this point what you have shown does not seem related to mat views or even to Teiid.

                                 

                                > when you mean pg datasource config, do you mean the database or the jdbc driver?

                                 

                                Mostly the jdbc driver.  You should try the source query through the datasource directly and not via Teiid to see if the pattern is the same on the pg side - we would expect that it would be as Teiid is just like any other consumer of Connections from a connection pool.

                                 

                                Steve