I've got a MySql table featuring its:
- ~25million rows (Presently)
- 3 indexes
- Every day, a crawler adds ~3million rows
- I am presently not searching too much, but your final estimate from the db could be ~CONST*e9 rows
- Presently 9.5giga
- innodb which is being read from while placing
The information itself includes a text of ~100 chars + several fields with meta data about this. The indexes would be the unique id, the author title, and also the author id.
Till now, everything went smooth, however the server is getting a difficult time handling the card inserts from the new data (~10seconds for every place which adds ~3k lines). I am looking for methods to overcome this problem. A Few Things I consider:
- Doing the index while placing takes effort. Not doing the work while placing, and just after X card inserts adding the indexes.
- Partitioning the information into different tables.
- Moving right into a small db, and every X minutes/days, moving the information in to the large db.
- Moving to another db. I am insufficient familiar with NoSql, will which help me resolve these complaints? Could it be a large effort to make use of it?
Each option has its own sub-options and problems, however i think I ought to first of all concentrate on getting a direction. Which route must i take and why? It is possible to different road I ought to think about?
BTW - There's also a choice not to keep all of the data, and just the various components I truly display, but that can make it out of the question some functional changes along the way that information is dealing with prior to being displayed.
may be the current engine optimal for that usage?
Perhaps you have concidered http://dev.mysql.com/doc/refman/5.1/en/partitioning-management.html
If you are adding 3,000,000 rows each day, and 3000 rows requires a 10 second transaction, you are speaking about 1,000 transactions each day, that ought to take about 170 minutes each day. That's not really much.
I believe I'd try
- reducing the amount of Place transactions by placing more rows per transaction
- tuning the server
You will probably find that placing more rows per transaction really takes less overall time. And when not, it's not hard to revert. Should you stash the rows elsewhere first, you are able to run the Place transactions throughout occasions of low load.
Tuning the server is most likely advisable regardless. For reference, begin to see the MySQL paperwork on Tuning Server Parameters.