MySQL Forums
Forum List  »  Partitioning

Re: Partitioning or indexing for millions of records
Posted by: Rick James
Date: November 30, 2013 01:30PM

> 20% of 4GB RAM(3.1GB for use)

Use:
key_buffer_size = 600M
(G = giga- = billion = 1e9; M = million = 1e6)
After changing my.ini, restart mysqld.

> every 15 minute you are going to insert 30,000 records
> batch insert statement

That is the best approach.

> INSERTs and SELECTs block each other.

To minimize blockage, use batches of 100-1000 records at a time. (Do not do 30K at once -- too much blockage, and very little speedup for the INSERT.)

> And yes we are going to purge old data also.

How long will you keep the data? Design the PARTITIONs as discussed in my link;
1 month: ~33 daily partitions
1 quarter: ~15 weekly partitions
1 year: ~14 monthly partitions
(etc).

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Partitioning or indexing for millions of records
1486
November 30, 2013 01:30PM


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.