Database Read/write splitting

[tcoker]

I have a client who runs OD with a MariaDB Galera cluster and ProxySQL as a database proxy layer. It is not optimally configured for high availability. I was told that in the past they tried to do read/write splitting through ProxySQL in order to leverage the read capabilities of the cluster but that Open Dental is not compatible with read-write splitting. The client wants to take another look at their proxy setup to see how we can improve it and make it more HA.

I didn't find any information on the topic when I searched this forum and the web. Has anyone had experience trying load balance an OD Galera cluster with read/write splitting and ProxySQL or another proxy such as MariaDB MaxScale or HAProxy?

[jsalmon]

I have only heard of problems when splitting database reads and writes. It would be a hefty Feature Request to overhaul the framework of Open Dental to support that paradigm.

We have been slowly exposing specific parts of our software (e.g. reports) to read-only servers (e.g. one-way replication) for operations that tend to be slower and only need to perform read operations: Report Setup: Report Server and Server Connections - General

[tcoker]

Thanks for the quick reply.

So if the proxy classifies queries before sending them to the cluster and decides to route a SELECT to one of the non-writing cluster nodes, that operation would be incompatible with Open Dental? I understand that at the application level but it seems to me that if the proxy is properly set up then it should be transparent to the application.

[jsalmon]

It will only work if the proxy can give you a 100% money-back guarantee that the non-writing server it chose for you will have the record that was just written. So far, it hasn't worked like that. You'll get instances of Open Dental that will insert a row, immediately attempt to select that row back out from the database and it won't be there. The program will crash or misbehave.

[tcoker]

Thanks @jsalmon, a couple more questions:

When the Open Dental application inserts a row and then immediately selects it, does that operation occur within the same database connection as the insert or does OD run that select using a separate connection?

Does Open Dental use prepared statements?

Does each database query sent by Open Dental run in an explicitly declared transaction (e.g. START TRANSACTION...COMMIT)?

[jsalmon]

When the Open Dental application inserts a row and then immediately selects it, does that operation occur within the same database connection as the insert or does OD run that select using a separate connection?

That decision is left up to the MySQL connector. The connection is often reused but a new one will be spun up if the connection is busy executing another query.

Does Open Dental use prepared statements?

No.

Does each database query sent by Open Dental run in an explicitly declared transaction (e.g. START TRANSACTION...COMMIT)?

No.

[tcoker]

Thanks!