Slowness while restarting VDB.

[kulbhushanc]

Hi,

I am trying to restart already deployed VDB, so that I can fetch updated schema of the respective database from the same VDB, everything works fine for me except teiid sever taking too much time to restart the VDB.

I have tried another option like updeploy and deploy again the same VDB but still I am getting same result.

Some obeservations:-

1.     For mysql database it takes lesser time as compare to sql-server database, but still time is considerable, it varies from 12 sec to 17 sec.

2.     As the number of VDBs on teiid server increases time takes to deploy and redeploy or restart the VDB gradually increases.

Thanks,

Kulbhushan Chaskar.

[rareddy]

you comment seems to indicate it was fast before, and it is slow in the version you are using. However, the VDB load time depends upon the

• size of your database
• location of the database (same machine, remote)
• import properties defined for each model in every vdb where you are updating metadata
• number of vdbs

BTW, you do not need to restart the server to reload metadata, you can use Admin API/CLI to reload a particular VDB's model. If want server needs to load fast then provide the metadata inline using DDL, that should be fast.

[kulbhushanc]

Thanks Ramesh,

Can you please tell me

How to use Admin API/CLI to reload a particular VDB's model? and what do you mean by provide the metadata inline using DDL, that should be fast?

Thanks,

Kulbhushan Chaskar.

[rareddy]

You can issue CLI command as

{code}

/subsystem=teiid:restart-vdb(vdb-name=foo, vdb-version=1, model-names=bar);

{code}

the same could be done programtically using the Admin API. Look for class Admin.java.

what do you mean by provide the metadata inline using DDL, that should be fast?

If you start defining the VDB with DDL inside to begin with, instead of getting metadata at deploy time then that will improve the time. Obviously that will not be updated automatically when server restarts or VDB redeploys. In that situation, if you know the changes to the source, then you manually line those changes in the VDB and re-deploy. There is more help on the way for these kind of usecases in TEIID-2578 coming in Teiid 9.2 version.

Ramesh..

[kulbhushanc]

Thanks Ramesh for the reply

     I am using teiid Admin api to restart, deploy and redeploy VDB, Admin api itself is taking too much time for these operations(restart,deploy and redeploy VDB) .

So I am curios about is there any way to carry out these operations in fraction of seconds? via admin api only. I am not looking for any manual approach.

     Also can you please tell me how to define VDB with DDL inside in it?

Kulbhushan...

[rareddy]

see Metadata Repositories · Teiid Documentation

[kulbhushanc]

Thank you very much Ramesh,

You mentioned in one of above reply that VDB load time also depends upon the number of VDBs.

What it indicates, the total number of VDBs deployed on teiid Server? Or VDBs is imported to the another VDB Using <import-vdb ...> tag?

And my another concern is:-

How many VDBs we can deploy on server?

[m.ardito]

Hi,

I think that having a look at your vdb structure (if it is dynamic, the xml), and the size of the exposed resources, could also help to spot some possible slowing factors...

I never noticed particular slowness loading or reloading vdb based only on jdbc datasources... I have a vdb that loads something like 2 different MSSQL database on different hosts, and 18 mysql database on a single host, plus one imported vdb that loads an xls source: this vdb currently exposes over 1700 tables, and over 30000 columns (soon this will increase), all with native metadata, and this loads in just a few seconds, to get an idea.

[edit]

I recenlty added some other source to the VDB, now it has

- 2 different MSSQL database on different hosts

- 18 mysql database on a single host,

- 2 imported vdb that load an xls source each

- 1 imported vdb that loads a google spreadsheet

and I tracked times in the server.log, this time: they are not fast as I remembered, still it's not an issue for us

2016-10-06 14:22:13,395 INFO  [org.jboss.as.repository] (XNIO-6 task-1)  WFLYDR0001: Content added at location...

2016-10-06 14:22:37,861 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (Worker30)  TEIID40003 VDB full.1 is set to ACTIVE

so, it's about 24 seconds total... but I also report here single loading times:

2016-10-06 14:22:13,395 INFO  [org.jboss.as.repository] (XNIO-6 task-1)  WFLYDR0001: Content added at location ...
2016-10-06 14:22:13,406 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-5)  TEIID40120 VDB full.1 will be removed from the repository
2016-10-06 14:22:13,411 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-5)  TEIID40119 VDB full.1 removed from the repository
2016-10-06 14:22:13,415 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50026 VDB "full.1[mysql01..." undeployed.
2016-10-06 14:22:13,454 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-5)  TEIID40120 VDB full.1 will be removed from the repository
2016-10-06 14:22:13,467 INFO  [org.jboss.as.server.deployment] (MSC service thread 1-1)  WFLYSRV0028: Stopped deployment full-vdb.xml (runtime-name: full-vdb.xml) in 65ms
2016-10-06 14:22:13,472 INFO  [org.jboss.as.server.deployment] (MSC service thread 1-4)  WFLYSRV0027: Starting deployment of "full-vdb.xml" (runtime-name: "full-vdb.xml")
2016-10-06 14:22:13,511 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-5)  TEIID40118 VDB full.1 added to the repository
2016-10-06 14:22:13,515 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql01" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,519 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql02" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,524 INFO  [org.teiid.CONNECTOR] (Worker30_async-teiid-threads58)  MySQL5ExecutionFactory Commit=true;DatabaseProductName=MySQL;...
2016-10-06 14:22:13,528 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql03" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,540 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql04" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,545 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql05" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,558 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql06" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,566 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql07" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,573 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql08" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,582 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql09" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,605 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql10" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,614 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql11" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,619 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql12" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,637 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql13" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,643 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql14" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,654 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql15" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,658 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql16" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,667 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql17" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,686 INFO  [org.teiid.RUNTIME] (Worker29_async-teiid-threads62)  TEIID50030 VDB full.1 model "mysql05" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:13,680 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mysql18" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,697 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mssql1" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,703 INFO  [org.teiid.RUNTIME] (MSC service thread 1-5)  TEIID50029 VDB full.1 model "mssql2" metadata is currently being loaded. Start Time: 06/10/16 14.22
2016-10-06 14:22:13,719 INFO  [org.teiid.RUNTIME] (Worker25_async-teiid-threads59)  TEIID50030 VDB full.1 model "mysql02" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:13,732 INFO  [org.jboss.as.server] (XNIO-6 task-1)  WFLYSRV0016: Replaced deployment "full-vdb.xml" with deployment "full-vdb.xml"
2016-10-06 14:22:13,738 INFO  [org.jboss.as.repository] (XNIO-6 task-1)  WFLYDR0002: Content removed from location ...
2016-10-06 14:22:13,762 INFO  [org.teiid.RUNTIME] (Worker29)  TEIID50030 VDB full.1 model "mysql11" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:13,763 INFO  [org.teiid.RUNTIME] (Worker27_async-teiid-threads67)  TEIID50030 VDB full.1 model "mysql10" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:13,786 INFO  [org.teiid.RUNTIME] (Worker24_async-teiid-threads64)  TEIID50030 VDB full.1 model "mysql07" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:13,799 INFO  [org.teiid.RUNTIME] (Worker30_async-teiid-threads58)  TEIID50030 VDB full.1 model "mysql01" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:13,869 INFO  [org.teiid.RUNTIME] (Worker29)  TEIID50030 VDB full.1 model "mysql13" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:13,898 INFO  [org.teiid.RUNTIME] (Worker33_async-teiid-threads65)  TEIID50030 VDB full.1 model "mysql08" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:13,995 INFO  [org.teiid.RUNTIME] (Worker30)  TEIID50030 VDB full.1 model "mysql16" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:14,004 INFO  [org.teiid.CONNECTOR] (Worker30)  SQLServerExecutionFactory Commit=true;DatabaseProductName=Microsoft SQL Server;...
2016-10-06 14:22:14,108 INFO  [org.teiid.RUNTIME] (Worker31_async-teiid-threads61)  TEIID50030 VDB full.1 model "mysql04" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:14,197 INFO  [org.teiid.RUNTIME] (Worker29)  TEIID50030 VDB full.1 model "mysql17" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:14,199 INFO  [org.teiid.RUNTIME] (Worker27)  TEIID50030 VDB full.1 model "mysql14" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:14,222 INFO  [org.teiid.RUNTIME] (Worker32_async-teiid-threads66)  TEIID50030 VDB full.1 model "mysql09" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:14,270 INFO  [org.teiid.RUNTIME] (Worker25)  TEIID50030 VDB full.1 model "mysql12" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:14,295 INFO  [org.teiid.RUNTIME] (Worker24)  TEIID50030 VDB full.1 model "mysql15" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:14,320 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker887_QueryProcessorQueue20165) ivjCArmVz/VV TEIID30013 Loading materialized view table #MAT_LISTA_AZIENDE_EEGAS_MAT.LISTAMAT
2016-10-06 14:22:14,671 INFO  [org.teiid.RUNTIME] (Worker28_async-teiid-threads63)  TEIID50030 VDB full.1 model "mysql06" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:14,713 INFO  [org.teiid.RUNTIME] (Worker33)  TEIID50030 VDB full.1 model "mysql18" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:15,209 INFO  [org.teiid.PROCESSOR.MATVIEWS] (Worker887_QueryProcessorQueue20169) ivjCArmVz/VV TEIID30014 Loaded materialized view table #MAT_LISTA_AZIENDE_EEGAS_MAT.LISTAMAT with row count 557.
2016-10-06 14:22:15,665 INFO  [org.teiid.RUNTIME] (Worker26_async-teiid-threads60)  TEIID50030 VDB full.1 model "mysql03" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:18,141 INFO  [org.teiid.RUNTIME] (Worker31)  TEIID50030 VDB full.1 model "mssql2" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:37,852 INFO  [org.teiid.RUNTIME] (Worker30)  TEIID50030 VDB full.1 model "mssql1" metadata loaded. End Time: 06/10/16 14.22
2016-10-06 14:22:37,861 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (Worker30)  TEIID40003 VDB full.1 is set to ACTIVE

so, all 18 mysql models load in less than 2 seconds, while 4.5 seconds are needed for the one mssql host, and 24 for the other mssql host...

of course imported models are already active, so not re-loading here...

about sources:

- faster mssql has ~140 tables, 2100 columns

- slower mssql has ~1000 tables, 18000 columns

- on average, mysql sources have ~30 tables, 500 columns

[/edit]

Loading web remote resources like REST service from third parties (liek google spreadsheets, or other) usually load slower than that, even with just a bunch of resources exposed, but I always use materialized views for that kind of data, to speed up client access, and I guess that initial loading takes that into account, too, and I'm not surprised it's quite slower... (it usually needs oauth2 token refresh, or sometimes views are based on procedures that iterate several web REST call to build up a (then materialized) view...

Obviously it could also depend on the teiid server performance (physical host or vm), and also the jvm parameters/limits used by the eap/wildfly instance, so if you're not sure, it could also help to get some configuration info, maybe.

Finally, if nothing can speed up that loading, you could also leverage vdb versioning to the rescue: you could load the new schema in a new vdb version, then load it, and after it loads, make client reconnect to get the new setup/schema (you can force reconnection or allow new connections only on the new version). This needs some planning and connection usage configuration, but it could be another Teiid feature to get help from...