Hello,
we have a multi tenant enabled database with three database areas and 255 Tables with indices and some lob fields.
The generated script to create a new tenant takes about 30 seconds to create a new tenant. Why is it so slow? I even don't see any raising in database size.
setSuperUser().
SET-EFFECTIVE-TENANT(cTenantName, "multidb") NO-ERROR.
IF NOT ERROR-STATUS:ERROR THEN
DO:
/* Tenant exists already */
RETURN TRUE.
END.
/* Start a service for the "multidb" database. */
service = NEW DataAdminService("multidb").
/* Instantiate a new ITenant */
tenant = service:NewTenant(cTenantName).
ASSIGN
tenant:Type = "Regular"
tenant:IsDataEnabled = YES
tenant:DefaultDataArea = service:GetArea("DataArea")
tenant:DefaultIndexArea = service:GetArea("IndexArea")
tenant:DefaultLobArea = service:GetArea("LobArea")
tenant:DefaultAllocation = "Immediate"
.
/* Instantiate a new IDomain */
domain = service:NewDomain(cDomainName).
ASSIGN
domain:AuthenticationSystem = service:GetAuthenticationSystem("_oeusertable")
domain:IsEnabled = YES
domain:Description = ""
domain:Comments = ""
domain:AuditingContext = ""
domain:AccessCode = cAccessCode
domain:SystemOptions = ""
domain:RuntimeOptions = ""
.
/* Instantiate a new IUser */
/* myUser = service:NewUser("master").
assign
myUser:Password = cPassword
myUser:Description = ""
myUser:IsSqlOnly = no
myUser:Number = 0
myUser:GivenName = ""
myUser:MiddleInitial = ""
myUser:SurName = ""
myUser:Telephone = ""
myUser:EMail = ""
.
*/
/* Add the new myUser to the domain's Users collection. */
/* domain:Users:Add(myUser).
*/
/* Add the new domain to the tenant's Domains collection. */
tenant:Domains:Add(domain).
/* Create (commit) the tenant in the service */
service:CreateTenant(tenant).
/** Set default allocation back to 'immediate' */
tenant:DefaultAllocation = "Immediate".
/* Update (commit) the Tenant with the DefaultAllocation. */
service:UpdateTenant(tenant).
Thank you
Regards
Peter
Mostly likely, the reason for the slow tenant creation is related to the number of objects to be allocated by adding this new tenant.In a MT database, each tenant owns its unique subset of the multi-tenant objects, and it takes time to create all of them.
In your example with 255 tables, let's say they are all multi-tenant tables and assume there are also multi-tenant 750 indexes and lobs associated with these tables. So there will be around 1000 multi-tenant objects to be created for this new tenant. Each object create requires a database cluster to store the basic information. Assume your db cluster size is 64 and block size is 8k, then the space to be allocated and formatted is 1000 * 64 * 8k = 512 MB. You may not see the database size grow physically, but if you ran dbanalys, you'll see lots of new objects get created internally, and the total database blocks may grow significantly.
Note that similar "slowness" can also happen when adding a multi-tenant table to a database with many tenants. Say there are 100 tenants in the database, and you're adding a multi-tenant table with 9 indexes and lobs. The schema for these 10 new objects need to be materialized for each tenant. So with cluster size of 64 and block size of 8K, you'll need to format 100 * 10 * 64 * 8K = 512 MB space as well.
Dapeng
Mostly likely, the reason for the slow tenant creation is related to the number of objects to be allocated by adding this new tenant.In a MT database, each tenant owns its unique subset of the multi-tenant objects, and it takes time to create all of them.
In your example with 255 tables, let's say they are all multi-tenant tables and assume there are also multi-tenant 750 indexes and lobs associated with these tables. So there will be around 1000 multi-tenant objects to be created for this new tenant. Each object create requires a database cluster to store the basic information. Assume your db cluster size is 64 and block size is 8k, then the space to be allocated and formatted is 1000 * 64 * 8k = 512 MB. You may not see the database size grow physically, but if you ran dbanalys, you'll see lots of new objects get created internally, and the total database blocks may grow significantly.
Note that similar "slowness" can also happen when adding a multi-tenant table to a database with many tenants. Say there are 100 tenants in the database, and you're adding a multi-tenant table with 9 indexes and lobs. The schema for these 10 new objects need to be materialized for each tenant. So with cluster size of 64 and block size of 8K, you'll need to format 100 * 10 * 64 * 8K = 512 MB space as well.
Dapeng
Thank you