Let’s say we have 100 MySQL servers deployed in 50 x 2-node clusters. The nodes in each cluster listen on the same port. Cluster1 consists of Node1A and Node1B, both listening on port 5001. Custer2 consists of Node2A and Node2B, both listening on port 5002, etc.
ProxySQL should listen on ports 5001, 5002, 5003, etc. When a connection comes in to port 5001, it should route the request to Node1A or Node1B. When a connection comes in to port 5002, it should route the request to Node2A or Node2B. And so on.
I don’t see how to set this up with ProxySQL. It looks like it is only designed to provide 1 front-end listener.
No issue listening, but you will have issues with the rules to support all of those ports. You would need at least 1 rule per port.
You might be better off having 1 proxysql per “cluster”, listening on 1 port with 1 or 2 rules for routing. Or might need to shard proxysql, for example proxysql1 ports 5001-5100, proxysql2 ports 5101-5200, etc
We can set up ProxySQL without rules based only on the host groups, but if we want read/write splitting or sharding (or anything else) we need rules.
…I’m thinking we can probably get away with using no rules at all for most of the back-end instances. We don’t typically need to shard or split, or rewrite queries. There may be exceptional cases, but they would be few. In most cases, we just want to proxy based on host groups. Hopefully ProxySQL can do that? I’m only familiar with products such as Nginx, HAProxy, and MySQL Router, none of which require an expensive rule for each back-end service.
I’m excited about the performance possibilities of ProxySQL and I’d hate to hamstring it with a bunch of rules right off the starting block. Would rule-less proxying work in our case?
You can only get away without rules if using different mysql users. Users that you add to ProxySQL have a default_hostgroup parameter. For example, userFoo’s default hostgroup is 11, which has node11A and node11B. ProxySQL would split all traffic 50/50 (reads and writes) between those two nodes for any connections with userFoo on any incoming port to proxysql.
If you want to do true incoming port → backend mapping, you’ll have to use rules.
I also must question your use of 2-node clusters. This very bad as the loss of either node takes the entire cluster offline (split-brain). Or are you using the word “cluster” to mean “source-replica replication”? If the later, then you don’t ever want writes going to a reader so you’d have to use 2 rules per replication group to ensure that writes only go to the source.
It would be helpful to have a better understanding of what you are trying to accomplish and why you feel using port-based mapping is the way to go.
That’s actually what we want. We have 500 databases. Each database is associated with a different customer, is hosted on a different logical DB cluster, and has different MySQL user accounts. So using different user accounts to “point” to different clusters seems like it could work, as long as it does not create a performance problem for ProxySQL trying to keep track of so many users. Each database has 5 user accounts, so there would be 2500 records in ProxySQL’s mysql_users table.
My description was overly simplistic. There are two data-bearing nodes and a quorum node. They are 3-node clusters, but the third node is just a tie-breaker. The other two nodes operate in active-standby mode. The MySQL service only listens on the active node. If the active node fails, then the standby becomes the new active and starts the MySQL service. The underlying storage is DRBD synchronous block-level replication, which does not permit split brain. From ProxySQL’s perspective, I guess one node of each pair would always be considered offline or shunned until a cluster fail-over occurs, then the roles would switch.
Wow! That’s very old tech. Nobody recommends that anymore for MySQL HA. You essentially always have 1 server which is offline, unable to be used. If you use more modern approaches like replication, or actual clustering, you can take advantage of the standby and use it for application queries.
Ok. So forget about the port mapping. Have ProxySQL listen on normal 3306 and all applications connect to a single standard port. Then proxysql manages mapping user → hostgroup. 2500 users might be a lot for proxysql.
DRBD excellent stuff and quite current. It’s similar to the replication technology that’s built into Nutanix and is at the leading edge of SDS. We looked hard at Galera/PXC, MySQL Group Replication, etc., and we understand why it is attractive to some people, especially when you have access to the application code and can develop with your clustering solution in mind. However, you don’t have that luxury when you’re running a proprietary canned app from some publisher, and there are certain functionalities that do not play well with those types of replication. Write performance suffers, deadlocks can occur, certain data integrity features are problematic, etc. We like DRBD because (a) it operates below the filesystem driver, so applications do not have to be cluster aware, (b) it is shared nothing, and (c) it prevents split brain, so sledgehammer resets are not necessary to prevent data corruption.
You make some good points on your DRBD use-case. Still, the first time I’ve heard of anyone using it in about 7 years (which I why I called it old tech).
If you don’t need any of the high-availability features and advanced rules that proxysql provides, why are you considering it over simpler solutions like HAProxy? Also, you have all this infrastructure and just 1 proxysql server? Doesn’t that create a SPOF? Seems like you would need around 4 proxysql servers behind a load-balancer (F5 or haproxy) which would balance the app traffic across the proxysql’s which would be directing traffic to mysql based on user.
Mainly because we were impressed with HAProxy’s performance metrics. We spent weeks bench-marking our new DB servers using different storage stacks, trying to make sure we didn’t leave IOPS on the table. It would be a pity to hamstring the servers with an under-performing proxy solution. We’re still somewhat hopeful that we can make use of ProxySQL, but in the meantime we have indeed fallen back upon HAProxy.
We have clustered Nginx (F5) reverse proxies in the DMZ and HAProxy running on a Corosync/Pacemaker cluster on the internal network. We may also install it locally, directly on the app servers.We’re still in the R&D phase. The database servers each have 4 x 25 Gbit NICs in 2 x bonded pairs, connected to 4 separate switches, which are spanned across 2 geographically separate data centers over 4 fully diverse 100 Gbit fiber paths. We’re as careful about SPOFs as we can afford to be.
Hi Team
i have a question regarding proxysql, i have 40 frontend application server each communicate to 40 database servers (say 3 application server can communicate to 1 database server as well) using single database user, i need to configure proxysql for this, the thing is we cannot create multiple same user to assign hostgroup id’s in proxysql, can some one suggest me how I can handle all the application and database servers using single proxysql user.
