-1

I have a system which performs many actions frequently and am looking at splitting databases up a bit to spread out the load and speed up the system hopefully.

The fear is that by splitting up these databases is that transactions may become out of sync.

My question is, if I split up my databases. Can I still perform transactions on say 3 to 6 databases at the same time? Meaning, if there's some sort of err, roll them all back at the same time?

Edit: Am looking at splitting up the databases since there's certain areas that will be called frequently but are unrelated to each other.

Edit 2: By database I mean a separate machine. Which I’m now seeing instance is probably the word I should use.

Improve this question
4
  • 1
    Care to mention what kind of "databases" you have in mind? I'd hate to suggest reading about XA and CICS if you're working with MySQL.
    – mustaccio
    Oct 11 at 1:55
  • Knowing your database system and version would be helpful here, please update your post's tags accordingly. Also, not sure how splitting your database into more databases would help performance, that'd be a new one to me, but again this would depend on what system you're using.
    – J.D.
    Oct 11 at 3:38
  • "Database" is a word that gets used ambiguously. Do you mean separate schemas on the same MySQL instance? Or do you mean separate MySQL instances?
    – Bill Karwin
    Oct 11 at 12:56
  • @BillKarwin Ah right. I was thinking to separate the machines entirely so they could operate independently of each other. As most operations do not need to know what each other is doing while doing the operation…..but there are other operations which will need to ping all of these machines. I suppose that’s still better since you can ping all of the machines simultaneously
    – Trevor Wood
    Oct 11 at 23:10
1

If you mean one instance of MySQL (on a single server, obviously), then the split into multiple CREATE DATABASE ... has no impact (favorable or unfavorable) on performance, nor on transactions, just a minor impact on syntax of queries.

If you mean "sharding", wherein separate servers hold different parts of the data -- such as by subsets of "users", then transactions won't work.

Instead, research "XA" as was way to do transactions across multiple instances.

If you need to do a SELECT to fetch rows from each of several shards, see MariaDB's Spider Engine. Or you can manually create multiple connections, gather the data from each server, then merge the info together. (This is not a "transaction" in the technical "ACID" sense.)

Improve this answer
1
  • I added some more.
    – Rick James
    Oct 12 at 0:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.