Mysql – Which of the following data duplication options across shards is recommended

MySQL

High performance mysql book suggests that for sharding a blog application, one may want to put comments data across 2 shards: first, on the shard of a person posting comment, and on the shard where the post is stored.

So this raises the question how to reliably duplicate this data. Which of the following data duplication options across shards is recommended?

Option 1: Make 2 separate inserts from the PHP script.
Pros: a) Logic is in application layer.
Cons: a) User is held for 2 inserts. b) This logic will need to be duplicated in every client trying to insert similar data.
Conclusion: Seems reasonable.

Option 2: Form federated tables and use some trigger to handle the insert of duplicate.
Pros: a) App layer doesn't need to worry about multiple inserts
Cons: a) Every shard need to have federated connection to every other shard; b) Federation will work on machines in LAN, but what about at 2 different sites. c) what if connection to federated server fails.
Conclusion: Doesn't seem like a sound idea.

Option 3: Messaging such as RabbitMQ
Pros: a) Different clients can insert data at one place, and all subscribers can consume the insert.
Cons: a) Complex; b) may impose overhead in order to host messaging server, and clients; c) not sure how will it work with a look-up service to locate appropriate shards
Conclusion: Not sure

Option 4: your suggestion?

I will greatly appreciate your help.

Best Answer

As you point out, having triggers between the various shards is silly; the whole reason for sharding is independent database operations. So you can throw it out right away.

Updating both tables at the same time is the approach with the fewest moving parts. Over the long term, it will be the most maintainable. And it will be the easiest to debug if something goes wrong.

But if response time is important, then you might think of some sort of messaging approach: update the comments-by-entry table, and queue a message to update the comments-by-user table. If it takes an hour for that message to be processed -- or if it gets lost in a system crash -- no big deal, you can always recover. By no means should you use a messaging approach to update both tables.

Answer by: @kdgregory Link: https://softwareengineering.stackexchange.com/a/134607/41398

Related Solutions

Mysql – Which database system can break me free

There's a lot to consider. I'm going to narrow this down to a single problem and offer one solution, although there's many approaches.

Problem: You need AD-HOC queries on lots of rows.

First, setup a server to handle writes and a replicated slave (running some flavor of MySQL 5.5.x) to handle reads. My personal preference is Percona Server. Your mileage may vary.

Once you have replication caught up, ALTER your slave table to: engine=InnoDB, ROW_FORMAT=COMPRESSED, partition by date, and index the data to optimize your query WHERE clauses. Each of these have an optimization for your case and there is further tuning required.

Finally, setup a schedule to archive/shard the data when it falls out of scope. Say 2-3 years. Depends on your SLA for providing back reporting. You can always union it if you have to.

MySQL 5.6 will have Memcached built in which will allow you to store pre-processed results. Map/Reduce is great, as long as it's not the first run of the reduce function. NoSQL is great as long as the data is already processed. Neither is really your problem.

Additions 2012-06-13: Since it appears my recommendations have stirred another response, I'll submit my reasons:

Why InnoDB in this case? With the constant insertions of many rows, you can use InnoDB to avoid implicit read locks at the table level.
Why COMPRESSED + Barracuda? "The compression means less data is transferred between disk and memory, and takes up less space in memory. The benefits are amplified for tables with secondary indexes, because index data is compressed also."
Why partition by date? Reduce scan breadth.
Why index properly? Decrease search times by reducing cpu bottleneck through elimination of full table scans.

Mysql – How to group the data in the following format

To display the given format, you can use the following queries instead:

For MySQL, use the following:

  SELECT QuaterName, COUNT(OrderID) FROM
    (
        SELECT QUARTER(OrderDate) QuaterName, OrderID from bab_orders
    ) T1 group by QuaterName;

for Oracle use the following:

SELECT QuaterName, COUNT(OrderID) FROM
(
    SELECT TO_CHAR(TO_DATE(OrderDate, 'dd.mm.yyyy'), 'Q') 
              QuaterName, OrderID from bab_orders
) T1 group by QuaterName;

to show always the Quarters even if you don't have a data, execute the following query:

SELECT QRTR, COUNT(OrderID) TheCount FROM
(
  SELECT QUARTER('2014-01-01')  QRTR  
  union
  SELECT QUARTER('2014-04-01')  QRTR
  union
  SELECT QUARTER('2014-08-01')  QRTR
  union
  SELECT QUARTER('2014-12-01')  QRTR
)  QRTR_T
LEFT JOIN 
(
   SELECT QUARTER(OrderDate) Quater, OrderID from bab_orders
) ORDERS
on Quater = QRTR
group by QRTR

Best Answer

Related Solutions

Mysql – Which database system can break me free

Mysql – How to group the data in the following format

Related Question