So I'm working on an app that wraps many of it's SQL queries in a transaction for it's SQL usage and I'm trying to think of some ways to scale the DB layer up....
Up until now we've been throwing hardware at the problem, slowly upgrading our Rackspace cloud servers...but we're reaching the upper echelons of their cloud server offerings.
I'm thinking the standard R/W split won't work for us because the transaction gets stuck to a single server eliminating the benefits of the R/W split. (Is that right?)
I've looked at dbShards and ScaleBase both of which look like interesting solutions to the problem, but as a cash strapped startup we'd prefer to use something OSS or devote some man hours creating a sharding strategy instead.
So I suppose the question boils down to, are Galera/MySQL Cluster and sharding my only two options for scaling this setup?
To answer some more questions...
We manage a great deal of financial and inventory related data, for this reason we wrap most of our SQL traffic in a transaction so it's all or nothing.
We have ~100 tables in our DB with ~30 of them getting a great deal of use. The most commonly used tables have anywhere from from 15MM to 35MM rows.
Our transactions span across 20-30 tables with joins between ~10 of them. The queried tables are rather large and we have made some inroads with respect to query optimization and proper indexing, but no one is a fully fledged DBA in house....closest is me haha
A lot of our DB traffic is machine generated, we do a lot of syncing with third party data sources. As such, most of the SQL queries are wrapped in a transaction to ensure data consistency when performing all the calculations and syncing with the third parties (and to allow for a rollback if the link to the third party gets disrupted)
Based off of our cacti monitoring it looks like a great deal of our SQL traffic is reads (~85%R to ~15%W)
Just as a note, we haven't hit too much of a bottleneck with our current cloud server but I'm doing my due diligence ahead of time to make sure we don't run into the wall without a plan.