Is there a limit on number of schemas in an Exasol database?

Contributor

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

  • Is there a limit - fixed or practical - on the number of schemas an instance can handle (10.000, 100.000, 1.000.000)?
  • Is there an overhead per schema, that affect the resources needed in an instance?
  • Is there a performance penalty due to the system tables growing large from a high amount of schemas, tables, columns, etc.?
  • Other things to take into account?

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.

1 ACCEPTED SOLUTION

Team Exasol
Team Exasol

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:

  1. Schema name filter for exa_schemas
  2. schema and table name filter for exa_dba_tables 
  3. schema and view filter for exa_dba_views
  4. ...

Our engine will be able to apply such filters deep in our engine to get optimal performance

View solution in original post

7 REPLIES 7

Padawan

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.

 

Team Exasol
Team Exasol

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:

  1. Schema name filter for exa_schemas
  2. schema and table name filter for exa_dba_tables 
  3. schema and view filter for exa_dba_views
  4. ...

Our engine will be able to apply such filters deep in our engine to get optimal performance

View solution in original post

Contributor
Thanks, then we will proceed down this path with easy at mind.

Xpert

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

Community Manager
Community Manager

Let me nudge @MichaelKoenigBY , if they could add their Ideas to this...

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

Xpert

There was also a user group just recently regarding multi tenancy and  how Blue Yonder  implemented this on Azure

Blue Yonder - Multi-Tenancy Azure Environement 

Xpert

Hi,

 

we did not yet run into problems however we have not that much different schemas

  • ~ 250 Schemas
  • ~ 8000 Tables
  • ~ 181k Columns
  • ~ 2000 Users

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.

power_bi_login.gif

 PS: this is a 18 node on-prem cluster with 24 TB of memory