[MXS-3255] Send calls to READS SQL DATA procedures to slaves Created: 2020-10-22 Updated: 2023-12-15 |
|
| Status: | Open |
| Project: | MariaDB MaxScale |
| Component/s: | None |
| Affects Version/s: | None |
| Fix Version/s: | Icebox |
| Type: | New Feature | Priority: | Major |
| Reporter: | Hartmut Holzgraefe | Assignee: | Joe Cotellese |
| Resolution: | Unresolved | Votes: | 0 |
| Labels: | None | ||
| Description |
|
Right now all procedure CALL statements are sent to the current master, as maxscale can't tell whether the procedure body would change any data or not. So load balancing of procedure calls can't happen (unless enforcing slave side execution somewhat manually, e.g. with "run on slave" hint filter hints) A procedure declared as READS SQL DATA is known to only read, not write, though. So when taking such procedure metadata into account, Maxscale could send calls to such procedures to read-only slaves. |
| Comments |
| Comment by markus makela [ 2023-09-27 ] |
|
Ideally the MariaDB server would enforce the read-onlyness of the stored procedure call. Currently the READS SQL DATA is only a suggestion:
If the server is able to somehow express that the stored procedure is guaranteed to not modify data, readwritesplit could treat it as a read. Injecting a SET SESSION TRANSACTION READ ONLY would also work as it prevents writes from being done. If the server responds with a read-only error, it could be retried and treated as a write. Another alternative is to detect if the server where the query is to be routed has read-only enabled and rely on the global read_only to prevent accidental writes. Since the mariadbmon can enforce that replicating servers are read-only, this seems like the simpler approach. |