Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a MyISAM table partitioned by range with 90 partitions representing the last 90 days. I expect this table to receive somewhere between 500 million to 1 billion records before i start rotating partitions (dropping old ones and creating new ones).

What server configuration options would you put in your .my.conf file to ensure that the fastest possible insertion into this table was possible at all times?

Any other tips would also be appreciated.

EDIT: By 'fastest possible' i mean best possible insert speed and being sustained once it contains 100's of millions of rows.

share|improve this question

closed as off-topic by Chris S Jul 26 '13 at 15:13

  • This question does not appear to be about professional server, networking, or related infrastructure administration within the scope defined in the help center.
If this question can be reworded to fit the rules in the help center, please edit the question.

    
Do you have any other requirements such as being able to SELECT, UPDATE and DELETE from these tables? Have you already decided on the storage engine and if so, which one did you choose? –  Ladadadada Jul 21 '13 at 15:06
    
Only select is necessary (but that is not the problem) and the storage engine is mentioned in the question. ta. –  Gary Willoughby Jul 21 '13 at 15:33
    
Not sure how I missed that. With MyISAM, whenever a SELECT is running, an INSERT will have to wait until that is finished before it can start. Indexes will be critical in ensuring that your SELECTs finish quickly. The query cache may also help depending on the read/write ratio. All of these have a negative performance impact on INSERTs and would be turned off if you did not need SELECTs. You might want to spend some time with this question. –  Ladadadada Jul 21 '13 at 16:12
    
Is this a data warehousing use case? –  HTTP500 Jul 23 '13 at 22:21
    
No, just a collection of logs access via a thin client. –  Gary Willoughby Jul 23 '13 at 22:31

2 Answers 2

When ur thinking about faster insert , MYISAM is a good choice , but retrieval will be difficult because entire table will be locked . If you plan for retrieval , MYISAM will not be good option , then we need go for Innodb because it supports row level locking . If your thinking about partition , create your partition based on your business logic. Try to eliminate all related keys apart from partition index

If we go for MYISAM , you should rethink about select queries

share|improve this answer
    
MySQL 5.6.6 implements partition lock pruning, which eliminates unneeded locks in many cases. –  Gary Willoughby Jul 26 '13 at 17:44

I guess your application logs data into the database, in one table. To get highest speedup you have to change your data (table) definition. You have to eliminate every index, auto increment, key or unique.

Better you refactor your application. Split your writes. First write into a log file, this is really fast. Second, use a second process that reads your data from log file and writes it into you database. You should split these new two application on different server.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.