MySQL Sharding tables/servers or other methods to reduce table sizes? I'm currently working with a table exceeding 12 million rows (at about 3 GB when exported with mysqldump) and am curious how huge a table can realistically get without any serious performance impact. The table is growing about 100,000-200,000 rows a day or so.

Should I be starting to think a little about sharding this data across multiple tables or mysql instances now before the data gets much larger? Currently the server this is running on has 1 GB of ram (though it'll soon be moving to a machine with 3/4 GB).

Has anyone got any tips/recommended reading that would push me in the right direction, or is this something I don't need to worry about yet?

Thanks :)
A good RDBMS can grow to accommodate extremely large data. 3Gb databases are very manageable, and very probably, as long as you can get a server with enough RAM, most queries will run pretty fast with little effort.

Even when you exceed RAM, indexes, caching and partitioning let you still perform well. Very often, applications access most a relatively small working set- for instance, 90% queries might be limited to last month's data- while the 10% can be queries over older data. "Last month's" data tends to be somewhat stable- it grows when you have more users, but other than that, it doesn't tend to grow with time. This "working set" often fits in RAM, gets cached and you still get great performance.

But then, you can again get slowness. With proper monitoring and analysis, you can locate the queries which are running slow and take steps to solve them.

This is often simple:


Queries or incorrect code: often we write queries that retrieve information which is unneeded, or which execute several queries when one would suffice and be faster (the typical case is an operation which needs to display n rows of a table and performs n queries when 1 would be enough. Performing aggregates outside the database is also frequent). This is easily fixed by changing your code
Queries which do not run efficiently. EXPLAIN is your friend here. Often, creating indexes that the query can use is enough (roughly, you'll want to index on columns which appear in the WHERE clause). Also, sometimes tweaking the query itself will yield good results


Another approach which gives good results is throwing hardware at the problem:


Buying more RAM
Getting more and better disks (RAID10 setups get faster if you add more disks, SSDs often have significant benefits, etc.)
It is not frequent, but sometimes CPU is your bottleneck- you can get faster processors and more cores/processors


In some other cases, replication and sharding might be a problem. Replication is complicated, but stuff such as Oracle RAC let's you build monster clusters (at a price). Sharding is another option, but it's often one of the most complex to implement- even applications which shard easily require lots of work to be sharded, and some applications can be notoriously hard to shard.