configure connection string for read-only round robin scale out operation for MariaDB

You do this via DNS.

You setup round robin DNS for your MariaDB host name, then DNS implements round robin access for you.

Aside: For this to work as expected, requires you have all your MariaDB instances running Multi Master Replication.

Normally you will also run lsynd or csync2 to sync up your files too... if you're running a CMS like WordPress or any other system where you have related PHP (or other) files changing, which are related to your database changes.

Hi,

Take a look at this blog.

Regards,
    Tomas Helgi

David Favor,

"requires you have all your MariaDB instances running Multi Master Replication."

mulit master mean write master?

I only need read-only scale out , why related to write
?

"Normally you will also run lsynd or csync2 to sync up your files too..."

see very complex and please explan why a much detail. I only want, e.g. one master and  4 x replica, I only want a connection string for an application to connect to replica 3-4 for a round robin read only operation. is it possible ?

please also help on this:

https://www.experts-exchange.com/questions/29145341/DML-and-DDL-operation-during-MariaDB-failing-over.html?headerLink=workspace_open_questions

You asked, "multi-master mean write master?"

Yes.

Because if you're actually doing round robin access, every instance must support writes as well as reads.

This becomes complex across several tasks... These come to mind... Let's say you're running WordPress or another CMS.

1) People modifying content, are best pinned to one instance during all their updates.

Solution: They can edit their local /etc/hosts + add an entry pointing to a single IP during their sessions.

2) User logins. If you have a user login facility, say running a membership or LMS (learning management system) site, then visitors will login one instance, then after the TTL period expires in their local DNS cache, they will be accessing a completely different instance.

Solution: Session management data must also live in same database, so sessions persist/work across all instances.

Tip: If this is your first time working with this type of system, best hire someone familiar with multi-master design.

Hint: Rarely is multi-instance site design required. Far better to just optimize a single instance so it can operate quickly enough to handle all traffic.

Note: It's very rare you can actually accomplish running any multi instance system like this using some sort of connection string hacking.

Simple won't work.

Best if you hire someone familiar with this type of design + go through your entire site data flow, end-to-end, before any design is done.

This will ensure you actually have a working system which can grow into your future requirements.

"Because if you're actually doing round robin access, every instance must support writes as well as reads."

why is a MUST ? but you are saying we need DNS for it, that's why ? DNS do not know if it is a write or a read operation.

"Tip: If this is your first time working with this type of system, best hire someone familiar with multi-master design."

no need, I just dont' understand what you meant by that! MaraiDB do not understand even once their proxy, maxscale, setup, MariaDB cluster still not in multi master node. But they said it is ! Once I test using their method, it is NOT! and they can't explain further.

we need a method to make sure that once multi master cause data conflict on the SAME data triple, where diff data goes !

MS SQL, oracle and DB2 will has a method for it, but for open source DB, it relies on application tier to DETECT it. I am more concern on data conflict.

"Hint: Rarely is multi-instance site design required. Far better to just optimize a single instance so it can operate quickly enough to handle all traffic."

you mean single write master is enough ?

"Note: It's very rare you can actually accomplish running any multi instance system like this using some sort of connection string hacking."

what I need is multi read only node so that I can load balance the read operation for reporting as reporting is one of the major daily operation user will feel slow and keep complaining.

MS SQL always on has this so I'd like to know how we configure mariaDB for this .

I misread your original question.

For read only, multi-instance systems, your setup will be...

1) One master (writes) + many slaves (reads).

2) You'll arrange for all writes to target the master.

3) You'll arrange for all reads to target either only the slaves or the master + all slaves.

4) To accomplish #3, you'll require some sort of proxy system running which is somewhat App aware, to distribute all incoming requests to the correct location - master or slave or both.

Looking through current online setup guides for WordPress I don't find any that are complete. All seem to leave out crucial steps.

Best to search for your App (like WordPress or whatever) + replication, both inside EE + elsewhere, to look for an existing guide targeting your specific App.

I'll continue looking + if I find one which is complete for WordPress I'll post the link.

The reason I suggest looking for a WordPress guide is that WordPress uses session data which must also live inside the replicated database, to support the case when a person does a login + maintains a long session where they eventually rotate across the slave + all masters over a long period of time.

Session management is the real killer here. Simple approach is to look at how replicated WordPress setup work + pull out whatever parts you require.

If you have no session management to handle, then just pick any replication setup guide to use.

https://www.digitalocean.com/community/tutorials/how-to-optimize-wordpress-performance-with-mysql-replication-on-ubuntu-14-04 provides a simple, complete WordPress example.

In this case, the HyperDB plugin is used to route all writes to the masters at the DB level, so IP simply rotate with no consideration as to which is master + which are slaves.

https://pantheon.io/docs/hyperdb/ provides a bit more clarity about actual HyperDB config.

please help on this :

https://www.experts-exchange.com/questions/29145341/DML-and-DDL-operation-during-MariaDB-failing-over.html?headerLink=workspace_open_questions

"3) You'll arrange for all reads to target either only the slaves or the master + all slaves."

I focus on how to arrange all read to OTHER target slave(s).

in MS SQL, what we need to tell application is a connection string, once application connect to MS SQL using that string, MS SQL will reroute that connection for multi nodes for READ-ONLY operation.

MSQL != MariaDB.

Best to open another question regarding MSSQL, as MSSQL experts may provide MSSQL specific info.

"MSQL != MariaDB."

yeah.

but what am I referring to is if MS SQL can do it , then how MariaDB handle it ?

the link you shown me,  use the HyperDB plugin to load balance read only ,right ? So is that mean by default MySQL can't ? from my understanding, with ProxySQL and HA proxy, it should be able to ?

1) but what am I referring to is if MS SQL can do it , then how MariaDB handle it ?

Round Robin DNS + either multi-master or master-slave with App layer logic like HyperDB.

2) the link you shown me,  use the HyperDB plugin to load balance read only ,right ?

Load balancing is always a site effect when running a multi-instance site.

The primary reason for using HyperDB is to run at App layer where sessions live, so reads + writes can be delivered to correct instance in a master-slave config. Using a multi-master config there's no requirement for App layer HyperDB type logic.

3) So is that mean by default MySQL can't ? from my understanding, with ProxySQL and HA proxy, it should be able to ?

Maybe. Maybe not.

This depends on the underlying App.

If sessions are in place, many logins which must persist longer than the DNS TTL, so every page transition/refresh may hit another instance... then the answer is no, you can't user a simple system like HAProxy for a master-slave config.

For a multi-master config, there'd be no reason to use ProxySQL or HAProxy.

Tip: Keep in mind any time you use any type of proxy, your throughput will suffer.

Last time I tested HAProxy, using HTTPS forwarding cut throughput by 50% + using TCP forwarding cut throughput by around 25%.

Be sure to factor in this consideration into your equation.

I haven't done load testing with HyperDB + my guess, since HyperDB runs at the App level, that throughput will be much slower than HAProxy.

Tip: Fastest setup will be to use a multi-master config, with App session data stored in same replicated database.

"Round Robin DNS + either multi-master or master-slave with App layer logic like HyperDB."

in MS SQL, it is just 2 x T- SQL query with an additional connection string to determine the routing.

"This depends on the underlying App."

so you mean database server still can't control it and must relies on application on top of the DB?

"Tip: Keep in mind any time you use any type of proxy, your throughput will suffer"

tks. but is that mean proxy is the bottleneck in that case ?

"Tip: Fastest setup will be to use a multi-master config, with App session data stored in same replicated database"

this means one more database has to setup to store the current routing configuration and each time user login , has to query that DB first to get a list of server the read only request should route to ?

"If sessions are in place, many logins which must persist longer than the DNS TTL, so every page transition/refresh may hit another instance... then the answer is no, you can't user a simple system like HAProxy for a master-slave config."

sorry a bit over simple for this description, please clarify a bit more.

any update for me ?