You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
We want to introduce support for application environments that use multiple databases for performance objectives, by developing support for the ORM to offload read-only queries to an alternate database to allow developers or vendors with platforms/infrastructure for Silverstripe CMS to alleviate load on a primary write database and improve scalability and performance of the overall database system
Take a look at how this is typically implemented in other ORMs and how it's used. There are likely unknown-unknowns about how this needs to function in order to be truly useful
We need to decide whether to allow explicitly using the primary DB for read operations for specific scenarios even when there's a read-only db available (e.g. if you immediately need to fetch the value you just wrote to the DB)
Other ORMs that allow read-only replicas actually allow an arbitrary number of replicas, not just the one. For example see Doctrine's replicas config and their old master/slave setup. We should probably aim to do the same, for maximum flexibility and scalability.
Will probably look at raw SQL and if it's a SELECT always use a read-replica, otherwise use the primary. However this means that any unsynced data probably won't be there. One solution could be to have a DB::withPrimary($callback) to ensure the primary database is used.
Laravel has a sticky option that means use the write database for all queries going forward in the current request cycle after a write operation has been performed. This solves the issue of needing to read something after writing straight away. It may make sense for us to do something similar, possibly just have it permanently on and not allow configuring it off as there's probably a million ways something could get out of sync and break. There may however be some scenarios where the database is being written to on every request e.g. custom logging which would disable using the replicas
Acceptance criteria
At least one additional database can be optionally configured via environment variables for read-only operations
When a read-only database is available, the primary database is only used for write operations (Steve: I'm not sure about this one, it may make sense to do something like the /admin section and cli always uses the primary, while the website frontend should use a replica though switches to the primary when needed e.g. userform submission)
The implications of when data is available are fully understood - i.e. if writing to one db and then reading from another, should developers expect the new data to be available immediately or not?
Documentation clearly outlines both how to set this up, and any gotchas that may arise.
This will probably involve an API break and require it be in CMS 6, though ideally we can implement this in CMS 5 so that the real world benefits are realised sooner
We want to introduce support for application environments that use multiple databases for performance objectives, by developing support for the ORM to offload read-only queries to an alternate database to allow developers or vendors with platforms/infrastructure for Silverstripe CMS to alleviate load on a primary write database and improve scalability and performance of the overall database system
Related issues
Notes
replicas
config and their old master/slave setup. We should probably aim to do the same, for maximum flexibility and scalability.write
database for all queries going forward in the current request cycle after a write operation has been performed. This solves the issue of needing to read something after writing straight away. It may make sense for us to do something similar, possibly just have it permanently on and not allow configuring it off as there's probably a million ways something could get out of sync and break. There may however be some scenarios where the database is being written to on every request e.g. custom logging which would disable using the replicasAcceptance criteria
Kitchen sink CI
^ failures are existing
PRs
The text was updated successfully, but these errors were encountered: