# PXC load balancing preference and raid question **URL:** \ **Category:** Percona XtraDB Cluster 8.x\ **Created:** [February 1, 2025, 9:03am UTC](https://forums.percona.com/t/pxc-load-balancing-preference-and-raid-question/36468 "2025-02-01T09:03:18Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![xirtam](https://avatars.discourse-cdn.com/v4/letter/x/82dd89/32.png) [@xirtam](https://forums.percona.com/u/xirtam)\ **Post date:** [February 1, 2025, 9:03am UTC](https://forums.percona.com/t/pxc-load-balancing-preference-and-raid-question/36468/1 "2025-02-01T09:03:18Z")
Hello, I’m about to setup a 3 node PXC and wanted to ask 3 quick questions. 1. Does PXC has any built-in balancer? I read about haproxy or sqlproxy everywhere, but I’m wondering if there is a simple balancing integrated in it. I don’t need advanced options. 2. If not, what is the current preferred balancer between the 2? Less overhead, faster connection, etc. Considering 99% of the queries I have are SELECTS. 3. Considering I have 2 identical nvme on each node, does it make sense to configure them in raid0 for speed and more storage? They are in raid1 at the moment, but since I will have 3 replicas thanks to PXC, does it still make sense to have a raid1 for data replication on each node? Is there any problem in running a PXC on raid0? Thanks ---
**Author:** ![anil.joshi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/anil.joshi/32/13100_2.png) [@anil.joshi](https://forums.percona.com/u/anil.joshi)\ **Post date:** [February 1, 2025, 2:51pm UTC](https://forums.percona.com/t/pxc-load-balancing-preference-and-raid-question/36468/2 "2025-02-01T14:51:21Z")
@xirtam Thanks for reaching out to us. Let me provide the response > Does PXC has any built-in balancer? I read about haproxy or sqlproxy everywhere, but I’m wondering if there is a simple balancing integrated in it. I don’t need advanced options. > > If not, what is the current preferred balancer between the 2? Less overhead, faster connection, etc. Considering 99% of the queries I have are SELECTS. Unfortunately PXC doesn’t come with any inbuilt LB however you can opt some popular choices (ProxySQL, Haproxy) ProxySQL operates in layer 7 so can understand SQL conversation better while Haproxy is a simple layer 4 proxy however looking over your requirement where you just reading 99% of your workload haproxy mignt be a good/light solution. Well ProxySQL has an additional advantage that it transparently handles the Read/Writes over a single TCP port while on Haproxy you have to handle read/writes over separate TCP ports. Both the Proxies provide the auto-failover solution also based on some internal/external health check scripts. Further, you can read more about both of them as below. > **[ProxySQL - A High Performance Open Source MySQL Proxy](https://proxysql.com/)** > > ProxySQL is a MySQL protocol proxy supporting Amazon Aurora, RDS, ClickHouse, Galera, Group Replication, MariaDB Server, NDB, Percona Server and more... vs > **[HAProxy - The Reliable, High Perf. TCP/HTTP Load Balancer](https://www.haproxy.org/)** > > Reliable, High Performance TCP/HTTP Load Balancer Moreover, for a simple reading purpose a async replication set also would be goof fit if you can accept some replication lag. Let me also add some setup/config docs. **PXC/ProxySQL** > **[Percona XtraDB Cluster - Load balancing with ProxySQL](https://docs.percona.com/percona-xtradb-cluster/5.7/howtos/proxysql.html)** > > ProxySQL is a high-performance SQL proxy. ProxySQL runs as a daemon watched by > a monitoring process. The process monitors the daemon and restarts it in case > of a crash to minimize downtime. **PXC/Haproxy:** > **[Percona XtraDB Cluster - Load balancing with HAProxy](https://docs.percona.com/percona-xtradb-cluster/8.0/haproxy.html#install)** > > The free and open source software, HAProxy, provides a high-availability load balancer and reverse proxy for TCP and HTTP-based applications. HAProxy can distribute requests across multiple servers, ensuring optimal performance and security. > Considering I have 2 identical nvme on each node, does it make sense to configure them in raid0 for speed and more storage? They are in raid1 at the moment, but since I will have 3 replicas thanks to PXC, does it still make sense to have a raid1 for data replication on each node? Is there any problem in running a PXC on raid0? Well its a choice between performance and redundancy. PXC has an advantage that it supports a true synchronous commits so possability of data loss is less. Still RAID 1 provides local redundancy at the node level but can introduce some amount of latency/performance trade-offs at the cost of durability. Since you are not doing much writes so RAID 0 should be fine from performance perspective as far as other nodes are available and syncing data. Rest, you can consider your business requirement and cost while taking decisions. ---
**Author:** ![xirtam](https://avatars.discourse-cdn.com/v4/letter/x/82dd89/32.png) [@xirtam](https://forums.percona.com/u/xirtam)\ **Post date:** [February 1, 2025, 3:09pm UTC](https://forums.percona.com/t/pxc-load-balancing-preference-and-raid-question/36468/3 "2025-02-01T15:09:58Z")
> [@anil.joshi](#): > > Well its a choice between performance and redundancy. > > PXC has an advantage that it supports a true synchronous commits so possability of data loss is less. Still RAID 1 provides local redundancy at the node level but can introduce some amount of latency/performance trade-offs at the cost of durability. > > Since you are not doing much writes so RAID 0 should be fine from performance perspective as far as other nodes are available and syncing data. Thanks for your reply. I already read the whole docs, those links included. Not doing much writes compared to reads, but still lots of writes in absolute numbers. I’m also going to setup, in addition to PXC, a full regular backups of all DBs using XtraBackup (1 full per day and incremental every 1 or 2hours) on an external network disk. Considering all this, I think that using RAID1 is probably overkill. I know you can NEVER be 100% sure, but PXC + manual replicated backups should already cover most of the disaster. Well, except meteorites 😃 So do you think I can safely go for RAID0 then? Also my question was implying another one, and it is: Do I have to keep attention to some specific my.cnf or PXC config value when using PXC or a DB server in RAID0? Considering I’m using only InnoDB tables. Any value I should strictly set for this setup other than the ones already suggested in the PXC Docs? Thanks again. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [February 3, 2025, 4:18am UTC](https://forums.percona.com/t/pxc-load-balancing-preference-and-raid-question/36468/4 "2025-02-03T04:18:28Z")
Hi @xirtam > [@xirtam](#): > > a full regular backups of all DBs using XtraBackup (1 full per day and incremental every 1 or 2hours) The daily fulls I agree with. Incrementals every 2 hours is a bit much. Consider how annoying this will be when you need to restore. The process is ‘restore full’, then “foreach incremental: apply incremental”. That could be up to 11 incrementals you need to apply. If, as you say, you don’t have lots of writes, consider using the built-in incremental system known as binary logging. Simply rsync your binary logs somewhere safe every 5m. When recovery time happens, restore the full, then replay the binlogs. > [@xirtam](#): > > So do you think I can safely go for RAID0 then? If you don’t have many writes, why is drive speed such a concern? Stick to standard RAID1 or RAID5 and run things like normal. The whole point of a cluster is that if one server fails, there’s at least 2 others ready to take over. > [@xirtam](#): > > Do I have to keep attention to some specific my.cnf or PXC config value when using PXC or a DB server in RAID0? No, there isn’t anything specific for RAID0. You can set `innodb_flush_log_at_trx_commit=2` to reduce disk fsync from ‘every commit’ (=1, default), to ‘every 1 second’ (=2). ---
**Author:** ![xirtam](https://avatars.discourse-cdn.com/v4/letter/x/82dd89/32.png) [@xirtam](https://forums.percona.com/u/xirtam)\ **Post date:** [February 3, 2025, 7:00am UTC](https://forums.percona.com/t/pxc-load-balancing-preference-and-raid-question/36468/5 "2025-02-03T07:00:08Z")
> [@matthewb](#): > > If you don’t have many writes, why is drive speed such a concern? Stick to standard RAID1 or RAID5 and run things like normal. The whole point of a cluster is that if one server fails, there’s at least 2 others ready to take over. Because I have heavy readings from multiple websites. I need speed for SELECTS not for writes. I’m actually using PXC with a balancer for this in the first place, not for security. Is this a wrong way to obtain faster readings? Should I just split the reading (websites) to different not clustered single DB Servers? > [@matthewb](#): > > Simply rsync your binary logs somewhere safe every 5m. When recovery time happens, restore the full, then replay the binlogs. I also need to be able to restore the single DB or table. Should I just switch to mydumper for that? AFAIK there is no logical backup with incremental system though, and having to backup the whole thing every X hours needs lots of space. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [February 3, 2025, 4:40pm UTC](https://forums.percona.com/t/pxc-load-balancing-preference-and-raid-question/36468/6 "2025-02-03T16:40:35Z")
> [@xirtam](#): > > Because I have heavy readings from multiple websites. I need speed for SELECTS not for writes. Speed for SELECTs does not come from disks, it comes from RAM/CPU. 99% of your reads should be coming from InnoDB’s buffer pool; that’s why the pool should be set to 80-90% of your system memory. Fast disks only move data into RAM fast. Once in memory, the disks are never touched for reads. Optimizing your storage for SELECTs won’t gain you anything. > [@xirtam](#): > > I’m actually using PXC with a balancer for this in the first place, not for security. Is this a wrong way to obtain faster readings? What you are doing is correct: Load balancing your SELECT traffic across multiple PXC nodes is the correct way to achieve higher read capacity. If you implement ProxySQL as your load balancer, you can have ProxySQL cache certain queries to speed things up even more. Or implement your own query cache using Valkey, or memcached. It only takes a few lines of code to implement such a cache in any language. > [@xirtam](#): > > Should I just split the reading (websites) to different not clustered single DB Servers? The benefit of clustering is having instant high-availability. If you switch to single servers, what’s your HA plan? > [@xirtam](#): > > I also need to be able to restore the single DB or table. Should I just switch to mydumper for that? Absolutely. Single DB, or single table restore with physical backups is an absolute nightmare. mydumper gives you the ability to do single-table restore with just a couple commands. > [@xirtam](#): > > AFAIK there is no logical backup with incremental system though Not true. Do some research on the binary logs, and understand what they record. Take a logical mydumper backup at 2am. A simple cron job saves the binary logs every 5m. Now, it’s 1pm and you need to restore database FOO. Use the mydumper backup from 2am and restore database FOO. Then use mysqlbinlog to replay all changes from 2am to 1pm for database FOO. The binary logs don’t let you filter per-table, only per-database. ---
**Author:** ![xirtam](https://avatars.discourse-cdn.com/v4/letter/x/82dd89/32.png) [@xirtam](https://forums.percona.com/u/xirtam)\ **Post date:** [February 3, 2025, 5:01pm UTC](https://forums.percona.com/t/pxc-load-balancing-preference-and-raid-question/36468/7 "2025-02-03T17:01:47Z")
> [@matthewb](#): > > The benefit of clustering is having instant high-availability. If you switch to single servers, what’s your HA plan? That’s not a problem. Every single server is mirrored both in raid and in a different location as a full logical+xtra backup. If a disaster happens I can turn a new DB server up within minutes. But since my main concern here is speed, not reliability, I wonder if having (for example) 12 websites on a 3 node PXC would be faster than having 4 website on 3 single DB servers. > [@matthewb](#): > > Speed for SELECTs does not come from disks, it comes from RAM/CPU. 99% of your reads should be coming from InnoDB’s buffer pool; that’s why the pool should be set to 80-90% of your system memory. Fast disks only move data into RAM fast. Once in memory, the disks are never touched for reads. Optimizing your storage for SELECTs won’t gain you anything. Well, my experience is different. My buffer pool is bigger than DB usage, still for some big and badly written queries the query time is high. Sometimes there are bad queries on CMS that can’t be changed ot that can’t use indexes because they nest SELECT WHERE IN (SELECT…). So I’m looking for a way to reduce the CPU stress on the DB server (at the moment a single one) when those happens. Either splitting to 3 single instances or using a cluster. I’m wondering what’s the best way to go right now. The specs of the DB servers are high, we are talking about EPYC 9454P 96core/256GB/NVMe boxes. > [@matthewb](#): > > The binary logs don’t let you filter per-table, only per-database. That’s my problem. Most of my restores are tables based like customer asking to restore a single table when they do stupid things. So my only chance is to stick with a daily or twice day mydumper cron I guess. ---
**Author:** ![anil.joshi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/anil.joshi/32/13100_2.png) [@anil.joshi](https://forums.percona.com/u/anil.joshi)\ **Post date:** [March 14, 2025, 5:53pm UTC](https://forums.percona.com/t/pxc-load-balancing-preference-and-raid-question/36468/8 "2025-03-14T17:53:47Z")
@xirtam > That’s not a problem. Every single server is mirrored both in raid and in a different location as a full logical+xtra backup. If a disaster happens I can turn a new DB server up within minutes. > But since my main concern here is speed, not reliability, I wonder if having (for example) 12 websites on a 3 node PXC would be faster than having 4 website on 3 single DB servers. By 3 single DB servers i assume you talking about async replication. If this is correct, it has its own challenges in the form of replication lag and missing a proper HA solution. On the other hand ,the PXC would more resilient and also no such replication delay concerns due to synchronous replication. As my colleague Matthew previously mentioned you can try ProxySQL caching - [Query Cache - ProxySQL](https://proxysql.com/documentation/query-cache/) if it serves your purpose or moreover you can also explore the other ways to cache workload using DB/Engines like **Valkey** or **memcached**. > Well, my experience is different. My buffer pool is bigger than DB usage, still for some big and badly written queries the query time is high. Sometimes there are bad queries on CMS that can’t be changed ot that can’t use indexes because they nest SELECT WHERE IN (SELECT…). So I’m looking for a way to reduce the CPU stress on the DB server (at the moment a single one) when those happens. Either splitting to 3 single instances or using a cluster. I’m wondering what’s the best way to go right now. The specs of the DB servers are high, we are talking about EPYC 9454P 96core/256GB/NVMe boxes. Yes, even after a way high allocated resources if still the workload is not optimized gaining peformance would be challenging. I can understand changing the queries would be hard due to dynamic nature of CMS tool. Here the thing is you already using LB(ProxySQL) to split your Read/Write so the workload is still spread over multiple nodes. Rest, you can explore the external caching as i mentioned above. Well for some reporting/Adhoc purpose you can also use a separate async node (which can be added in any existing PXC node) and spread some heavy modules there if it acceptable to your use case or business requirement. This will avoid any direct impact on the Main setup due to any un-optimized workload or CMS queries. > That’s my problem. Most of my restores are tables based like customer asking to restore a single table when they do stupid things. So my only chance is to stick with a daily or twice day mydumper cron I guess. Isn’t like that you can’t restore a single table with xtrabackup (physical backup). You can do by using the steps mentioned in the manual - [Restore individual tables - Percona XtraBackup](https://docs.percona.com/percona-xtrabackup/8.0/restore-individual-tables.html) but yes a logical one like mydumper would be more convinient. ---
**Author:** ![xirtam](https://avatars.discourse-cdn.com/v4/letter/x/82dd89/32.png) [@xirtam](https://forums.percona.com/u/xirtam)\ **Post date:** [March 14, 2025, 6:03pm UTC](https://forums.percona.com/t/pxc-load-balancing-preference-and-raid-question/36468/9 "2025-03-14T18:03:00Z")
> [@anil.joshi](#): > > By 3 single DB servers i assume you talking about async replication. If this is correct, it has its own challenges in the form of replication lag and missing a proper HA solution. No I’m talking about 3 single separate instances, 3 different DB servers. Anyway for now I’m stuck, becouse proxySQL don’t support our backup solution (JetBackup). It uses mysqldump and some queries/SET statements are not compatible apparently (getting server has gone away errors even with 1GB max\_packet\_size which is the highest for proxysql). I already openend bug reports on both projects, but seems it’a a long thing to fix. HAproxy is not an options, because I need to split queries based on mysql users. At the moment I’m evaluating what to do, I’ll probably go to 3 single separate instances if I can’t get Jetbackup to work with proxySQL. I’m still testing some things hoping to make it work, but I don’t have many hopes. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [March 14, 2025, 6:25pm UTC](https://forums.percona.com/t/pxc-load-balancing-preference-and-raid-question/36468/10 "2025-03-14T18:25:24Z")
> [@xirtam](#): > > get Jetbackup to work with proxySQL 99.99999% of ProxySQL users **do not** run their backup through proxysql. Backups should always be executed **directly** at the server. Also, I **strongly recommend** that you switch to [mydumper](https://github.com/mydumper/mydumper) as mydumper is multi-threaded (mysqldump is not) and provides file-per-table backup dumps. Your backups will finish faster with more restore flexibility. ---
**Author:** ![xirtam](https://avatars.discourse-cdn.com/v4/letter/x/82dd89/32.png) [@xirtam](https://forums.percona.com/u/xirtam)\ **Post date:** [March 14, 2025, 6:31pm UTC](https://forums.percona.com/t/pxc-load-balancing-preference-and-raid-question/36468/11 "2025-03-14T18:31:17Z")
I don’t use mysqldump directly. I use JetBackup, which is a third party solution which uses mysqldump. I have to use this because I’m using Plesk and I need to provide customers a frontend to manage backups. Thats why it goes trough proxysql as it takes the DB server from plesk user accounts. I already asked a feature request to specify the direct IP of each server for backup. But it requires time to develop on their end.