Configuring read-only and write-only DB (for HA & performance)

[Patrick Nelson]

I was researching the possibility of upgrading my SilverStripe website from a single database to a database cluster to help provide high availability and then boost performance (if possible). However, to get the most out of the performance benefits, you need to distribute the majority of your requests across the cluster (i.e. the read-only operations, SELECT) which then requires the ability to segment configuration by read vs write. In this setup, more costly write operations would then be performed on a separate master database when then replicates those writes to slave DB's.

I started reading through the documentation for options to configure the DBAL to allow for separate read-only and write specific operations but at first glance I couldn't find anything in documentation nor in code. Is this a feature that exists at all in 3.x or upcoming 4.x or could possibly already be en queue for future implementation somewhere? 

I'm asking here first since I'm not sure if this exists already somewhere and I'm just missing something. For context, Laravel has a configuration option for this and I think it'd be cool for SS to allow for the ability to automatically direct queries to the proper host/port/etc depending the type of query. 

[Marcus Nyeholt]

Hi Patrick,

I've got a module that does this, with two alternate implementations. It's a little old but should be usable still

https://github.com/nyeholt/silverstripe-splitdb

The subclass method is probably easier to understand, whereas the aspect mechanism is more generic if you're not using MySQL

Cheers,

Marcus

--
You received this message because you are subscribed to the Google Groups "SilverStripe Core Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to silverstripe-dev+unsubscribe@googlegroups.com.
To post to this group, send email to silverstripe-dev@googlegroups.com.
Visit this group at https://groups.google.com/group/silverstripe-dev.
For more options, visit https://groups.google.com/d/optout.

[Patrick Nelson]

Great, thanks Marcus! I've reviewed the code too, well done. It's good to see a relatively compact and simple solution to this issue. I wonder if something like this should stay a separate module or built into core somehow? I know often the answer to custom functionality is to roll it out as a module but considering other frameworks support this out of the box, but this sort of feature would be great (albeit potentially complicated).

I'll admit I'm having a hard time wrapping my head around the code, since the example YAML config uses class names for the Injector that are not consistent with the classes in code, e.g. SplitterWriteDatabase vs ReadWriteMySQLDatabase. Also, why is it passing DB credentials into the constructor (via Injector) when connection parameters like that are actually passed into ->connect()? In my arch, I'd prefer not to place credentials into YAML but consolidate environment-specific config like that into my _ss_environment.php file anyway.

[Patrick Nelson]

Also, interestingly while researching how the module you linked works (discovering SS Aspects for the first time), this SS Aspects documentation page appears to use this very database read/write functionality as an example:

https://docs.silverstripe.org/en/3.1/developer_guides/extending/aspects/ 

Is there documentation explaining this obscure looking percent sign syntax in YAML?  e.g. %$FooBar. I'm only finding examples and no clear documentation anywhere about what it means (at least not after some failed attempts at googling the answer). That pesky percentage sign is very difficult to search for. I can't find any direct explanations on the Injector or configuration documentation pages, either (again only examples that happen to utilize it without explanation). My wild guess is that this works as a sort of variable/placeholder for a finally injected class name (e.g. I might have CustomClass setup to override BuiltinClass and %$BuiltinClass resolves to CustomClass but I'm just guessing here). Is that what it does?! 😖

I think it'd help if that aspect/AOP documentation there actually used class names in the initial explanation that are also demonstrated in the code below. e.g. Makes mention of "MyService" but no example YAML config demonstrating how that ties into AopProxyService. Instead, it uses that esoteric percentage sign syntax, which just makes the material much more dense and difficult to grasp 🙁

[Marcus Nyeholt]

Hi Patrick,

Just on the %$ syntax - it's effectively a property resolution operator, and simply means "lookup the object named %${Name} in the injector and bind it in". 

If you'd prefer to keep the SplitterWriteDatabase in code, you can do so by manually creating the database object and registering it the injector - you'll need to do this in _config.php to make sure it's available by the time queries start being executed. Something along the lines of

$params = [

'server' => FROM_CONSTANT_IN_SS_ENV

'username' => FROM_CONSTANT_IN_SS_ENV

'password' => FROM_CONSTANT_IN_SS_ENV

];

$writeDb = new MySQLDatabase;

$writeDb->connect($params);

Injector::inst()->registerService($writeDb, 'SplitterWriteDatabase');

[Patrick Nelson]

I'm especially curious about how this works, however:

Also, why is it passing DB credentials into the constructor (via Injector) when connection parameters like that are actually passed into ->connect()?

Magic? 

[Marcus Nyeholt]

Ah - that's because it's for an older version where MySQLDatabase::__construct did take connection params in the constructor. That's how old the module is I guess! 

I'm not sure if the current impl of MySQLDatabase has a convenient hook point for using injected connection parameters - it might be that it needs a subclass that exposes that ability.