MySQL Forums
Forum List  »  Partitioning

Re: Partitioning or indexing for millions of records
Posted by: Rick James
Date: November 27, 2013 12:39PM

There are a lot of factors.

How do you need to access records? One at a time? Or summaries over a period of time? Please provide sample SELECTs.

Will you be purging 'old' data? If so, after how long?

How many GB will the table eventually become?

Is the schema normalized?

Please SHOW CREATE TABLE (at least as it currently stands).

> heard indexing can hurt insertion

You have a "Fact" table. The more indexes you have on it, the slower the INSERTs will be.

> which is better indexing or partitioning

They serve different, but overlapping, purposes. I need more information to finish answering this question.

> What other factors(cache or buffering) i should use.

For MyISAM, set key_buffer_size to about 20% of _available_ RAM.

More reading:
Partitioning advice:
http://mysql.rjweb.org/doc.php/ricksrots#partitioning
If you will be purging old data:
http://mysql.rjweb.org/doc.php/partitionmaint
A discussion of Data Warehousing, which is sounds like you have:
http://mysql.rjweb.org/doc.php/datawarehouse

At 20M rows, possibly everything is cached in RAM, hence fast. After a year, you will have 700M rows, and it will probably no longer fit in RAM. Each INDEX will need updating on each INSERT. Some INDEXes are easily cached (eg, AUTO_INCREMENT, or time-based); others are not (UUID/GUID/MD5 are especially terrible due to randomness). For example, if you have an indexed UUID field, the INSERTs will slow down because of needing to hit the disk for every insert. Ordinary disks can handle about 100 hits per second = 9M per day. So, you should not be in trouble with 60M/month.

On the other hand, if your INSERTs come in bursts, random indexes would slow down each burst.

With MyISAM (as opposed to InnoDB), INSERTs and SELECTs block each other.

Provide more information; I will provide more insight.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Partitioning or indexing for millions of records
2250
November 27, 2013 12:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.