Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a table that I'm pretty sure in the very near future (within a year) will reach 14 million rows, and I'm trying to scale out my web app before hitting any bottleneck. I'm in dilemma whether I should use sharding or replication?

What is a good strategy for my web app?

EDIT:

Table structure in which I'll have millions of rows:

    id mediumint(8) unsigned NO  NULL  auto_increment   
    user_id  smallint(5) unsigned NO  NULL  
    jqci_id  mediumint(8) unsigned NO   NULL  
    selected_answer  enum('a','b','c','d')  NO  NULL
share|improve this question
2  
One case against sharding: mysqlperformanceblog.com/2009/08/06/why-you-dont-want-to-shard – KM01 Feb 3 '12 at 14:37
14 million rows isn't huge unless you are doing a lot of full table scans or aggregating large chunks of data. – Aaron Brown Feb 3 '12 at 22:08
@AaronBrown, 14 million rows is for the first year, read the question carefully, the next year it'll reach 28 million and so on. – john.locke Feb 4 '12 at 3:13
You have a couple years to find out if it's going to actually be a problem. What is the table structure? What kinds of queries are you performing? You have provided no context except for the number of rows, which doesn't mean much. what is the bottleneck that you are trying to solve for? – Aaron Brown Feb 4 '12 at 3:43
@AaronBrown I hope the edit part sheds some light on the question. – john.locke Feb 4 '12 at 17:07

3 Answers

up vote 1 down vote

Mysql replication won't address performance issues on large tables. It just allows you to have another copy you might use for failover, backup or reporting in cases of heavy hitting queries.

Your table schema is tiny. Even with 14 million rows you could fit that in ram on even modest hardware.

All the same, if you want to look at sharding you could read up on mysql partitioning

You say you expect 14 million rows; you're dangerously close to the max value for your medium PK there. Just go head and make that an int instead of worrying about the extra byte/entry.

share|improve this answer
thanks for the tip, I don't know why did I choose medium int!! – john.locke Feb 5 '12 at 3:56
You can use mysql replication to forward all the reads to the slaves and reduce server load, why not? – john.locke Feb 5 '12 at 3:57
Yeah, you can offload read work to a slave. I guess I've generally just done that for more heavy lifting reporting queries and keeping live application reads on the master. Reason being we don't want to read stale data if the slave has fallen behind or introduce an unneeded point of failure for the applications (e.g. replication breaking) – atxdba Feb 5 '12 at 4:13
up vote 0 down vote

If I understand the question, I presume that your growing table needs all 14 million current rows. If not, why not archive what is not needed during non-peak times?

share|improve this answer
up vote 0 down vote

Since your table definition involves a grain of fact (the selected_answer column), why not add a datetime column, then utilize MySQL table partitioning to horizontally shard your data? Explained here. Then you can use EXPLAIN to examine your queries to make sure they match up with your indexing strategy.

share|improve this answer
You mean partitioning the table for example by year or by month, is that what you say? – john.locke May 5 '12 at 12:42
Of course this would be specialized for your case, but in a nutshell, yes. Year,month,week,day, whatever your strategy is. – randy melder May 6 '12 at 17:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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