Which of the following shard data duplication options is recommended?

Question

The high-performance mysql book suggests that to outline the blog application, you might want to put the comment data through 2 fragments: firstly, on the fragment of the post about the person’s publication and on the fragment where the message is stored.

Thus, the question arises of how to reliably duplicate this data. Which of the following shard data duplication options is recommended?

Option 1: Make 2 separate insertions from a PHP script.
Pros: a) Logic is at the application level.
Cons: a) The user is held for two inserts. b) This logic should be duplicated for each client trying to insert similar data.
Conclusion: Seems reasonable.

Option 2: Create federated tables and use some trigger to handle duplicate insertion.
Pros: a) at the application level, no need to worry about multiple inserts
Cons: a) Each splinter must have a federated connection with each other splinter; b) The Federation will operate on machines on the local network, but as for two different sites. c) what happens if the connection to the federated server fails.
Conclusion: this does not seem to be a sound idea.

Option 3. Messages such as RabbitMQ
Pros: a) Different clients can insert data in one place, and all subscribers can consume the insert.
Cons: a) complex; b) may impose an overhead charge for hosting the messaging server and clients; c) not sure how he will work with the search service to find suitable fragments
Conclusion: Not Sure

Option 4: your suggestion?

I will be very grateful for your help.

Answer 1

As you point out, having triggers between different shards is silly; the whole reason for the fragments is an independent operation database. Therefore, you can immediately throw it away.

Updating both tables simultaneously is an approach with the least amount of moving parts. In the long run, it will be most convenient. And it will be easiest to debug if something goes wrong.

But if the response time is important, then you might think of some sort of messaging approach: update the table "for comments" and leave a queue to update the table of comments separately. If it takes an hour for this message to be processed, or if it is lost if the system crashes - It's okay, you can always recover. In no case should you use the messaging approach to update both tables.

Answer to this question: @kdgregory Link: https://softwareengineering.stackexchange.com/a/134607/41398