Implemented [SUGGESTION] MySQL Read-Slave, Write-Master

[gwinans]

To assist with scalability, it would benefit XenForo greatly to have the ability to send reads to a secondary server.

Config:

$config['db']['master']['host'] = ...
...
 
$config['db']['slave'][1]['host'] = ...
...
$config['db']['slave'][5]['host'] = ...

This will allow very simplistic horizontal scaling. It can be done as a round-robin system or each slave can host a specific portion of data.

Checks would need to be made to ensure that the slave being read from isn't behind - if it is significantly behind, read from the master or move to the next slave.

 

[r1pe]

Do you experience any problems yet? Do you have a large forum running with xenforo? I'm really interested in performance related infos.
I know that vBulletin has a lot of problems with slow read queries, but xenforo? Since search-function can use sphinx, there should not be any slow-queries.

 

[gwinans]

I occasionally act as the DBA for bukkit.org.

During peak times, a very well-configured MySQL instance will occasionally just about fall over itself trying to keep up with the load.

The idea should be more correctly defined as:

Read+Write Master and one (or more) read-slaves to handle offloading traffic via pseudo load-balancing (round-robin, LRU, randomly chosen). This suggestion can only give benefits. There are no real downsides to it - other than writing the necessary code to handle figuring out which server to hit for data.

 

[r1pe]

True. It has only benifits, except that a master-slave environment is more expensive that a single DB Server + Failover DB server.
Anyway, a good script should support master/slave db connections. You are right. It would be a good feature that Xenforo need to compare with vBulletin's scalability.

 

[gwinans]

True. It has only benifits, except that a master-slave environment is more expensive that a single DB Server + Failover DB server.
Anyway, a good script should support master/slave db connections. You are right. It would be a good feature that Xenforo need to compare with vBulletin's scalability.

I don't understand your logic on expense.

Master-Slave costs the same as DB + Failover, as your failover should be a slave. Might as well use it to offload reads.

 

[digitalpoint]

+1 to this suggestion.

Most forum software that implement master/slave options, usually do so with a 1 master and 1 slave setup in mind... Might be worth checking out the HyperDB class for WordPress for some ideas. It's one of the better implementations I've seen. You can define an infinite number of "writable" servers as well as an infinite number of "read-only" servers. The class handles weighting, failover, etc. all automatically (it's what wordpress.com uses to run it's millions of hosting WordPress blogs).

http://wordpress.org/extend/plugins/hyperdb/

 

[p4guru]

+1 as well

definitely worth having

 

[AlexT]

Deleted

 

[digitalpoint]

I've been thinking about this some more today and what might be the best way to do this without major changes (like flagging individual queries as to them being read or write).

The absolute simplest way would be to just extend the Db class... if the query starts with "SELECT", route it to slave servers, otherwise route it to master. You of course would run into the occasional issue where something was being written with a HTTP redirection afterwards to a page that reads back what was just written and what was being written to the master wasn't replicated to the slave before it was read back on the destination page (it would be very rare that an HTTP round trip could be faster than local DB server replication though).

Then I was thinking about how to keep it simple but a little more advanced... like if a query is being done as a write, and it's to a table other than xf_session, we somehow flag that user to route all DB reads to the master for the next x seconds. Then the problem becomes how to flag that user... you couldn't do it in the session table because it would defeat the whole purpose (you would need to always read the session table from the master).

Maybe rely on the client-side to tell us if it recently did a write (like maybe as simple as a cookie that expires after a few seconds)?

Just trying to think of a way that wouldn't require a major overhaul of the existing system, but get the job done at the end of the day... Anyone have any other ideas?

 

[EasyTarget]

You wouldn't want to base things on just query type. The difficulty with round robin scenarios is that if the user session is shunted between servers. As long as the user connection is kept on the same server they won't / can't see any read / write traps.

If I post message I want to see the results immediately. If I am shifted between two servers, write on one, read on the other and the replication hasn't incurred, I will get a stall or null set. Worst case scenario for the user.

If you only offloaded searches to slave databases the potential damage might be minimal.

The easiest solution would be to have star pattern replication and the current user session locked to one server.

If someone is having delays, just splitting your web server and database server can dramatically improve things.

 

[Floris]

I think we're talking about a situation where people are running vBulletin with 30 million posts, spread over 7 memcached servers, etc. master/slave replication is almost a must to upkeep performance.

 

[digitalpoint]

Yeah, as Floris said, I'm more talking about a site in particular that already uses 4 dedicated web servers and 4 dedicated DB servers and sees millions of unique visitors each day (and growing). Handling thousands of SQL queries per second 24/7...

As far as issues with replication not happening, that is the nature of replication. But the way it works is under normal circumstances stuff is replicated to all slaves in less than 0.1 seconds. The worry about replication not happening is similar to worrying about having a web server or DB server because, "What if it goes down and doesn't work as expected?"

 

[EasyTarget]

1000's of sql queries per second, doesn't really indicate what the workload is. What's the split between read and write queries. Are the write queries normally insert or update? Read queries can be quick or slow depending on the join and group by scenarios. What % is replication cost? i.e. if you have 20% write queries, you are upping your write queries to ~45%.
1000 * 20% = 200
200 * 4 = 800
800 / (1000+800) = 45%
And the problem gets worst with the more servers you add.

With only 4 db's it sounds like you have a star replication pattern, i.e. server A can push to B,C,D and server B can push to A,C,D...etc... Which is good approach for forum software since there is a low chance of collisions where server A and server B are both trying to update a record at the same time. Although you can get insert issues if you try and use similar primary keys. The method to force the replication can make a huge difference, if you are using triggers vs sequenced inserts.

With XF, the simplest implementation is still star replication pattern, using sequenced queries. Each node uses separate key id sequencing so there isn't any chance of collisions on inserts.

The data writer class would have to be extended to allow for something like...
update {servername}.{database}.table_A
set x = 1 ;

repeat for the other servers' databases.

The more servers you add the more cross talk, replication queries that will be added.

WordPress' solution is to have a group of master's and each master only pushes to the other masters and its slaves. It cuts down the cross talk and allows for better scalability.

Although before any of this is necessary, I would think that heavy dbase tuning and partitioning the tables is more appropriate. It should be able to handle most user's needs. It would be nice if XF released an official db-tuning guide for their forum.

 

[digitalpoint]

Our setup is a single master, 3 slaves. Writes are minimal compared to reads. reads are at least 95% of all the queries. Writes are pretty evenly split between updates and inserts.

Actually there is a 4th slave that we use to route reads to that we know will be slow. Any query that takes longer than a fraction of a second is not acceptable for me to allow to run against the primary slaves.

At some point we are going to move to the NDB storage engine, where every node is a valid master for writing. But that's overkill even for us at this point (the setup I'm looking at should be able to handle around 8M SQL reads and 2M SQL writes per second... more info here).

 

[AlexT]

Deleted

 

[digitalpoint]

Yeah, but you are still making the assumption that your replication is failing for some reason. If replication is failing (either because a slave is down, has a broken network link, or just lagging by more than 1 second), you should be doing something to fix the underlying issue (ie. monitoring system should route around lagging/broken slaves, take them out of the cluster, etc.)

In my particular setup (doing thousands of SQL queries I second), I've not had an unplanned failure (or replication lagging of more than 1 second) of a slave that wasn't automatically resolved via monitoring within 10 seconds of the issue starting (and we have been running that way for about 10 years now).

If we are going to go down the "what if" scenarios that could happen with your database server, what if your master DB server fails/goes down under the current 1 DB server scenario. Also would be bad... but not something that really needs to be handled at the application level imo.

 

[AlexT]

Deleted

 

[EasyTarget]

If we are going to go down the "what if" scenarios that could happen with your database server, what if your master DB server fails/goes down under the current 1 DB server scenario. Also would be bad... but not something that really needs to be handled at the application level imo.

Where do you want to handle the scenario? at the database level? auto-promote one of the slaves to master?

 

[digitalpoint]

I agree that it's not the task of the application to handle replication issues. And I applaud you for running a successful non-stop replication farm for so long.

However, IMHO it would be wrong to assume that everyone handles their slaves the same way you do. Replication slaves do not have to be up all the time. Sometimes they are taken down for a purpose; for instance, in a simple 1 master - 1 slave configuration, the slave could be taken shortly offline every day to perform cold backups. If what we're discussing here is a better than simple implementation of a master/slave configuration, it'd be wrong to assume that the slave is always present and/or not lagging for more than one second. Hence my alternative suggestions (and I am sure there're more).

Agreed... but if someone is scheduling downtime for a slave, I would *hope* they don't just blindly take it offline (even if there was a failover mechanism at the application level).

Where do you want to handle the scenario? at the database level? auto-promote one of the slaves to master?

Yeah, it should be handled at the server level imo. If someone has the actual need to go down the road of a multi-DB setup, I think it's fair to say that they should have the knowledge on how to run/manage their DB servers properly.

You could make all sorts of "what if" scenarios of things that really shouldn't be handled at the application level... what if your web server goes down, should the app try to restart it? What if network goes down, should the app try to change the routing tables on the server to try and route around connectivity issues?

Personally I don't think the front-end application should be managing any of that stuff, and it has no business trying to make decisions on what to do if a server fails (DB, web, memcache, etc.)

 

[AlexT]

Deleted