Databases, pools, migration

What this is about

In MULTI every tenant has its own database. Each database comes with three things that CDMS manages:

the database itself, named after the tenant key,
its schema, that is the tables of the models,
a connection pool. That is a supply of open connections to the database that requests borrow instead of building a new one every time.

In addition there is one object per target that Hibernate needs to work: the EntityManagerFactory. It knows the models of the target and is built on first access. Pool and schema come into being with it.

The life cycle of a persistence target

1

CDMS
first access to tenant acme since startup, or CIAS provisions acme
2

CDMS→Database
asks the database server: does the database acme exist?
3

CDMS→Database
if it is missing and creation is approved → CREATE DATABASE acme
4

CDMS
builds the connection pool for acme
5

CDMS→Database
brings the schema up to date
6

CDMS
builds the EntityManagerFactory with the tenant and user models

Result: From now on all requests of acme use pool and factory until they are evicted.

When the database is created

There are two ways that lead to the same result:

Provisioning up front or on first access

At provisioningOn first accessSINGLE

When: CIAS runs in the same application as CDMS and creates the tenant.

1

CIAS
creates the tenant acme
2

CIAS→CDMS
asks for acme to be provisioned
3

CDMS→Database
creates database and schema as described above

Result: If this fails, operations learn about it when the customer is created, not the customer on their first request. Creation can safely be repeated.

When: CIAS runs as a separate service, or provisioning was skipped.

1

Client→CDMS
first request for acme
2

CDMS→Database
creates database and schema as described above

Result: The first request takes longer. If creation fails, this request gets the error.

When: CODAMAI_PERSISTENCE_TENANT_MODE=SINGLE

There is only the one database. Provisioning per tenant has nothing to do and reports success right away.

Result: The one database must exist, or it is created at startup as described above.

What CDMS does when it needs the database

Database server reachable	Database exists	Creation approved	Result
yes	yes	–	database is used
yes	no	yes	database is created and used
yes	no	no	500 `CDMS_TENANT_DATASOURCE_NOT_FOUND`
no	–	–	503 `CDMS_TENANT_DATASOURCE_UNAVAILABLE`, no creation attempt

The approval is CODAMAI_PERSISTENCE_DATABASE_AUTO_CREATE_TENANT_DATABASE=true. Without it, operations create every tenant database themselves. An unreachable server never counts as “database missing”. Otherwise a short outage would trigger a creation.

CDMS can create databases automatically on MySQL, with character set utf8mb4. Right before the CREATE DATABASE, CDMS checks the name once more: only lowercase letters, digits and hyphens.

Migrating the schema

Migrating means adapting the tables of a database to the state of the models, for example adding a new column. Each database is migrated separately, namely when CDMS builds its EntityManagerFactory. After every startup that happens on the first access to the tenant.

Two kinds of migration

HIBERNATE

default

Hibernate compares models and tables and adds what is missing
adds tables and columns, deletes and renames nothing
no versions, no log in the database
CODAMAI_PERSISTENCE_DATABASE_AUTO_UPDATE=false switches adding off; Hibernate then only checks

FLYWAY

versioned scripts

SQL scripts with a version number, shipped inside the application
separate folders for the system database and the tenant databases
every database keeps its own log, so it only gets the missing scripts
afterwards Hibernate only checks whether tables and models match

Which kind applies is set in CDMS_DATABASE_MIGRATION_MODE (HIBERNATE or FLYWAY). By default the scripts are in db/migration/system and db/migration/tenant.

The system database and the tenant databases know different models: the system database only system models, a tenant database only tenant and user models. Only the revisions table that the history needs exists in every database.

Connection pools

Every target has its own pool. With a hundred tenants that makes a hundred pools. So that together they do not overload the database server, in MULTI they keep no connections open while idle.

Setting	Default	Meaning
`CODAMAI_PERSISTENCE_DATABASE_POOL_SIZE`	20	at most this many connections per pool
`CODAMAI_PERSISTENCE_DATABASE_POOL_MIN_IDLE`	derived	connections that stay open while idle: 0 in `MULTI`, as many as the pool size in `SINGLE`
`CODAMAI_PERSISTENCE_DATABASE_POOL_IDLE_TIMEOUT_SECONDS`	600	after this many seconds without use a surplus connection is closed

A request usually borrows one connection per database it touches and returns it at the end of the request.

Evicting unused tenants

CDMS does not keep factory and pool ready for every tenant permanently. It evicts them when they have not been needed for a while:

Setting	Default	Meaning
`CODAMAI_PERSISTENCE_DATABASE_FACTORY_CACHE_SIZE`	100	at most this many tenants ready at the same time; beyond that the one unused for the longest is evicted
`CODAMAI_PERSISTENCE_DATABASE_FACTORY_CACHE_IDLE_SECONDS`	1800	after this many seconds without access a tenant is evicted; 0 switches this off

Evicted means: new requests rebuild factory and pool, including the migration check. Running requests finish undisturbed. Closing only happens when the last of them is done. The system database and the database of SINGLE are never evicted.

Pitfalls

Where to go next

When which database is chosen: Which database? The persistence target
A new tenant across both modules: A new tenant, end to end
How the schemas of both modules fit together: Databases and schemas