Primary/Secondary choosing querybuilder

[AndyScherzinger]

In case of write operations mark table as dirty and if read request comes in and table is in a "primary-enforced reads after writes list", so primary-forced reads only happen if write happen and table is configured in the static list where this behavior needs to happen.

[CarlSchwan]

We could use something like this https://github.com/nalgoo/cluster-connection to determine to which nodes to send the DB query

[juliusknorr]

https://github.com/doctrine/dbal/blob/45941c67dd0505dab2fb970786d93055b7cbd2b6/src/Connections/PrimaryReadReplicaConnection.php could be useful

• This might only help for performance in cases where the db proxy currently distributes all reads/writes to the master
• Could still be useful to prevent deadlocks or read after write inconsistency
• Can help to track casual reads by logging such cases

[juliusknorr]

• Figure out a test setup for a cluster through docker: wip: Add mariadb primary/secondary cluster nextcloud-docker-dev#241
• Try wrapping dbal PrimaryReadReplicaConnection into our connection handling
• To discuss: Do we need separate handling of core/app code
• To discuss: Do we need a way for apps to manually decide to pick primary/replica

[mickenordin]

I think this would be very useful to get high availability with the use of proxysql or similar software proxies in between nextcloud and the database cluser. If I can help out in any way, I will. I have many Galera clusters where Nextcloud is running that I can test stuff out on.

[juliusknorr]

Thanks for the offer @mickenordin

A first draft to make use of replica connections is in #41998, though I have not dived into how we could perform the primary enforcement on a per table level yet. Currently doctrine DBAL handles this on a per connection level.