MySQL Load Balancing

2
votes

We have 6 Servers (4 Applications servers and 2 DB Servers)

We are using HAProxy to load balance between the Application and API servers (2/2)

Now the issue I'm having is that the system administrator setup a Master/Slave on the MySQL but it's always failing and until now we cannot use the slave since most data are always corrupted and we always need to fix it and each time we are getting different errors .

We tried to make some sort of load balancing for the read/write (write on master , read on slave) but we were not able to use that since slave data are not always correct .

What I'm wondering is how the big guys proceed when dealing with high load servers where you always need the data to be accurate and cannot take any risk?

Can someone tell me his own experience and what he used ?

What i found : Percona XtraDB Cluster , but before going into this direction need input ...

Thank's !!

3
mysqlload-balancinghaproxymaster-slavedatabase-cluster
if application data model is simple you can consider database sharding but be careful while doing this, sometimes it is more problematic because you need to handle all these shards in your business logic. – Rams
"it's always failing and until now we cannot use the slave since most data are always corrupted" If that is the case, then it is being set up incorrectly. MySQL replication works correctly when set up correctly. – Michael - sqlbot
@Michael-sqlbot I agree but we are always getting errors , like Error 'The table 'xxxxx' is full' on query. Default database: 'xxxxx'. Query: 'ALTER TABLE xxxxx ADD INDEX IX_fill_empty_xxx_cron (type_id, e_id, model_id) so until we are 100% the slave wont crash we can't use that thing – Tarek
You can quite safely issue the query SHOW SLAVE STATUS; as often as needed, from an automated system to proactively monitor replication and catch problems faster. This example suggests that the slave doesn't have enough disk space. As a rule, the slave server needs the same or more or everything the master has -- disk size, I/O performance, CPU core speed (though fewer CPU cores may be okay) -- and running the same or slightly newer version of MySQL itself. I suspect getting to the bottom of the underlying issues will ultimately be better for you than changing strategy. – Michael - sqlbot
@Michael-sqlbot they have the exact same spec. , i think it's a messed up configuration thats causing the issue . – Tarek

3 Answers

3
votes

You can choose MySQL/MariaDB+ Percona + HAproxy. This combination support Master- Master synchronization and Data sync work really well. The most of the Real-time Data synchronization has Issue with primary and foreign Key. You can avoid those issue too using Percona. Go ahead and Good Luck

2
votes

The "table is full" error means your slave doesn't have enough space to perform the ALTER TABLE. You need to get larger disks to resolve that error.

But the subtext is that no one is monitoring your database servers, and that's a bigger problem. You need to get a database administrator, or else get a professional service to do it.

What I'm wondering is how the big guys proceed when dealing with high load servers where you always need the data to be accurate and cannot take any risk?

First, get it out of your head that any system has no risk. That's impossible, if you plan to use the system at all. You can't eliminate the possibility of errors, but you can be prepared to recover from them seamlessly.

The big guys do the following:

  1. Hire operations staff including system administrators, network administrators, database administrators to take care of the servers.

  2. Monitor everything. Use software to track system load, disk space, errors, and many other things continuously. The best option is New Relic. For MySQL slave integrity, use a tool like pt-table-checksum.

  3. Redundancy. Create standby systems and data to take over when (not if) the primary system fails.

You probably want to learn about the field of high availability architecture. Check out this talk: Scalable Internet Architectures

1
votes

Get on amazon ec2. You can launch 4 app server along with 2 db servers on the fly and set up load balancing using aws engineering features.

http://docs.aws.amazon.com/ElasticLoadBalancing/latest/DeveloperGuide/elb-getting-started.html#define-load-balancer

https://aws.amazon.com/articles/1639