1 2 Previous Next 16 Replies Latest reply on Oct 14, 2016 3:21 PM by Ivan Chan

    Slowness while deploying VDB.

    Kulbhushan Chaskar Master

      We are deploying VDB via admin api to connect multiple databases like mysql, sql-server, oracle using non-xa data-sources. While deploying VDB teiid server takes long time to deploy and activate VDB.

       

      Below are the statistics of VDB deployment for different databases.

       

      DatabasesTime(Minute)
      mysql 0.5-1 Minutes
      sql-server3 - 4 Minutes
      Oracle1.5 - 2 Minutes


      Is this expected time to deploy VDB with above mentioned databases? Can we improve the VDB deployment time interval?

       

      Thanks,

      Kulbhushan Chaskar.

        • 1. Re: Slowness while deploying VDB.
          Ramesh Reddy Master

          That may be due the time it takes to import metadata from the source database in each case. You can trying using importer properties on your model to restrict what you are trying to import. Especially try setting the "schemaPattern" in importer properties JDBC Translators · Teiid Documentation

           

          Oracle translator in absence of  this property tries to import all the visible schema from the source database. In MySQL and SQL-Server the visibility of other schemas in the database to the importing user is much more limited by default.

           

          Ramesh..

          • 2. Re: Slowness while deploying VDB.
            Ivan Chan Apprentice

            I am facing the similar issue in Oracle.  I am joining 2 schema together from same oracle DB using Teiid.  And it took me 2 minutes to deploy VDB.  I have attached my import properties and DDL.  Is there anyway to improve the performance on Oracle?

             

            And I am using the following import properties:

            Adding Model - SCHEMA = [SUGARCRM], MODELNAME = [SugarCRMSubDS], CONNECTORNAME = [439793206], CONNECTIONNAME = [439793206]

            TEIID IMPORT PROPERTY [import.userFullSchemaName, false]

            TEIID IMPORT PROPERTY [import.trimColumnNames, true]

            TEIID IMPORT PROPERTY [import.schemaPattern, SUGARCRM]

            Adding Model - SCHEMA = [FOODMART], MODELNAME = [FoodmartSubDS], CONNECTORNAME = [-813757334], CONNECTIONNAME = [-813757334]

            TEIID IMPORT PROPERTY [import.userFullSchemaName, false]

            TEIID IMPORT PROPERTY [import.trimColumnNames, true]

            TEIID IMPORT PROPERTY [import.schemaPattern, FOODMART]

             

            Added schema as attachment.

            • 3. Re: Slowness while deploying VDB.
              Ramesh Reddy Master

              Ivan,

               

              You have these tables defined inside your VDB or you are importing these during the deployment from Oracle data source? Read my comments above, try inline DDL to see if that helps.

               

              Ramesh..

              • 4. Re: Slowness while deploying VDB.
                Marco Ardito Master

                Hi,

                 

                in this other thread (Re: Slowness while restarting VDB. ) I recently updated my post, adding details and timings about loading my VDB (through web console though) to give you some better reference, have you seen them?

                 

                have you tried to deploy the same VDB through web interface? I usually deploy by web console or by jboss-cli.sh and timings are similar...

                 

                hth.

                • 5. Re: Slowness while deploying VDB.
                  Ivan Chan Apprentice

                  Hi Ramesh,

                   

                  We are importing tables during the deployment from oracle DS.  It took 2 minutes for oracle, but only 2 second for postgres for identical tables and data.  InlineDDL is not an option for us at this moment.  What else can we do to improve teiid performance on oracle?

                  • 6. Re: Slowness while deploying VDB.
                    Ramesh Reddy Master

                    I advise you to set

                     

                    <property name="importer.tableTypes" value="TABLE,VIEW"/>
                    <property name="importer.schemaPattern" value="my-schema"/>

                     

                    inside your vdb's oracle model element to make sure you are not retrieving the whole database metadata down.

                     

                    Ramesh..

                    • 7. Re: Slowness while deploying VDB.
                      Steven Hawkins Master

                      Is it possible to use debug logging to see what step of the import is taking so long?  Generally if you have set the schema pattern set, that is sufficient.  Just to double check, the message above says import.schemaPattern - the actual import property is importer.schemaPattern

                      • 8. Re: Slowness while deploying VDB.
                        Ivan Chan Apprentice

                        It is not due to debug logging and I was using "importer.schemaPattern".   Sorry for the confusion.

                         

                        And I have tried to simplify my vdb by only selecting "table" type and  containing only 1 model (schema) in VDB which is pointed to oracle schema "SUGARCRM".   It still took 79823ms.

                         

                        <entry key="importer.tableTypes" value="TABLE"/>

                        <entry key="importer.userFullSchemaName" value="false"/>

                        <entry key="importer.trimColumnNames" value="true"/>

                        <entry key="importer.schemaPattern" value="SUGARCRM"/>

                         

                        If I put 2 models in VDB which is pointed oracle schema "SUGARCRM" and oracle schema "FOODMART".  It took total  112705ms to deploy VDB.  Same code and same db schemas, it only took 2000ms on postgres.

                         

                        Model 1:

                        <entry key="importer.tableTypes" value="TABLE"/>

                        <entry key="importer.userFullSchemaName" value="false"/>

                        <entry key="importer.trimColumnNames" value="true"/>

                        <entry key="importer.schemaPattern" value="SUGARCRM"/>

                        Model 2:

                        <entry key="importer.tableTypes" value="TABLE"/>

                        <entry key="importer.userFullSchemaName" value="false"/>

                        <entry key="importer.trimColumnNames" value="true"/>

                        <entry key="importer.schemaPattern" value="FOODMART"/>

                         

                         

                         

                        • 9. Re: Slowness while deploying VDB.
                          Steven Hawkins Master

                          Is it possible to turn on debug logging at least for org.teiid.CONNECTOR to see what import step is taking the longest?

                          • 10. Re: Slowness while deploying VDB.
                            Ivan Chan Apprentice

                            Hi Steven,

                             

                            Thank you for looking.  Here is the debug log from org.teiid.CONNECTOR.  It took total 90 seconds for importing index info for 2 schemas and 25 seconds for importing foreign keys.

                             

                            2016-10-13 14:09:44,416 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing tables

                            2016-10-13 14:09:44,508 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing columns

                            2016-10-13 14:09:46,011 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing primary keys

                            2016-10-13 14:09:52,421 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing index info

                            2016-10-13 14:10:49,110 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing foreign keys

                            2016-10-13 14:11:09,816 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing tables

                            2016-10-13 14:11:09,881 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing columns

                            2016-10-13 14:11:10,795 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing primary keys

                            2016-10-13 14:11:12,673 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing index info

                            2016-10-13 14:11:41,599 DEBUG CONNECTOR,http-apr-8080-exec-5:67 - JDBCMetadataProcessor - Importing foreign keys

                            2016-10-13 14:11:45,566 DEBUG VirtualDataSourceQueryService,http-apr-8080-exec-5:247 - Deployment time for virtual data source, -2146727971: 121460ms

                            • 11. Re: Slowness while deploying VDB.
                              Steven Hawkins Master

                              What oracle driver/server version are you using?  It seems like there may be some issues with Oracle's implementation of those DatabaseMetaData.getImportedKeys and DatabaseMetadata.getIndexInfo that is causing the slow down.

                              • 12. Re: Slowness while deploying VDB.
                                Ivan Chan Apprentice

                                I am using progress oracle driver 5.13 to connect to oracle 11g.

                                • 13. Re: Slowness while deploying VDB.
                                  Ramesh Reddy Master

                                  Try Oracle native drive to see if there is any difference?

                                  • 14. Re: Slowness while deploying VDB.
                                    Ivan Chan Apprentice

                                    Will do.  I will let you guys know the result soon.  Thanks for your suggestion.

                                    1 2 Previous Next