high availability discussion db model - mysql

Question

Could you please share your insight about scalability?

Let's say I have a simple following MySQL/RDBMS db for tree-like discussion:

Tables:

• discussion (id, url)
• comment (id, discussionId, parentCommentId, slug)
• comment_vote (discussionId, commentId, userId, value)

The idea is to perform less-frequent writes (contrary to more-frequent reads) into this RDBMS structure and after write rebuild cache for entire discussion into some read cache (probably document db), where is stored format that can be served without further processing to the client.

• Let's expect 250MB of new data each day or 1000 requests per minute (90% reads).
• In comment_vote we should somehow ensure, that for specific comment, there is at max 1 vote for each user.
• The db is sharded with discussionId key and we have db cluster with arbitrary number of nodes

1./ How far can we go in reality with this layout? I mean, we have only 3 tables here. Is there any obvious bottleneck? Like rebuilding of indexes, some table-level-locking,... on each insert in tables, that should have hundred of gigabites and more?

2./ Will it be more reasonable to use document database also for writes, because e.g. they could handle better physical locking for smaller parts?

3./ Any other ideas / better solutions?

Many thanks.

Answer 1

Well, managing high load is very comprehensive task, so you can try your luck on https://dba.stackexchange.com/ for example

Initial thoughts

1. You can try PostgreSQL as more powerful alternative for MySQL
2. For forum-like records it can be good solution to build PARTITIONING based on DATE values of comments/discussions. Thus you need to add DATE fields - last update for example, last read etc. This values will also help you logic decide whether or not there is a need for archiving
3. If you need to implement fast full-text search MySQL is not the best way