Take the 2-minute tour ×

I have a MySql table which consists of:

  1. ~25million rows (CURRENTLY)
  2. 3 indexes
  3. Each day, a crawler adds ~3million rows
  4. I'm currently not looking too far, but a final estimate of the db can be ~CONST*e9 rows
  5. Currently 9.5giga
  6. innodb and it is being read from while inserting

The data itself consists of a text of ~100 chars + several fields with meta data about it. The indexes are the unique id, the writer name, and the writer id.

Till now, everything went smooth, but now the server is having a hard time handling the inserts of the new data (~10seconds for each insert which adds ~3k lines). I'm trying to find ways to overcome this issue. Things I consider:

  1. Doing the index while inserting takes effort. Maybe not doing it while inserting, and only after X inserts adding the indexes.
  2. Partitioning the data into different tables.
  3. Crawling into a small db, and each X minutes/days, moving the data into the big db.
  4. Moving to a different db. I'm not enough acquainted with NoSql, will that help me resolve these problems? Is it a big effort to use it?

Each option has its sub-options and dilemmas, but I think I should firstly focus on having a direction. Which route should I take and why? Is there a different road I should think of?

BTW - There is also an option to not keep all the data, and only the parts I really display, but that will make it impossible to do some functional changes in the process that data is going through before being displayed.

share|improve this question
What does the data look like? And the indexes? –  z5h Jun 20 '11 at 18:00
Updated the question with answers to your questions. –  Noam Jun 20 '11 at 18:04
innodb or myisam? Is the table being used for read queries while you are inserting? –  ggiroux Jun 20 '11 at 18:46
@ggiroux updated question:"innodb and it is being read from while inserting" –  Noam Jun 20 '11 at 19:17
How much memory does the server have, and how much is mysql allowed to use? On a DB that size, I'd check into whether the various caches are overflowing such that MySQL has to hit the disk far more frequently, which is a massive slowdown (disk is many orders of magnitude slower than ram). –  Marc B Jun 20 '11 at 19:20
add comment

2 Answers

is the current engine optimal for the usage?

Have you concidered http://dev.mysql.com/doc/refman/5.1/en/partitioning-management.html

share|improve this answer
I haven't. I assume that fits inside options number 2. Will that be enough considering my numbers? –  Noam Jun 20 '11 at 18:09
What are the other needs and uses of the data and how intensive? –  Imre L Jun 22 '11 at 17:09
This data is being read from to db with each visit to a web-site. This is the main data that the site is built upon –  Noam Jun 23 '11 at 16:07
Have you tried partitioning? Did it actually help? Any pointers for the process? –  Noam Jun 23 '11 at 16:31
add comment

If you're adding 3,000,000 rows a day, and 3000 rows takes a 10 second transaction, you're talking about 1,000 transactions a day, which should take about 170 minutes a day. That's really not that much.

I think I'd first try

  1. reducing the number of INSERT transactions by inserting more rows per transaction
  2. tuning the server

You might find that inserting more rows per transaction actually takes less overall time. And if not, it's easy to revert. If you stash the rows somewhere else first, you can run the INSERT transactions during times of low load.

Tuning the server is probably a good idea regardless. For reference, see the MySQL docs on Tuning Server Parameters.

share|improve this answer
Can you elaborate on "tuning the server"? –  Noam Jun 21 '11 at 11:32
I added a link to "Tuning Server Parameters". –  Mike Sherrill 'Cat Recall' Jun 21 '11 at 13:35
add comment

Your Answer


