20-11-2020 10:35 AM
Hi Exasol
We are building a SaaS solution, where we plan to put each customers data in separate schema. Over time this will hopefulle mount to a lot of schemas.
In order to cover the bases I would love your take on the implications of this approach
I would expect that this approach, where the amount of date per customer is small, have implications on the data distribution rules once we go into production and establish a cluster setup, and I expect to get back to you once we get there.
Solved! Go to Solution.
20-11-2020 01:53 PM
Hi @bakka
There is no limit in regards to the amount of objects in a database since Version 6.2 (I hope the version is correct, might also be 6.1 😉). Objects are schemas, tables, views, functions, scripts. That said: there is no limit in amount of schemas.
But of course there is some overhead in regards to system table / metadata queries with an increasing amount of objects. I am not aware that this ever had a bigger impact on query performance.
To minimize the overhead on system table / metadata queries it is highly recommended to use selective filters for those. Some examples:
Our engine will be able to apply such filters deep in our engine to get optimal performance
21-11-2020 05:11 PM
At BlueYonder we have schema numbers of the order of upto 1k per database and a factor of 2 of corresponding roles, and 20k tables.
In that region we never faced any significant issues that could be traced to the pure overhead of schema/object management (compared to user behavior and his data), but that's obviously quite on the low end compared to the numbers you were suggesting.
In general our experiences align with Matze's statements.
20-11-2020 01:53 PM
Hi @bakka
There is no limit in regards to the amount of objects in a database since Version 6.2 (I hope the version is correct, might also be 6.1 😉). Objects are schemas, tables, views, functions, scripts. That said: there is no limit in amount of schemas.
But of course there is some overhead in regards to system table / metadata queries with an increasing amount of objects. I am not aware that this ever had a bigger impact on query performance.
To minimize the overhead on system table / metadata queries it is highly recommended to use selective filters for those. Some examples:
Our engine will be able to apply such filters deep in our engine to get optimal performance
20-11-2020 03:55 PM
20-11-2020 02:31 PM - edited 20-11-2020 03:28 PM
not true for all cases 😉
/* snapshot execution */
SELECT
edu.USER_NAME, edrp.GRANTED_ROLE , EDRp.ADMIN_OPTION
FROM
sys.EXA_DBA_USERS edu
INNER JOIN sys.EXA_DBA_ROLE_PRIVS edrp
ON
edu.USER_NAME = edrp.GRANTEE
INNER JOIN SYS.EXA_DBA_OBJ_PRIVS edop
ON edop.GRANTEE = edrp.GRANTED_ROLE
WHERE
edu.user_name = ?;
Duration: 278 seconds.
So stay way from too many roles
20-11-2020 01:20 PM
Let me nudge @MichaelKoenigBY , if they could add their Ideas to this...
20-11-2020 11:22 AM
There was also a user group just recently regarding multi tenancy and how Blue Yonder implemented this on Azure
20-11-2020 11:17 AM
Hi,
we did not yet run into problems however we have not that much different schemas
This is not the a problem in any way however we automatically generate roles for every object and schema to be able to restrict user access to a minimum.
The roles do have an impact on performance
Table | Cardinality | Time for count |
---|---|---|
EXA_DBA_ROLES | 100359 | 91 seconds |
EXA_DBA_USERS | 1916 | 58 seconds |
EXA_DBA_ROLE_PRIVS | 38647 | 47 seconds |
EXA_DBA_OBJ_PRIVS | 195595 | 121 seconds |
This can really be a pain in the ass when doing automation.
This can also have an impact on the time it takes for users to log on for example in Power BI.
PS: this is a 18 node on-prem cluster with 24 TB of memory
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In